Oracle Apex pop up lov should show full number but grid display only first 4 digits

Question

I have an Interactive grid.

select emp_name,dept_name,bonus,grade from employees;

Here dept_name is pop up lov with select query as :

select dept_name from departments;

Now all departments are named as:

01-Sales
02-Marketing
03-Accounts

and so on..

The requirement is that when the list is expanded in column it should show complete department names with the number prefixes.

But when the IG is displayed or loaded it should just show the first 2 digits, i.e. 01 or 02 or 03 instead of full name.

I tried using substr in the select query but it still displays the full name.

select emp_name,substr (dept_name,2) as dept_name,bonus,grade from employees;

Answer 1

As far as I can tell, that requirement requires a little bit more effort than you'd expect.

Interactive grid's source query:

select e.empno,
       e.ename, 
       e.deptno,
       e.job
from emp e

deptno column should be set as a Popup LOV whose source is a Shared component List of values which looks like this:

select d.deptno as disp_val,
       d.dname  as dname,   
       d.deptno as ret_val
from dept d
order by d.deptno

There are 3 values in this query, while "ordinary" LOVs require two of them: a display value and a return value. Apex lets you create additional columns for items that support that, and Popup LOVs are among them.

Set LOV column mapping as:

• Return: RET_VAL (it'll display department number, e.g. 10, 20, ...)

• Display: DISP_VAL (it is just the same as RET_VAL as you want to actually display only department code, not its name)

• Additional display columns (that's crucial here!): it is a shuttle item - move all items to the right hand side of the shuttle, i.e. it should contain RET_VAL, DISP_VAL and DNAME

  

Back to Page designer.

Create additional item in Interactive Grid region, let's call it dummy, make it hidden, its source is None. It'll be used in the next step.

Navigate to deptno column properties and set Additional outputs property to DNAME:DUMMY which means: take dname from the LOV and put its value into dummy column (i.e. don't do anything as this is just a dummy hidden column).

Run the page.

Initially, you see only department code (e.g. 10). When you double-click the cell and open LOV, you'll see both department code and its name. Select any value, Save, and you'll (again) see code only.

---

As I said, not as easy as it sounds. Perhaps someone else know simpler way to do that.