7 Replies Latest reply on Mar 18, 2014 6:17 AM by philmodjunk

    Filtering a Summary Field on a Layout

    jkostenbader

      Title

      Filtering a Summary Field on a Layout

      Post

           I have a summary field that "totals" the value of one or more projects for each of my accounts on the accounts layout. The summary works fine and totals everything up nicely. What would really be ideal is for that calculation to not add projects that have a status of "cancelled" or "complete". There does not seem to be an ability to filter this total. I tried using a calculation field with something like:

           If(projects:project status <> "Cancelled" and projects;project status <> "Complete"; Sum(PO Value))

           the above statement seem to only account for the first project per account (not the rest if there is more than one). Am I missing an approach that would be better in this situation

           Regards

            

           John

        • 1. Re: Filtering a Summary Field on a Layout
          philmodjunk

               Presumably you meant to post this calculation:

               If(projects::project status <> "Cancelled" and projects::project status <> "Complete"; Sum(Projects::PO Value))

               And defined this calculation field in a table that is related to Projects in a one to many relationship:

               SomeTable----<Projects

               But I could be guessing incorrectly.

               When a calculation refers to a field from a related table (Projects::ProjectStatus), it only access the value of the "first related record". It does not access the data in any other related table. Thus your sum function returns the total of PO Value from all related records (regardless of status) or no sum at all depending on the value of project status in the first related record.

               But there are multiple other methods for selectively computing the desired sum.

               The simplest is to define cOpenProjectPOValue in the project status table as:

               If ( project status ≠ "cancelled" and project status ≠ "Complete" ; PO Value )

               Then, in your other table, you can use Sum (projects::cOpenProjectPOValue ) and sum will return the total for just the PO of the open projects that are related to the current record in "SomeTable".

          • 2. Re: Filtering a Summary Field on a Layout
            jkostenbader

                 Let me see if I can restate this and understand. We're dealing with two tables. "Accounts" and "Projects". Projects is link to accounts in a one-to-many relationship. The value I'd like displayed is on a layout based on the "accounts" table (project(s) are being displayed in a portal). You would like me to configure two calculated values. The first calculated values should be in the "projects" table and should look something like this:

                 If (Status <> "Complete" Or Status <> "Cancelled"; PO Value). Lets call this c_FilteredPOValue

                 The second calculated value should be configured in the "accounts" table and should look something like this:

            c_OutstandingPOValue = Sum(c_FilteredPOValue)

                 Do I have that right or did I misunderstand you

                 John

            • 3. Re: Filtering a Summary Field on a Layout
              philmodjunk

                   It would be Sum ( Projects::c_FilteredPOValue ) not Sum ( c_FilteredPOValue )

                   And that highlights a key assumption on my part that may be incorrect. In which table have you defined POValue? is it defined in Accounts or Projects?

                   I'm now guessing that it is defined in Accounts and will change what has to be done to get the result that you want.

              • 4. Re: Filtering a Summary Field on a Layout
                jkostenbader

                     Yes...my bad on the typo. Your original assumption was correct. PO Value is defined in the "Projects" table not "Accounts". I think I have it set-up as your describe but it still does not calculate correctly. It still appears to be taking the entire value despite the project status:

                      

                • 5. Re: Filtering a Summary Field on a Layout
                  philmodjunk

                       My mistake. Replace "or" with "and" as you want values where status is not completed AND not cancelled.

                  • 6. Re: Filtering a Summary Field on a Layout
                    jkostenbader

                         Ahhh...I should have seen that....my fault I think. That made the difference. Two pair of eyes always better than one I guess. Thanks very much. I'm not sure if your one person or many but your timely answers to my novice questions over the last several weeks have helped me greatly make the transition from Access (still have way too much to learn)

                    • 7. Re: Filtering a Summary Field on a Layout
                      philmodjunk

                           I'm just me, no "ghost helpers" nor am I employed by FileMaker Inc.