Please help! Date Scripts

[Zane Williams]

Hi,

I'm using Filemaker pro 11 to create a database that holds details of clients monthly payments on 12 month contracts. Clients have the option of paying uprfont or paying monthly via direct debit. On their file i want there to be a field called 'Paid', with two possible answers "Yes" or "No." I want the 'Paid' field on the first of every month to revert back to "No" across the board except for those clients that have paid upfront. This would indicate that they need to be debited for this month. To indicate that a client has paid upfront I have another field called 'Payment Upfront' with two possible answers "Yes" and "No" again. Can someone please help me to construct the if statement or script that will accomplish this?

Essentially I want a script or calculation that says "If day of month = 1st, Paid = No, unless payment upfront = Yes"

Any help would be much appreciated!

Cheers, Zane

[Vaughan]

I want the 'Paid' field on the first of every month to revert back to "No" across the board...

Trust me, you don't want this.

Instead, create a related table of Payments. Make a payment record for each payment the person has to make: if the person pays up front then create 1 payment record, if they have 12 payments then make 12.

Each payment record needs an amount field, a date due field, and a date paid field.

Payments can have the following status: paid, overdue, or scheduled. (Scheduled are payments whose due date is in the future.) Payments are marked as paid when the Date paid has a value in it.

Create an unstored calculation field to display the status:

Case( 



not isempty( Date Paid ) ; "Paid" ; 



Get( currentdate ) < Date Due ; "Scheduled" ; 



"Overdue" 



)

Using multiple related payment records allows the history of the account to be kept.

It is possible to make this much more complex but this is about the minimum.