Oct 02, 2020 11:59 AM
Not sure why I keep getting an error message when I try to automate this script:
I just need the first 5 digits of the Project code pasted into the 5 digit (linked) column. If it doesn’t already exist, it needs to create a new record.
Help?
Oct 02, 2020 12:00 PM
Full code:
//home table – long//
let longTable = base.getTable(“AIR Project List(SubCodes)”);
let queryResult = await longTable.selectRecordsAsync();
let record = queryResult.records[0];
let codeFull = record.getCellValueAsString(“Project Code”);
let fiveD = codeFull.substring(0,4)
//destination table - short//
let shortTable = base.getTable(“AIR Project List (5 digit)”);
let shortQuery = await shortTable.selectRecordsAsync();
let config = input.config();
// Find the matching record//
let matched = shortQuery.records.filter(code => {return code.name === config.fiveD});
let codeRecord;
// If a matching record exists, use it; otherwise make a new record//
if (matched.length)
codeRecord = matched[0];
else
codeRecord = await shortTable.createRecordAsync({"Project Code_First5": config.fiveD});
await longTable.updateRecordAsync(config.recordId, {“AIR Project Code 5digit”: [{id: codeRecord.id}]});
Oct 02, 2020 12:22 PM
Does the script work when there is a matching record?
If it only fails when the script needs to create a matching record, the problem probably is because the createRecordAsync
function returns the id of the new record, not the full record.
Oct 02, 2020 12:39 PM
There are a few errors that I see:
recordId
, but you’re only using it to update the found/created record in the latter part of the script. If I understand your goal correctly, that record also contains the full project code that you’re trying to find in your second table.fiveD
, but no input variable exists with that name.recordId
input variable that’s not currently being used.fiveD
variable, you’re actually only grabbing the first four. The substring
method grabs a series of characters starting with the position indicated by the first parameter, up to but not including the position indicated by the last parameter. That second number should be 5, not 4.Here’s how I think the code should look with those changes:
//home table – long//
let longTable = base.getTable("AIR Project List(SubCodes)");
let queryResult = await longTable.selectRecordsAsync();
let config = input.config();
let record = queryResult.getRecord(config.recordId);
let codeFull = record.getCellValueAsString("Project Code");
let fiveD = codeFull.substring(0,5);
//destination table - short//
let shortTable = base.getTable("AIR Project List (5 digit)");
let shortQuery = await shortTable.selectRecordsAsync();
// Find the matching record//
let matched = shortQuery.records.filter(code => {return code.name === fiveD});
let codeRecord;
// If a matching record exists, use it; otherwise make a new record//
if (matched.length)
codeRecord = matched[0];
else
codeRecord = await shortTable.createRecordAsync({"Project Code_First5": fiveD});
await longTable.updateRecordAsync(config.recordId, {"AIR Project Code 5digit": [{id: codeRecord.id}]});
Oct 02, 2020 02:39 PM
Great job finding and investigating the different issues in the script.
Your code is clearly written and easy to read.
I suggest the changing the variable codeRecord
to codeRecordId
because the createRecordAsync
function returns a record id, not a complete record. You can see the changes in the snippet below.
let codeRecordId; // change name of variable to reflect new value
if (matched.length)
codeRecordId= matched[0].id; // get the id of the record, not just the record
else
codeRecordId= await shortTable.createRecordAsync({"Project Code_First5": fiveD}); // returns an id, not a record
await longTable.updateRecordAsync(config.recordId, {"AIR Project Code 5digit": [{id: codeRecordId}]});
Oct 02, 2020 10:42 PM
Great catch, @kuovonne! I haven’t written many scripts that create records, and still need to be reminded that createRecordAsync
returns just the ID.
Oct 03, 2020 07:40 AM
Unfortunately even with those updates I am still receiving the same error code. The Linked column fields cannot accept that value.
The ultimate goal here is to automate what I would do manually, which is just paste the first 5 digits of the project code into the 5 digit column, which is linked, creating (or using an existing) record in the 5 digit linked tab.
So, still wont let me do that.
Oct 03, 2020 07:43 AM
If i run the code
let longTable = base.getTable(“AIR Project List(SubCodes)”);
let queryResult = await longTable.selectRecordsAsync();
let config = input.config();
let record = queryResult.getRecord(config.recordId);
let codeFull = record.getCellValueAsString(“Project Code”);
let fiveD = codeFull.substring(0,4)
it will output the 5 digits into the log. So I KNOW it pulls the right data from the right place. It just wont paste it into the linked field on that record.
Thank you so much for helping me with this @Justin_Barrett and @kuovonne!!
Oct 03, 2020 08:30 AM
I recommend that you write the script using scripting app so that you can better debug the script. For example, in an automation, you can’t tell where the process is breaking down. It could be that you are receiving the wrong digits. It could be that you are filtering on the wrong value. It could be that the read and write formats are different, etc. There are way too many possibilities given that we don’t know the field types or data values.
Oct 04, 2020 09:53 AM
I have tried the script in the scripting app. It pulls the correct data, and before the If/Then statement console.log(fiveD) returns the correct value. The same error message occurs in the scripting app. The issue is that the fiveD value cannot be added to the linked column field.
Oct 04, 2020 02:03 PM
When writing values to a linked record field in a script (either scripting app or a automation script action) you cannot “paste” in the desired text the way you can with the user interface. To set the value for a linked record, see the write format for a linked record.
Between the code that @Justin_Barrett wrote and the code that I wrote, you should be able to get the record id and write it in the correct format. However, there could still be problems elsewhere. For example. we don’t know the field name or field type of the field in the short table, and either could be causing problems.
It is also surprising that you are getting a five digit code from the line fullCode.substring(0,4)
as I would expect to get only four digits from that line.
It would be easier to help you if you could include screen captures of your data (including column headings), your code, and the error messages from scrpting app.
Oct 09, 2020 08:22 AM
So I played with it some more and got it to write in the 5 digit code value. ( added the output.text to ensure pulling the first 5 digits in a string)
However, it only does it for the first record. How can I fix it to run through all records?
(Or - if this is automated to trigger on a record creation on the Long Code tab, will it not matter?
Thanks again for all your help!
Oct 09, 2020 10:22 AM
Congrats on getting the script to work as a script app. If you convert the script to an automation script action, the script does not need to run through all records. Because automations are triggered by a specific record, each automation run will only affect that one record.
Oct 09, 2020 10:24 AM
If I wanted to leave it as a Scripting App, how would I have it run through all the records?
Oct 10, 2020 12:16 PM
To have the script run through all the records in a view of a table, you need to get all the records in the view, and then loop through them.
let table = base.getTable("Table Name")
let view = table.getView("View Name")
let queryResults = await view.selectRecordsAsync()
for (let record of queryResults.records) {
// do stuff with your record here
// either directly
// or by calling a function that acts on one record
}
May 05, 2021 10:40 AM
I am having the same issue and there does not seem to be a solution. Who would have thought updating values would be so difficult.
What is the value that a linked record is expecting?
Jun 26, 2021 12:30 PM
I’m having a similar issue.
the list is > 10 items, and yet the loop completes with “updateRecordAsync” writing only a single element, the last element in the list.
for (let thisWord of list_of_unique_feeling_words_in_this_transcript) {
let feelingsTableRecord = feelingsTableQuery.records.filter(record => record.getCellValue("Feeling Word") === thisWord.toString());
await memberTable.updateRecordAsync(thisMemberRecordID[0], {
"Overall Feelings Words Used": [...thisMemberRecordID[0].getCellValue("Overall Feelings Words Used"),{id: feelingsTableRecord[0].id}]
});
};
Jun 27, 2021 10:34 AM
I don’t see this sentiment stated here often, but Airtable’s docs, as detailed as they are, are a terrible place to actually learn JavaScript.
Every functionality of the platform is well-chronicled, sure, but in order to read the notation, you’re expected to have a solid grasp of JS context, scope, object-oriented programming, ternary operators, IP/TCP specification (how many “I can’t write a fetch() request, hurr durr” posts are we getting here daily?), JSON standard, immutability, and I’m probably forgetting a bunch of other stuff right now.
I’m not saying this to discourage you @Nicholas_Venturino but to encourage you to get your JS fundamentals elsewhere; Mozilla’s docs are an excellent place to start, as evidenced by how eager Airtable’s PR reps are to link to them, whatever the occasion.
Once you become more comfortable with the language, I’d suggest reading ECMA-262 directly but that’s probably too much right now.
As for how to bend linked record fields to your will, Airtable expects the following format:
let recordToUpdate = "recxyzxyxzxyz" //this is supposed to be a record.id
let recordsToLink = [{id:"recQdBXPuNonFOa4X"},{id:"recQdpxPuOonFOh4X"}]
//assuming your linked record field is called Notes and is located in a table called Table 1
await base.getTable("Table 1").updateRecordAsync(
recordToUpdate ,{
"Notes": recordsToLink
});
Note that this setup will overwritte the linked record field, so if you want to add to its existing contents, you’ll want to make sure the recordsToLink array includes those contents before executing the aforementioned lines.
You should be able to do that by querying the record to update, then doing something in the vein of:
recordsToLink.push(yourQueriedRecord.getCellValue("Notes"))
Again, try to work on your fundamentals in a low-stress environment or otherwise none of this will make too much sense and copy-pasted code is a nightmare to debug, assuming what I typed here even works lol.
Jun 27, 2021 01:47 PM
Very true. Airtable’s scripting documentation is a great place to learn Airtable’s scripting API, but a poor place to start if you do not yet know JavaScript fundamentals. New script writers do not need to know quite as much as Dominik lists in his post, but they do need to know enough JavaScript to be able to read and understand the examples in the Airtable scripting documentation before the scripting documentation will be of use.
Jun 27, 2021 06:31 PM
Just to close the loop for anyone else looking at this issue. I was able to get it working by creating a new array and using two different spread operators.
let arr = []
for (let w of list_of_unique_feeling_words_in_this_transcript){
let feelingsTableRecords = feelingsTableQuery.records.filter(record => record.getCellValue("Feeling Word") === w.toString())
arr.push(feelingsTableRecords[0])
}
if (members_feelings_records != null ){
await memberTable.updateRecordAsync(thisMemberRecordID[0], {
"Overall Feelings Words Used": [...thisMemberRecordID[0].getCellValue("Overall Feelings Words Used"),...arr]
});
}
It’s still unclear to me why my previous code block doesn’t run as expected. I’m guessing it’s something to do with “updateRecordAsync” being an async function.