Help

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

Topic Labels: Automations Formulas
Solved
Jump to Solution
251 6
cancel
Showing results for 
Search instead for 
Did you mean: 
hauwi
6 - Interface Innovator
6 - Interface Innovator

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? 🙂

1 Solution

Accepted Solutions
TheTimeSavingCo
16 - Uranus
16 - Uranus

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

Screen Recording 2024-02-15 at 11.10.20 AM.gif

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?

 

See Solution in Thread

6 Replies 6

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

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.

 

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

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

Screenshot 2024-02-08 at 11.24.11 AM.png

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:

Screenshot 2024-02-08 at 11.24.08 AM.png

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
16 - Uranus
16 - Uranus

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

Screen Recording 2024-02-15 at 11.10.20 AM.gif

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?

 

Perfect! Thanks so much for your help.