Help

Re: Clear/Blank out Linked Field via Automated Script

1601 3
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello all,

I’ve been updating a Record in a Linked Table to point to a different Main Record using the below code;

let _ins = input.config();

let main_table = base.getTable("Main Table");
let linked_table = base.getTable("Linked Table");

let main_rec    = _ins._get_record;
let linked_rec  = _ins._get_linked_record;
let linked_val  = _ins._get_linked_value;

console.log(`main_rec:   ${main_rec}`);
console.log(`linked_rec: ${linked_rec}`);
console.info(`linked_val: ${linked_val}`);

if (linked_val == "Not Listed") {
    console.warn("Not Listed - Clearing and rewriting entry");
        
    await linked_table.updateRecordAsync(`${linked_rec}`, {
        "Linked Text": `Example updated ${linked_table} record text`,
      //"Main"   : [{id:`${main_rec}`}] //Field is updated with expected record via recordID
        "Main"   : [{id:`${""}`}] //Attempt to clear field is not working...
        });
    console.info(`Updated Table "${linked_table.name}" record ${linked_rec} with "${main_rec}`);
};

This particular line of code works, updating an entry

//Field is updated with expected record via recordID
 "Main" : [{id:`${main_rec}`}] 

But what if I want to update the entry with a blank value, clear out the field for that record?

// Attempt to clear field is not working
"Main" : [{id:`${""}`}] 

I’ve tried null, undefined, and a few other ideas - none of which are executing. Keen for some help if anyone can spare a moment.

I’m also keen to rewrite my code with more direct calls. For example, although this works it makes me feel uneasy, and I’d like to know the preferred syntax;

    await linked_table.updateRecordAsync(`${linked_rec}`, {}

I’m sure I’m not to be using string literals for record ID lookups :joy: … yeah it works, no I’m not proud of it. Appreciate some pointers!

13 Replies 13

This should work:

"Main" : [] 

As to your other questions about preferred syntax and pointers, I need those too hah, sorry I can’t help with those

Ahh-haha, I just had to step out of the object! :dizzy_face: Thanks for that.

Interesting that you too are calling Linked Fields via string literals. Will be keen to see what others have to say.

Hi @Karlstens, just sharing my approach to making these kinds of calls and reducing the amount of code lines:

I usually don’t assign the input.config to a variable and then also don’t assign the input.config values to variables. So I just access the values directly. For the linked record, you’ve selected the Record_ID I guess. So the code for updating the linked table would be:

    await linked_table.updateRecordAsync(input.config().linked_rec[0], {...})

Of course linked fields can have multiple records, so rec ids are always stored as an array, even if it’s just one linked record. That’s why it’s safe to access the first and only array item here.

I hope that was what you were interested in and not just me rambling in the Airtable community haha

Hi,

I would return to task description. How do you define a Main Record for that? If you search for some value, for example, by ‘Find Records’, to find single id, you can receive zero or multiple values.
So, your input data for main expected to be be array of IDs, not a single id.
Of course, you can revert it to a single id (array with single element), but then you need to solve ‘what if zero/multiple’ in your code.

When I learned to write scripts, I always wasted 30-50% of time on final part, constructing update or create arrays with all those different types of brackets. So, I turned to reusable arrow functions. And learned array transforming methods instead of looping through single variables.

In your case, I would use (considering main_rec is array, linked_rec is ID).

const link=rec=>({id:rec.id})
await linked_table.updateRecordAsync(linked_rec,{'Main':main_rec.map(link)})

btw, you can use same variable names for input data (i mean, main_rec instead of _get_record, linked_rec instead of _get_linked_record, etc… ) and then use destructuring syntax:

let {main_rec,linked_rec,linked_value}=input.config();

Thanks for the help @Rupert_Hoffschmidt and @Alexey_Gusev - this advice is priceless.

I’ve now a much better understanding of where I was tying myself up in knots, but am no longer going around in circles.

Regarding arrow functions, I’ve been learning them in the various JS tutorials that I’ve been following over the past year, and you’re right - it’s time to start flexing them in my Airtable scripts. The problem with watching tutorials - is they’re never in context of your real world projects, sometimes I get stuck in the forever-student learning loop, and takes a day or two to warm back up to writing decent scripts.

I’ll refresh a few things tomorrow and post my refinements at some point.

Heya Alexey,

Any tips for processing a selection of record? For example, I have a for loop that executes, but know from my parallel JavaScript learning that there’s a million ways to do this, may of which have been modernised (such as your example of an arrow function technique).

Simple working example below - that I believe could be written better - I’ll have a tinker with some ideas over the weekend, pending on yours and others feedback.

let table = base.getTable("Records");
let query = await table.selectRecordsAsync({fields: []});
let records = query.records;
for (var inc = 0; inc < records.length; inc++) {
    await table.updateRecordAsync(records[inc].id , {
             "Number": 1,
        }
    );
}
console.log(`Updated ${inc} records to number 1`);

Most AT scripts are transformation of some input data (usually queried by selectRecordsAsync) to some output array for updateRecordsAsync or createRecordsAsync.

My usual pattern is:
const table=base.getTable(‘SomeTable’)

const query=await table.selectRecordsAsync({fields:[some_fields]})

const upd=query.records.map(SOME_FUNCTION)
while (upd.length) await table.updateRecordsAsync(upd.splice(0,50))```

the rest are chain of transformation with map, filter, find, sometimes reduce. JS “array helpers”
forEach is a compact version of for loop, but sometimes script can be written without direct loop command at all.

Beginners usually avoid multiple updates command, but looping single update with updateRecordAsync can cause problem when you have hundreds of records to update, since AT has limit for updates per second.
from example:

     // Only up to 50 updates are allowed at one time, so do it in batches
        while (updates.length > 0) {
            await table.updateRecordsAsync(updates.slice(0, 50));
            updates = updates.slice(50);
        }

With new JS syntax, this can be rewritten, because splice does both operations: cuts 50 elements from array (updates.slice(50)) and returns them to the function which called it - updateRecordsAsync(updates.slice(0, 50));
Also, 0 considered as ‘falsy’ value, so while (updates.length > 0) can be changed to while(updates.length)

so, it turns to
while (updates.length) await table.updateRecordsAsync(updates.splice(0,50))

(before using functions from other people code, to learn coding instead of learn copy-pasting, you must be sure to understand how they working :grinning: )

there are also two very useful types of data: Set and Map
another good practice: instead of ‘hardcoding’ text values everywhere in code, they should be assigned to variables somewhere at the beginning, sometimes inside single function or array.

for example, script to mark duplicates:

const table=base.getTable('SomeTable')
const [CHECK,MARK,DUP]=['Checkfield','Markfield','Duplicate']
const query=await table.selectRecordsAsync({fields:[CHECK]})
const val=r=>r.getCellValue(CHECK)
const valueset=new Map(query.records.map(rec=>[val(rec),rec.id]))
const others=query.recordIds.filter(id=>(![...valueset.values()].includes(id)))
const dupes=new Set(others.map(id=>val(query.getRecord(id))))
const update=r=>({'id':r.id,'fields':{[MARK]:DUP}})
const upd=query.records.filter(r=>dupes.has(val(r))).map(update)
while (upd.length) await table.updateRecordsAsync(upd.splice(0,50))```

for your example case, that’s what I mean about transformation

let table=base.getTable("Records");
let query=await table.selectRecordsAsync({fields: []});
let setOne=r=>({'id':r.id,'fields':{"Number": 1}})
let upd=query.records.map(setOne);
while (upd.length) await table.updateRecordsAsync(upd.splice(0,50))

This is the best. Thanks so much for these pointers. At the moment, as my projects only require a few records to be updated during Automation execution, I knew that I could get away with my knowledge of updateRecordAsync but new the sooner I venture into proper bulk execution of record updating using the technique you’ve described, the better!

I will setup a test project today with a few thousand records, and see if I can toggle some numbers and strings in batches on them all via updateRecordsAsync. :grinning_face_with_big_eyes:

How are you setting the input variable _get_linked_record? Is it the result of a “Find Records” action or is it from a triggering record? What does typeof linked_rec produce? If linked_rec is a string, why not use the variable by itself?

It’s a mix of Automation input, but also record queries. This morning my previous JS learning finally “clicked” in my head of how to approach problems within Airtable scripts, and now I’m leveraging .filter and .map by chaining them together, and drawing data out of getCellValue etc.

//console.log(query.records.filter( record => (record.getCellValue("Numbers") == "0")).map( record => record.getCellValue("Important Note")));

It’s all coming back to me, just like riding a bike! Feels good. :smiling_face_with_sunglasses:

This looks suspicious to me. It looks like you are comparing the value of a number field with a text string, and the two would never be equal. If the “Numbers” field actually returns a text string that looks like a number, then it is badly named.

Yes, good spot. But it’s purposefully a string field filled with leading zero number entries that have a set/locked digit length. Aren’t inherited projects just the dandiest things?