I am creating a new record and don't know how to populate the 'link' fields

This is my code and I can’t populate the field ‘Activities’ which is linked to another table, the error is in line 24

When writing to a linked field, use an array of record ids, not the record names.

"Activities": [{id: record.id}]
4 Likes

I think the issue is the value of the “id” which needs to be a record ID, not a field value. Perhaps changing record.getCellValue('Name') to record.id will work.

Ooops! My browser just refreshed and I see @kuovonne has already sussed this out.

Thank you a lot,

The problem that I am having right now is that I don’t know how to retrieve the records’ id of the linked table.

I added this code

// new code
let queryResultT = await projects.selectRecordsAsync();
for (let record of queryResultT.records) {
output.text(record.id);
let lastRecordID = record.id;
}
// new code

but here:

create the Event-Activities Records
for (let record of result.records) {
// creates new records in tasks table
await tasks.createRecordAsync( {
‘Event’ : [{id: lastRecordID;}],
/* ‘Activities’: [{id: record.getCellValue(‘Name’)}], */
});

I get an error that says " can not find name ‘lastRecordID’"

Thanks in advance

Benito

@Benito_Abraham, correct - record.id of what table exactly?

This is where I typically create a JSON object containing all of the records with an index based on record.getCellValue(‘Name’) of the linked table. With such an object - indeed, a “hash index” of that targeted lookup table, I can quickly get the record ID of any item based on “Name”.

There may be more elegant ways to achieve this, but this is how I do it.

Hash Index Function

For every record in the lookup table (i.e. tableName), create an element whose ID is based on Name (i.e., fieldName). This function returns an index object that can be used to retrieve the record ID based on Name.

//
// create hash index
//
async function createIndex(tableName, fieldName)
{
    let thisTable    = base.getTable(tableName);
    let queryResults = await thisTable.selectRecordsAsync();
    let oIndex       = {};
    for (let record of queryResults.records)
    {
        oIndex[record.getCellValue(fieldName).toString().toLowerCase()] = record.id;
    }
    return(oIndex);
}

I call this function at the very top of my script so that the JSON object it creates is available throughout the process:

// create an index of name ids
var oLookupTableIndex = await createIndex("My Lookup Table", "Name");

Then, I am able to pull the record ID of any lookup item like this:

... [ { "id" : oLookupTableIndex[record.getCellValue(‘Name’)] } ]

This is not a simple concept, but it is very efficient.

3 Likes

Thank you very much !!

1 Like

@Benito_Abraham Welcome to the wonderful world of variable scope in JavaScript.

You set the variable lastRecordID in the for loop. Notice the use of the let when you declare it. That means that variable exists only within the scope of the for loop. If you try to use it outside that scope, it will be undefined.

// new code
let queryResultT = await projects.selectRecordsAsync();
for (let record of queryResultT.records) {
  output.text(record.id);
  let lastRecordID = record.id;
}
// new code

When you want to use the variable lastRecordID later on, it is undefined, because you are in a different scope.

To fix the problem, you need to declare the lastRecordID variable in a scope that is accessible to both where you want to set it and where you want to use it. (You can declare it without setting a value.) Then, just set it in your for loop.

If you’re new to JavaScript, I recommend reading up on variable scope. Understanding scope can save you a lot of headaches.

2 Likes

@Bill.French
I find the idea of a giant hash table mapping a field value to a record id intriguing, but I don’t quite understand the purpose. (I understand how you do it, but not why.) Would you mind answering a few questions about it?

  • How do you deal with situations when multiple records can have the same value for the given fieldName? Airtable even lets primary fields have the same value, so even the primary field can have repeat values, even thought repeats in the primary field usually indicate a design problem.

  • I noticed that you convert the cell value to lower case. Doesn’t this increase the risk having multiple records with the same key, and thus leaving out a record?

  • Under what circumstances will a script need to lookup the record id from a field value, and can be 100% that there is only one matching record?

  • Do you use this mostly to matchup primary field values to record ids?

Sure…

Well, that would make it not a true hash index, right?

But this can work in your favor depending on the requirements because a has index, where keys are not unique, is essentially a roll-up - an aggregation of sorts and one where you can easily modify the function to include other fields = even those that are accumulating.

This is simply a requirement of the code library I pulled this example from. But it demonstrates how one might create a hash index mapping many case formats into one key. Again, a benefit in some cases but could =create issues in others.

In this circumstance apparently (according to @Benito_Abraham), but I commonly have data sources that were never linked and if you’re going to establish links, you have to look up the record without the benefit of knowing the ID - e.g., a key of some sort, preferably unique.

As you made clear, Airtable doesn’t help with this, so this is a known issue and we have to be cautious when working without a net. :slight_smile: Anyone who is using JSON objects in this manner is likely to have a good data design under the hood. If they don’t - well - not much we can do about that. Airtable allows poor data designs to emerge; with power comes responsibility.

No - index hash objects have many uses; the biggest is performance because it bakes an unlimited number of lookup queries into one query.

1 Like

Thank you for being so patient with my questions! I understand better now.
I can see how dealing with legacy systems that were never properly linked could necessitate such workarounds. You definitely have to work with legacy systems much more than I do! Learning that these problems exist is fun.

That makes sense. I thought that in this particular case, the script was trying to lookup a primary field value in order to lookup a record id, and that confused me.

Thank you for all your help, I just wanted you guys to know that I finally made it work and in case someone may find it useful, want to share the code with you:

// pick tables from your base here
let f_tasks = base.getTable(‘ACTIVITIES (CHECKLIST)’); // activities to be used as a template
let f_projects = base.getTable(‘EVENTS CATEGORIES’); // Events to be used as a template
let projects = base.getTable(‘Events’); // new Events table
let tasks = base.getTable(‘Events - Activities’); // new activities table
// prompt the user to pick a name for our new Event
let name = await input.textAsync('Please type here the Name of the Event you are about to create ');
// prompt the user to pick a template for our new Event
let category = await input.recordAsync(‘Please type here the category of the new Event’,f_projects);
// get all the records from the f_task table that meet the required event category
let JSONCategory = JSON.stringify(category);
let categoryView = JSONCategory.substring(JSONCategory.search(‘name’)+7,JSONCategory.length-2);
let result = await f_tasks.getView(categoryView).selectRecordsAsync();
// create the Event Record
let projectId = await projects.createRecordAsync({
‘Name’: name,
‘Category’: [category],
});

// create the Event-Activities Records
for (let record of result.records) {
var firstLoop = ‘Y’;
if(firstLoop == ‘Y’){
let queryResultT = await projects.selectRecordsAsync();
for (let recordx of queryResultT.recordIds) {
var recordt = [{id: recordx}];
}
firstLoop = ‘N’;
}
await tasks.createRecordAsync( {
‘Event’ : recordt,
‘Activities’: [record],
});
}
output.text(‘Done!’);

Came across this thread while looking for a way to link fields and find record ids. Here is the function I use to find a unique value and get the id.

async function findRecord(tableName, fieldName, searchValue)
{
    let returnID = '';
    let thisTable = base.getTable(tableName);
    let thisQuery = await thisTable.selectRecordsAsync();
    let thisRecords = thisQuery.records.filter(record => {
        let thisID = record.getCellValue(fieldName);
        return thisID == searchValue
    });

    for (let record of thisRecords){
        returnID = record.id;
    }
    return returnID;
}
1 Like