Hi Jordan!
Welcome to the Community 
This is absolutely possible with Grist! I built out an example based on your description. Take a look at the link below;
https://public.getgrist.com/a6Uu2rdiwKX6/Community-Forum-570
You should be able to save this to your personal page and copy formulas, make changes, etc. Just click the share icon then ‘Duplicate Document’.
Feel free to ignore the ‘Patients’ page. I used this to create a unique ID for each patient. Since you already have a unique ID assigned and will be importing it, you won’t need to do this in the document.
The APPOINTMENTS table contains the Client ID, Date of Appt and the appointment type to mimic the information you would be importing.
Now into the real fun - creating the summary table!
Select the green ‘Add New’ button and ‘Add Widget to Page’ > Select Widget = Table > Select data = Appointments > Group by = Client ID. To create a summary table (and open the Group By menu), click the green summation icon to the right of the table name.
When you add this widget to the page, it will have two columns; Client ID and count. Count shows how many appointments exist for each client ID.
In this summary table, we’ll need to create a formula column called ‘Most Recent Appointment Type’. Add a new column by clicking the + icon to the right of the Count column. Then, select ‘Add New Column’. In the Column Configuration Panel on the right hand side of the page, under Column Behavior, click ‘Set formula’.
Then, we enter our formula:
appointments = Appointments.lookupRecords(Client_ID=$Client_ID)
most_recent_appt = max(appointments.Date) if appointments else None
Appointments.lookupOne(Client_ID=$Client_ID, Date=most_recent_appt).Appointment_Type
I’ll walk you through this equation so you can see what it’s doing. To do this, I am going to use two columns; Most Recent Appt and Most Recent Type. Again, this is just so you can understand what the equation is doing.
Most Recent Appt is our helper column. It represents the first portion of our equation. The following is the formula I am using in the ‘Most Recent Appt’ column:
appointments = Appointments.lookupRecords(Client_ID=$Client_ID)
return max(appointments.Date) if appointments else None
You’ll notice that if you replace ‘return’ with ‘most_recent_appt’, it’s the first two lines of our initial equation. The value returned by the equation above is our value for Most Recent Appt.
In formulas, case matters so ‘appointments’ and ‘Appointments’ are two different variables. ‘appointments’ is a generic variable I assigned. You could replace this with ‘X’ anywhere you see it in the equation and it would still work.
I’ll break this formula down into pieces so you can understand what it’s doing;
Appointments.lookupRecords(Client_ID=$Client_ID)
In the ‘Appointments’ table, look up records where the Client ID matches in both tables.
This creates a list of all appointments for each Client ID, as you see in the screenshot below.
The formula above follows the format TABLE_NAME.lookupRecords(COLUMN_A=$COLUMN_B). COLUMN_A is the name of the column in the table being looked up (named at the beginning of the formula) and COLUMN_B is the column in the current table / the table you are entering the formula in. In my example, the table being looked up is ‘Appointments’ and the columns we are comparing have the same name; Client ID.
We assign this list to a variable, ‘appointments’ so now we have:
appointments = Appointments.lookupRecords(Client_ID=$Client_ID)
Moving into the second part of our formula:
return max(appointments.Date) if appointments else None
Return the highest appointment date if there are any appointments. If no appointments, return nothing. Remember, ‘appointments’ is just a variable representing the data returned from the first formula (all appointments for each client ID).
If I just return (appointments.Date), it will give me a list of all appointment dates for that Client ID, like you see in the screenshot below;
So, first line assigns the list of appointments to the variable ‘appointments’ and second line says “Give me the dates for each of those appointments”.
Now, we use the max() function to find the most recent date in that list.
Last, we add the if else statement (…if appointments else None). If appointments exist, run this formula and return a value. If no appointment exists, do nothing. Without this portion of the formula, you’ll get errors for any Client ID that has zero appointments.
Now that we have our helper column, we can use it to find the Appointment Type for the most recent Appointment. The formula used in the ‘Most Recent Type’ column is nearly identical to the last line of the formula used in our ‘Most Recent Appointment Type’ column at the very beginning. The difference here is that ‘Most Recent Appt’ is a column header so it includes a $ in front of it. The Most Recent Appt column is representing the most_recent_appt variable used in our initial formula. Here is the formula for the ‘Most Recent type’ column:
Appointments.lookupOne(Client_ID=$Client_ID, Date=$Most_Recent_Appt).Appointment_Type
The majority of this formula should look familiar the first line of the equation in the ‘Most Recent Appt’ column. Before, we used the lookupRecords formula but here we are using lookupOne. The difference is that lookupRecords returns a list of records and lookupOne returns only one. Both follow the same format, just replace lookupRecords with lookupOne to get the following;
TABLE_NAME.lookupOne(COLUMN_A=$COLUMN_B)
COLUMN_A is the name of the column in the table being looked up (named at the beginning of the formula) and COLUMN_B is the column in the current table / the table you are entering the formula in. In my example, the table being looked up is ‘Appointments’ and the first set of columns we are comparing have the same name; Client ID. The second set of columns we are comparing are the Date column from the Appointments table and the ‘Most Recent Appt’ column in this table. We need both the Client ID and the dates to match to return a record.
So - first portion of the equation is:
Appointments.lookupOne(Client_ID=$Client_ID, Date=$Most_Recent_Appt)
Check the Appointments table for a record where the Client ID matches the Client ID in this table and the Date column in the Appointments table matches the Most Recent Appt column in this table.
One piece of the original equation was left off. You’ll notice that .Appointment_Type is at the end of our lookupOne formula. We refer to this as dot notation.
In the first part of the equation, we found a record where the Client IDs match and the Dates match. Key word is record - the entire row makes up a record. Just using the first portion of our formula, you’ll see below it returns a record. The first returns the record assigned ID = 20 in the Appointments table. Next, is ID =13.
So, we need to get specific information from this record. That’s where dot notation comes in.
Appointments.lookupOne(Client_ID=$Client_ID, Date=$Most_Recent_Appt).Appointment_Type
Find the record in the Appointments table where Client IDs match and Date column matches the Most Recent Appt column. Then return the Appointment Type for that record.
Now, we see the Most Recent Appt Type for the record where Client IDs match and Date = Most Recent Appt!
Ok - let’s put this all back together.
Formula for Most Recent Appt:
appointments = Appointments.lookupRecords(Client_ID=$Client_ID)
return max(appointments.Date) if appointments else None
Represents the first two lines of our original equation:
appointments = Appointments.lookupRecords(Client_ID=$Client_ID)
most_recent_appt = max(appointments.Date) if appointments else None
In our initial equation, most_recent_appt is assigned as a variable. In my example, I used the Most Recent Appt column to represent this variable. What the formula is saying: find all records in the Appointments table where the Client ID in both tables match. Assign ‘appointments’ as the variable to represent this list of appointments. Find the most recent date in this list of appointments. Assign most_recent_appt as the variable to represent this date. I used the Most Recent App column to represent this variable. If no appointments exist, return nothing.
Formula for Most Recent Type:
Appointments.lookupOne(Client_ID=$Client_ID, Date=$Most_Recent_Appt).Appointment_Type
Represents the last line of our original formula:
Appointments.lookupOne(Client_ID=$Client_ID, Date=most_recent_appt).Appointment_Type
Again, the column $Most_Recent_Appt represents the variable most_recent_appt and that is the only difference between these two equations. What the formula is saying: Find a single record in the Appointments table where both the Client ID columns in each match and the Date column matches the value for most_recent_app. Again the key word is record > this will find a record that matches. We need to specify which piece of information we want from that record. We specify Appointment_Type and that’s the final returned value for our equation!
Our final equation:
appointments = Appointments.lookupRecords(Client_ID=$Client_ID)
most_recent_appt = max(appointments.Date) if appointments else None
Appointments.lookupOne(Client_ID=$Client_ID, Date=most_recent_appt).Appointment_Type
I did include the extra columns for the explanation in the document I linked at the top but these columns are hidden. If you want to un-hide columns, go to the Table Configuration menu on the right-hand side of the screen. Towards the bottom, you’ll see Visible and Hidden Columns. To un-hide, hover over the column header in the list and a small eye icon will appear. Click this to make a column visible. You can do the same in the visible column to hide columns as well.
I know that is a lot of information - please let me know if you have any questions. Hope this helps!
