Checking sequentially against an array

[Gabrielle_Stich]

I am making an automatically repopulating task list. I can’t use the Workday function because we work on Saturdays and have a non-standard holiday schedule. The formula I am using checks a field which adds days based on a daily, weekly, monthly assignment to the old due date. I have a formula that successfully checks that new date against the holiday string rollup, but it can only add 1 date at a time- so if there are multiple holidays in a row, it doesn’t recognize that.

Is there a way to make this formula keep checking the Date Calculation 1 sequentially against my rollup of days to skip until it reaches a workday again?

IF(FIND({Date Calculation 1 String}, {Skip String Rollup}&""),(DATEADD({Date Calculation 1}, 1, ‘day’)),
{Date Calculation 1})

I’m guessing I need to change the Dateadd value of 1 day, but I’m not sure how. Any help is appreciated.

[Justin_Barrett]

Gabrielle_Stichweh:

Is there a way to make this formula keep checking the Date Calculation 1 sequentially against my rollup of days to skip until it reaches a workday again?

Unfortunately not. Airtable doesn’t currently provide any means in its formula system for iterating through items in an array. In my own task setup, I run a script daily (via automation) that does all processing on repeating tasks to move them to their new dates based on their individual frequency settings.

[Gabrielle_Stich]

Gotcha. That’s too bad. In case anyone is having a similar problem, her is how I got around it:

The first date calculation generates the next date based on frequency:

DATESTR(DATEADD(Due, (SWITCH({Frequency}, “Daily”, 1, “Weekly”, 7, “Biweekly”, 14, 28)), ‘day’))

Then I use a link to a record which contains all of the days off, and a string rollup of those dates. I then used a series of fields which check the next workday based off of the previous column. I just stacked those fields up, each capable of skipping two days off, until I had enough for the maximum number of days off in a row. The final field is then referenced in an automation to generate the next task.

DATETIME_PARSE(DATESTR(IF(FIND((DATESTR( IF(FIND({HH}, {Skip String Rollup}&""),(DATEADD({HH}, (SWITCH({Frequency}, “Daily”, 1, “Weekly”, 7, “Biweekly”, 14, 28)), ‘day’)), {HH})) ), {Skip String Rollup}&""),(DATEADD((DATESTR( IF(FIND({HH}, {Skip String Rollup}&""),(DATEADD({HH}, (SWITCH({Frequency}, “Daily”, 1, “Weekly”, 7, “Biweekly”, 14, 28)), ‘day’)), {HH})) ), (SWITCH({Frequency}, “Daily”, 1, “Weekly”, 7, “Biweekly”, 14, 28)), ‘day’)),
(DATESTR( IF(FIND({HH}, {Skip String Rollup}&""),(DATEADD({HH},
(SWITCH({Frequency}, “Daily”, 1, “Weekly”, 7, “Biweekly”, 14, 28))
, ‘day’)), {HH})) ))))

The {HH} is just changed to reference the previous field each time. Not the most graceful solution, but it does get the job done!

[Justin_Barrett]

That’s definitely a cumbersome process. Are you interested in exploring a scripting-based solution? If your base is in a Pro-plan workspace or higher, a daily automation could be designed that runs a script action to update all records based on their individual schedules. If script actions aren’t possible, you could still run a script manually in a Scripting app that does the same thing.