Identify and link n-to-many related records in two tables

Hello there,

I am trying to achieve a couple of things with my Airtable setup and I am stuck. Hope someone has a good idea.

There are two tables [Main] and [Calc] with the following info.

[Main] Table I create, export and send it to someone else
{Headline} Headline of a record or multiple records
{Headline2} Headline2 of a record or multiple records
{Headline3} Headline3 of a record or multiple records
{Headline4} Headline4 of a record or multiple records

[Calc] Table I receive back
{Headline} Headline of a record
{Headline2} Headline2 of a record or multiple records
{Headline3} Headline3 of a record or multiple records
{Headline4} Headline4 of a record or multiple records
{GroupID} Group id of a record or multiple records
{UniqueID} Unique id per record

Each {Attachment} in [Main] receives a {GID} in [Calc] and there could be multiple records with a {UID} per {GID}. I would like to identify related records and have the information about the relation in both tables [Main] and [Calc] in order to further process linked data. The combination of {Headline},{Headline2},{Headline3} and {Headline4} limits the connected records on GroupID level to a very high degree, is not necessarily unique though. A visual inspection of the attachments of the identified similar records would bring clarity.

Example for a table I create:

[Main]

{Headline} {Headline2} {Headline3} {Headline4} [Attachment]

Test1

Test2

Test3

Test4

Attachment1

Test1

Test2

Test3 Test4

Attachment2

Test1

Test2

Test3 Test5

Attachment3

 

Example for a table I receive in return:

[Calc]

{Headline} {Headline2} {Headline3} {Headline4} [Attachment] {GroupID}

{UniqueID}

Test1

Test2

Test3

Test4

Attachment1

GID1  UID1

Test1

Test2

Test3 Test4

Attachment1

GID1 UID2

Test1

Test2

Test3 Test4

Attachment1

GID1  UID3

Test1

Test2 Test3 Test4 Attachment1 GID1 UID4
Test1 

Test2

Test3 Test4 Attachment2 GID2 UID5
Test1 Test2 Test3 Test4 Attachment2 GID2 UID6 
Test1 Test2 Test3 Test4 Attachment2

GID2

UID7 
Test1 Test2  Test3  Test5 Attachment3 GID3 UID8 
Test1  Test2  Test3  Test5 Attachment3 GID3  UID9 

 
The desired outcome would look like this:

[Main] 

{Headline} {Headline2} {Headline3} {Headline4} [Attachment] {GroupID} {UniqueIDs}

Test1

Test2

Test3

Test4

Attachment1

GID1

UID1, UID2, UID3, UID4

Test1

Test2

Test3 Test4

Attachment2

GID2

UID5, UID6, UID7

Test1

Test2

Test3 Test5

Attachment3

GID3 

UID8, UID9

 

Any ideas?Thank you very much in advance!

 

Cheers
Christoph

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.