Automation confusion

[Scott_Brasted]

I am trying to learn to use automations to update 2 summary tables so I do not have to keep copying and pasting every time I update a table.

Table for new record:  Donations.  Summary Table: Donation Totals Yearly Summary Report.

The two table are linked by the Donation Date primary field in the Donations table. The summary table linked field is called Donation Table.

Let me summarize what I think I need to do to accomplish this. I need to create a trigger that says When a Record is Created in the Donations table. This tests fine.

Then when I create that record in the donations table I need to have an action happen. I have chosen the Update Record in the action. That action is to update the Donation Totals Yearly Summary Report table/Donation Table field with the contents of the Donation Date field in the Donations table.

To do that I need to choose the Update record choice in the Action type field of the automations screen. Then choose the Table in the configuration section. I have chosen the Donation Totals Yearly Summary Report table.

Then it asks for the Record ID. This is where I am stuck. I do not understand what it is looking for. I have tried copying the field URL for the field in Donations and the field URL for the field in the summary report.Neither works. I have also tried to copy the record id from th trigger test. That does not wrk either. What am I missing?

I find this very frustrating. It cannot be this hard.  Can anyone provide a little clarity here? 

Thanks, Scott

[Alexey_Gusev]

Hi,

Try to clarify your goal. You have Donations table with X records  and Totals table with Y records.
You Create new record in Donations. Airtable needs to know which of Y records you want to Update in Totals. So, it asks for record ID.

indeed, 'When a Record is Created' is rarely used in real life, because it runs right after record created,  and it is empty yet. Anything you put in record after that, will not affect other data, because automation considered as run and completed.

In Airtable, each table is not a spreadsheet with rows and columns. To better understand, consider each table as a set of records (each row is a record). Each table contains list of similar entities and links define how they related to each other. Like if you have football tournament, you'll have a list of players, list of teams and list of games. Each player linked to the team, each game linked to 2 records in teams. That's how it works. (someone could create table of players for each team, but it's bad design - similar entities (players in that case) should be in the same table)



If you want to get Totals in other table, you should use core built-in functionality - linking records, lookups, rollups. It's much easier than do it by automations, when you understand how it is working (understanding is not easy, but once you get it, you will see other Airtable stuff under the right angle)

To be less confused when learning automatics, try to do something within the same table

The task like 'When I input something in Table 1, do the action in table 2" usually demand some kind of connection between records via linking or action like 'Find records' . Like 'When I type something in text field, for example "cat" then click on a checkbox. go to Table 2 and search all records having "cat" in their text field, and do something with them'

[TheTimeSavingCo · Accepted solution]

Does this look right to you?

If so, check out this base

To set it up, you'll have an automation that triggers when the 'Donation date' field is not empty.  (Whether this trigger makes sense depends on how your data gets keyed in though, so lemme know if this bit gives you issues where the automation is triggering too early or too often or something)

Then you'll use the Update Record action you mentioned on the 'Donations' table and put in the record ID of the triggering record:

In the linked field to the Donation Totals Yearly Summary Report table, we put in the 'Year of donation' field value, which outputs the year value of the 'Donation date' field' with the following formula:

YEAR({Donation Date})

And this is it working:

[Scott_Brasted]

Hello, and thank you for your very kind answer and demonstration. I am sorry to be so long in answering. I was out of the country and away from communications. So I have been looking at your explanation. It has taken me awhile but I think I get what you are saying. All the work is done no the donations table. Because the donations table and the summary table are linked, when the donations table is updated the summary tables automatically updates too?

Because when I look at the automation in your sample base, I only see references to the donations table and not the summary table. It is a little confusing not to have to explicitly name the summary table and fields. Am I on the right track?

I spent years developing Access DBs. So I am having trouble leaving that world behind. I am probably overthinking this. I'm gong to try your idea tonight.

Thank, Scott

[TheTimeSavingCo · Accepted solution]

re: All the work is done no the donations table. Because the donations table and the summary table are linked, when the donations table is updated the summary tables automatically updates too?

Yeap that's right

---

re: Because when I look at the automation in your sample base, I only see references to the donations table and not the summary table. It is a little confusing not to have to explicitly name the summary table and fields. Am I on the right track?

Yeap you're on the right track!  We're updating the Donations table to create the link to the Summary table

[Scott_Brasted]

Well, now it makes sense. It is working on both sides with one small idiosyncrasy. The Donations table has the Donation Date (date field) as the primary field. The summary table has Year (single line text) as the primary field. Rightnow it is requiring me to add the year to the primary field manualy. Is it possible to have the automation add the new year every year? IS this clear? Thanks, Scott

[Alexey_Gusev · Accepted solution]

Hi,
Actually, you don't need to add year manually
Each time you are trying to put value in a linked field, at first at tries to link to a record with that value.

If it cannot find that, it will auto-create a new record with that value in primary field.
Such behavior can be used for many purposes: find duplicates and their number, get a list of unique values, create pivot table etc.
Imagine you have only Donations table. You create new linked field and choose 'to a new table'. Field created and empty. Then you can copy-paste whole column 'Year of donation' into linked field. When you switch to the new created table, you will see it populated will unique list of all years you had just pasted in field.
Note: auto-creation not works if you make primary field a formula

[Scott_Brasted]

Many Thanks!

[Alexey_Gusev]

Important Note: Values with commas or ampersands can cause issues as they're interpreted as multiple entries. To avoid this, wrap these values in quotation marks before inserting them.

I think, it's better to show than explain.

The problem:

Possible solution: