Tricky calculation for summarization needed

[Rich S]

Hi all:

This is tricky. (See attached. Sorry, I can't upload the working file here because it's work-related.)

What you're looking at is a portal (LINE_ITEMS) in the parent table, ORDER. What we want to do is summarize by each order's related product codes and total the quantities of each per order, so in this example I want to summarize the total of PT's quantity (975) and W11's total quantity (975) on their own lines for this order ("John Smith"), so as a rough look it would look like:

John Smith

     PT......975

    W11.....975

 

I inserted Summary fields for Product Code (List of) and Quantity (Total of) in the LINE_ITEMS table, but I don't know how to tie it all together from ORDER. Any ideas?

 

Cheers,

Rich

[comment]

2 hours ago, Rich S said:

I want to summarize the total of PT's quantity (975) and W11's total quantity (975) on their own lines

What exactly do you mean by "on their own lines"? Your current portal shows 4 lines for 2 products. Do you want to retain the 4 lines and duplicate the summaries or show only 2 lines?

 

[Rich S]

Show only two lines.

[comment]

19 minutes ago, Rich S said:

Show only two lines.

OK. I can think of several ways to do this, but since you asked for a calculation let's go there.

The basic idea is to iterate over the child line items and add each quantity to a "bucket" associated with a specific product code. If the line items were sorted by the product code, you could fill a bucket until the product code changes, then start a new bucket. As it appears they are not, the task is more challenging and we will do this in two steps:

1. Create a JSON object where each bucket is represented by a name/value pair; this allows us to add each quantity to the correct bucket, whether it already exists or not;
2. Convert the JSON to a text where each bucket is a line containing the product code and the accumulated quantity, separated by a tab character.

While ( [
n = Count ( LINE_ITEMS::OrderID ) ;
json = "{}"
] ; 
n ; 
[
key = GetNthRecord ( LINE_ITEMS::Product Code ; n ) ; 
qty = GetNthRecord ( LINE_ITEMS::Qty ; n ) ; 
json = JSONSetElement ( json ; key ; JSONGetElement ( json ; key ) + qty ; 2 ) ;
n = n - 1
] ;
While ( [
keys = JSONListKeys ( json ; "" ) ;
n = ValueCount ( keys ) ; 
result = "" 
] ;
n ;
[
key = GetValue ( keys ; n ) ;
value = JSONGetElement ( json ; key ) ; 
result =  List ( key & Char (9) & value; result ) ; 
n = n - 1
] ;
result
)
)

Keep in mind that the result here is Text.

The other way (or rather ways) to tackle this is to produce a portal that shows only two lines (in the current example). This can be done either by filtering a portal to the LINE_ITEMS table (see Getting More Out of Filtered Portals: 3.Unique Values) or by showing a portal to the Products table. Both of these methods require adding another occurrence of the LINE_ITEMS table to the relationship graph.

For completeness, I will also mention 2 older methods:

• Instead of filtering the portal you could use a technique known here as the "Ugo method" for de-duping a portal;
• You could produce the text summary by script using the Fast Summaries method by Mikhail Edoshin.