Hello, I am creating an app for inventory ma...

references
(Apex Hub) #1

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?

(Marc Dillon) #2

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])