Oracle Apex- Update field in dml form on basis of another field which is not primary key

Question

Can we update a columns value based on another column name in dml form?

Ex: I have created a normal region with static content. P1_PERSON_ID - Primary key P1_PERSON_NAME P1_ASSIGNED_DEPT P1_APPROVER

In processing- Automatic DML Processing set. So when this page is run, i wish to select person name (it is select list) and for that person and that assigned_Dept set approver value.

As i select person name, it would in backend fetch person id which is primary key and accordingly update other columns.

So say when i select Person_Name -> Mark, Assigned_Dept HR and put Approver as 1. I want the table, it_people in this case to update approver as 1 for person_name as Mark and dept as HR. Something like update it_people set approver-1 where person_name= Mark and assigned_Dept=HR. Primary key is PERSON_ID but because the form wont have any prefetched value for particular employee, i want it to update one column based on another column values. Is it possible?

P.S. I had raised similar question but could not explain the correct issue so raised here to clearly mention the requirement.

Oracle Apex 20.1 Automatic DML processing not working to save updates

Old thread

Koen Lostrie Koen Lostrie · Accepted Answer · 2020-05-18T13:19:19

I don't have access to your data so I gave it a try with the sample dataset EMP/DEPT. This is a testcase that does exactly what you describe.

Create a form with report on table emp
In the form attributes, only check "Update Row" as "Allowed Operations"
For this example, I want to select an employee and update his salary using built in row processing. So I hid all the columns except for the SAL column. Then I made the P1_EMPNO a select list (instead of hidden). This is the primary key field, so if you make it a select list, the selected value in the select list is the primary key for the row you want to update. Source query for P1_EMPNO:

SELECT ename,
       empno
  FROM emp

Now access the form via the "edit" link on the emp report for any employee. Select an employee and change the salary. If you save the correct row is updated.

--Koen

Oracle Apex- Update field in dml form on basis of another field which is not primary key

1 Answers