Update batch fields based on current number and day

[Dennis_Yu]

Hi everyone,

So I am working on an automation that can help me do progress tracking, and I want the automation to help me find records that are based on the day of the week, and change the progress by +1.

The current solution I have is:
Scheduled Trigger
Once a week
Mondays

Find Records with Mondays.

Conditional Actions:
if Progress is exactly 1
Update to 2

if Progress is exactly 2
Update to 3

And I would multiply this by 7 for the 7 days of the week

But I realize, it does not update the entire batch but only one entry?
Any potential solutions?

[TheTimeSavingCo]

Hey Dennis, I think to batch update records via an automation you’re going to have to use a script which requires you to have a Pro subscription I’m afraid

Alternatively, you could set up a formula field to be the trigger instead by with like DATETIME_FORMAT(TODAY(), 'ddd') = {Day of the week} or something. The downside would be that you now are consuming one automation per record that you need to modify, which might not be tenable either

Let me know if you want to go down the script road and I’ll see what I can do for you

[ScottWorld]

Welcome to the community, @Dennis_Yu!

You’ve hit up against (or might soon hit up against) a few of the unfortunate limitations of Airtable’s automations, which are:

(1) Airtable will only process a set of records as one unified group of records instead of individual records,
(2) You can’t perform math in an automation,
and
(3) You can’t have an automation go down multiple conditional paths.

To start with, I should mention that none of these limitations exist in Make, which is why I personally accomplish this type of automation over there.

Not only does Make.com remove all of Airtable’s automation limitations, but it is also incredibly easy to setup something like this over there. This automation would only take 2 simple steps in Make. See screenshot below to see how easy this would be to setup in Make.

However, if you want to keep all of this in Airtable itself, that is totally possible too! You will just need to jump through some hoops & create some workarounds to make it happen.

1. The first workaround answers the question of “how can you update multiple records individually”?

The trick here is to get Airtable to operate on multiple records successively, since Airtable offers no built-in looping of records.

There are 2 different tricks that you can use to make this happen:

(a) Create a formula field that results in the number 1 when the current day is equal to what you’re looking for. (Or when the current time is greater than or equal to what you’re looking for.) Then, trigger your automation to be based on that formula. The potential downside to this is that this formula won’t update itself while your base is asleep (i.e. when nobody is using the base), so you might need to wake up your Airtable base at least once per day with another automation on a schedule that does anything in your base to wake it up, such as simply searching for records.

or

(b) The much easier trick to make this happen adds a little bit of clutter to your base, but it won’t suffer from the problem of your base falling asleep as long as you put it on a schedule. I outline this trick in depth in the video podcast below. In this podcast, I discuss this trick in the context of sending multiple personalized & individualized emails, but it could also be used in your situation to update multiple records individually:

2. The 2nd workaround answers the question of “how to perform math in automation actions”?

This can’t be done in an automation action itself, so you need to create & hide a formula field that already has your desired mathematical result prepared & ready to go.

Then, you would simply refer to that formula field in your automation, and insert it wherever you need it to be inserted.

3. The 3rd workaround answers the question of “how to go down multiple conditional paths in Airtable?”

This isn’t possible. You would need to put all the steps that you need within each conditional path. Or you would need to create multiple automations.

As you can see, all of this can be done natively in Airtable if you know the tricks on how to do it, but it’s always a ton easier to use a professional automation tool like Make.com.

p.s. If your company has a budget for your project and you’d like to hire an expert Airtable consultant to help you create this, please feel free to contact me through my website: Airtable consulting — ScottWorld

ScottWorld • Best Airtable Consultants, Airtable Experts, Airtable Consulting,...

ScottWorld • Best Airtable Consultants, Airtable Experts, Airtable...

ScottWorld provides the world's best Airtable consultants, expert Airtable training, best Airtable consulting, and Claris FileMaker Pro developers.

[Dennis_Yu]

Much appreciated Adam, I think we will be moving into Pro subscription as an eventuality due to the sheer numbers needed.

I will let you know if we do plan to go down that path! Thank you very much.

[Dennis_Yu]

Hey Scott, thank you for your response.

I am currently looking into the Make’s functions thank you!

[Karlstens]

The automation scripting in AT Pro is excellent. I use it extensively in my own business and for customers that I now deliver to.

You’ll need a solid understanding is Javascript fundamentals, which thankfully there’s a lot of great content out there to help you.

[Dennis_Yu]

Hi Scott,

I’m having trouble getting the update records portion of make.com to run the way i want it to.

I have no coding experience or handle with formulae so when I’m trying to search for the fields using make, the following is what I input:
{Field 1} = “Drop down 1”

But I also want to include {Field 2} = “Drop down 1”
Do I just use the AND function?

On top of that, I am struggling to get the UPDATE RECORD portion to work.
I wanted make to search for the above, then update
{Field 1}
if = “Drop down 1”, then update to “Drop down 2”
if = “Drop down 2”, then update to “Drop down 3”
if = “Drop down 3”, then update to “Drop down 4”

So on and so forth, is this still possible?

[ScottWorld · Accepted solution]

Hi @Dennis_Yu,

You can always test your formulas in Airtable first, to see if they result in the number 1. If they result in the number 1 (or any other non-zero value), then Make will successfully return those records to you during a search.

So, in your situation above, you would probably want to use the OR function like this:

OR(
{Field 1}="Drop down 1",
{Field 2}="Drop down 1"
)

You can test that formula in Airtable to see if it results in a one (1) or a zero (0) for each record. If it results in a 1, and you use that same formula in Make, then Make will find those records.

There are several different ways to create conditional logic in Make. Your easiest way would probably be to add a router, and then apply different filters to different paths. You can learn more about routers & filters here.