automation run a script to collect sum of previous values in a cell

[Ella]

Hello everyone,

I would like to run an automation to keep track of the previous values of a cell so that I can see how inventory is being withdrawn from the database and have a correct running tally of inventory items.

Currently my airtable is set up like this.

The quantity removed field is constantly updated as product is removed. I would like to have the sum or some record of all the values ever written into this field in the previously removed field. I have tried formulas but I keep running into circular references. Therefore I am trying to create an automation which will add the new the new value entered to the quantity removed field to the previously removed field value.

For example: on the first day Quantity removed is entered from softr and now has the value 15 for a particular product. Therefore previously removed should be 15. 

On the second day another entry is made to the Quantity removed (value 20). Now the previously removed value should be 35.

This must all happen to the same record because the original lot code must be the same.

This is the code I am using:

const newRecord = input.config();

console.log(newRecord); // Verify the structure of newRecord in the console

const previousValue = newRecord["Previously removed product"];

const currentValue = newRecord["Quantity Removed from Freezer"];

if (previousValue !== currentValue) {

  newRecord["Previously removed product"] = previousValue + currentValue;

}

output.set("record", newRecord);

If anyone thinks they can point out my errors or point me in the right direction, I would appreciate it so much.

Thanks in advance!

[Abi_arvi]

Hello,

Sorry, I Didn't get you. So here you want to update the removed value? Based on the what's? 

 Explain little bit clear or send me the screen shot of the form and table

[Ella]

Hi,

Thanks for replying!

I have a number field {Quantity Removed from Freezer} that will be edited frequently (a few times a week) and I need to keep a running tally of all the numbers that were ever entered in that field. The same record will be edited instead of creating a new record because I need to keep track of items by lot code. The user interface is softr.  I have included screenshots of the formulas I have tried in airtable but they didn't do what I need so I am trying to automate it.

I might have to add another table to keep track of all the edits to {Quantity Removed from Freezer}.

[TheTimeSavingCo · Accepted solution]

Yeah, you're going to need another table I'm afraid. 

1. Create a new table to track the edits
2. Create a linked field between that table and your current table
3. Create a new checkbox field called "Log change" or something
4. Create an automation that'll run every time "Log Change" is updated, and its action will be to create a new record in the table you created in step 1, log the number that was removed, and link itself to the triggering record
5. In your current table, create a rollup field with "SUM(values)" to sum up the total amount that was removed

[Ella]

Thanks @TheTimeSavingCo  that is really helpful!! I will try that.