I have a table of clients, each of whom has been assigned a coach.
I have a second table of appointments which is coming from Acuity. In that table, I have the clients linked so I can see all of the client's meetings with anyone in our company.
What I want to do is find the FIRST time that a client has met with their coach. This would not be their first meeting ever; that would be too easy.
Within the "clients" table, I have the "client" name, the "coach" name (and a ton of other data)
Within the "appointments" table, I have the "client" name, the "coach" name, the "calendar" the appointment was booked on (which would match the "coach"), and obviously the dates and times of appointments.
I can use "min" formula to find the first appointment, but I'm struggling with the first appointment with the specific team member that is the "coach".
Assuming that people who are coaches always act as coaches, store the role of the person in the staff table. Have a lookup of the role of the person that the client met with in the appointments table. Then in the clients table, use a conditional rollup that looks for the earliest date where the role is a coach.