Hi, I have to put up a customer archive and in each registry I would like to set a billing frequency, Monthly, Bimonthly, Quarterly, Half-yearly or Annual. Given this assumption, every month I invoice, I would like Filemaker to show me only customers whose frequency matches.
Obviously, once an invoice has been issued, it should automatically update the next expiration date.
What advice would you give me to deal with this, considering that I still have to start the project?
Thank you.
You'll need at least a field in the Customers table of InvoiceFrequency. And table for Invoices with an InvoiceDate
It's not clear what you're invoicing. Often businesses invoice for services performed on specific days. It sounds like you are doing some recurring billing for a subscription service?
If that's true, then you'll likely want a script to run monthly to create the invoices. It can be set as a scheduled server script.
You could go in a couple directions for determining which Customers need invoicing. My preference would be to loop through Customer records and compare the date of the most recent invoice (as seen through a relationship) to an invoice date calculated by using the current date and the Customer's InvoiceFrequency. If an invoice is needed, create one through another relationship to Invoices ("Magic Key" would be good here).
That's the broad strokes. What needs clarification?
I have seen this done two ways. A FREQ field on the "customer profile". Although was more so for how often to generate a statement of account to the customer. Invoices were usually billed daily. But programming could "hold" invoices until the billing cycle was called then tag the invoices as being sent.
In my system, I am billing a contracted service. So the FREQ is noted on the "contract profile". I currently only have MONTHLY and ANNUALLY. But the coding is in place to handle QUARTERLY. Each contract profile has the expiration date (renewal date). Each month we perform a find on those expiring current month and year mm/*/yyyy. From this found set a billing records are created, invoiced and upload to our accounting software. Then the expiration date (renewal date) is updated based on the FREQ, in our scenario bumping the MONTHLY by 1 month and the ANNUALLY by bumping the year by 1.
Although not specific to Invoicing, you may find this thread of help as well.
If I assume that these schedules are overlapping (two customers might have monthly frequency but have different start dates), then I would suggest that each customer needs a "next billing date" field in their record, as well as a "billing frequency" field. Every day you would find customers whose "next billing date" is today, create invoices, and then update the next billing date according to the selected schedule.
You haven't said very much about where the financial information comes from. If you are a consultant logging billable hours, then my suggestion above works quite well as any pending charges simply get added to the next invoice. If it's a subscription with a fixed fee per billing period, then you could add a new field to insert that number. But if the charges are calculated in some other way, the approach might be different.