I have a database that records Client details in one table and then Appointments for each client in another table.
I am making some scripts to produce summary reports that need to show data over a monthly period and a yearly period.
Here is my problem.
One of the things I need to produce is a calculation of "repeat clients". That is to say, clients who have previously made appointments that make a new appointment.
I currently do this by calculating - in a field in the Client table - whether a client has had more than Appointment and then I have a summary field that counts each related Appointment FK to give a total. Excellent, this all works fine. However, I have just been asked to refine how this calculation is done. In particular I now need to count every repeat appointment within the given period.
The logic is something like this:
(for both Monthly reports and a financial year report)
1. if(client has previously made an appointment)
count(number of appointments they have made in the period)
2. total for all clients in period
This would be pretty simple with come kind of "countif" function - I could loop through each repeat client and produce a subtotal for any appointment that was made during the corresponding period - however there does not appear to be a countif function and I d not have access to Filemaker Pro Advanced to write my own. What can I do?
Add a global field (or two) to define the period of time. Use these global fields plus the primary key to create a new relationship to appointments that only displays related records for the time period.