Best way to extract one client's multiple records from a table, then list info from 3 fields.

[hauwi]

Hi. I'm sorry if this sounds a bit vague, but what I'm trying to do is this:

I conduct sessions with students and each session is a record. At the end of a month I have a table with multiple students and their sessions. Some students will have 4 or 5 sessions per month.

What I want to do is find all sessions for each student, then extract the 'date' and 'duration' fields data to make a list of all the sessions the student has had in that month. I will then use an automation to email this information.

I'm not sure where to start with this one. Is there a nice, elegant way to do this? 🙂

[TheTimeSavingCo]

Ah, to do this I think you're going to need a table where each record represents a single student for a single month

I would tackle this by creating a formula field that created a unique combination in your 'Sessions' table, perhaps a combination of the student's full name and the month year, e.g. 'Jerry Seinfeld - Feb 2024'.  If you've got unique IDs for each student that'd be even better, and you should use those instead of the full name

I'd then use an automation to copy the value of that formula field and paste it into a linked field to that new table I mentioned.  In the new table, I'd then create rollups etc to display the data from the multiple sessions the student had that month

Lemme know if you could use an example and I'll throw something together for you

[hauwi]

Thank you for your help with this.

Great. I do use unique IDs for each student. So I've created a new table called 'sessions by student'. It has the fields: 'Student ID', 'Session Start Date/Time', and 'Duration'. However, I'm not sure exactly how to write the formula you mentioned. So yes, an example would be much appreciated when you have a moment!

I reckon I will be fine creating the automation itself.

Thanks again.

[TheTimeSavingCo]

Sure, here's the formula I used for the examples below:

Name & " - " & DATETIME_FORMAT(Date, 'MMM YYYY')

 

And as you can see, it links the sessions from the same student and month together, allowing you to create the rollups/lookups you'd want:

[hauwi]

Hi - and apologies for the delay. I've been away - whilst working on it sporadically.

I'm annoyed that I can't get my head around this. I've managed to do what you said, then created an automation that creates a new record in the 'sessions' table - which propagates the primary field with the 'unique combination' data. However, the 'sessions' field remains empty.

So, I'm sorry, I probably do need a bit more of a 'walk-through' of the process - including the automation. - If you can possibly find the time and patience!

Very many thanks.

[TheTimeSavingCo · Accepted solution]

Oh for sure, I've put the automation together here for you

Ah the automation needs to copy the value of that formula field and paste it into the linked field of the triggering record, not create a new record in the linked table, does that make sense?

[hauwi]

Perfect! Thanks so much for your help.