2 Replies Latest reply on Oct 29, 2013 8:03 AM by MOnikaVermeulen

    calculate value base on records older than today date

    MOnikaVermeulen

      Title

      calculate value base on records older than today date

      Post

           hi there.

           I'm having difficulty with the following function: 

      If (Invoices::Date ≤ Get ( CurrentDate ); (Debit amount Total -  Customers::TotalInvoiced); "")

           In theory what I'm trying to do is deduct the amount that has been invoiced from the amount that has been paid from two relating records, based on their date range (today and older). I want to create future dated payments that are scheduled to reflect only from the first of every month, but I do not want those scheduled payments to be included in the calculation.

           I do not want to have to ad new records for all my clients every month as this is extreamly time consuming.

           For some reason my calculation is not excluding records older than today and keeps on returning the result for all records and not just those within that date range.

           I have recently moved from Microsoft Access and i cannot seem to be able to recreate this simple function within Filemaker Pro

           Some urgent assistance would be appreciated

            

        • 1. Re: calculate value base on records older than today date
          philmodjunk

               Your calculation refers to Invoices::Date, Debit amount Total and customers::TotalInvoiced. That could be references to data in up to 3 different tables. In what table is this calculation field defined?

               When a calculation references a data field in another table, it references data in the first related record. When it refers to a summary field from a different table the summary total returned is based on all related records.

               In order for your calculation to selectively Sum records from a related table--which is what I am guessing that you are trying to do here, you need to use either:

               a) a relationship that links to the correct group of records

               b) ExecuteSQL

          • 2. Re: calculate value base on records older than today date
            MOnikaVermeulen

                 thank you. i realized what the problem was and calculated the total in the correct table first before referencing it in the next calculation. all working now, thank you for the tip and quick reply