Hello all
I’m building a form with apex 20.1 and got stuck with an issue. The situation is this.
The form is based on a table, and it will be used to register employees for a company. In the form I’ve one field for the “company” which is a simple LOV. Next there are two other LOV fields, one for “departments”, and the other for “department subdivision's”. The “department subdivision's” table has a foreign key to the “departments” table, and the “departments” table has also a foreign key to the “companies” table.
Considering that the employees table is indexed by companies I must include the “company” field on it. Regarding at the “department” and “department subdivision's” fields, I decided to just store the “ department subdivision's” value, cause there is unique relation with the department table.
So, I created the form defining the “company” field as a simple LOV pointing to the companies table and it will be stored in the table for employees. The “department” field is a form item defined as LOV (not included into the employees table), which has as parent the “company” field. The “department subdivision's” LOV field is included in the employees table, and has as parent the “department” field. Until this point the form works as expected, and the data is shown accordingly, and saved to the employees table without any issue.
The problem arise when I need to update an employee record. After opening the form the “company” field shows the label for the department value, the department field is empty as expected because that field is not stored on the employees table, but the “department subdivision's” only shows the value stored on the table, not the label associated in the department subdivisions table.
To handle this issue I first tried to create a dynamic action at page load which retrieve the department value on the “department subdivision's” table and assign that value to the “department” field, but it didn’t worked. The “department” stay always empty. To check if something went missing, I modified the “department” as a numeric field, and the value corresponding to the department is shown as it should, so the issue has something to do with LOV and the cascading between them.
Thinking that maybe the “department subdivision's” LOV which point to the “department” as a parent is involved, I decided to set up the LOV as a function returning an SQL which only execute a query that point to the “department” table when that field is not null, but this also failed.
I thought to add a field on the employees table for the department, but I’m not sure because any change on the department subdivision will generate an inconsistency with the information stored on the employee table.
So, until now I didn’t find a solution for this issue.
Appreciate if anyone has any suggestion about how to solve this issue.
