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.
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’"
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:
@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.
@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?
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. :slightly_smiling_face: 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.
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.
