madman411 Posted September 16, 2015 Share Posted September 16, 2015 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 More sharing options...
madman411 Posted September 19, 2015 Author Share Posted September 19, 2015 Still really struggling with this one. Has anybody had success in achieving something similar? Link to comment Share on other sites More sharing options...
comment Posted September 19, 2015 Share Posted September 19, 2015 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. 2 Link to comment Share on other sites More sharing options...
madman411 Posted September 19, 2015 Author Share Posted September 19, 2015 (edited) 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 Link to comment Share on other sites More sharing options...
Ocean West Posted September 19, 2015 Share Posted September 19, 2015 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 More sharing options...
madman411 Posted September 19, 2015 Author Share Posted September 19, 2015 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 More sharing options...
comment Posted September 19, 2015 Share Posted September 19, 2015 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. Link to comment Share on other sites More sharing options...
madman411 Posted September 19, 2015 Author Share Posted September 19, 2015 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 More sharing options...
comment Posted September 20, 2015 Share Posted September 20, 2015 (edited) 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 2 Link to comment Share on other sites More sharing options...
LaRetta Posted September 20, 2015 Share Posted September 20, 2015 (edited) 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 1 Link to comment Share on other sites More sharing options...
madman411 Posted September 20, 2015 Author Share Posted September 20, 2015 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 More sharing options...
comment Posted September 20, 2015 Share Posted September 20, 2015 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 More sharing options...
madman411 Posted September 20, 2015 Author Share Posted September 20, 2015 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 More sharing options...
Recommended Posts
This topic is 3268 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 accountSign in
Already have an account? Sign in here.
Sign In Now