Can AT directly link two fields that have been indirectly linked in another table?

[Eli_Kent]

I’ve got a situation where a group of three tables all link to each other.

[Workers} links to {Companies} and {Workers/Site Visit}
{Companies} links to [Workers} and {Workers/Site Visit}
{Workers/Site Visit} links to [Workers} and {Companies}

When our agent goes to a job site, she fills out a Workers/Site Visit which lists each worker she encountered on the jobsite and which company each worker was working for.

Let’s say Diane went to the jobsite and met John, who was working for Pink Lightning Construction. She also met Barbara and Francine who were working for Bedrock Concrete. All of this would be reflected in Workers/Site Visit.

The thing that I would like to happen is for the indirectly linking of Juan to John Lightning and Barbara and Francine to Bedrock would trigger a direct link to these companies in their respective records. I could do a rollup field in {Workers}, as you can see in the photos, but that doesn’t resolve my problem satisfactorally.

Does there exist some mechanism within Airtable to directly link fields that are indirectly linked in another table? Or is that something I can/would have to accomplish through Zappier or some such app?

Thank you in advance!

[Justin_Barrett]

Eli_Kent:

Does there exist some mechanism within Airtable to directly link fields that are indirectly linked in another table? Or is that something I can/would have to accomplish through Zappier or some such app?

Airtable doesn’t provide any automatic linking features that would make that possible. While Zapier or Integromat could do the job, you could also do this with the new scripting action beta. Without going into detail, you’d detect a change in that rollup field to see when a worker is connected to a company with which they hadn’t previously been associated. That would trigger a script, and the script could make an actual link using that information.

[Eli_Kent]

So basically the script would be comparing the two “company” fields in {workers} and whenever a new company popped up in the ‘companies rollup’ that wasn’t in the ‘companies linked field’ , it would trigger the addition of that company in the linked field record? Is that right?

I know nothing about scripts. Is that something you think I could reasonably expect to figure out in say half a day? Or is it more complex?

[Justin_Barrett]

You’d need a formula field to compare the two “companies” fields (linked vs rollup), and a view to only show records where there was a mismatch. That view would be the trigger for the script, which would verify the disparity and make the necessary links to sync the link field with the rollup.

Half a day to figure this out? Mmm…maybe. That depends on how quickly you can pick up JavaScript, and also wrap your head around how JavaScript connects to Airtable to make all this happen. If you’d like some one-on-one help, message me and I’ll see what I can do.

[Eli_Kent]

So what I’m asking it to do is compare Array B to Array A and return any value that exists in Array B that isn’t in Array A. Could a formula do that for me? I’m under the impression that I would need script for it.

[Justin_Barrett]

Airtable formulas are very limited in their array processing capabilities, and the process you mention isn’t something that a formula can do. It will definitely require a script.

[Eli_Kent]

Cool. Thanks for your input Justin. I’m going to poke around a little. If I find I need your help I’ll PM you.

:pray:

[Eli_Kent]

I realized that AT’s grouping function in the Worker table accomplishes exactly what the Worker/Site Visit table to do, but more streamlined. No need for any scritping.

Thanks for your help, Justin. I really appreciate it.