9 Replies Latest reply on Mar 1, 2012 9:38 AM by philmodjunk

    Sum Calculation in Filtered Portal

    AmandaBurch

      Title

      Sum Calculation in Filtered Portal

      Post

      I have a portal on Employee Scheduling (table) displaying individual line items--or Projects (table)--via a Employee ID relationship. I have a filter set up to restrict portal results by selecting a Month and would now like to set up a calculation to sum the percent of allocation (field) per project with the filter in place. For example, an emloyee has two projects in the portal--one at 40% and the other at 25% in the month of April. I can get the caluclation to work when all line items are viewed in the portal, but not when filtering by month.

        • 1. Re: Sum Calculation in Filtered Portal
          philmodjunk

          Calculation fields, including yours with the sum function, evaluate at the data or table level and this ignores layout based settings such as a portal filter. (The field will evalulate the same on any layout based on the same table regardless of filter settings.)

          You have two options:

          Create a relationship for your portal that includes the Month info as part of the relationship. Then your sum function will work as you no longer have a portal filter. The reltaionship can refer to a global field so your portal's look and function will be the same, but your relationship graph may have just got a bit more complicated if you end up adding a new table occurrence for your portal in order to get this to work.

          Define a summary field in the Portal's table to take the place of the sum calculation in the layout's table. The summary field's value will be limited by your portal filter so this should still work for you. The main limitation here is that if you edit data in the portal, summary fields get a bit "clunky" in how they update when compared to a calculation field that uses Sum to produce the same total.

          • 2. Re: Sum Calculation in Filtered Portal
            AmandaBurch

            The first option looks tricky, so I tried the second. I created a summary field on the portal's table to sum the contents of the % of total allocation field. However, the number is still totaling all allocations and not those shown when the portal is filtered by month. Not sure what's wrong here... Need some help.

            • 3. Re: Sum Calculation in Filtered Portal
              philmodjunk

              The summary field has to be put inside either the same portal or a one row duplicate portal with the same filter.

              • 4. Re: Sum Calculation in Filtered Portal
                AmandaBurch

                I tried putting the summary field in the same portal and in a one two duplicate portal with same filter -- and still getting the same result. A total that isn't being recognized by the filter. Any thoughts?

                • 5. Re: Sum Calculation in Filtered Portal
                  philmodjunk

                  I'd need to see exactly what you have set up to see why it isn't working for you.

                  • 6. Re: Sum Calculation in Filtered Portal
                    AmandaBurch

                    Portal filter is: Projects::Month = SME Scheduling::gSelected_Month or
                    IsEmpty ( SME Scheduling::gSelected_Month )

                    Summary is set up at "Total" field on Projects layout and included in portal on SME Scheduling layout. Set up as " = Total Percent of Allocation".

                    Tables + relationship are attached in picture.

                     

                    • 7. Re: Sum Calculation in Filtered Portal
                      philmodjunk

                      This all looks correct. Let's check some details not shown in your screen shot:

                      In layout setup for your layout, the exact text "SME Scheduling" is selected in "Show Records from".

                      IN portal setup, the exact text: "Projects" is listed in "Show related records from".

                      If you enter layout mode and click the summary field, "Display data from" in the top of the inspector's data tab shows: Projects::Total.

                      Correct?

                      And this field is completely inside the borders of the portal with not even a single pixel extending past the borders of the portal?

                      If so, it should work for you.

                      PS. you may want to modify your portal filter to include the year as well as the month or you may get records from January 2011 and January 2012 appearing in the portal at the same time.

                      • 8. Re: Sum Calculation in Filtered Portal
                        AmandaBurch

                        Oh my gosh! The summary field was slightly outside of the portal borders. I didn't even notice....

                        If I set up the portal filter to include year, I will use the same type of calculation with a global field for year. However, when I go into my portal setup with my existing calculation there, what operator do I need to use between the two (i.e., what do I place after this "Projects::Month = SME Scheduling::gSelected_Month or IsEmpty ( SME Scheduling::gSelected_Month )" to specify a second filter)?

                        THANK YOU!

                        • 9. Re: Sum Calculation in Filtered Portal
                          philmodjunk

                          (Projects::Year = SME Scheduling::gYear AND Projects::Month = SME Scheduling::gSelected_Month) or IsEmpty ( SME Scheduling::gSelected_Month )