Forum post
Hi there,
I am trying to set up a recurring payments system.
I have set up the following fields:-
StartDate
frequencyOfPayment
numberOfPayments
nextInvoiceDate
lastPaymentDate
What calculations can I use to calculate nextInvoiceDate and lastPaymentDate??
Some sort of date function I imagine.
Could anyone also recommend how I can automaticallly add a recurring payment thats payable to an invoice table?? Perhaps using a script that runs every day on the server (we have filemaker server 10)
Many thanks for your kind help.
Best Answer
Answer
The following calculation should return the date of the last payment =
Case (
frequency = "daily" ;
StartDate + numberOfPayments ;
frequency = "weekly" ;
StartDate + numberOfPayments * 7 ;
frequency = "monthly" ;
Date ( Month ( StartDate ) + numberOfPayments ; Day ( StartDate ) ; Year ( StartDate ) )
)
To calculate the next payment, you need to calculate how many intervals have already elapsed, round the result up to the nearest integer, then plug this as the numberOfPayments into the same formula.It might be more efficient to script the creation of payment records in a related table at the time the invoice is issued.
jonnyt wrote:
could you give me a high level plain english overview of how you would script an invoicing function to be run manually whenever the user wants to run it.
1. If there are related Payments, delete them;
2. Put StartDate and numberOfPayments into variables.
3. In the Payments table, loop: increase $startDate by an interval, decrease $numberOfPayments by 1, create a new record. Exit loop when $numberOfPayments is depleted.
jonnyt wrote:
the calculation for monthly doesnt seem to work when it goes into the next year
Can you provide a specific example of "doesnt seem to work?
If you would be so kind could you help with the calculation to work out the next payment date?
Im then going to create a server script that runs daily and searches for any next payment datesthat match todays date, then the script inserts the payment into the payments table.
I will then create a second script that creates a report showing all outstanding payments that havent yet been invoiced.
Does the above sound about right?
jonnyt wrote:
Im then going to create a server script that runs daily and searches for any next payment datesthat match todays date, then the script inserts the payment into the payments table.
I am not sure what you are trying to achieve by that. I would create all the individual payment records at the time the invoice is issued.
The following calculation should return the date of the last payment =
Case (
frequency = "daily" ;
StartDate + numberOfPayments ;
frequency = "weekly" ;
StartDate + numberOfPayments * 7 ;
frequency = "monthly" ;
Date ( Month ( StartDate ) + numberOfPayments ; Day ( StartDate ) ; Year ( StartDate ) )
)
To calculate the next payment, you need to calculate how many intervals have already elapsed, round the result up to the nearest integer, then plug this as the numberOfPayments into the same formula.
It might be more efficient to script the creation of payment records in a related table at the time the invoice is issued.