Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 11, 2023 08:29 PM
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!
Solved! Go to Solution.
Jul 13, 2023 12:09 AM
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
Jul 11, 2023 10:32 PM
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
Jul 12, 2023 06:32 AM
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!!
Jul 13, 2023 12:09 AM
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
Jul 13, 2023 02:57 PM
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) |
Jul 13, 2023 08:32 PM
The new table would be the following I believe
Month - Company (primary field) | Session # (1, 2, 3) (linked record) | Count Field (count record) |
Aug 18, 2023 07:55 PM
Hey Adam @TheTimeSavingCo - just wanted to say this WORKED! Thanks for your help on this!!