3 Replies Latest reply on May 5, 2015 7:34 AM by philmodjunk

    How to do a If this = that, then do a sum of X field?



      How to do a If this = that, then do a sum of X field?


      I'm trying to setup a field where If Type = "Sale", then it shows me the sum of a certain field. 


      My current formula is If ((Type = "Sale") ; Sum ( Total Sale Price )) and while I don't get an error, I also don't get anything in my field, it's just blank. 

      Thanks for any help.


        • 1. Re: How to do a If this = that, then do a sum of X field?

          Seems like something is wrong with the Sum function on my end. I took out the If Type = "Sale" and just left Sum (Total System Size) and that is also blank, so need to see about getting that to work before I do any "If" calculations.

          • 2. Re: How to do a If this = that, then do a sum of X field?

            Finally got everything working how I wanted it with a different method. 

            My field "Total Sale Price" was a Summary of "Sale Price" and my goal was to only show data from records where Type =  Sale.

            I ended up making a new field that said If (Type = "Sale" ; Sale Price) which basically only had the Sale Price data in it if Type = Sale.

            I then made another field that was a Summary of the new field above and everything is working as indented. :)

            • 3. Re: How to do a If this = that, then do a sum of X field?

              Here's why your first attempt fails. (The sum function works just fine, BTW):

              If ((Type = "Sale") ; Sum ( Total Sale Price ))

              If this is a direct copy/paste of your calculation. Sum ( Total Sale Price ) is only referencing the value of this field in the current record. If type = "sale", it returns the value of this single field--which might be a summary field "grand total" value based on the current found set. If type does note equal "sale", then your IF function returns nothing.

              Sum, even if Type = "sale" does not really do anything in this calculation.

              If you look up the function in FileMaker help, you;ll find that there are 3 ways to use it:

              Sum ( FIeld1; field2; field3 ) which will be the total Field1 + field2 + field 3, all from the same record

              Sum (RepeatingField) which will be the sum of all values of all repetitions of this field, all from the same record

              Sum (RelatedTable::Field) which is the most common way to use this function, It sums field over the set of all related records, such as all the recors showing in an unfiltered portal.

              You appear to be using Sum ( Field1 ), which will simply return the value of Field1. Then you enclosed it inside an If function that either allows the function to evaluate--returning the value of field1 or does not allow it to evaluate--returning nothing.

              There are several other methods that can selectively sum data from multiple records besides the method you worked out.

              ExecuteSQL can use sum with a WHERE clause to selectively sum data.

              Sum ( RelatedTable::field) can selectively sum, if you set up the relationship to the related table to match only to the records you want to sum. (This can be a "self join" with two occurrences of the same table on each side of the relationship.)

              In a summary report, a simple summary field can display sub totals if you place the summary field inside a sub summary layout part.

              Filtered portals with a summary field inside the portal row can show a total of just the related records that pass the filter expression