Jump to content

Subsummary in portal


madman411

This topic is 3085 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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/

Link to comment
Share on other sites

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.

  • Like 2
Link to comment
Share on other sites

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 by madman411
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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".

Link to comment
Share on other sites

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 by LaRetta
  • Like 1
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 3085 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.