I have a tab within a table, the tab is called PTO. the PTO tab has two different views I am working between for this automation. The PTO tab as a whole is populated from forms that are submitted containing requests for time off from employees. This form contains fields for [start date] and [end date] and is intended to capture both single and mult-day requests off. The first automation sends an actionable slack message for approval, which is working correctly. The second automation I am trying to set-up is intended to send a message to a slack channel each day that there is an employee out of office, listing which employee is out of office. (Bonus points for this only running Monday-Friday, even if the dates fall on the weekend).
My first attempt at the automation was setting up the second view of the same tab, which would be filtered to only show records based on conditions, but I wasn't able to find a way for the records to populate if the request required more dynamic info, such as it falling across more than 2 days (start date and end date).
The second attempt was to create two formula fields within the first view, one being:
AND(TODAY() >= {Start Date}, TODAY() <= {End Date})
and the second being:
IF(IsTodayWithinRange, "Out of Office", "In Office")
and then having the slack message send based on the second view being filtered to only show data where the column for the second formula field read "Out of Office".
However, I tested this with a record listing a [start date] of today's current date, and it was still showing as In Office, therefore not filling to the second page, and not triggering the message.
Is there an easier way to do this? Is there something I'm missing to make this 2-formula solution work, if that is the best solution for this?
