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:
- item name
- stock quantity
Stock Receipt table has a list of stock receipts
Stock Receipt fields:
- stock receipt name (text)
- date (date)
- 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.