2 Replies Latest reply on Mar 22, 2012 3:12 AM by EwoudDeMan

    Calculation with Invoices (Dates)



      Calculation with Invoices (Dates)


      I have been working with Filemaker Pro over the last 2 months and must admit I've become quite an addict.

      Now I've come across a little problem that seems simple, but I can't find the solution yet.

      It's about a simple invoice-table with the 4 fields; invoicedate, invoice-amount, debtornumber and InvOutstandingDays.

      For every invoice; I have to look back in time for maximum 12 months, sum all the invoice-amounts (minus the invoice-amount of the record in use) with the same debtornumber if InvOutstandingDays<61 days (this last field indicates payment behaviour). The sum has to be presented per invoicedate as this outcome will be used as a creditlimit per invoicedate.

      Anyone with a great idea?




        • 1. Re: Calculation with Invoices (Dates)

          You need an Invoices "self join" that matches to the records you've specified. Such a relationship uses a second occurrence of invoices so that you can relate the invoices table to itself.

          In manage | database | relationships, select Invoices and click the duplicate button (2 green Plus Signs). This creates a new occurrence of the same table. It does not duplicate the table. You can then double click the new occurrence to name it something more descriptive than Invoices 2, such as: OutstandingInvoices.

          Then you can set up this relationship:

          Invoices::debtornumber = OustandingInvoices::debtornumber AND
          Invoices::const61 > OustandingInvoices::InvOutstandingDays AND
          Invoices::cDate12Mo > OustandingInvoices::Invoicedate AND
          Invoices::InvoiceID ≠ OustandingInvoices::InvoiceID

          const61 would be calculation field that returns the constant value: 61.

          cDate12Mo would compute your "past 12 months" date. It could be computed a number of ways depending on what you mean by that:

          Exactly 12 months from today's date:

          Let ( today = Get ( CurrentDate ) ; Date ( Month ( today ) + 12 ; day ( today ) ; year ( today ) ) )

          12 months from today's date, adjusted to the first of that month:

          Let ( today = Get ( CurrentDate ) ; Date ( Month ( today ) + 12 ; 1 ; year ( today ) ) )

          Exactly 12 months from the invoice's date:

          Let ( d = Get ( InvoiceDate ) ; Date ( Month ( d ) + 12 ; day ( d ) ; year ( d ) ) )

          12 months from invoice's date, adjusted to the first of that month:

          Let ( d = Get ( InvoiceDate ) ; Date ( Month ( d ) + 12 ; 1 ; year ( d ) ) )

          If you use a calculation with Get ( CurrentDate ) in it,  make sure to make this an unstored calculation rather than stored.

          Then, Sum ( OustandingInvoices::InvoiceAmount ) will produce the total you want.

          • 2. Re: Calculation with Invoices (Dates)


            thxs for the quick answer and your comments solved the problem completely. Also the concept of a self-join is now much clearer to me.