Hello there, rookie here. I have an Order Capture app (based on the Order Capture How to app) with a table “Catalogue” of items we hire out columns “Name”, “Item Id”, “Price”, “Stock” etc. A table of “Customers”, “New Hires/Orders”, with “Order Details” and slices for “Open” and “Completed Orders”.
In the Catalogue table I have a virtual column called “Related Order Details” REF_ROWS(“Order Details”, “Item Id”) and I set up another virtual column “Stock Calculation” with formula [Stock] - COUNT([Related Order Details]), which drops the “Stock” of the “Item Id” that I add to an “Open Order”. This works fine but I’m struggling to find a way to reset the “Item Id” stock to its original value once the Order becomes Complete and the item is returned.
At the moment the only workaround I managed to find is to have the “Stock” column (name displayed “Restock”) as a stepper number, find each Item Id that was hired and restock it manually. That sets the “Stock Calculation” to the right value but I’d like to know if there is a way to automatically restock an item without having to delete the order and related details once the order becomes Complete? I would like to be able to keep a record of all the Hires we have.
Thank you and apologies for the poem, I’m pretty new to all this and don’t know how to word things efficiently