@Mention Autocomplete in Fields

ORACLE APEX

How I Added @Mention Autocomplete in Oracle APEX Fields

Type @ in a field, pick a customer from a list, and the name or email is filled in for you. Just like Slack, but inside your own APEX page.

Live Demo

Tribute.js AJAX Callback apex_json Textarea highlight Mention chips

I wanted one simple thing in my Oracle APEX app. When I type @ in a text box, a small list of customers should open, like in Slack or WhatsApp. I pick one, and it goes into the text.

I am still learning APEX, so I thought this would take one hour. It took me almost two days. I got the dropdown wrong, then the email field wrong, then the colors wrong. In this blog I will show you what I tried, what failed, and the final code that works for me.

1What I built

My page number is 20, so all my items start with P20_. I made two fields:

💬

Comments

A textarea. Type @, pick a customer, and @Full Name is added.

📧

Email Address

A text field. Type @, pick a customer, and the email is added.

🧡

Orange mentions

While typing, every mention turns bold orange so it looks real.

🏷️

Side panel

A panel on the right shows all mentions as small chips with a counter.

2My table

I used a simple table called CUSTOMERS. You can use any table, just change the column names in the SQL later.

Column	Type	What it stores
CUSTOMER_ID	NUMBER	Auto number, the primary key
FULL_NAME	VARCHAR2(255)	What this customer is called
EMAIL_ADDRESS	VARCHAR2(255)	The email the person uses for the account

3Create the page items

Open Page 20 in Page Designer. Right click your region and choose Create Page Item. Make these two items:

• P20_COMMENTS with type Textarea
• P20_EMAIL_ADDRESS with type Text Field

The names must be exactly like this. I will use them in the JavaScript, and one wrong letter will break everything.

4Add the Tribute.js files

Tribute.js is a small library that shows the list when you type a trigger letter. It does not need jQuery.

I tried this first, but it didn't work

I downloaded the files and uploaded them to Static Application Files. Then I wrote the wrong path in the page. The browser could not find the file and nothing happened, with no error on the page itself. I only saw it in the Console after pressing F12.

Finally this approach worked

I used the CDN links instead. Click the page name at the top of the Rendering tree. Paste the first link in JavaScript, File URLs and the second link in CSS, File URLs.

JavaScript File URL

https://cdn.jsdelivr.net/npm/tributejs@5.1.3/dist/tribute.min.js

CSS File URL

https://cdn.jsdelivr.net/npm/tributejs@5.1.3/dist/tribute.css

Then in CSS, Inline I added these two lines. Without the first line my dropdown went behind the page region and I could not see it.

CSS

.tribute-container { z-index: 99999; }
.tribute-container li small { color: #888; margin-left: 6px; }

5Get the customers with an AJAX Callback

This was the part I did not understand at the start. JavaScript runs in the browser, so it cannot run SQL by itself. It needs to ask the database for data.

I tried this first, but it didn't work

I tried to put my SQL query inside the JavaScript code. Of course the browser does not understand SQL. Then I tried loading all customers into one big list when the page opens. It worked with a few rows, but it is a bad idea if the table grows to thousands of rows.

Finally this approach worked

An AJAX Callback. The JavaScript sends what I typed after the @, the callback runs the SQL, and it sends back only 10 matching rows as JSON.

Go to the Processing tab, right click Ajax Callback and create one. Name it GET_CUSTOMERS and paste this code.

PL/SQL

DECLARE
  l_search VARCHAR2(255) := apex_application.g_x01;
BEGIN
  apex_json.open_array;

  FOR r IN (
    SELECT customer_id, full_name, email_address
      FROM customers
     WHERE l_search IS NULL
        OR UPPER(full_name)     LIKE '%' || UPPER(l_search) || '%'
        OR UPPER(email_address) LIKE '%' || UPPER(l_search) || '%'
     ORDER BY full_name
     FETCH FIRST 10 ROWS ONLY
  ) LOOP
    apex_json.open_object;
    apex_json.write('id',    r.customer_id);
    apex_json.write('name',  r.full_name);
    apex_json.write('email', r.email_address);
    apex_json.close_object;
  END LOOP;

  apex_json.close_array;
END;

One issue I faced

I first wrote the callback name in small letters in my JavaScript, like get_customers. I got a not found error. Write the name exactly the same in both places, and keep the callback on Page 20.

6Connect the dropdown with JavaScript

Click the page name again. In JavaScript, Execute when Page Loads paste the code below. I made two Tribute objects. The first one is for the textarea and inserts @Full Name. The second one is for the email field and inserts the email address.

JavaScript

function fetchCustomers(text, cb) {
  apex.server.process(
    "GET_CUSTOMERS",
    { x01: text },
    {
      dataType: "json",
      success: function (data) { cb(data); },
      error:   function ()     { cb([]); }
    }
  );
}

function menuTemplate(item) {
  return apex.util.escapeHTML(item.original.name) +
         " <small>" + apex.util.escapeHTML(item.original.email) + "</small>";
}

var tributeComments = new Tribute({
  trigger: "@",
  values: fetchCustomers,
  lookup: function (item) { return item.name + " " + item.email; },
  fillAttr: "name",
  menuItemTemplate: menuTemplate,
  selectTemplate: function (item) { return "@" + item.original.name; },
  noMatchTemplate: function () { return "<li>No customer found</li>"; },
  allowSpaces: true
});
tributeComments.attach(document.getElementById("P20_COMMENTS"));

var tributeEmail = new Tribute({
  trigger: "@",
  values: fetchCustomers,
  lookup: function (item) { return item.name + " " + item.email; },
  fillAttr: "email",
  menuItemTemplate: menuTemplate,
  selectTemplate: function (item) { return item.original.email; },
  noMatchTemplate: function () { return "<li>No customer found</li>"; },
  allowSpaces: true
});
tributeEmail.attach(document.getElementById("P20_EMAIL_ADDRESS"));

Now click Save and Run, click inside Comments and type @jo. The list opens and it filters while you type. Use the arrow keys and Enter, or just click a name. That was a happy moment for me.

7Problems I faced after it worked

The basic mention was done, but I wanted it to look better. This is where I spent most of my time.

Problem 1: The mention chips did not go away

I tried this first, but it didn't work

I added a small chip each time a customer was picked. But when I deleted the mention from the text, the chip stayed there. It looked like the mention was still in the comment, which was wrong.

Finally this approach worked

I stopped adding chips on click. Now I read the full text on every key press, find all the mentions again, and build the chips from zero. If the mention is deleted from the text, the chip is gone too.

JavaScript

function getMentions(text) {
  var found = text.match(/@\p{Lu}[\p{L}'-]*(?: \p{Lu}[\p{L}'-]*){0,2}/gu) || [];
  return found.filter(function (n, i) { return found.indexOf(n) === i; });
}

function showChips(box, names) {
  box.innerHTML = "";
  names.forEach(function (n) {
    var chip = document.createElement("span");
    chip.className = "mh-chip";
    chip.textContent = n;
    box.appendChild(chip);
  });
}

Problem 2: A textarea cannot color only some words

I tried this first, but it didn't work

I wanted the mention to be bold and orange while typing. I tried color: orange on the textarea, but then the whole text turned orange. I also searched for a way to style one word inside a textarea. There is none, because a textarea only has plain text.

Finally this approach worked

I used a small trick. I put a second box right behind the textarea and copy the same text into it. In that back box I can wrap the mentions in a mark tag and color them. Then I make the real textarea text invisible, but I keep the blinking cursor. So you type in the real field, and you see the colored copy behind it.

CSS

.mh-hl-on {
  position: relative;
  background: transparent !important;
  color: transparent !important;
  -webkit-text-fill-color: transparent;
  caret-color: #1e1b4b;
}

.mh-back {
  position: absolute;
  overflow: hidden;
  pointer-events: none;
  box-sizing: border-box;
  border-style: solid;
  border-color: transparent;
  background: #fff;
  color: #1e1b4b;
  white-space: pre-wrap;
  overflow-wrap: break-word;
}

.mh-m {
  background: rgba(249, 115, 22, .13);
  border-radius: 4px;
  color: #ea580c;
  -webkit-text-stroke: .6px #ea580c;
}

JavaScript

function esc(s) {
  return s.replace(/&amp;/g, "&amp;").replace(/</g, "&lt;").replace(/>/g, "&gt;");
}

var ta = document.getElementById("P20_COMMENTS");
var wrap = ta.parentNode;
wrap.style.position = "relative";

var back = document.createElement("div");
back.className = "mh-back";
var inner = document.createElement("div");
back.appendChild(inner);
wrap.insertBefore(back, ta);
ta.classList.add("mh-hl-on");

function sync() {
  var cs = getComputedStyle(ta);
  back.style.left = ta.offsetLeft + "px";
  back.style.top = ta.offsetTop + "px";
  back.style.width = ta.offsetWidth + "px";
  back.style.height = ta.offsetHeight + "px";
  back.style.font = cs.font;
  back.style.lineHeight = cs.lineHeight;
  back.style.padding = cs.padding;
  back.style.borderWidth = cs.borderTopWidth;
  inner.style.transform = "translateY(" + (-ta.scrollTop) + "px)";
}

function update() {
  var html = esc(ta.value).replace(
    /(@\p{Lu}[\p{L}'-]*(?: \p{Lu}[\p{L}'-]*){0,2})/gu,
    '<mark class="mh-m">$1</mark>'
  );
  inner.innerHTML = html + "\u200b";
  sync();
}

ta.addEventListener("input", update);
ta.addEventListener("scroll", sync);
ta.addEventListener("tribute-replaced", function () { setTimeout(update, 0); });
update();

One issue I faced

My first version used real bold text for the mention. The bold letters are wider, so the cursor did not match the text behind it and everything looked shifted. I used a very thin orange outline on the letters instead. It looks bold, but the letter width stays the same, so the cursor stays in the right place.

Problem 3: My fields were outside the banner

I tried this first, but it didn't work

I made a nice banner with a Static Content region and the two fields were in another region below it. It looked like two separate sections. Moving items between regions in Page Designer did not give me the layout I wanted.

Finally this approach worked

I put an empty div with the id mhFields inside my banner HTML. Then a small script picks up the two field containers and moves them into it.

JavaScript

var slot = document.getElementById("mhFields");

["P20_COMMENTS_CONTAINER", "P20_EMAIL_ADDRESS_CONTAINER"].forEach(function (id) {
  var c = document.getElementById(id);
  if (c) slot.appendChild(c);
});

Problem 4: Wrong email in my screenshot

In one test my email field showed alex.binette@internalmail with no .com at the end. I thought my code was cutting the text. It was not. The mention inserts exactly what is saved in EMAIL_ADDRESS, and the row in my table was saved like that. I fixed the data in SQL Workshop and it was fine.

8Small tips from me

• Press Ctrl + F5 after every change. The browser keeps the old CSS and JavaScript, and I lost a lot of time before I learned this.
• Press F12 and open the Console when something is not working. Most of my problems were written there in plain words.
• Return only 10 rows from the SQL. The page stays fast even when the table is big.
• Use UPPER in the SQL so that small letters and capital letters both match.
• Add an Email validation on P20_EMAIL_ADDRESS so that only a proper email is saved.

That is all

I hope this saves you the two days I lost. If you are new to APEX like me, do not worry about the errors. Each one teaches you something. You can try the working page with the Live Demo button at the top, and if you get stuck, check the Console first.