Discuse what is the best solution for setting formula in Inventory Dashboard

[hien_nguyen]

Hi all,
I have a dashboard for checking inventory base in the filter form for chosing period days and warehouse.

I have tried many way for imporving calcultion and syncing speed but it seem not be good for me.

• If I set formulas for columns Last Qty’, Purchase Qty’, Sale Qty’ and Stock Qty’, the app syncing slowl with function Sum(Select(…,and(in(…),…).
• If I set formulas in Spreadsheet, It helps my app starting and syncing faster but results be deplayed and only show after syncing the app.
• If I set formulas in the action, I only set value for one rows, the resut return very quick but I can not set for all rows because appsheet need moretime to run all action with loops.
• If I set formula in the app formula. Appsheet cannot calculate and return to result if I didn’t edit the rows.
  So How to we set formulars for in inventory table for return result imediately after filter in slice form and it doesn’t make the app running poor performance?

Please share me your experience.

Thanks

[MultiTech]

hien_nguyen:

If I set formulas for columns Last Qty’, Purchase Qty’, Sale Qty’ and Stock Qty’, the app syncing slowl with function Sum(Select(…,and(in(…),…).

There’s your problem - you’re using brute-force SELECT() statements.

• How many of these do you have throughout your app?

---

By and large, the AppSheet community is missing these two essential skills:

• Data Subsets

  • using slices to store partial results of SELECT() formulas, and REF_ROWS() to get specific lists
    • think: Ref_Rows("Open_Purchase_Order_Slice", "Purchase_Order_Parent_Link")
    • where ‘Open_Purchase_Order_Slice’ is a slice of the Purchase_Orders table, but only those that are open.
      - When I use this inside a Ref_Rows() on the ‘Parent Table’ (whatever that table may be), it gives me essentially the same result of a SELECT(PurchaseOrders[PurchaseOrderID], and([Purchase_Order_Parent_Link] = [_thisrow].[Parent_Table_ID], [Purchase_Order_Status] = "Open")), but without the brute-force.

• List Dereferences

  • to get derivative lists from Ref_Rows() efficiently
  • List Dereference | AppSheet Help Center
    • SUM([Related PurchaseOrders][Purchase_Order_Total])
      is the same as
      SELECT(PurchaseOrders[Purchase_Order_Total], [Purchase_Order_Parent_Link] = [_ThisRow].[Parent_Table_ID])
      - … but without the brute force

[hien_nguyen]

My list of product have more than thousand products.
So I have to set a virtual or real column to calculate total purchase qty’, total sale qty’, total stock for them every period time based in on filter form. With Ref_row and sum([parentid].[Qty]) I only count total for product. So if I want to check qty for selectes period times. I have to use SELECT or formula in Spread sheet.

Vào 22:15, T.2, 27 Th12, 2021 MultiTech_Visions via AppSheet Creator Community <appsheet@discoursemail.com> đã viết:

[MultiTech]

If you’ve connected your “Purchase_Order_Line_Item” table to the “Products” table via a reference:

• then on the Products table you’ll have a reverse reference: [Related Purchase_Order_Line_items]

From this you can list de-reference the totals, and sum them all together.

hien_nguyen:

With Ref_row and sum([parentid].[Qty]) I only count total for product. So if I want to check qty for selectes period times. I have to use SELECT or formula in Spread sheet.

• Create a table to hold this sort of information

Either you’re wanting to do a report, so you’d create a Report table in this instance, or you’re doing something like Weekly/Monthly/Yearly inventory, in which case you’d create a corresponding table for that.

• Doing it this way allows you to have a “Parent” level that’s specific to the data you’re entering.