2 Replies Latest reply on Dec 17, 2013 4:55 PM by WarrenAuld

    Totalling based on Date and Checkbox values

    WarrenAuld

      Title

      Totalling based on Date and Checkbox values

      Post

           Hi,

           I would like to total three fields based on three criteria:

             
      1.           the date is greater than today (currentdate), AND
      2.      
      3.           the DepositPaid field (Yes/No checkbox) is Yes, AND/OR
      4.      
      5.           the BalancePaid field (Yes/No checkbox) is Yes

           The fields to total are BalanceAmount, DepositAmount and TotalPaidAmount (this is the total of BalanceAmount and DepositAmount).

           So in effect, I want to only add amounts for records that are in the future, that have values in the DepositAmount that have been paid (DepositPaid) and the BalanceAmount that have been paid (BalancePaid).

           These can be displayed at the bottom of my current Details Layout.

           Hope this makes sense.

           I'm using Filemaker Pro Advanced 11.0v4 (Mac)

           Cheers,

           Wazza

        • 1. Re: Totalling based on Date and Checkbox values
          philmodjunk

               Option 1: Perform a find for just records meeting this criteria and then summary fields that total these fields will report the correct totals.

               Option 2: A relationship could be defined that only match to these records, then a Sum function or a reference to a summary field in the related table will produce the requested total.

               Option 3: If you upgrade to FileMaker 12 or 13, you can use ExecuteSQL to get these totals.

               Option 4: You can define a calculation field for each of these three fields such as:

               IF ( ( DateField > Get ( CurrentDate ) AND ( DepositPaid = "yes" or balancePaid = "yes) ; BalanceAmount )

               Then add a summary field to total up the value of this calculation field.

          • 2. Re: Totalling based on Date and Checkbox values
            WarrenAuld

                 Thanks for that. I went Option 4. Worked a treat!