Hello,
I am creating an app for inventory management. Currently, I have 2 tables.
I have a table called “Books Stock” which contains 2 columns. The first column is called “Book Name”, and the second “Book Amount”. This is a read only table which has rows with every book as well as the amount of books we have.
The second table is called “Purchased Books” which is a table for entries. It contains the columns; Students Name, Book Purchased, Date Purchased
Whenever a student buys a book, an entry is made for the “Purchased Books” table in order to keep a record. The “Book Purchased” column is a reference to the “Book Stock” table and is uses a drop down in order to select the book which was purchased.
What I am trying to achieve is that whenever an entry is made in the “Purchased Books” column for a certain book, the “Book Amount” value is decreased by one. Essentially, whenever a book is purchased it will automatically update the stock so that it always shows the accurate amount of books I currently have in stock.
How would I be able to do this?
Because you made the reference from Purchased to Stock table, your Stock table should have an automatically generated Virtual Column titled “Related Purchased Books”. You should create a new Virtual Column that will contain your actual Stock Amount. Use the app formula: [Book Amount] - COUNT([Related Purchased Books])
User | Count |
---|---|
61 | |
25 | |
14 | |
11 | |
6 |