Hi, I'm trying to creat an order capture app ...

(Fer Calvano) #1

Hi, I’m trying to creat an order capture app with 2 tables:

20_salesordersheader.xlsx

-> orders 21_salesordersdetails.xlsx

-> orders details.

Both tables are linked by the field: “Orden_Numero” . Sales amount is loaded in the details table (field: “Monto_Facturado_ARS”), but I would like to have totals also in the orders tables.

I’m using following app formula, but the result displayed is cero…

SUM ( SELECT (21_salesordersdetails.xlsx[Monto_Facturado_ARS ],[Orden_Numero] = [_THISROW].[Orden_Numero] ))

Anybody knows what should I change in the formula?

Thanks! Fernando

(Aleksi Alkio) #2

The most powerful way to calculate the sum from the child table is… SUM(SELECT([VirtualListName][QuantityColumn],TRUE)). This won’t need to read the whole orderdetail table again because the order already “knows” it’s orderdetails.

(Suvrutt Gurjar) #3

Thank you @Aleksi_Alkio, as always your guiding input is much more efficient.

(S.A.T. GAME WORLD) #4

ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्ब्न्ब्ब्ब्ब्ब@Suvrutt_Gurjar @Suvrutt_Gurjar @Suvrutt_Gurjar @Suvrutt_Gurjar @Suvrutt_Gurjar

Translate

(Fer Calvano) #5

Thanks to both. It is now working OK!!

(Suvrutt Gurjar) #6

Hi @Fer_Calvano, As per my understanding, your expression appears correct. I am sure you are using correct table and field names as displayed in app editor.

I request you to go through following, just

to be doubly sure.

I presume the field, ,"“Monto_Facturado_ARS” is defined as column type price

in both the tables and

A) this field computes

the sales amount of each individual item in the order details table.

For example if there are five items in the Order details table related to one order then, there are 5 "“Monto_Facturado_ARS” values to add in expression you have mentioned.

B) In the Orders table the corresponding field "“Monto_Facturado_ARS”

is aggregate field for the entire order value of 5 items.