Lookup/Identify "First Occurrence" of a Row?

[Casey_Truffo]

Hello! new user here so thanks in advance.

I have 2 tables - customers and appointments. In the appointments table, each row is a different appointment (date) made by a customer. I would like to identify the first appointment date made by a customer in a more automated way that looking at each customer record. Any ideas? Thanks!

[Katherine_Duh]

Hey there! This post I made about doing something similar might be of some use. Although it’s for retrieving the most recent appointment date rather than the first date, it’ll work fine if you replace the aggregation function with MIN(values) rather than MAX(values).

[Casey_Truffo]

Thanks!! I will try it!

[Casey_Truffo]

Thanks @Katherine_Duh! That really helped! Now I have a rollup field that has the date they started (first appointment). I then made another field/column that is a dateformat column to pull out the month/year. I want to then calculate how many new clients in each month by practitioner.

Tables:
Invoices

• invoice ID
• Patient ID
• Issue Date
• Practitioner

Clients
-patient ID

• Date they started (MIN rollup from the invoices table - issue date)
• Month/year they started (DATETIME formula to pul MM/YYYY from the Date they started rollup field)

Practitioners

Now I would like to have a table that has (or somewhere note) t# of new clients by month for each practitioner.

So the count all the clients who started (first invoice) in 01/2016 for Practitioner Mary, and in that same month for Practitioner John, etc

Then do the same for 02/2106.

I started to create a table with “Month they started” as primary field but I could not link it back to the Clients->Month they started field since it was a primary field. Then I tried to put the number of new clients by month they started in the Practitioners Table but seems like I need another table?

Any advice would be appreciated. Thanks!

[Casey_Truffo]

I may have just answered my own question - I grouped them and it seems to be working :slightly_smiling_face: