Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Retreiving nested object id from Linked Record return

Topic Labels: Automations
Solved
Jump to Solution
4301 4
cancel
Showing results for 
Search instead for 
Did you mean: 

Hey all,

I’ve been happy with my JavaScript progress but have hit a bit of a roadblock this morning, hopefully it’s an easy one.

I’m parsing records with a matching string Name field - in this example “Tropical :palm_tree: ”. Then, from each returned record, I’m then filtering out any records where “myLinkedField” field is null.

image

From the four records found, we return the three with Colours.

What I’m now trying to achieve is to grab the array of linked “Colours” Record Ids found in the remaining filtered records (3 in total) - which I can do, but not in the desired return structure.

My filter/map code returns the correct data, but I’m needing help to massage it into a simple array that I can then use to stamp into the original record after deleting the duplicates.

//Tropical 🌴 De-dupe
let table = base.getTable("Themes");
let themeQuery = await table.getView("Amazing Themes").selectRecordsAsync({
    fields: ["Theme Name","Created","Colours"],
        sorts: [
        {field: "Created", direction: "asc"},
    ]});

let duplicateRecordsId = themeQuery.records.filter( 
    record => (
        record.getCellValue("Theme Name")   === "Tropical 🌴"
    &&  record.getCellValue("Colours")      !== null
    )).map( 
    record => [record.getCellValue("Colours")]);

console.log(duplicateRecordsId)

Console Log looks good, but as you can see - the id object data I’m trying to access is buried. Example below;
image

Worth noting that some Objects may contain multiple id’s themselves - for example;
image

So I’m needing to understand our options for mutating this return so that I may build an array such as;

['recuzJljXWT9xMILS', 'recgGfX0WjTBALgGH', 'reckjjVgEHBM0nSgM']

From which I can then convert the simply array into an object to use to create Multi-Linked Records (with the below code).

let Content = ['recuzJljXWT9xMILS', 'recgGfX0WjTBALgGH', 'reckjjVgEHBM0nSgM']
var myCombinedContent = Content.map(each => ({
    id : each
}));

The above returns this, which is perfect for use.

[ { id: 'recuzJljXWT9xMILS' },
  { id: 'recgGfX0WjTBALgGH' },
  { id: 'reckjjVgEHBM0nSgM' } ]

Greatly appreciate any help here. I just can’t seem to get the syntax correct in my .map()

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

First off, you’ve created a nested array with this part of of your current code:

...
record => [record.getCellValue("Colours")]);

The square braces around the value retrieval create a literal array, meaning that for each record the arrow function will return a single-item array that contains the array of link data for the {Colours} field. Get rid of the square braces and you’ll remove that extra array.

To get the IDs themselves, use another .map inside that last arrow function:

...
record => record.getCellValue("Colours").map(link => link.id));

The problem is that you still have an array of arrays, effectively something like this:

[
    ["recuzJljXWT9xMILS"],
    ["recgGfX0WjTBALgGH"],
    ["reckjjVgEHBM0nSgM", "recp553s0No2mgE6P"],
]

Thankfully JavaScript arrays have a .flat method that can mash all of that down into a single array. Tack that onto the very end and you’ll have a single array of IDs:

let duplicateRecordsId = themeQuery.records.filter( 
    record => (
        record.getCellValue("Theme Name")   === "Tropical 🌴"
    &&  record.getCellValue("Colours")      !== null
    )).map(
    record => record.getCellValue("Colours").map(link => link.id)).flat();

As an aside, you can also simplify some other parts:

  • Because the {Theme Name} field is the primary field, you can access its contents using the .name attribute on the record.
  • Similar to short-circuiting an Airtable IF() function by looking for any content in a field like this:
    IF(Field, result_if_true, optional_result_if_false)
    You can short-circuit a .getCellValue check and skip the comparison against null like this:
    record.getCellValue("Colours")
    Any non-null value will be equivalent to true.

Combining those with the other changes above, the modified code looks like this:

let duplicateRecordsId = themeQuery.records.filter(record => 
    record.name === "Tropical 🌴" && record.getCellValue("Colours")
    ).map(record => record.getCellValue("Colours").map(link => link.id)).flat();

See Solution in Thread

4 Replies 4
Justin_Barrett
18 - Pluto
18 - Pluto

First off, you’ve created a nested array with this part of of your current code:

...
record => [record.getCellValue("Colours")]);

The square braces around the value retrieval create a literal array, meaning that for each record the arrow function will return a single-item array that contains the array of link data for the {Colours} field. Get rid of the square braces and you’ll remove that extra array.

To get the IDs themselves, use another .map inside that last arrow function:

...
record => record.getCellValue("Colours").map(link => link.id));

The problem is that you still have an array of arrays, effectively something like this:

[
    ["recuzJljXWT9xMILS"],
    ["recgGfX0WjTBALgGH"],
    ["reckjjVgEHBM0nSgM", "recp553s0No2mgE6P"],
]

Thankfully JavaScript arrays have a .flat method that can mash all of that down into a single array. Tack that onto the very end and you’ll have a single array of IDs:

let duplicateRecordsId = themeQuery.records.filter( 
    record => (
        record.getCellValue("Theme Name")   === "Tropical 🌴"
    &&  record.getCellValue("Colours")      !== null
    )).map(
    record => record.getCellValue("Colours").map(link => link.id)).flat();

As an aside, you can also simplify some other parts:

  • Because the {Theme Name} field is the primary field, you can access its contents using the .name attribute on the record.
  • Similar to short-circuiting an Airtable IF() function by looking for any content in a field like this:
    IF(Field, result_if_true, optional_result_if_false)
    You can short-circuit a .getCellValue check and skip the comparison against null like this:
    record.getCellValue("Colours")
    Any non-null value will be equivalent to true.

Combining those with the other changes above, the modified code looks like this:

let duplicateRecordsId = themeQuery.records.filter(record => 
    record.name === "Tropical 🌴" && record.getCellValue("Colours")
    ).map(record => record.getCellValue("Colours").map(link => link.id)).flat();

Hi,
just a brief pack of ideas

  • use flatMap() instead of map
  • remove [] from [record.getCellValue("Colours")]
    to remove duplicates from array, add it to Set, then spread the Set back to Array. (add nonul filter if needed)
    let dedupedContent=[...new Set(Content)] . filter(n=>n)

You can directly convert it to final form removing other properties besides ‘id’
let delname=({id,…others})=>({id}) , but i’m not sure if dedupe and filter will work as expected…

I can’t thank you enough for sharing your knowledge. This is excellent!

@Alexey_Gusev - good spot, I was causing my own troubles on top of trying to find the correct solution. :grinning_face_with_sweat: I was having a go at destructuring before I decided to employ @Justin_Barrett 's solution.

With destructuring in mind, I’m curious if it’s actually of any use here? But time to sleep on it - I need to give my brain a rest! :brain:

As I now have a working solution in my sandbox I’ll be closing this thread. :slightly_smiling_face:

A few tips for others venturing into this territory;

  • Learn JavaScript Sets(). They’re perfect for parsing an array of record IDs that may contain duplicate recordIds and will remove them without fuss. Your code might look a little strange as you need to convert an array into a set (which is not an array but looks similar) and then back to an array, but it works regardless. Example;
    let myClensedArray = Array.from(new Set(myArrayFullOfDupeIds))
    You can nest the entire filter/map exercise within the new Set(), at least I did so without problems.

  • I tested approx 2,000 record match and didn’t notice any performance issues. AT Automation performed quickly and as expected. In reading about others who’ve experienced performance degradation, I suspect this might be more due to heavy linked formula/roll-up use, which I’m also using but within reason. I’ll see if I can simulate the production environment more closely and anticipate if they’ll be any unsightly delays with de-duping upon record creation (not that it’s a huge issue anyway, as records are created via Form or existing record duplication).

  • Array.shift() works well, it passes the first array element (from for example, an array of RecordIDs) to a variable as a string, and mutates the remaining array by subtracting that shifted first element. So long as you sort your query and the first array RecordID is the record you wish to keep, then shift() will separate the record from the others allowing them to then be deleted. I sort my dupes by record creation date, and delete the newest duplicates whilst keeping the very first original record.
    let keepRecord = duplicateRecords.shift();

  • I’m testing out a guard clause technique using throw, such as “throw” when a new record is created but no duplicates are found;
    throw console.log("No duplicates found. Exiting");
    This works, but I’m not sure if this will cause me to receive error notifications when an automation exists. Haven’t checked my phone yet…

  • And finally, I found that using VS Code with the Quokka extension (you can get away with Community edition, but Pro does offer the bundled Wallaby Explorer which is great for exploring large/deep objects) allows for substantially faster development. I realised over the weekend that the easiest way for me to work with Airtable data would be to JSON.stringify all my Airtable queries and then take them into VS Code to get a better understanding of them, figure out how to hack-them-up, and then copy my local code back into the Automation to then watch magic happen. :smiling_face_with_sunglasses:

  • 12HOURLATERNINJA-EDIT: Destructuring! Almost forgot - it definitely has it’s place, such as when you need to process a single record but have been sent an array/list of records. So long as you know that the list is only containing one record (or you’re OK with disregarding every record in the array with exception to the first element[0]) then use destructuring to copy that record. The below code passes the first recordID from existingRecordIdArray into the keepRecord variable using destructuring, where the brackets/braces are placed on the left side of the equals operator.
    let [keepRecord] = inputConfig.existingRecordIdArray;

OK, that’s my write-up for now. Thanks again for your help everyone. :beers: