Filter ref field based on input to current record being entered

[Dan_Coe]

Hi guys. I am a bit stuck with this one and could use your suggestions.

I have a scheduling app which is used to assign workers to a project on a specific date.

The below is a simplified version of my tables and columns:
Workers

• worker_id
• worker_name
  Projects
• project_id
• project_name
  Schedule
• schedule_id
• date
• worker_id
• project_id

In the scheduling app I have a form view with the following fields:

• date
• ref to select worker_id from worker table
• ref to select project_id from project table

I want to filter the worker_id’s to display only the workers who have not already been assigned to a project on that date. I.e filter schedule records to match the selected date of the current record being entered, then match those worker_id’s to the worker_id’s in the worker tables then display only those which do not have matches.

Thanks for your help!

[Pratyay_Rakshit · Accepted solution]

in the valid if of the workers column in the scheduling table:

filter(“Workers”,
NOT(
IN(
[Worker_ID], select(Schedule[Worker_ID], [Date]=[_THISROW].[Date])
)
)
)

Let us know if you have trouble understanding the expression.

[Dan_Coe]

Thanks Pratyay! That worked perfectly. I had tried similar expressions but not in the valid if field as I thought they would still display but not allow to submit. Thanks again!