Re: Creating a Calendar or List that Counts How Many Times a Date is Used
Sorry that this question looks a little like a written math problem, but:
- We are tracking employee requested PTO via Airtable.
- When an employee uses their PTO, they can request to pull from a group of 5 temps to cover their shifts.
- We'd like for employees to be able to see a list of dates that still have temps available before they put in their PTO requests.
What I can seem to do is group by date of time off requested, and it shows me the total count for how many times a specific date has a request for a temp. (See below - 1 person requested a temp on 3/14, while 4 people requested a temp on 3/15.)
Or, I can have a calendar view, stripped of personal information, that shows how many requests have been made of a specific day. (See below.)
However, what I would like to do is have a formula like TempsAvail = 5 - SUM(RequestDateCount), and then have either a list or a calendar spit out that number. (Apologies for the ugly formula, I'm not sure how I would write this.)
Is it possible to have a calendar or list view that can show this information? Sort of like a calendar of automatically updating black-out dates for available temps? Thank you!
Hmm, for the list view you could:
1. Create a new table called "Dates" and link it to the request table:
2. Whenever a new request gets made, have an automation paste the date into the linked field to the Dates table:
You can now use a "Count" field to count how many requests have been made for that day, and you can show "Dates" table to your users
If you're alright with showing the total number of requests on each day then this setup will work just fine in a Calendar. If you want to show availability that's a lot more involved and I figure showing the total number of requests works just fine for you?
