Filter Table when selecting item from Ref

[Domearian]

Hi All, A bit of a complex one.

I have 3 tables:

1 - Employee Info  2 - Assigned Competencies  3 - List of Competencies

Employee is assigned a competencies from the list of competencies. Assigned Competencies is a ref to Employee Info. List of Competencies is a ref to Assigned Competencies.

The List of Competencies has a name and ID. When a competency is assigned to an employee, the employee record shows a complete list of competency IDs and also a REF ROWS list of assigned competencies.

I want to filter the list of competencies for the employee I am looking at. Currently, the list shows all available list of competencies. So how can I filter Table 3 when adding a competency into table 2 which is then viewable against the employee on table 1.

[lynchk21]

What is the reference column between the Employee table and the Employee Assignments table? 

It seems like you just need to use suggested values on your Employee Assignments[SQEP Code] column with a SELECT and NOT(IN()) expression which references the Employee[Assigned Codes] column

[Domearian]

Within the Employee table, I am able to list all the quals not assigned:

SELECT(SQEP List[SQEP List No], (NOT(CONTAINS([_THISROW].[Assigned Codes],[SQEP List No]))))

That took me a bit of time to come up with but that is in the employee database itself so the "Assigned Codes" are there so it's a straight Select lookup of the SQEP List Data into the data already there.

But when assigning an item via the little associated "add" button in the associated data LIST, the SQEP List shows everything. I need it to look up the "Assigned Codes" in the Employee data using the "Unique ID" (which is assigned to the employee) and then filter out everything out already assigned (Assigned Codes) from the SQEP List. So in the Ref: Valid If? It is complex to understand I grant you.