Update attendance count and points with automated script

[Kaleb_Penner1]

I run a kids club and do my database through airtable and would love to automate some updates after club each week.

1. Want to have the “pts this week” column be multiplied by 10 and added to the existing value in the “total points” column.
2. Want to increment the attendance column value by 1 if the “pts this week” column is not blank.

Not sure I can swing this with a formula or an automated script run weekly.

Zero scripting experience. I know how to do this through third party apps (Wayscipt, Pabbly, etc) but would like to learn here. Thought I’d ask for help and see where I end up as this probably is a very simple script.

[Bill_Felix]

Welcome @Kaleb_Penner1!

Kaleb_Penner1:

but would like to learn here.

:slightly_smiling_face:

My journey started by doing as much as possible in Formulas/Views/Zapier (& now Automations) and only then graduating to scripting… so I have prepared a solution for both 1. & 2. that stays away from for loops for now…

so assuming your Base looks like this:

Let’s add a few Formula fields:

• pts this week * 10: Formula = IF({pts this week}>0,{pts this week}*10,BLANK())
• snapshot points: Formula = IF({pts this week * 10},SUM({total points},{pts this week * 10}),BLANK())
• snapshot attendance: Formula = IF({pts this week * 10},SUM(attendance,1),BLANK())

Right out of the gate we are saying that if pts this week is greater than zero then we do stuff, otherwise BLANK() just says leave things empty.

Really nothing that exciting:

:thinking: Now because of millisecond difference in keystrokes while entering data you will see that these numbers ‘take a second’ to ‘settle down’ in the UI. In the background, everything is happening much faster. So instead of just starting an Automation when pts this week changes we have to ‘delay’ or account for a ‘delay’ before using our snapshot fields to overwrite our total points & attendance fields. :dizzy_face:

Let’s add two more fields:

• pts change:
  
• when to trigger automation: Formula = IF(AND({pts this week * 10}, DATETIME_DIFF({pts change},NOW(),'milliseconds')<0,DATETIME_FORMAT(TODAY(),'dddd')='Friday'),'Now', BLANK())

So now you should be realizing that scripting for loops are complicated and seemingly out of reach now, but these type of ‘hacks’ are not for the feint of heart either. So to unpack the Formula field from above: we need it end up saying ‘Now’ or staying empty, we also need it to cover some assumptions like that the data entry process has ‘settled down’ and that we are running this on ‘Friday’. You can adjust ‘Friday’ to be any day really. But noticed that it is hardcoded into this Formula.

Next set up a View:

Lastly, we create our Automation:

• Trigger: When a record enters a view so, you find the view you just created :point_up_2:
• Action: Update a record:
  

Welp, you’ll change the data throughout the week, everything will lay in waiting until the right conditions present themselves and then on whatever day you choose things will change. It’s ugly, but it’s hands-off.

**note: you can totally skip the delay,Now(),millisecond stuff if you always enter data on Friday and always run this on Sunday for instance… Just wanted to give you something to think about as you gradually start moving toward scripting…

[Justin_Barrett]

Bill_Felix:

Let’s add a few Formula fields:

• pts this week * 10 : Formula = IF({pts this week}>0,{pts this week}*10,BLANK())
• snapshot points : Formula = IF({pts this week * 10},SUM({total points},{pts this week * 10}),BLANK())
• snapshot attendance : Formula = IF({pts this week * 10},SUM(attendance,1),BLANK())

Right out of the gate we are saying that if pts this week is greater than zero then we do stuff, otherwise BLANK() just says leave things empty.

You can drop the BLANK() function off the end of all of those formulas. Airtable’s documentation doesn’t mention this, but you can omit the third argument in the IF() function. Doing so automatically returns nothing, leaving the field blank by default. Long story short, in every place where people tend to use BLANK(), there are ways of omitting it and getting the same result.

[Bill_Felix]

@Justin_Barrett I didn’t know that… thanks for the tip! :call_me_hand:

But what say you about the ‘readability’ of the Formula when that is the case? Most of my programming background says ‘never let the reader assume’, but yeah these Formulas can get annoyingly verbose in such a confined space that Airtable is practically begging us to take shortcuts whenever possible. I’m conflicted. :point_left: :flushed: :point_right:

[Justin_Barrett]

@Bill_Felix Admittedly I don’t have any formal programming training—I started playing with code on my own when I was a youth (decades ago)—so I tend to be a bit more relaxed about some CS rules/guidelines/what-have-you. :slightly_smiling_face:

When it comes to BLANK(), I feel that leaving it out improves readability simply from the angle of less stuff for my eyes to take in and process as I’m scanning the text, plus it’s one less thing that I have to type when building the formula.

For me it kinda falls into the same category as some of the optimizations that are used in JavaScript to see if something is “truthy” or “falsy”. For example, instead of literally comparing against an empty string:

if (myString === "") ...

this is more streamlined:

if (!myString) ...

Another example is using a multi-line if...else structure when a ternary operator will suffice in many situations, or building a for loop to process an array when it can be done in a single line with the .map() array method. Once you learn such optimizations, you rarely revert back to the long way, and I would argue that the BLANK()-omission shortcut is in a similar category.

[Bill_Felix]

These are excellent examples and I too use those streamlined versions. Ok, I’m sold, onward!