Filter ref field based on input to current record being entered

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!

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.

3 Likes

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!

2 Likes