5 Replies Latest reply on Nov 21, 2014 6:24 AM by philmodjunk

    Portal Custom Filter Summary Calculation

    Sam_1

      Title

      Portal Custom Filter Summary Calculation

      Post

      I have a Portal that I am custom Filtering using Global Variables. I am creating another portal (using same Filter) and putting the Summary fields in it. This way I am able to calculate the sum total of custom 'filtered' records.

      But is there anyway I can use this Summary field in calculation without showing them on the Layout. I ONLY want to show the new calculated field. 

      Example: Product:  A, B, C, D   Sales: 100, 200, 250, 300

      User enter Sales (Global Variable) >100 --> I use this value to filter the records

      Total Product Sales (Summary field): 750  ---> anyway I can use it in calculation

      I know I can use extract 750 using GetLayoutObject, but then I cannot perform calculation and also I have to put the object in the layout (that I do not want to do)

      Thank you!

        • 1. Re: Portal Custom Filter Summary Calculation
          philmodjunk

          There is no way to access the value of such a summary field value.

          You'll need to do one of the following:

          Define a new relationship where GlobalFieldValue < Sales is one of the match field values so that your portal filter expression is no longer needed.

          Use ExecuteSQL to reproduce both the relationship and the filter logic.

          Use a script to find the records, compute the total and return this value back to the current record of the original layout (this can be very slow...)

          • 2. Re: Portal Custom Filter Summary Calculation
            Sam_1

            Thank you for quick response.

            I was trying to avoid the way you suggested as it's slow and complicates the DB structure [I will have to use this logic multiple times]

            Can you suggest any other way this is possible. I am open to all options, as long as they don't drag the speed of the overall DB system!

             

            • 3. Re: Portal Custom Filter Summary Calculation
              philmodjunk

              Hmmm, you say "the way", but I described three different ways. Don't think I can come up with four. wink

              Actually, a relationship based filter is usually many times faster than a portal filter based filter. That option has to evaluate the filter for every related record and can lead to very slow updates.

              • 4. Re: Portal Custom Filter Summary Calculation
                Sam_1

                Thank you for your suggestions. I used the following method

                'Define a new relationship where GlobalFieldValue < Sales is one of the match field values so that your portal filter expression is no longer needed"    and it is working good.

                But then I had following issue based on client request. In addition to Sales value, they want to have global variable for Product Type

                for example: Product with sales >100 and Type=Home (options are home,office,both,N/A)

                now the filter works good (and portal shows all correct values) when the user enters both the information i.e Sales and Type. But is empty when it user enters ONLY one of the global field say Sales>100 and leaves Type empty

                the client wants to see what are all the product type for product for Sales>100 (irrelevant of the product type), and do it in same portal rather than moving to different portal. 

                Thank you !

                • 5. Re: Portal Custom Filter Summary Calculation
                  philmodjunk

                  You can use two portals and put them inside different panels of an invisible tab or slide control. When the user edits data in one of the global fields, the OnObjectSave trigger on both fields can be set to run a script that checks for data in both fields and selects the appropriate tab panel by using go to object. You'll need to use the inspector to assign object names to each panel of the control. If you are careful in placing and designing the two portals to be identical, the user never even knows that there are two portals unless you choose to tell them that's what you did.

                  There are also ways to set up an executeSQL query that returns a total and you can build in code to handle one criterion or two when implementing that method as well.