Filter out users from being assigned tasks

I want to be able to filter out users from being assigned tasks that are due on a certain date if they submitted a time off request that overlaps with the due date.

For example if a task has a due date that is due on 9/20 and one of my users submitted a time off request from 9/18-9/22, I want the assignee column to not show that one user.

Anyone have any idea how this can be done?

Airtable - Maintenance - This is my base right now, and for this base, a task is a “Work Order”. The worker column links to my user table and I’m struggling to connect the time off requests together.

Hi,

Yes, the idea is to create a view, which dynamically filter available options and won’t let (by 'Limit record selection by a view") choose resource that currently not available.
I already answered on a case today, which is solved in a same way.

Hmm, I can’t think of a way to prevent the assignee column from showing the user I’m afraid

To solve the problem of not assigning work to people who are on PTO, the main issue would be having to loop through all the PTO that worker has and checking it against the task’s due date, which is difficult to do with Airtable’s native functionality

A workaround would be to create another link from the Work Order table to the Users table, and in the Users table, and add a lookup field to the due date.

This would allow you to pull over just the due date of the latest task, and you could then pull that into the Time Off Requests table to check it against all of that person’s PTO via a formula field, does that make sense?

With that, you would have a workflow of:

  1. Assign person to task
  2. An automation puts that person into that linked field I mentioned above
  3. The formula field in the Work Orders table reflects whether this person’s available or not
  4. If they’re not available, you assign someone else

This would use an automation run each time you assigned a person though

If you don’t want to use any automation runs, you’d just put the person into the linked field I mentioned above manually, which would cause the formula field to update as well