Aug 18, 2020 02:07 PM
I recently discovered Automation feature, and I would like to replace my many dozens of Zapier zaps. However, I am having trouble finding any documentation about it. There isn’t even a community tag for it! When I do any kind of search I come up with nothing. Anyway, I’m stuck in a couple of places, and I could use some help!
I have a very large and kludgy Airtable base for my work that has Projects, People, Tasks, LineItems… over 30 tables, and the information is all connected, which is the purpose of Airtable! The most typical automation is to create a new linked record in several tables when a project is created.
First I found a Script that would do this, and it works great when I click the button. So I copied the script into the Automation to be triggered automatically, and even though I’m passing in the RecordID using the config structures, I just can’t figure out how to make it work. I could use some help there. Here’s the code:
let config = input.config();
let recordId = config.recordId;
let ownerId = config.taskOwnerId
console.log(recordId);
console.log(ownerId)
let table = base.getTable("Timesheet Entries");
let query = await table.selectRecordsAsync();
let record = query.getRecord(recordId);
await table.updateRecordAsync(recordId, {
"Owner": [{id: recordId}],
});
When I run the test it just spins and times out. I would prefer to use scripts to do this kind of operation, but I discovered that creating new records is available as actions. So I tried that, and it works for most of my use cases. When a Project record is added a couple of default budget items are created. In our system, Tasks are children of Budget Items, and I would like to create a default task with the project owner as the task owner. BudgetItem has a lookup for Owner, and I can reference the ownerID, but the test fails.
In other words, using recordID’s pulled from lookups (or a text, I tried that too) throws an error when trying to use that ID to create a new link. (project => budget item => task linked to project owner)
What am I doing wrong?
Aug 18, 2020 03:11 PM
await table.updateRecordAsync(recordId, {
"Owner": [{id: recordId}],
});
^ That is saying the equivalent of “Update the X record so that the Owner of X is X.”. I’m pretty sure you want to say “Update the X record so that the Owner of X is Y”.
Since you never made use of your ownerId
variable I can only guess your script is supposed to say:
await table.updateRecordAsync(recordId, {
"Owner": [{id: ownerId}],
});
You also aren’t using your query
or record
variables so you can go ahead and delete.
Aug 18, 2020 04:27 PM
Thanks so much for your reply. So I fixed the part you suggested:
let config = input.config();
let recordId = config.recordId; // this grabs the ID of timesheet entry record
let ownerId = config.taskOwnerId // this is a lookup of a record ID field in the owners table
console.log("recordId = " + recordId);
console.log("ownerId = " + ownerId);
let table = base.getTable("Timesheet Entries");
await table.updateRecordAsync(recordId, {
"Owner": [{id: ownerId}],
});
Now I’m getting this error:
“Error: Field “Owner” cannot accept the provided value.
at main on line 7”
"Owner: is a linked record field, and I’m passing it Any ideas?
Aug 18, 2020 04:44 PM
Since you’re already console-logging the value of ownerId
, does it report a single value as a string (i.e. recXXXX
), an array of value(s) (i.e. [recXXXX]
), or neither? You’ll need to pass a single value as a string.
Aug 18, 2020 05:25 PM
Yes, there is a single record ID value that is the correct one. I even tried wrapping it in quotes, but I still get the same error.
"Owner": [{id: '"' + ownerId + '"'}], /// no effect
Aug 18, 2020 05:40 PM
Can you paste the exact value you get when you console.log(ownerId)
?
(Also manually wrapping a variable’s output in quotes is unnecessary)
Aug 18, 2020 05:49 PM
btw, in my original post I had also noted an issue attempting something very similar but not within javascript. I was attempting to link a record to a looked up record ID, and I solved the issue by passing in a formula containing the lookup value:
lookupRecordId & “”
which makes it a string.
PS thanks so much for your help!
Aug 18, 2020 06:06 PM
I think I found a workaround! Instead of using a lookup, I used a rollup with arrayjoin as an aggregator, which makes a string. I passed in that variable, and the automation worked!
So has the issue all along been type mismatch?
Aug 18, 2020 06:07 PM
Probably. You said that your ownerId
variable is pulling from a lookup. Lookup fields most often return arrays, not single values. Even though what the console shows appears clean, it may still be an array. Try output.inspect
instead of console.log
. My gut says it’ll show you an array, which means you’ll have to grab the array’s first item, or do what you did and force-convert it to a string.
Aug 18, 2020 06:22 PM
Thanks so much, Kamille and Justin. Figuring this out is key to so many things I need to do! The console output concealing the true data type is certainly confusing! This is not the first time I’ve solved tricky issues by using a rollup instead of a lookup.