Hi all, I have a problem on how to count total amount in order, where we have many related items.
When we add another item to the order, It automatically adds another row in Google Sheet, same unique order ID.
It sounds like you just have 1 sheet for the orders and items. I might have missed this in what you wrote but you should have a table for the selectable items (Table Item) and then another table for the order (Table Orders) and then another table for the items attached to that order (Table Order Items). On Table Order Items, it could just be three columns (Assuming that you want to add multiple of the same item to the same order). A column for a key, a column for the order key, and a column for the item key.
Then you will be able to sum up all of the items for that order from the Order Items table.
You might review this: References Between Tables | AppSheet Help Center
Thank you, I have 3 sheet, once is Order, second is Order Item and third is Item.
When I add new related item in order with price, quantity and amount everything is ok, but I want total amount โlast priceโ for invoice, where we have items value.
I got lost right here.
Could you post some screenshots from the tables schemas?
If you are on windows you can use WIN+Shift+S and just copy that here
Mac: Command+Shift+4
Thanks.
What do you mean by:
??
About the formula shown in your screenshot, I have two questions:
Also, if you have columns referencing each other, you should be able to sum the values using a list dereference using the [Related something] column on your Parent table
My formula is wrong, because I need help.
ID column is only Item and Order sheet.
In order item sheet we have order item ID, Which is automaticaly.
When I add items in order, we have different order items id and same order id, itโs ok.
but I want to make dynamic process for total amount.
โcf99c9a6โ works in formula and I take result, but I want dynamic automatical process
Got it.
The column [ID] after AND( needs to be replaced by the one that has the ID from the parent.
Example:
Table โOrdersโ is Parent. Table โOrder Itemsโ the child.
Order has an [ID] column as the key column.
Order Items has [Order ID] column that is ref to Orders table.
You should point the expression as:
[Order ID]=[_THISROW].[ID]
Assuming you are doing all of this from the โOrdersโ table
Hope itโs clear for you
Assuming that โPreliminary invoiceโ is a column from Order table, please share a screenshot of the Order table in order to see were the ID of that table is.
Thanks
Not proud to say it, but we still experience the same problem - no result after too many atempts
So our formula goes like that:
sum(select(Order Item[Amount], AND([ID] = [_THISROW].[ID], IN([Order ID], {โ404af464โ}))))
The expression works for an unique Order ID only - that is โ404af464โ
How to make it dynamic, i.e. changing from row to row?
We have just copied that unique ID โ404af464โ from a Google Spreadsheet. It worked
The 3rd day of bumping into wall
User | Count |
---|---|
62 | |
25 | |
14 | |
11 | |
6 |