Hi everyone, my first post here. Not sure if I will be able to describe my scenario clear enough but I will give it a try:)
I have a custom step form (using a helper table and its detail screen) writing to 2 different Glide tables called Orders and Line Items. The line item table has a specific product on each row, including a column for qty. It also has a relation column to a Products table which I use to get price, stock qty for each product etc.
When completing the order form I would like to give the user an option to choose whether the qty for each line item in the order should be deducted from the stock qty in the Products table or not. Put simply, I would like each completed order to also update my inventory. But I can not figure out how to do it
How can I get the relation to Product table to work from my helper table? Or will it never work since the relation from each line item can have multiple product related to it?
Sorry if this is very unclear, and I am very grateful for any input and help with this.
I would include a boolean column in the Line Items table, and set that to true if that line item should be deducted from the Product Inventory. You can then add an if-then-else column that returns the quantity if the boolean column is checked. Then you can rollup that column via a relation from your Products table to determine a total.
Ah, of course. So just to make sure that I understand you correct:
Add a relation column in the Products table to the related Line Item records. Then a rollup column in Products that shows the sum of the qty column for all related Line Item records. Then use this rollup column together with some math column in the Product table to calculate the current stock.
Something like that? Thank you so much for taking your time to help me!
This one would actually be a sum of the if-then-else column, because that column will only have values for those line items that should affect stock count.
Yes of course, thank you so much! May I also ask if there is a better way to handle the “Add items to order” part in terms of UX instead of what I am doing now? At the moment I pick products from a Choice component, set the desired qty and then use a button that Adds a row to the Line Item table. Please see screenshot attached.
You are right. I use a custom step form for this since I have some earlier steps in the process writing to other tables where they can add new customers, make some other choices for the order etc. Is there a way to reduce the needed updates if I stick with the helper table and the custom form? Or would I have to redo it and use a form screen instead.
Difficult to say without actually seeing your setup in front of me, but probably not.
I see Custom Forms as a bit of a trade-off. They do allow you to create additional functionality that isn’t possible with native forms, but the trade-off is there a cost to pay in the form of additional updates. So whether or not to use them basically comes down to a business decision - does the need for the enhanced functionality justify the additional cost?
