Subsummary in portal

[madman411]

Anyone achieved this? I want to add [a] line(s) to a portal and have it sub-total the lines above it. The portal has drag-n-drop functionality, so the calc needs to update if a related record is "dragged" above the sub-summary line.

I found the following article, but I'm having a hard time following it because the author is using fields in relationship joins that he never explains.

http://www.linearchat.co.uk/2011/05/sub-summarys-with-in-portals-pt-ii-may/

[madman411]

Still really struggling with this one. Has anybody had success in achieving something similar?

[comment]

1. It is not possible to "add lines" to a portal. A portal shows exactly one row for each related record.

2. If a portal is sorted by the breakfield, it can show subsummary values using a calculation field (defined in the child table) =

GetSummary ( SummaryField ; BreakField )

 A more sophisticated version:

Case ( 
BreakField ≠ GetNthRecord ( BreakField ; Get ( RecordNumber ) - 1 ) ;
GetSummary ( SummaryField ; BreakField )
)

will display the sub-summary value only in the first row of each group.

Note that this may be slow and thus not suitable for large counts of related records.

[madman411]

Thank you, comment. I will be testing this with my current configuration. 

I'm trying to use this in a quoting system - the orders aren't large. Perhaps 5 items tops.

Current setup is an 'Order' table linked to a 'LineItems' table by an OrderID key.

When you refer to the BreakField, what exactly is contained in that field? This is where I was also struggling with the original article - I'm not sure how his calculations were able to determine what related record was set to "subtotal". My current setup has the "ItemCode" within the LineItems table set to "SUB" which also sets a SubTotalFlag field to 1. This also controls my conditional formatting for that "subtotal" row. I also have a current 'sort' field which is currently how the portal is sorting itself when I perform a row drag and drop.

Edited September 19, 2015 by madman411

[Ocean West]

To make a faux sub summary portal the method is to use a virtual list technique that create the needed rows for display - however that would make it really for viewing or navigation, editing would need to be done in a popover or in a separate window/layout.

[madman411]

To make a faux sub summary portal the method is to use a virtual list technique that create the needed rows for display - however that would make it really for viewing or navigation, editing would need to be done in a popover or in a separate window/layout.

Hi Ocean West

This would only be for viewing - can you elaborate a little more? I'm not familiar with virtual lists. 

[comment]

When you refer to the BreakField, what exactly is contained in that field?

Breakfield is the field by which you want to group and sub-summarize your records.

[madman411]

Breakfield is the field by which you want to group and sub-summarize your records.

So they would all contain the same value, yes? One group of records would contain perhaps a "1", the next a "2" - I have tried this but my sub totals seem to factor in the other groups as well. Group "1" would subtotal itself correctly, but group "2" would also add the subtotal from group "1".

[comment]

So they would all contain the same value, yes?

Yes.

 

I have tried this but my sub totals seem to factor in the other groups as well.

Works fine for me:

SubSummaryInPortal.fp7

 

 

 

 

Edited September 20, 2015 by comment

[LaRetta]

I really like this, Michael!  Both the standard display in your sample and your example calculation which produces the result only on the first of each BreakField row are great techniques. Your second example seems a nice way to make break field separators in a portal as well.

Thank you for sharing it.  :-)

Edited September 20, 2015 by LaRetta

[madman411]

Thanks, comment. There was a step I overlooked that I discovered when reviewing your example file. Your example is a massive help.

I suppose the only other thing I would want your opinion on is re-generating the break field. Your example is a bit different from what the original article was describing. Using the Case() function I was able to get the subtotal down to display on the subtotal portal line only, and that works like a charm...

Case (
subFlag = 1 ; ( SummaryField ; BreakField)
)

The original article talked about being able to re-arrange portal rows and the subtotal calc and breakfield would update to reflect any new portal rows added/moved between a previous subtotal "line" and the next. He appeared to create two self joins to be able to do this (judging from the screen shots he posted). One for the sort and one for the subtotal calc itself. The following code is what he provided his readers which I'm assuming is what was supposed to generate and and recalculate what would be the break field:

Let ( [

L = List(QuoteLine_QUOTELINE__sort_calc::__kp_QUOTELINE);
p = PatternCount ( Left ( L ; Position ( L & "¶" ; __kp_QUOTELINE & "¶" ; 1 ; 1 )) ; "¶" );
s = List ( QuoteLine_QUOTELINE__sort_calc::d_Sort) ;
v = GetValue ( s ; p )

];

Case (
p = 0 ; 0 ;
v
)
)

I suppose my question is how could we modify your file to calculate the breakfield (or category field) instead of manually entering a value? In my file the drag and drop feature updates a sort field, so perhaps the breakfield needs to be able to reference that?

[comment]

The original article talked about ...

Sorry, I don't want to go there. You asked about subsummary in portal, and I answered with no reference to that (or any other) article. This answer should work for any breakfield, calculated or not. Of course, a calculated breakfield will make this even slower.

[madman411]

Sorry, I don't want to go there. You asked about subsummary in portal, and I answered with no reference to that (or any other) article. This answer should work for any breakfield, calculated or not. Of course, a calculated breakfield will make this even slower.

Thanks anyway