Sep 16, 2022 11:31 AM
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.
Sep 16, 2022 01:21 PM
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.
Sep 16, 2022 08:27 PM
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:
Work Orderstable reflects whether this person’s available or not
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