5 Replies Latest reply on May 31, 2016 2:30 AM by yantihadi

    Aggregating Filtered Portal Result

    yantihadi

      Hello,

       

      has anyone have experience with summing up filtered portal result?

       

      I did with this below script, however, when there is no result in the portal (based on filter parameters - in this case "range of date") ,

      the total result in filter sick leave still shows the sum of the total record for an employee (6 days), instead of 0 (zero day), as per below screen shot :

      (This is the modification of Personnel Records starter solutions, for Time Off calculation).

       

      Screen Shot 2016-05-25 at 09.46.25.png

      This is the filter portal record calculation :

      Screen Shot 2016-05-25 at 09.45.48.png

       

      Does anyone has any idea how to "zeroing" the result?

       

      Thank You very much.

      Milka

        • 1. Re: Aggregating Filtered Portal Result
          erolst

          How are you aggregating the results?

           

          You cannot simply use the relationship, because that returns the results for the unfiltered related set.

          • 2. Re: Aggregating Filtered Portal Result
            yantihadi

            Hello,

             

            I use the calculation to sum the Total Day shown in the filter :

             

            Let ( [ $$TotalDay = Case ( IsEmpty ( $TotalDay ) ; 0 ; $$TotalDay) + Time Off::Day  ;

            $TotalDay = True ] ; True )

             

            And then, display $$TotalDay , as below :

             

            Screen Shot 2016-05-25 at 11.56.04.png

            Also, a conditional formating for $TotalDay as below :

            Screen Shot 2016-05-25 at 12.02.30.png

            -------------------------------------------------------------------------------------------------------------------------------------

            This works whenever there is result for the date range selected , as below :

             

            Screen Shot 2016-05-25 at 11.58.47.png

             

             

            But, when within the date range there is no data, the number of sick leave turns to Total Day again, which in this case = 6 days. I actually intend to display 0 day.

             

            Screen Shot 2016-05-25 at 12.01.35.png

            Thank You very much.

            Milka

            • 3. Re: Aggregating Filtered Portal Result
              yantihadi

              Hello,

               

              does anyone have any ideas about this situation/issue? Any feedback appreciated.

               

               

              Regards,

              Milka

              • 4. Re: Aggregating Filtered Portal Result
                user19752

                As you saw, if there is no related records the variable never be set.

                How about using "hide object when" or conditional formatting calculation on the merge variable?

                Or if the layout is used as form view, you can use onRecordLoad trigger script to set the value to zero.

                Set variable to zero in onObjectSave trigger script on filter fields. This may be run before portal update.

                 

                Hmm, are you already using any trigger script to update merge variable, for after changing filter fields?

                • 5. Re: Aggregating Filtered Portal Result
                  yantihadi

                  Hello,

                   

                  Thank you for your solution.

                  As per your suggestion, I set $$ Totalday back to 0 in ObjectSave trigger, and it works.

                  I did before Flush Cached, I thought it might do the same, but it was not.

                  Thanks again for helping me out.

                   

                  Milka