Hi guys and fellow airtable experts. I am creating an inventory system in airtable with a stock receipt table that will record any changes to my main table's item stocks. It can be additions if I receive goods and subtractions if I sell away the goods.
Main table has a list of items.
Main table fields:
Stock Receipt table has a list of stock receipts
Stock Receipt fields:
stock receipt name (text)
item name (linked to main table's item name)
quantity adjusted (number, can be pos+ or neg-)
How do I make it so that when a stock receipt is created, airtable automatically compares the stock receipt entry and updates the main table's quantity for that item?
E.g. Stock of item was 10. A stock receipt was created with quantity of" -1". new updated stock of item is now 9.
Given that the two tables are already linked, you can add a rollup field in the "Main Table" that rolls up the "Quantity Adjusted" field in the "Stock Receipt" table, with the formula of `SUM(values)`, and it should do what you're looking for I believe
Thank you for the suggestion. This sounds very taxing and resource intense as every time, the system will have to do a sum of all fields which can hundreds of records. Is there a way for a field value to add/ subtract field a field in another table? So everything, a record is added, it updates the linked record in another field. No need to validate the whole list of records.