Total Amount Problem

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

2 Likes

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

1 Like

Thanks.
What do you mean by:

??

About the formula shown in your screenshot, I have two questions:

  1. “AND([ID]…” Is [ID] a column from Order Item table? I can’t see it on the right
  2. Why there is a hard coded Key? Just for testing?

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

1 Like

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

1 Like

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

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