Merge two "Link to another record..." fields
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.
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).
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).
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.
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;
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:
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);
//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)
then table A should have:
concatenate with comma and paste whole formula field in target link field
CONCATENATE(tabB,',',tabB2)
btw, that’s how
new Set([...firstArrayObj.map, ...secondArrayObj.map]) from @Karlstens example looks in UI:
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’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.
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).
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).
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.
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;
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:
