Skip to main content

Checking sequentially against an array

  • November 16, 2021
  • 3 replies
  • 42 views
G

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.

3 replies

Justin_Barrett
Forum|alt.badge.img+21

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.

G
  • Gabrielle_Stich
  • Author
  • New Participant
  • November 23, 2021

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.


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
Forum|alt.badge.img+21

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!


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.

Terms & ConditionsAccessibility statement