How to use Oracle APEX dynamic action to populate a 'form on a table' field on the basis of other fields and underlying sequence?

Question

I am implementing a 'form on a table' that will allow the end-user to create records in the database. I would like to generate the primary key for the underlying table based on the selections the user makes as well as the underlying sequence. The basic formula for the primary key should be a concatenation of the 'disposal site code' + 'year of study' + str(sequence). The sequence is should start at and increment by 1 so that the first project created in the database end with -1 and the nth with -n.

For example if the user selects the 'Disposal Site' as 'Seldom', the field 'Disposal Site Code' should be dynamically populated by 'CA-AT-D130' (a 1:1 code alphanumerical code for each site) and after selecting the year, for example 2022, the project number would dynamically be populated as CA-AT-D130-2022-01 (assuming first entry in the table). After creating the new record CA-AT-D130-2022-01 would become the 'project number'.

Can anyone recommend the idiomatic way to do this in APEX? I have a feeling what I am needlessly complicated something that should be relatively straightforward.

Answer 1

There are 2 ways of doing this that are a lot simpler than what you are trying. There is no reason at all to use a dynamic action, this can just be done using apex page processing.

option 1: in the database: Create an trigger on the table that sets the primary key column based on the other column values & sequence

option 2: in apex: Create an after submit computation on the page item to compute the value based on the other page item values & the sequence.

option 3: what I would do instead: In my personal opinion this isn't a good option for a primary key. The primary key should just be a unique row identifier with no business value. Just use an identity column for that and no additional work is needed in db or apex. Sounds like you want to capture business information in the key. Well... make that a "pseudo-primary key" and store that information in a column with a unique index in your database. Or, use a virtual column in your table that calculates this value from the primary key column and the other columns you need.