Hello,
I have a table that shows the following column:
The quantity however derived and is a summation of the following tables:
I would like the quantity sum to be equals to the sum of the quantity from both tables with the largest step number, i.e 37+31 (step 40 quantity + step30 quantity)
How do i go about showing this quantity sum in a table? Thank you
Why not create two aggregates that are sorted on Step Number (descending) and only retrieve one record (that will be the highest step number) and then sum them?
You can also create an Advanced SQL that will do this in one step. A bit more efficient and quicker but also a lot harder to create and maintain. If the table doesn't involve thousands of visible rows then two aggregates might be a viable solution.
Edit:
As for the implementation. I would create a Web Block that takes the SalesOrderId an input. This will perform the 2 queries and the calculation. It will then display the value in an expression which would be the only element on the canvas.
In the table I would add the column "Quantity Sum", drag the Web Block into this column and configure the input parameter.
Hope this helps,
Vincent
Hello, Thank you for your response.
The example that i mentioned above is a simplified version of my actual problem. I might have multiple of those tables that I would need to sum the quantity, not only 2 tables, so having 2 aggregates will not help. I was thinking along of the lines of using a data action/client action to replace the expression in the quantitysum but im not sure if that will even work. Else I would need to use sql method like you suggested. Thank you!
Hi Low Nico,
In the first screenshot, which table you are referring to? is that database table or table UI widget?
Inorder to get the largest step number , you can apply a sorting in the aggregate with step number attribute in a DESC order and max records as 1.
follow the same for the second aggregate as well.
Now prepare a structure with ID and QuantitySum attributes. Create a local variable with list of created structure data type.
Now append those aggregate results to this list variable.
Local list variable will be the source for the Table UI widget.
Hope it helps.