Automation triggers new record to be created, but what about record updates?

[Jazmyn_Beauchan]

Hello!

Im an airtable newbie with very light coding experience. Having several challenges setting up the bases for my team, but there’s one in particular that I cant seem to figure out.

I’ve created an automation that triggers record B to be created on table B whenever a new record (record A) enters the view on table A.
Table B’s purpose is to aggregate information from table A and table C and provide a top level view of many different scheduled communications. The problem that I have now, however, is that whenever there are updates to record A or C—like a date change, for example—the updates arent passed along to record B. I tried to create an automation triggered when record A is updated, but am unable to link it to record B.
Are any of the below possible through a script or automation?
1- when record A is updated, the now outdated, record B is deleted and a new, updated, record B is created
2- when record A is updated a new record B is created, script runs to remove older record by searching for duplicated names
3- when record A is updated, record B is updated

Any help or insight is appreciated!

[kuovonne]

Welcome to the Airtable community!

I’d go with option 3 and no coding.

Link the two tables with linked record fields. When you create a record in table B, link it to the triggering record in table A. This will create a backlink in record A, so that record A now knows the record id of the corresponding record in table B. Then when record A is updated, use the record id in the linked record field to identify the record in table B that also needs updating. No scripting required.

You don’t provide enough information about the records in table C to determine how to push updates from table C to table B, but it can probably be done with scripting.

[Angel_Baena]

Thanks Kuovonne, that’s a nice approach for us newbies.

There is something so far I couldn’t figure out: how to modify a numerical field depending on the value it previously had?

I’m trying to update a stock table, adding or substracting items but I’m not able to get the previous value into the math equation. Any tips would be highly appreciated.

Thanks a lot!

[kuovonne]

Once a values changes in Airtable, the only way to access the previous values is for a human to look in the record history. If you need access to the previous value, you need a different system. Sometimes this means having another field to store the previous value and determining the logic/code for maintaining it. Sometimes this means moving the data to a linked record field with a new table.

[Angel_Baena]

Thank you that’s a good approach, but I apologize I didn’t express myself properly:

I didn’t mean previuous value, but current value.

new quantity = current quantity + difference

Thanks a lot,

Angel

[kuovonne]

Can you provide a series of screen captures that show where the new quantity, current quantity, and difference are input, stored, and copied?

[Angel_Baena]

Thank you kuovonne,

This is the stock table (“Inventario”), field “Unidades” is current stock:

This is the input table (“Movimientos”), field “Unidades” is the quantity to add or substract from “Inventario/Unidades” field, and “Vino” is the link to the item in “Inventario” to be modified.

I hope I could explain better now, if you need anything else please let me know.

Thanks a lot!!

Angel

[kuovonne]

Thank you for the screen shots. Which table is the table A in your original post?

In the [Movimientos] table, do you create a new record each time there is an amount to be added? Or are you updating an existing record in the [Movimientos] table?

If you are updating an existing record in the [Movimientos] table, that record is already linked to the record in [Inventario], right? There is also only one record in [Movimientos] every every record in [Inventario]? In the [Inventario] table, include a lookup field of the {Cantidad} and a formula field that adds {Unidades} to the looked up {Catidad}.

Then have an automation that

1. Runs after {Cantidad} is updated and the lookup and formula fields have time to recalculate.
2. Identifies the record in the [Inventario] table by the record ID from the {Vino} field in the [Movimientos].
3. For the [Inventario] record, copies the formula field with the sum to the {Unidades} field.
4. For the original record in the [Movimientos] table, resets the {Cantidad} to zero.

However, updating the quantity in one table based on a linked record seems a bit strange to me. It is a bit more common to have a new record every time there is an amount to be added or subtracted, then a rollup field would calculate the current sum. Of course, your base may have other constraints that prevent that design.

[Angel_Baena]

Thank you Kuovonne,

The original post is not mine, but I replied because I found it related.

As you say, [Movimientos] item is not updated but created a new record each time.

[kuovonne]

Ah, if a new record is created each time, that requires a different approach. Is the new record linked already? If so, use a rollup field to sum the values across all the linked records to determine the current total.