# Re: Update attendance count and points with automated script

1662 3
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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.

5 Replies 5
6 - Interface Innovator

Welcome @Kaleb_Penner1!

: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:

• `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…

18 - Pluto

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.

6 - Interface Innovator

@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:

18 - Pluto

@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.

6 - Interface Innovator

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