Hope you are ready for some newbie questions ;)

[Tor_Martin_Norv]

Hello Airtable community. I am a designer whose brain is hurting after struggling to get a decent grip on building dynamic databases in Airtable. I have a lot of questions, maybe you have some of the answers?

This is my Airtable: Airtable - Hodebra aktiviteter

Q1: How can I make sure that any tasks that are marked complete (logget in Norwegian), are reset to incomplete every night (Ikke logget in Norwegian). I have tried to schedule automation, but I can’t find out how to describe the record ID, when using “changing a record” as action. I would like to change ALL the records that has been completed back to incomplete.

Q2: How can I summarize the values in a specific field (Score aktivitet) based on the field “Device.ID” In other words, how can I calculate how many points the user (Device.ID) has in “Score akivitet”, “Score sammen” and “Score meningsfylt”?

Q3: How can I track which task is most completed? In my Airtable, I have several views. The first view “Alle aktiviteter” should only show tasks that are not complete. That I could do with filters. But how can I count every time a task is being completed? And by that way know which task is completed the most times? Can I do this with automation? When a record comes into view (from “incomplete activities” to “complete activities” view, have some sort of counter that adds +1 to the “times this activity has been completed”?

I start with these questions :slightly_smiling_face: Hopefully some Airtable wizards can help me on my way to understand the possibilities of this intriguing piece of software.

Regards,
Tor Martin

[Databaser]

Hi @Tor_Martin_Norvik and welcome to the community!

Tor_Martin_Norvik:

Q1: How can I make sure that any tasks that are marked complete (logget in Norwegian), are reset to incomplete every night (Ikke logget in Norwegian). I have tried to schedule automation, but I can’t find out how to describe the record ID, when using “changing a record” as action. I would like to change ALL the records that has been completed back to incomplete.

When using “update record” as an action, use the “+” under “record ID”, choose “from step 1” (don’t forget to “run test” your trigger step) and just insert “record ID”.

Tor_Martin_Norvik:

Q2: How can I summarize the values in a specific field (Score aktivitet) based on the field “Device.ID” In other words, how can I calculate how many points the user (Device.ID) has in “Score akivitet”, “Score sammen” and “Score meningsfylt”?

Have you tried “grouping” on “Device.ID”? Then the summary will not only show at the bottom (which you already have), but also per group.

Tor_Martin_Norvik:

Q3: How can I track which task is most completed? In my Airtable, I have several views. The first view “Alle aktiviteter” should only show tasks that are not complete. That I could do with filters. But how can I count every time a task is being completed? And by that way know which task is completed the most times? Can I do this with automation? When a record comes into view (from “incomplete activities” to “complete activities” view, have some sort of counter that adds +1 to the “times this activity has been completed”?

If you use the same record every time (incomplete > complete > incomplete > …), you could use a number field that starts with 0, in combination with a formula field that adds “+1” every time you status
equals “complete”. Use an automation “when record enters view” (and make a view with filter "where status is “complete”) to update your number field with the new number in you formula field.

[Tor_Martin_Norv]

Thanks @Databaser for your answers!

Databaser:

When using “update record” as an action, use the “+” under “record ID”, choose “from step 1” (don’t forget to “run test” your trigger step) and just insert “record ID”.

I am trying to do this with a “At a schedule time” trigger. So it starts an action every night.

But when using the use the “+” under “record ID”, I cant seem to have the ability to choose “Record ID”

Databaser:

Have you tried “grouping” on “Device.ID”? Then the summary will not only show at the bottom (which you already have), but also per group.

Yes. This is great, but how can I get the summary through Airtable API? This database is for a mobile app, and I want to have a GET Request that is able to get hold of the sum.

Databaser:

If you use the same record every time (incomplete > complete > incomplete > …), you could use a number field that starts with 0, in combination with a formula field that adds “+1” every time you status
equals “complete”. Use an automation “when record enters view” (and make a view with filter "where status is “complete”) to update your number field with the new number in you formula field.

This is a great tips. But I am not sure if this suits my app logic. Or maybe I am complicating it… My app should have a list of 10 tasks. The 10 tasks are the same for every user. When a user completed his task, the task is set to “completed”. But the task reset during the night back to “incomplete”.

What I think my challenge is. When a user completes a task, will the “completed” task get a new Record.ID? Since I need to personalize the incomplete/complete tasks for every user by using Device.ID. Sorry if this is a bunch of rambling, but I find this sort of things a bit tricky. If you know about Bravo, you can also check out my Figma here: Figma which is the design for the app. Maybe its easier to understand what I am talking about by looking at the actual design of the app :slightly_smiling_face:

Thanks again for your insight into this matter :thumbs_up:

[Databaser]

Tor_Martin_Norvik:

Databaser:

When using “update record” as an action, use the “+” under “record ID”, choose “from step 1” (don’t forget to “run test” your trigger step) and just insert “record ID”.

I am trying to do this with a “At a schedule time” trigger. So it starts an action every night.

Ah, then you will have to add an “find records” action in between where you filter which records the automation should find (eg: where “task” is “complete”). Then you can use the “update record” action, where you use the ID’s from the records found in step 2.

---

Tor_Martin_Norvik:

Databaser:

Have you tried “grouping” on “Device.ID”? Then the summary will not only show at the bottom (which you already have), but also per group.

Yes. This is great, but how can I get the summary through Airtable API? This database is for a mobile app, and I want to have a GET Request that is able to get hold of the sum.

I’m not an API man, so can’t answer that question.

Tor_Martin_Norvik:

This is a great tips. But I am not sure if this suits my app logic. Or maybe I am complicating it… My app should have a list of 10 tasks. The 10 tasks are the same for every user. When a user completed his task, the task is set to “completed”. But the task reset during the night back to “incomplete”.

Hmm, I can only stick to Airtable, but when you would use 10 task per user instead of 10 tasks for all, you could use automations to create them when you add a new user and an time triggerd automation that resets all the “completes” to “incomplets”.

Not quite sure if this helps you enough, but maybe smarter people then me will also answer :slightly_smiling_face:

[Tor_Martin_Norv]

Databaser:

Ah, then you will have to add an “find records” action in between where you filter which records the automation should find (eg: where “task” is “complete”). Then you can use the “update record” action, where you use the ID’s from the records found in step 2.

Close, but no cigar yet :slightly_smiling_face: Guess I am missing a step.
The “Find records” works great. It is able to find all the records that are complete (Logget):

If I have 4 records that should be reset from “complete” to “incomplete”, I get this problem:

But the “update record” only worked when there was just one “complete” record to reset.

Do you know why this is happening? I appreciate your guidance :winking_face:

[Databaser]

Tor_Martin_Norvik:

Do you know why this is happening? I appreciate your guidance :winking_face:

Hmm, I’m getting the same mistake. @ScottWorld , what are we doing wrong here? :slightly_smiling_face:

[ScottWorld]

Unfortunately, the “Find Records” action in Airtable’s Automations doesn’t function how most of us would have expected it to function. :cold_sweat:

The “Find Records” action only returns an array of Record ID’s, but Airtable won’t loop through those record ID’s to take individual actions on those individual records.

The only thing it does is give us a list of Record ID’s which we can then paste (as a group of ID’s) into a linked record field or pass into a JavaScript for looping there. That’s about it. (We can’t separate out the individual ID’s nor loop through the individual ID’s.)

Off the top of my head, I can’t really think of what else we could do with those Record ID’s, although I’m sure there’s something that I’m not thinking of.

In my personal opinion, it’s a real shame that Airtable implemented it in this way, and it should be improved. I would highly recommend emailing support@airtable.com to request that they improve this.

So if you want to update the value of individual records, you can’t use the “Find Records” action to find those records. I know, it makes no sense at all. :man_shrugging:

(Some might say it’s essentially useless, although I could see some use cases for pasting a list of Record ID’s into a linked record field).

So, unfortunately, these would be your only options:

• Use “Find Records” and then trigger your own custom JavaScript to loop through those Record ID’s and take individual actions on those records.
• Create an automation that doesn’t incorporate “find records”. This would trigger on one record at a time.
• In my personal opinion, the best and easiest low-code/no-code way of handling automations is to use the professional automation platform Integromat. Integromat has NONE of the limitations of Airtable’s automations, and many people can get away with their free pricing plan, so it won’t even cost you any extra money. (Note that I am a professional Airtable consultant and a Registered Integromat Partner, and the Integromat link contains my personal referral code.)

But I would urge everyone to email support@airtable.com to ask them about improving the Automations to improve the handling of “Find Records” in Automations.