Search code examples
oracleoracle-apexlov

Oracle APEX return multiple values in LoV

I have a field as PopUp LOV and as source a shared component with the corresponding code.

    `SELECT u.Lastname || ', ' || u.Firstname AS displayed, i.IUUID
from INTERNAL_SUPERVISORS i
    left outer join USERS u on i.UUID=u.UUID
   
     union
SELECT u2.Lastname || ', ' || u2.Firstname AS displayed, p.PRID
    FROM PROFESSOR p
    left outer join USERS u2 on p.UUID=u2.UUID `

This is my column mapping in the LoV:

I want it to be possible to select a person from the one or from another table and give different IDs as return value depending on the selection.

With this implementation it is possible to see and select persons from both tables but when I save the form, I cannot see the User from the professor table but can only see the person from the other table. Is it because of the return value in the column mapping? If so is it possible to select two possible return values?

Solution

  • I cannot see the User from the professor table

    I'd say that it depends on how you're looking at it. If data in a table (you use to store values selected from that LoV) corresponds to two tables, then - when reviewing data - you have to join it to both other tables - internal_supervisors and professor.

    Usually, when designing data model, we use foreign keys to maintain referential integrity. As you allow both iuuid and prid to be stored, then it means that you have to check both of those tables while retrieving data.