# How to combine multiple linked records into one field

Topic Labels: Automations Formulas
Solved
Jump to Solution
1412 6
cancel
Showing results for
Search instead for
Did you mean:
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

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

6 Replies 6
18 - Pluto

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

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).

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

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

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