Help

Re: Merge two "Link to another record..." fields

Solved
Jump to Solution
2990 0
cancel
Showing results for 
Search instead for 
Did you mean: 
jord8on
7 - App Architect
7 - App Architect

Continuing the discussion from Dedupe - Merge fields from duplicated records, Merge data A (AND) data B, instead of data A (OR) B:

I’ve tried to make sense of a dozen posts about merging fields, etc. but can’t find something that I can understand, to merge two fields - which are referencing records in another table.

Here’s the scenario:

  1. “Table A” has hundreds of sources
  2. “Table B” has a field “Column1” which links to a few records from Table A
  3. “Table B” also has a field “Colum2” which links to some records from Table A
  4. e.g. Colmun1 might link to 6 records
  5. e.g. Column2 might link to 7 records
  6. Some of the records in Column1 and Column2 are the same. Let’s say that 4 records are the same.

We want to combine or merge Column1 and Column2 so that all linked records are in one column, with no duplicate linked records. If we need to create additional columns to accomplish this, that is fine.

Here’s a video with a visualization and explanation of what we’re looking to do:

Any advice, workflows, or formula suggestions on this would be greatly appreciated! :pray:

1 Solution

Accepted Solutions

I’ve created a sample base, you may copy it to your workspace if you like too, let me know.

Here is a Palette table, and the idea is we have colours in three linked fields. An old, a new, and also a merged colour field.

image

The colour table is straight forward, containing colours and their associated linked records to the palette table (which are created automatically when you link fields in the Palette table).

image

The idea is, to trigger the Merge script, simply tick the record tick-box, and then the Automation will trigger and merge the old and new linked fields into the merge field.

The Automation is based on a Record Matches Condition trigger, I then used the Automation conditional Logic cause it may make it easier to follow (you can see instantly that a script executes upon the tick-box being ticked).

image

And the script below takes the record ID’s from the old and the new linked field, combines them into a Set (google javascript Sets) which removes duplicates.

image

The resulting array is then mapped them into the Object format required by Airtable to be written into the third linked field “Merged Palette”.

For this script to work, I pass the tick-box record field from the Automation trigger into the script like this;

image

const inputConfig = input.config();
const table = base.getTable("Palattes");
const myRecord = await table.selectRecordAsync(inputConfig.recordID, { fields: ["Old Palette", "New Palette"]
})

const firstArrayObj = myRecord.getCellValue("Old Palette")
const secondArrayObj = myRecord.getCellValue("New Palette")

//Convert the two object arrays, into one string array. Set will de-duplicate, then need to convert back to array, from that set. 
let deDupedArray = Array.from(
	new Set([...firstArrayObj.map( record => record.id), ...secondArrayObj.map( record => record.id)]));
console.log(deDupedArray);

//We then take that de-dupled string Array, and stamp it into the Object format for use with Airtable Link Field. 
let mergedArrayObj = deDupedArray
	.filter((element) => element != null)
	.map((element) => ({
		id: element
	}));

console.log(mergedArrayObj);

await table.updateRecordAsync(inputConfig.recordID, {
    "Merged Palette": mergedArrayObj,
    "Merge Palette" : false
});

What I’ve demo’d here can be done differently and in various ways, but I’m hoping this will get you to where you need to be. I watched your recording, and couldn’t help but help you more as there’s nothing worse than manual workflows that needn’t be. :slightly_smiling_face:

OK, my lunch break is over, back to my own work! :hamburger:

See Solution in Thread

6 Replies 6

I typically solve Link Merge problems with either the Script Extension, or an Automation Script.

Linked Field objects will fail if you attempt to merge them via script but they’re containing duplicate data. Sets are an easy way to clear out duplicate array items (record IDs), but the trick is to convert an array of objects, to an array string, then de-duplicate that string array, then convert it back to the Object for use with the Link Field.

Hopefully my code explains this better - Here is a screenshot of VS Code, where I use a plugin called Quokka to help me debug as I write (note the dark blue trailing text of Console.Log);

image


//Starting data - two Linked Fields that we need to merge together, removing duplicates.
const firstArrayObj = [{ id: "recABC123"}, { id: "recXYZ321"}];
const secondArrayObj = [{ id: "recABC123"}, { id: "recDEF456"}, { id: "recGHI789"}];

//Convert the two object arrays, into one string array. Set will de-duplicate, then need to convert back to array, from that set. 
let deDupedArray = Array.from(
	new Set([...firstArrayObj.map( record => record.id), ...secondArrayObj.map( record => record.id)]));
console.log(deDupedArray);

//We then take that de-dupled string Array, and stamp it into the Object format for use with Airtable Link Field. 
let newRecordArrayObj = deDupedArray
	.filter((element) => element != null)
	.map((element) => ({
		id: element
	}));

console.log(newRecordArrayObj);

There’s a million ways to code something - this is one method I’ve settled on that seems to work well enough. There might be conditions where it needs to be more robust etc, but it generally works.

Hi,
the task looks quite simple, with no code or script needed, but it seems like you didn’t show most important thing (or maybe I missed it) - how these links looks from ‘other side’.
I mean, Table A has 3 columns linked to table B, with links respective to links from B to A from your video
if this is table B (just took anything from my test from example)
image
then table A should have:
image

concatenate with comma and paste whole formula field in target link field
CONCATENATE(tabB,',',tabB2)

image

btw, that’s how
new Set([...firstArrayObj.map, ...secondArrayObj.map]) from @Karlstens example looks in UI:
image

and by way, if your records in table B has commas or & in primary field, you may have problem with paste. in that case add RECORD_ID() formula field in TabB, took it via lookup in tabA and do the same concatenation/paste. When you paste list of record IDs in link field, it automatically links to them.

p.s if you need to do it several times, I would suggest to fit script above to your needs

I personally find formula’s a pain to use in such a scenario, mainly due to their inability to work with true arrays, things get very messy very quickly - hence me recommending going straight for scripting with this request.

@Karlstens, thank you! The logic here is brilliant… and a script/flow to make it all happen… it would seem.

Unfortunately, I didn’t have the chops to employ the script and get what I needed from it. I tried to add a script from an Airtable extension and didn’t know which fields I needed to change, so kind of got lost, right after pasting your snippet.

But you inspired me so I figured out a flow that is quite laborious but much better than doing it all manually…

  • I went back to Table A and created a formula field that extracted the record id for reach record.
  • I went into Table B and created a Lookup field for Column1 and a second lookup field for Column2 where I was looking up the Record ID field
  • I copied both cells, and pasted in a text editor, and merged them all together, separated by a comma+space
  • Pasted the full list (duplicates and all) in this tool: Remove duplicates from list | Helper Set
  • Copied the output list, then pasted in the new Source of Truth field.

Here’s a video illustration:

I’ve created a sample base, you may copy it to your workspace if you like too, let me know.

Here is a Palette table, and the idea is we have colours in three linked fields. An old, a new, and also a merged colour field.

image

The colour table is straight forward, containing colours and their associated linked records to the palette table (which are created automatically when you link fields in the Palette table).

image

The idea is, to trigger the Merge script, simply tick the record tick-box, and then the Automation will trigger and merge the old and new linked fields into the merge field.

The Automation is based on a Record Matches Condition trigger, I then used the Automation conditional Logic cause it may make it easier to follow (you can see instantly that a script executes upon the tick-box being ticked).

image

And the script below takes the record ID’s from the old and the new linked field, combines them into a Set (google javascript Sets) which removes duplicates.

image

The resulting array is then mapped them into the Object format required by Airtable to be written into the third linked field “Merged Palette”.

For this script to work, I pass the tick-box record field from the Automation trigger into the script like this;

image

const inputConfig = input.config();
const table = base.getTable("Palattes");
const myRecord = await table.selectRecordAsync(inputConfig.recordID, { fields: ["Old Palette", "New Palette"]
})

const firstArrayObj = myRecord.getCellValue("Old Palette")
const secondArrayObj = myRecord.getCellValue("New Palette")

//Convert the two object arrays, into one string array. Set will de-duplicate, then need to convert back to array, from that set. 
let deDupedArray = Array.from(
	new Set([...firstArrayObj.map( record => record.id), ...secondArrayObj.map( record => record.id)]));
console.log(deDupedArray);

//We then take that de-dupled string Array, and stamp it into the Object format for use with Airtable Link Field. 
let mergedArrayObj = deDupedArray
	.filter((element) => element != null)
	.map((element) => ({
		id: element
	}));

console.log(mergedArrayObj);

await table.updateRecordAsync(inputConfig.recordID, {
    "Merged Palette": mergedArrayObj,
    "Merge Palette" : false
});

What I’ve demo’d here can be done differently and in various ways, but I’m hoping this will get you to where you need to be. I watched your recording, and couldn’t help but help you more as there’s nothing worse than manual workflows that needn’t be. :slightly_smiling_face:

OK, my lunch break is over, back to my own work! :hamburger:

A_M
4 - Data Explorer
4 - Data Explorer

The above didn't work for me, but was very helpful. Here's what I used (with my own fields)

 

 

const table = base.getTable("CLIENTS");
let inputConfig = input.config();
//let myRecord = inputConfig.myRecord;
let myRecord = await table.selectRecordAsync(inputConfig.myRecord)

const firstArrayObj = myRecord.getCellValue("Duplicate Check phone")
const secondArrayObj = myRecord.getCellValue("Duplicate Check email")

let deDupedArray;

if(firstArrayObj != null && secondArrayObj != null){
  //Convert the two object arrays, into one string array. Set will de-duplicate, then need to convert back to array, from that set. 
  deDupedArray = Array.from(
    new Set([...firstArrayObj
    .filter((element) => element != null)
    .map( record => record.id), 
      ...secondArrayObj
      .filter((element) => element != null)
      .map( record => record.id)]));
  console.log("deDupedArray");
  console.log(deDupedArray);
}
else if(firstArrayObj != null){
  deDupedArray = firstArrayObj
    .filter((element) => element != null)
    .map( record => record.id);
}
else if(secondArrayObj != null){
  deDupedArray = secondArrayObj
    .filter((element) => element != null)
    .map( record => record.id);
}


if(deDupedArray){
  //We then take that de-dupled string Array, and stamp it into the Object format for use with Airtable Link Field. 
  let mergedArrayObj = deDupedArray
    .filter((element) => element != null)
    .map((element) => ({
      id: element
    }));

  console.log("mergedArrayObj");
  console.log(mergedArrayObj);

  await table.updateRecordAsync(myRecord.id, {
      "Both Duplicate Checks": mergedArrayObj
  });
}

 

And don't forget to create an input variable (over on the left): myRecord