How to referencing a field within a field

[bill1]

Given this record

“fields”: {
“Opportunity name”: “Timbershadow expansion”,
“Owner”: {
“id”: “usrTqRt8glK7HcmEg”,
“email”: “katherineduh+collab8@demo.htable.com”,
“name”: “Casey Park”
}

I want to perform a filter to exclude it. Neither of this worked and I can’t find an info on the pages:

var filter = “NOT({Owner.name} = ‘Casey Park’)”;
var filter = “NOT({Owner}{name} = ‘Casey Park’)”;

[Justin_Barrett]

Welcome to the community, @bill1! :grinning_face_with_big_eyes: First off, I changed the category of your post because what you’re asking about is tied to Airtable’s scripting API, not formulas. Formula functions are not usable inside the scripting environment, which is one of the reasons why the NOT() formula function isn’t working as you listed.

When retrieving a value from a field in a record, it’s not as easy as retrieving the value associated with an object property. You need to use the getCellValue() method to retrieve the cell value first. If Airtable returns an object—as it would with a collaborator field—then you can dive deeper into that object using normal object.property syntax.

Assuming for now that you have a record assigned to a variable named record, you could do something like this:

let owner = record.getCellValue("Owner");
let ownerName = owner.name;

Or as expressed as a single line:

let ownerName = record.getCellValue("Owner").name;

Now let’s talk about filtering. The most common filtering process involves using the filter method on an array, such as the array of records that you can get from a table query. I won’t get into the nitty-gritty of this method, but I will point you to a helpful reference that will explain it. In the examples on that page, the filter function is defined separately, but a more compact design uses an arrow function. This is what I’m using for the example below.

Here’s a short snippet that takes what admittedly-little I know about your base design, combines it with what I showed above about how to get the name of a collaborator, and gives you a way to find all records where Casey Park is not the collaborator in the {Owner} field:

const table = base.getTable("Table Name");
const query = await table.selectRecordsAsync({fields: ["Opportunity name", "Owner"]});
const notCasey = query.records.filter(record => record.getCellValue("Owner") && record.getCellValue("Owner").name !== "Casey Park");

The arrow function used in the filter() method in the third line will only return true for records where the {Owner} field isn’t empty, and the name of the collaborator in that field isn’t “Casey Park”.

[bill1]

Thanks for the detailed response and fixing my title. I am new to Airtable and should have included more context.
Within node.js I am defining an Airtable filter and then appending that to the Airtable REST API in a manner similar to

var filter = this_is_what_I_am_trying_to_determine;
var url = `${BASE_ENDPOINT}/${targetBaseId}/${table}?`;
url += "filterByFormula=" + encodeURIComponent(filter);

In my example I want a filter that excludes opportunities with an owner name of “Casey Park”.
The name value is within the outer Owner field and how to reference that pathing is what
I believe I need. My Google searches only find the simple immediate field case, not 2 level one.

[Justin_Barrett]

Thanks for the clarification. In that context, filter would be based on a formula like this:

NOT(Owner = "Casey Park")

While scripts get a lot more detail about the selection in a collaborator field—or a field that returns a collaborator, like the “last modified by” field—the field will return a string representing the name of the collaborator when requested by a formula, so you only need the field name.

var filter = "NOT(Owner = 'Casey Park')";

[bill1]

Thanks for the info. It is clearer why my searches were not coming up with anything.

Coming from a “dot path notation” mindset I never expected that behavior. So it seems the rule in the filterByFormula world is that the reference to the collaborator field (Owner) returns the underlying field’s name (“Casey Park”)?

FWIW each Opportunity record has an Owner collaborator field which in turn contains name and email fields. It seems that there is no way to filterByFormula using some email criteria then?

I really appreciate your clarification. Safe Holidays.

[Justin_Barrett]

bill1:

Coming from a “dot path notation” mindset I never expected that behavior. So it seems the rule in the filterByFormula world is that the reference to the collaborator field (Owner) returns the underlying field’s name (“Casey Park”)?

Correct, though a more accurate way of phrasing it would be that the rule in the formula world is what you described. What is passed to filterByFormula must be a valid formula, meaning the type of formula that Airtable could accept in a formula field. A good rule of thumb would be to test the formula in an actual formula field first before passing it to filterByFormula.

bill1:

FWIW each Opportunity record has an Owner collaborator field which in turn contains name and email fields.

More accurately, a collaborator field returns an object that contains name and email properties, but that’s only when you’re directly querying a collaborator field from Airtable’s scripting environments. When using the REST API, Airtable forces you to use filterByFormula as a filtering mechanism when querying records, which means you’re limited by what the formula system can do. When a formula retrieves the value of a collaborator field, all that’s returns is a string representing the collaborator name.

That said, there’s one more level to consider. When using the REST API, and Airtable returns records to you, there is more data in those records than just what a formula field can access. But if you want to filter those records before retrieval, it’s done through filterByFormula, which has the restrictions described.

bill1:

It seems that there is no way to filterByFormula using some email criteria then?

Correct, because of that formula-field-access limitation that I just described.

[bill1]

Thank you for the super detailed explanation. I’m sure others in the community will benefit in the future.