APEX - Creating a page with multiple forms linked to multiple related tables ... that all submit with a single button?

Question

I have two tables in APEX that are related by their primary key. One table (APEX_MAIN) contains the main document metadata in our system, and the other (APEX_DATES) contains important dates related to this document processing.

For my team, I created a contrl panel where they can interact with all this data. The problem is that now they change the information in APEX_MAIN on the page, and then change APEX_DATES to another. I would really like to have these forms on one page and send updates to the appropriate tables and rows with a single submit button. I installed this currently using two different areas on the same page, but I get errors as with the initial row selection (which row is always selected, 2 seems to work, but then the elements of the page in the form that was selected first are empty?) and with sending (it gives some error about changing information in the database from the moment the update request was sent). Can anybody help me?

Answer 1

This is a limitation of the built-in forms of Apex, unfortunately, you can have only one automatic process of selecting lines per page. You can have more than one region of the form on the page, but you must encode all the processing of fetching and submitting yourself if you do this (not so difficult, but you also need to take care of optimistic blocking, etc.).

Dividing one table into several areas is quite possible, even using the built-in form functionality, since the area itself is just a layout object, it does not have any functions associated with it.

Manually forming shapes is pretty straight forward, but a bit more.

Items

They must have the source set in the Static Text column, and not in the database column.

Buttons

You will need the "Create", "Apply Changes", "Delete" button to submit the page. They need unique query values ​​so that you know which table is being processed, for example. CREATE_EMP You can make button conventions, for example. Create only when the PK element is null.

Row fetch process

This will be a simple PL / SQL process, for example:

select ename, job, sal into :p1_ename, :p1_job, :p1_sal from emp where empno = :p1_empno; 

It must be conditional so that it only starts when you enter the form, and not after each page load - otherwise, if there are validation errors, any changes will be lost. This can be controlled using a hidden element that is initially null but set to a non-zero value when the page loads. Only select a row if the hidden element is null.

Submit process (s)

You can have 3 separate processes for inserting, updating, deleting associated with buttons, or one process that looks at the value :request to see what needs to be done. In any case, the processes will contain simple DML, for example:

 insert into emp (empno, ename, job, sal) values (:p1_empno, :p1_ename, :p1_job, :p1_sal); 

Optimistic block

I skipped this above for simplicity, but one thing that inline forms do for you is an “optimistic lock” to prevent 2 users from updating the same record at the same time, with one update overwriting the other. There are various methods that you can use to do this. It is common to use OWA_OPT_LOCK.CHECKSUM to compare a record as it was when it was selected as it was at the time the update was committed.

In the process of sampling:

 select ename, job, sal, owa_opt_lock.checksum('SCOTT','EMP',ROWID) into :p1_ename, :p1_job, :p1_sal, :p1_checksum from emp where empno = :p1_empno; 

In the process of sending for an update:

 update emp set job = :p1_job, sal = :p1_sal where empno = :p1_empno and owa_opt_lock.checksum('SCOTT','EMP',ROWID) = :p1_checksum; if sql%rowcount = 0 then -- handle fact that update failed eg raise_application_error end if;