Modal Dialog Form Primary Key item not refreshing in Parent Page-CodePudding

I am beginner in Oracle APEX (Version 22.11) and I hope, that someone can help me:

I have an Interactive Report as Parent Page. I get the data with a (simple) sql query. There is also a Create-Button, which works fine. It opens an empty Modal Dialog Form, where I can fill in the entries. There is a dynamic action, which refresh this Interactive Report by Closing Dialog.

On another page I have this Modal Dialog Form, which use the same sql query as the Parent Page and have the four button 'Cancel','Delete','Save' and 'Create'. I choose also a primary key and do different setting:

under 'Processing' I make Form - Automatic Row Processing(DML) and also Yes to 'Return Primary Key(s) after Insert
for the 'Save'-Button I have the following Behavior:
- Action: Submit Page
- Database Action: SQL UPDATE action
Also I have for this button the following Server-side Condition:
- Type: Item is NOT NULL
- Item: [Item of the primary key]

Now, I have the following problem with this Save-Button (the other buttons works fine): When I edit the Radio Button or the Textfield in this Modal Dialog Form and click the Save-Button, the Parent Page takes over the changes. But when I change the Textfield of the primary key Item or when I change another Textfield or a Radio Button together with this Primary-Key-Textfield, the Parent Page doesn't take over the changes.

Where is the mistake? Can anyone help me?

CodePudding user response：

I think what happens is something else than what you think is happening. As a test I created an interactive report with a form on the EMP sample table (you can generate that in SQL Workshop > Utilities > Sample Datasets). Out of the box the report refreshes when a change in the modal form is submitted. In the modal form P102_EMPNO is marked as primary key.

No what does that mean ? It means that that page item is used by the apex engine to uniquely define the row when executing dml (update/delete) on that row.

I changed the page item P103_EMPNO to a text field, enabled debug and submitted the form. This is what debug shows:

As you can see, the where clause of the update statement is:

where "EMPNO"=:APEX$PK1

I'm assuming the bind variable :APEX$PK1 is what the apex engine has decided is the bind variable for the (first) primary key column in the form.

So... if you change the primary key, then it is NOT going to update the primary key for that row. Instead it is going to try to update a row that has the newly entered primary key value. And the refresh you're expecting is not happening... because no rows were updated.

The reason is that primary keys should never be changed during normal operation in a relational database. They uniquely define the rows - that is what a primary key does. The form region is based on this principle and the behaviour that you're seeing is expected. Also, the reason that the primary key page item is hidden or display only when a form is created is that a primary shouldn't be changed.

Now if you really want to change the primary key value (my advice: DONT. EVER.) then you can probably create a form yourself and use the rowid to identify the row. Out of the box the form region does not let you select the row id.