Certified FileMaker Pro Consultant MICHAEL EDWARD KUPIETZ

At https://www.reddit.com/r/filemaker/comments/1v7byt1/get_number_of_records_per_person_for_the_current/, a redditor asked,

I use FileMaker for my patient files and am trying to find a way to display how many times that patient has come this year (1 record = 1 visit). I have a PatientID field and a TreatmentDate field. What is the best way to do this? I’d like to display this info at the top of each new record for that patient.

Here’s a small demo file showing how this is done: Counting-records-by-year.fmp12
The technique, as described in my comment on that post:

You need a table, we’ll call it Appointments, with the following fields:

PrimaryKey – (this is a default field created when you create a table in FileMaker, it automatically contains a unique serial number for each record)
CreationTimestamp (this is a default field created when you create a table in FileMaker, it automatically contains the date and time each record was created)
PatientID – a data entry field
AppointmentDate – a data entry field
CurrentYear – a calculated field, set to = year ( CreationTimestamp )
AppointmentYear – a calculated field, set to = year ( AppointmentDate )

Then you create what’s called a self-join relationship — a relationship from fields in a table back to fields in that same table. We’ll call that relationship Appointments_sameYear.

The relationship to define has two matching fields:

Appointments::PatientID = Appointments_sameYear::PatientID
AND
Appointments::CurrentYear = Appointments_sameYear::AppointmentYear

Then you create one last calculated field:

AppointmentsThisYear = count ( Appointments_sameYear::PrimaryKey)

Then you build your data entry layout on the Appointments table. When you create a new record, as soon as you enter a patient ID, AppointmentsThisYear will show how many appointments the patient has had this year.

I’ll have a demo of this posted in a minute, will come back with the link so you can see a working copy.

One note: AppointmentsThisYear will always update for all patient records when you add new appointments… if you have a patient with 3 appointments this year, all three records will show ‘3’ for this field. If you want it to show the number of appointments they’d had at the time the appointment is created, so the first appointment record shows 0 previous appointments, the second appointment for the year shows 1, the third shows 2, you’d make it a number field with an autoenter calculation, instead of a calculated field. (I do already have a demo that does something like this: https://www.kupietz.com/filemaker-pro-self-join-auto-fill/ shows how to increment a counter per customer records, although it isn’t filtered by year.)