Updatable Interactive Grid based on multiple tables

Question

I have 3 tables: Employees, Locations, and EmpLocation

Employees stores employee data with Employee_ID as primary key,
Locations stores location data with Location_ID.
EmpLocation stores Employee_ID and Location_ID. Each Employee can have multiple locations.

I need to create an interactive grid that will display data for all employees, including those that do not have a location assigned to them. Then the user needs to have the ability to select a location for those employees that have it as null, and and also to add an additional location to any employee. I figured what I need is two select lists in an editable grid. So new row could be added by selecting an employee and corresponding location.

I used the following query:

SELECT el.Employee_ID, el.Location_ID
FROM EmpLocations el

Then I made each column a select list where the first one pulls data from Employees table:

SELECT Employee_FName, Employee_ID FROM Employees

and second - from Locations:

SELECT Location_Name, Location_ID FROM Locations

Everything looks good, but this way I only get employees who has at least one location assigned to them. I need to see all amployees and whether or not they have any locations assigned to them ( each employee can have more than one location assigned).

So I changed the source query for the grid to:

SELECT e.Employee_ID, el.Location_ID
FROM Employees e 
LEFT JOIN EmpLocations el ON e.Employee_ID=el.Employee_ID

But in this case, will it be possible to make the grid updatable since there are two tables involved?

Littlefoot Littlefoot · Accepted Answer · 2018-09-18T19:53:01

I'd say yes, but you should get rid of the automatic row processing (created by Apex, by default) and write your own PL/SQL code which would do inserting/updating. Something like an instead-of trigger on a view.

Also, for updating purposes (if you'll allow it), include the ROWID pseudocolumn.

Code (untested) would look like this:

begin 
  case when :apex$row_status = 'C' then 
         insert into emplocations (employee_id, location_id)
         values (:employee_id, :location_id);
       when :apex$row_status = 'U' then
         update emplocations set
           employee_id = :employee_id,
           location_id = :location_id
           where rowid = :rowid;
       end;
end;

Updatable Interactive Grid based on multiple tables

1 Answers