Hello!
Let me first paint the picture of what I am working with:
1 base, 4 tabs: "🧍♀️People", "🏝️ Absence Management", "🏝️ Leave types" and "📅 Public holidays"
Relevant columns:
- In "🧍♀️People" one column has the staff "Name" and the other column has a "Leave Location" (which refers to the location they are based in and to which public leave should be based on)
- In "🏝️ Leave types", we have a choice of "Leave" called: "🌞 Holiday", "🗓️ Public Holiday", "🌞🗓️ Holiday + Public Holiday", "🤒 Sick".
- In "🏝️ Absence Management", we have date fields with leave "Start Date" and "End Date", linked fields to "🧍♀️People" taking "Name" and look up "Leave Location", linked field to "🏝️ Leave types"
- In "📅 Public holidays" we have a first field which is a formula of date + "Name" of the public leave, then a "Date" field, a multiple select "Leave Location" field.
What I want is that each time someone submit a Leave Form (linked to "🏝️ Absence Management"), the automation:
- Checks whether "📅 Public holidays"' any of the records' "Leave Location" is the same as the one of the person who submitted it.
- If yes, then it should check if the date of the record in " 📅 Public holidays" is comprised between "Start Date" and "End Date" of the Leave requested.
- If yes, then it should input the amount of public holiday days in a dedicated field called "Public Holiday included" and if possible, even add the matches to the "Public holiday" linked field in "🏝️ Absence Management" (in fact, if we can make the later happen then we could just have a count field!)
In a second step, I would want that if:
if public holiday leave = 0 → an actionable message in sent on Slack to ask for approval for the leave.
if public holiday leave < leave asked and is not = 0 → change the linked record to "🌞🗓️ Holiday + Public Holiday" + send an actionable message in sent on Slack to ask for approval for the leave.
if public leave = leave asked → change the linked record to "🌞🗓️ Holiday + Public Holiday" + approve
To approve, the same name selected by the leave requester as the "Approver/s" (which is a linked record to "🧍♀️People" in "🏝️ Absence Management") needs to be selected in a collaborator field called "✍🏽 Approved by"
I have been battling with scripts and ChatGPT for a while now and would be grateful for anyone support if you are up to the challenge 🙏 (video attached)
