Thanks in advance for any advice.
We are counseling agency that sees couples and individuals.
I want to count how many sessions we do by practitioner. and how many clients we see.
We have a table called Appointments that has:
Appointment ID
Patient ID
Start date and time of Appt
Prractitioner
Invoice ID
etc
We have a table called Clients that has:
Patient ID
that is linked to the appointments and invoices table
We have Invoices Table
Invoice ID
Patient ID
Appointment ID
Date Paid etc.
Step 1: I want to count how many sessions we do by practitioner.
The complication comes in that if we see a couple together, there are two records in the Appointment table for the session (start time + Practitioner) for that particular (one) session - one appointment record for one partner (Patient ID) and one for the other partner (Patient ID). So that is only one session - but two appointment records - two different Appointment IDs.
I am thinking I could count unique “start time + Practitioner” in a filter - but can I use that filtered-sum in another calculation - say in a different table?
I am also thinking I would like to count number of clients by practitioner - but to count the ‘couple’ (two different patient IDs) as one client as well as when one of the clients comes alone. I was thinking I could concatenate the Patient IDs somehow if the startdate-time and practitoners were the same…
Is this beyond Airtable? Or might I need a paid consultant?
Again, thanks in advance…
