9 Replies Latest reply on Jul 3, 2014 7:51 AM by beverly

    Summary Field for Portal

    tm9

      Hi everyone,

       

      I have a layout for browsing accounts and "Account" as the table. In this layout I have a portal from a "Transactions" Table that is related to the Account table via the KP for each account.

       

      The portal is set to be sorted by month & year by changing a global variable (in a drop down menu). The sorting works great!

       

      The problem is that as the portal changes when I change the month desired to view, I need to have a total $ amount for each months transactions that I can view on that layout. I thought I knew how to do this but I cannot get it to work.

       

      Each record in the transaction table has a field with the month/year. There are also two different types of transactions. One is "In" and one is "Out." I have a portal for each kind of transaction on the layout. I need totals for both portals as I change the month/year. I am using the format 2014_06 as the naming convention for months/years.

       

      Thanks for the help!

        • 1. Re: Summary Field for Portal
          Mike_Mitchell

          If you're using filtered portals (and, based on your description of using a global variable instead of a field, I surmise that you are), then you won't be able to use a typical calculation involving Sum. The reason for this is aggregate functions operate on the underlying relationship and ignore portal filters (which are only a display device). So you'll need to use another method for making your calculation work.

           

          One method would be to use ExecuteSQL and include the month and year in the query. Another method would be to use a Script Trigger to insert the value into a global variable after running a script that totals up the values (not a method I'd really recommend, but you can do it).

           

          Another way to do this would be to shift away from using portal filters and instead filter the relationships. You can substitute a global field instead of a variable to hold the desired month, and insert that as a relationship predicate on your Graph. If you do this, then the Sum calculation will work properly (based on the filtered relationship).

           

          HTH

           

          Mike

          1 of 1 people found this helpful
          • 2. Re: Summary Field for Portal

            Create a summary field in your portal table, based upon 'Total of In' and another 'Total of Out'.  You can even have a calculation in your child table which is cTotalAmount  (In - Out) and then create a summary 'Total of cTotalAmount'.  Place summary from your portal (child) table on your parent layout.

             

            If you are filtering a portal (not relational filtering but using portal filter), then you can create a single-row portal with same filtering criteria as your filtered portal.  Place your summary fields from your child within it.  Your summary will change according to the portals.

             

            ADDED:  If you are using separation, and depending upon how your In and Out are determined (if they are cascading calculations for example), you MAY need to refresh the portal for it to update.  Usually that is not required however.

             

            Message was edited by: LaRetta

            • 3. Re: Summary Field for Portal

              I was a bit unclear on the combinations of portals and relationships.  In general, if you have a relation-filtered portal and you wish to further filter it, you can do so by incorporating only the additional filter at the portal level.  So the majority will be filtered by the relationship and any final 'month/year' can be filtered at the portal itself.

               

              Regardless, a summary field from the child table will adjust accordingly. Just remember that 'portal filtered portals' must be within a one-row portal based upon the same relationship.

              1 of 1 people found this helpful
              • 4. Re: Summary Field for Portal
                Mike_Mitchell

                Interesting! Have you ever compared performance using this method (aggregating in the child table) versus using an aggregate calculation in the parent table?

                • 5. Re: Summary Field for Portal

                  Hi Mike,

                   

                  Actually I have tested in v11 and summary is more efficient was the same.  However, I recall reading somewhere that it is slower mobile but I have not re-tested it; there have been tweaks to the calculation engine as you know through versions 12-13, even between Updaters.  I know that portal filtering has been improved.  Maybe HOnza has some statistics for us!  I haven't ran FMBench for awhile - thanks for the reminder that we are due for more tests!

                   

                  In all, I prefer summary fields.  Why?  Because a single summary field in LineItems (for example), can provide summary to ANY parent table (one of the beauties with entity-style bi-directional relationships).  So it can provide total in Invoices, in Customers, in Products ... all from a single summary field in LineItems - just place it where you need it. 

                   

                  To me, a single summary beats three individual Sum() calculations (one in each of the three tables).  In general, I limit aggregates if mobile anyway writing static whenever possible and ExecuteSQL() can help there.  But I do not use ExecuteSQL() in calculation fields at all ... too unpredictable in what records are downloaded. 

                   

                  So summary fields are definitely in my toolkit.  :-)

                   

                  Message was edited by: LaRetta

                  • 6. Re: Summary Field for Portal
                    Mike_Mitchell

                    Fascinating. I was already percolating possibilities you mention - the ability to eliminate the Sum calcs in the main table. If there's little to no impact from a performance standpoint, then this is a boon.

                    • 7. Re: Summary Field for Portal
                      tm9

                      Thanks! I was using the single row portal but I did not have the relational filtering correct. I got it work and did it how you suggested by using the portal to only filter the year_month.

                       

                      Thanks again!!!

                       

                      And thanks Mike for chiming in. I can always so some advice on the SQL.

                      • 8. Re: Summary Field for Portal

                        One thing the ex-accountant in me must mention ... it works better to use a single amount field.  If you take an amount off (negative) or add on a charge (positive), it will simplify you and your Users' currency interactions. Use additional fields such as Type, GL or Account Code, or even a calculation of =

                         

                        Case ( Amount < 0 ; "OUT" ; "IN" )

                         

                        can help for grouping in sub-summary reports. 

                         

                        Reporting and summarizing become easier and you eliminate the possibility of staff wanting to 'credit' an IN by putting a minus amount in the IN field ... those kinds of things can trip a User up. 

                         

                        You may be too far into it to change now; only you can decide, so this is offered only as a consideration.

                        • 9. Re: Summary Field for Portal
                          beverly

                          I've read through all the replies and want to be sure this is clear.