8 Replies Latest reply on Jul 6, 2012 12:25 PM by philmodjunk

    Sum of Prices using Case Statement

    kallain

      Title

      Sum of Prices using Case Statement

      Post

      Hello,

      I am trying to use a calculation field to get the sum of a subset of records using a case statement.

      I have a table called 'Bids' with the fields 'Bid Price' and 'Mark'. I have a second table called 'Shots' with a field called 'Shot Cost'

      On the 'Shots' layout, I have a portal to the 'Bids' table containing the fields mentioned above. I'm trying to get the 'shot cost' field to display the sum of 'bid price', but ONLY when the 'mark' field contains the word "Mark". 

      The 'shot cost' field is currently set up as a calculation with the following:

      Case ( Bids::Mark="Mark" ; Sum ( Bids::Bid Price ) )

      But this results in a sum of ALL records instead of only those that are 'marked'

      For instance:

      Record 1 = bid price $10, MARK

      Record 2 = bid price $20 (not marked)

      Record 3 = bid price $30 (not marked)

      Result of Shot Cost = $60 (I only want it to count record 1, it should be $10)

       

      Any assistance would be greatly appreciated.

      Thank you,

      K

        • 1. Re: Sum of Prices using Case Statement
          philmodjunk

          Actually, you have two possible outcomes with your case function. YOu can get the sum of all related records or a null value. Which you will get will depend on the value of Bids::Mark for the first  related record in Bids for the current Shots record.

          This question pops up here fairly often. See this thread for how to get the totals you want:  Sum_Calculation based on condition

          If you have FileMaker 12, you can also get this selective total using the ExecuteSQL function.

          • 2. Re: Sum of Prices using Case Statement
            kallain

            Thank you for the reply Phil, that link was very helpful. 

            It's funny because these fields were initially set up per your Option 1 in the linked thread, and it worked well for a while. But any time I try to import a large number of records, the 'shot cost' field stops updating. So I was trying to find a work around. Have you heard of this happening before?

            Thanks,

            K

            • 3. Re: Sum of Prices using Case Statement
              kallain

              I just tried option 2 that you described in the linked thread, and it worked great. My second question would be as follows:

              Is there a way to get a summary of the summary field appearing in the filtered portal?

              To extrapolate, I have a filtered portal to the 'Bids' table that shows only the records that are "Marked", this portal contains a summary field 'TotalBidsMarked' showing the total price for only those filtered records. On the 'Shots' table I would like another summary field that adds up the 'TotalBidsMarked' field. Is this possible?

              Thanks again for your help!

              -K

              • 4. Re: Sum of Prices using Case Statement
                philmodjunk

                Option 2: (Summary field in filtered portal) is a "display total only" option. It doesn't let you use that total in other calculations. You should use Option 1 for that. I'm a bit puzzled by this statement you made earlier:

                it worked well for a while. But any time I try to import a large number of records, the 'shot cost' field stops updating.

                Option 1 shouldn't be affected by the number of records in your found set, nor by whether the records are either imported or entered one at a time manually.

                I suggest revisting that solution and taking a look at the details in how you implemented that approach. Of particular concern would be if any of the fields involved use aut-enter calcualtions instead of being defined as calculation fields like I describe in the other thread. Auto-enter calcualtions, under certain circumstances--such as an import of records where the "allow auto-entry" option is not specified can fail to update as expected--so that's one possible explanation here.

                • 5. Re: Sum of Prices using Case Statement
                  kallain

                  I decided to go back to basics, and create a new database file to explore Option 1 solution again. I'm still unable to get it to work. In this new solution the 'shot cost' field remains empty. I have uploaded frame grabs of what I built. Can you see what I'm missing? I'm sure it's something simple.

                   

                  • 6. Re: Sum of Prices using Case Statement
                    kallain
                    /files/347f5be5d1/frm2.png 545x176
                    • 7. Re: Sum of Prices using Case Statement
                      kallain
                      /files/dacf4cbc8b/frm3.png 580x165
                      • 8. Re: Sum of Prices using Case Statement
                        philmodjunk

                        The problem lies with Bids::BidSelectedRelater. It's an unstored calculation and thus cannot be used like this in your relationship.