Help

Airtable Cobuilder is here! Learn more about our new no-code app creation feature, powered by AI on the Airtable Academy

Re: Roll Up of Linked Records as Linked Records

Solved
Jump to Solution
1258 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Dara_Molotsky
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi all,

I'm looking to roll up a set of linked records into one total linked records field.

I am building a sales CRM and have different recruiters being labeled for different phases. Below is an example of my "leads" database. All items in brackets are [linked records] to the recruiter tab, where i've build recruiter interfaces for them to interact with their current lead statuses and etc.

For example:

Prospect LeadRecruiter Phase 1Recruiter Phase 2Recruiter Phase 3Total Recruiters
Prospect 1[Sally][Jane][Sarah][Sally] [Jane] [Sarah]
Prospect 2[Jane] [Sally] [Sally][Jane] [Sally]

Right now, I can't get the "total recruiters" field to autopopulate based on linked records in phases 1, 2, and 3. Recruiters, via the interface, can select a different recruiter to be on phase 2 or 3 once past phase 1. Ideally, I want every recruiter who is in touch with a prospect to be linked in the "total recruiters" column so that recruiters can easily see all of the prospects they have, and then what phase they are assigned to within that particular prospect.

I just need a way to "rollup" the linked columns for phase 1, 2, 3 into a linked record field for total recruiters. I've been able to run scripts before to look up matching fields and return the correct linked record in a different table... so I know it's possible in theory. I've looked into automated scripting for this specific use case but I'm a beginner at JS so haven't been able to manipulate the code to do what I need. 

Thanks in advance for the help!

1 Solution

Accepted Solutions

Hey, figured this out. Needed to add a "find records" action before I could update them.

Automations

When record is updated: 
- When the "formula total recruiters" field is updated (which lists Recruiter 1, 2, 3 with conditional commas for linked record identification, formula: 

REGEX_REPLACE(
CONCATENATE(
IF({Recruiter Phase 1}, {Recruiter Phase 1} & ", "),
IF({Recruiter Phase 2}, {Recruiter Phase 2} & ", "),
IF({Recruiter Phase 3}, {Recruiter Phase 3} & ", ")
),
", $",
""
)
Find Records
- Find recruiters where their link to phase 1 lead, phase 2 lead, or phase 3 lead are not empty
 
Update Record
- Find original record in Leads table
- Update with formula field from step 1

See Solution in Thread

2 Replies 2

Hmm, if the primary field in the "Recruiter" table is unique, you shouldn't need scripting and should be able to do this via automations I think

Try this:
1. Create a "Formula" field to gather all the values of the "Recruiter Phase 1" to "Recruiter Phase 3" fields
  - So it should look something like "Sally, Jane, Sarah" or "Jane, Sally"
2. Create an automation that will trigger if "Recruiter Phase 1", "Recruiter Phase 2" or "Recruiter Phase 3" are updated
  - Give it an "Update record" action, and its action will be to paste the value from the formula field from step 1 into the linked field "Total Recruiters"

This should work I think

Hey, figured this out. Needed to add a "find records" action before I could update them.

Automations

When record is updated: 
- When the "formula total recruiters" field is updated (which lists Recruiter 1, 2, 3 with conditional commas for linked record identification, formula: 

REGEX_REPLACE(
CONCATENATE(
IF({Recruiter Phase 1}, {Recruiter Phase 1} & ", "),
IF({Recruiter Phase 2}, {Recruiter Phase 2} & ", "),
IF({Recruiter Phase 3}, {Recruiter Phase 3} & ", ")
),
", $",
""
)
Find Records
- Find recruiters where their link to phase 1 lead, phase 2 lead, or phase 3 lead are not empty
 
Update Record
- Find original record in Leads table
- Update with formula field from step 1