How to combine multiple linked records into one field

[Sara_Ophoff]

Hello Airtable Community!

I am trying to build something and I am STUMPED. Hoping someone can help! 

CONTEXT: I run a coaching business that services multiple clients. I am trying to build a tracker that tracks the number of coaching sessions that are booked each month.

I would like to track (1) total number of monthly sessions across all clients and (2) the total number monthly sessions for each client. 

Clients can book up to 3 sessions.

Currently, I am only doing (1) total number of monthly sessions across all clients. I have a table that records each booked session and links it to the month that it was booked in. The linked record then uses a count field to sum up the number of Session #1s, Session #2s, and Session #3s. Lastly, I use a formula field to sum up the total number of All Sessions (#1 + #2 + #3) to get a total of sessions booked for each month.

THE PROBLEM: Currently, since each record is linked separately by session number, I am not able to easily use a Roll-up Field to count the number of each session per client within each linked record. 

See below (sensitive info is masked... you may need to zoom in 🔎!). I have -

1. A linked record for each session 
2. A count of the number of sessions for that month 
3. A total for all sessions that month 

QUESTION: I am wondering if it is possible to combine multiple linked records (in this case 3) into one field (keeping the records linked) titled "All Sessions".

That way, I can pull out the number of All Sessions per month per client by using a Roll-up Field to count the number of sessions per client (client are represented as different colors on the far right of the above screen grab). 

So ideally, the fields would be: 

Session #1 (linked record)

Session #2 (linked record)

Session #3(linked record)

All Sessions (first 3 fields combined into one linked record field)

Count of all Sessions (total sessions per month)

Count of Sessions per month for Black Client (Roll-up)

Count of Sessions per month for Green Client (Roll-up)

Count of Sessions per month for Purple Client (Roll-up)

Count of Sessions per month for Yellow Client (Roll-up)

Count of Sessions per month for Blue Client (Roll-up)

Count of Sessions per month for Red Client (Roll-up)

Is it possible to combine multiple linked records into one field that contains all linked values? Totally stumped and open to any recommendations! 

[TheTimeSavingCo]

Hmm, could you provide a screenshot of the "Sessions" table?  If a single "Session" record is only ever linked to one month and one "Sessions #[Number]" linked field, then this should be doable.  If not, you're going to need to restructure your base I think

[Sara_Ophoff]

Hi Adam ( @TheTimeSavingCo )! 

For sure - here is how the base is currently structured (sensitive info masked + each color represents a different company).

There is (1) the date of each session, (2) formula to convert each session date to month / year format, (3) a linked record to the session data table (shown above!)

Let me know what you think. I am also open to suggestions on how to restructure this table too. I have tried so many things and am totally stuck.

Thanks!!  

[TheTimeSavingCo · Accepted solution]

Hmm, I think if I were you I'd set up a new table where each record represented a single month and company pairing.  You'd then be able to refer to that table to see how many sessions there were per client for each month

I'd accomplish this by:
1. Creating that new table I mentioned, let's call it "Month <> Company"
2. Create a linked field to the "Sessions" table
3. Create an automation that'll trigger whenever "Session #1 Link (PL)" is not empty
  - Action: Update the triggering record by pasting the "Session #1 Stamp" and the "Company" value in the linked field to the "Month <> Company" table, e.g. "Apr / 2023 - Company A"
4. Create the same automations for Session #2 Link (PL) and Session #3 Link (PL)

In "Months <> Company", you can now create a "Count" field that'll display the number of sessions the company had for that month

[Sara_Ophoff]

Adam - @TheTimeSavingCo, 

Thanks! will try this recommendation and that automation makes sense! Would you be able to share a sample of the fields needed in the new table? I am more of a visual learner. 

Would it look like this? 

Session # (1, 2, 3) (text)

Month - Company (linked record)

Count Field (count record)

[TheTimeSavingCo]

The new table would be the following I believe

Month - Company (primary field)

Session # (1, 2, 3) (linked record)

Count Field (count record)

[Sara_Ophoff]

Hey Adam @TheTimeSavingCo - just wanted to say this WORKED! Thanks for your help on this!!