Troubleshooting Automations

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?

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.

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?

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.

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

Can you paste the exact value you get when you console.log(ownerId)?

(Also manually wrapping a variable’s output in quotes is unnecessary)

CONSOLE.LOG

  1. “recordId = recmFF1a8zmhQoBSm”

CONSOLE.LOG

  1. “ownerId = recAbW2ArcSIQwM8I”

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!

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?

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.

1 Like

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.

Lookups are great if you don’t need to do much (data-wise) with what you’re looking up, other than see it in the base. The moment you need to actually take that looked-up data and use it elsewhere, it can become troublesome if you don’t remember that it’s most likely outputting an array.

This is a bit of an oversimplification, but a rollup is largely a lookup with a built-in formula. If you don’t use an aggregation formula, it’ll output an array just like a lookup does. However, you can’t always get the same result by using both a lookup and a formula field vs a rollup with its built-in formula, as some functions behave slightly differently inside rollup fields compared to formulas.

Glad you were able to work it out. If you want to limit extaneous fields in your base you could probably force the Lookup value to be a string in the script using ownerId.toString() or String(ownerId).

1 Like

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.