Purpose: Show a drop down list of a lookup table, excluding values already used in a grid during Inline Add
This uses MySQL's GROUP_CONCAT to form a csv of the previously used values in the grid and NOT FIND_IN_LIST to form the condition filter.
Limitations: If you open multiple inline add rows, they will show the same filtered list, because data has not yet been saved.
Verified on: Inline Add/Edit and Add/Edit Page (PHPR 10.03 Build 34427)
Follow the steps below:
==============
Assuming the grid/list table is employee_list with a lookup field EmpNo and the lookup table is: EmpNo_lookup_table
Go to Page Designer for the employee_list grid, and click the "View As/Edit As" of the lookup field EmpNo, with values populated from a lookup table EmpNo_lookup_table
- To exclude the previously selected values in the grid, set the WHERE conditions for the drop down list as follows:
NOT FIND_IN_SET
(
EmpNo , (SELECT GROUP_CONCAT(DISTINCT EmpNo ORDER BY EmpNo SEPARATOR ',') FROM employee_list)
);
The SQL condition SELECT GROUP_CONCAT(DISTINCT EmpNo ORDER BY EmpNo SEPARATOR ',') FROM employee_list concatenates the used values from the grid into a csv list, eg. if 10002, 10004 have been used in the grid table, it will give a csv: 10002, 10004
It then forms a condition excluding the values used in the employee_list grid:
NOT FIND_IN_SET (EmpNo, (10002, 10004) )
Here's what you will see, for instance:
EmpNo_lookup_table has values: 10001, 10002, 10003, 10004, 10005
employee_list (list/grid) has values:
EmpNo | EmpName | EmpType
1st row: 10002 | ABC | type1
2st row: 10004 | DEF | type2
When you click inline add:
The drop down list for EmpNo_lookup_table will only show: 10001, 10003, 10005
