Roll Up of Linked Records as Linked Records

[Dara_Molotsky]

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 Lead	Recruiter Phase 1	Recruiter Phase 2	Recruiter Phase 3	Total 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!

[TheTimeSavingCo]

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

[Dara_Molotsky · Accepted solution]

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