Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

How to combine multiple linked records into one field

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

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 

Screen Shot 2023-07-11 at 10.46.37 PM.png

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! 

1 Solution

Accepted Solutions

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

See Solution in Thread

6 Replies 6

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
6 - Interface Innovator
6 - Interface Innovator

Hi Adam ( @TheTimeSavingCo )! 

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

Screen Shot 2023-07-12 at 9.21.34 AM.png

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!!  

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
6 - Interface Innovator
6 - Interface Innovator

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)

 

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
6 - Interface Innovator
6 - Interface Innovator

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