I would like to implement the following.
The user has the possibility to select a responsible person with a pop-up LOV, the responsible person can be either a staff member or a manager. Both are stored in different tables, but should be displayed to the user.
I use the following sql query for the list of value:
SELECT u.Lastname || ', ' || u.Firstname AS displayed, s.STID AS ID
from STAFF s
left outer join USERS u on s.UUID=u.UUID
SELECT u2.Lastname || ', ' || u2.Firstname AS displayed, m.MAID AS ID
FROM MANAGER m
left outer join USERS u2 on m.UUID=u2.UUID
Depending on what the user selects for this column I want set the Foreign Key either to the table STAFF or to the table MANAGER. This implementation is desirable because the user has the possibility to see all possible responsible persons he/she chooses at once, without the need to specify (Is it a staff member or manager?).
So in general, the question arises, can you automatically set a foreign key to the corresponding table based on a lov choise of the user?
Another possibility: create two hidden page items, each has a foreign key constraint to the corresponding page. Depending on the return value of the list of value, you could then set an ID in the corresponding page item.