4 Replies Latest reply on Feb 22, 2013 8:56 AM by Matty_1

    Conditional Sum()



      Conditional Sum()


           Is there a way to have the sum function only calculate when the match field IDs are the same?

           The four tables in question are: Personnel Records, Employment History and Entitlements and Administrative TO-DO.  In my Admin to-do I have a portal which calls upon all sorts of tables effective dates and when they come into a certain range of the current date they appear in the portals as to do tasks.  My trouble is this:

           I want my entitlements to show up one month before they need to be updated to give my administrative team time to take all the appropriate actions that comes with entitlement increases.  At this moment I cannot create a proper relationship because eligibility for entitlement increases is based on a calculation of years of service which cannot be indexed.  I have a calculation field in my personnel records table that sums all the work days an employee has worked with us.  This calculation calls on the employment history  record linked to the employee in question and sums the days the between the start and end dates (if end date is empty it takes current date).  Several of our employees are seasonal which is the reason for this calculation.  My entitlement records then have a calculation field that takes the required years of service, subtracts current years of service, multiplies by 365 and then adds this to the current date to produce an "Effective Date" for the entitlement increases.

           As you know, I can't use the Effective Date in a relationship because it's calling on all sorts of fields from all sorts of tables and cannot be indexed.  One solution is to amalgamate Employment History and Entitlements but I don't know of a way to make the sum function "smart" and bring it into the Employment History table instead of assessing from the Personnel Records.

        • 1. Re: Conditional Sum()

               If you have this relationship:

               TableA::ID = TableB::ID

               Then Sum ( TableB::NumberField ) can be defined in TableA to compute the sum of all records in TableB with the same ID as the current record in TableA. A summary field defined in TableB and placed on a tableA layout will also show this same total.

               While you cannot use that unstored calculation field as a match field on the "many" side of a relationship, it can be used in a portal filter to control which related records appear in the portal and a summary field from the portal's table, placed in the portal row, will give you the total of all the records that appear in that portal.

               There are several other ways that you can get selective sums: Sum_Calculation based on condition

          • 2. Re: Conditional Sum()

                 Right I'm aware of this, but I also use a Count function in my administrative table that show whether a particular tab has any relevant to-dos that day.  If I use a portal filter the count function counts all relevant records and not the filtered results.  This was an issue for me earlier this week and I was able to design my realtionships to avoid this .... now I'm stumped.

            • 3. Re: Conditional Sum()

                        If I use a portal filter the count function counts all relevant records and not the filtered results.

                   Which is why I did not describe using that option. If you define a "count" type summary field in the portal's table, however, you can place that summary field on your layout inside a filtered, one row portal and it will correctly display a count consistent with your portal filter.

              • 4. Re: Conditional Sum()

                     Oh yes of course .... all this time trying to reinvent the wheel just for a count when the fix was simple.  As always thanks phil!