Filter by linked table data

[sull]

I am trying to do a curl command where I filter results from a linked table that has multiple linked records… I have the record ID for the linked table record. Tried some encoded filterbyformula variations but get zero results.

linked field name is “sections”. I tried this most recently:

filterByFormula=FIND(%22rec0nTsg5N5p1i5Pv%22%2C+%7Bsections%7D)

Thanks for help.

[Kasra]

If you want to filter by linked record IDs, create a formula field in the linked table with the RECORD_ID() formula. Then create a lookup field in the original table to lookup the record ID from the linked records. Then you can filterByFormula on the lookup field.

In formulas, the value of a linked record field is the primary cell values of the linked records, not their IDs, which is why what you tried doesn’t work.

By the way, for debugging, you can create a formula field in the table you’re querying with the same formula. If the formula evaluates to a non-empty non-zero result, that record would be returned in a filterByFormula query.

[sull]

Thanks for clarification, I will try that.

[Chester_McLaugh]

My approach is to make multiple requests using the RECORD_ID() function in the filterByFormula parameter. This allows me the flexibility to filter any request by any field of a linked table while keeping my tables free from data that end users don’t care about. Admittedly, this requires some code and logic in your application, so if you’re just doing a one-off request, you’re probably better off doing what @Kasra outlined.

Before I forget, check out Postman, it’s the best thing that ever happened to APIs!

But for an example, let’s consider two tables: Events and Locations, each with a reciprocal linked field of the same name. To get Events by Location, I work backwards and first get Locations (filtered by whatever I want).

To filter Events by two Locations, I would first get Locations by specifying the RECORD_ID:
/Locations?filterByFormula=OR(RECORD_ID()='recXXXX',RECORD_ID()='recXXXX')

Or I could filter by any other field in the Locations table:
/Locations?filterByFormula=OR({Name}='Sammys Pub',{Name}='Jimmys Tavern')

And if you don’t need any of the fields from the Locations table (i.e. you only wanted to filter by it, not retrieve it and use it), then you should specify the fields parameter so that it’s a “leaner” request:

/Locations?filterByFormula={Zip Code}='12345'&fields[]=Events

So, now that we have a list of Event IDs “filtered” by whatever parameters from the Locations table we wanted, we can simply create a filterByFormula string from the array of Event IDs
/Events?filterByFormula=OR(RECORD_ID()='recXXXX',RECORD_ID()='recXXXX',...)

I have found this approach to be extremely robust (I’ve specified >500 record IDs in a single request without issue, taking paging into account of course). It’s also extremely flexible, allowing API requests to filter based on any field from any linked table.

If you go this route, the next hurdle will be to create an “index” of results based on record ID so that while you’re looping through your Events, your code can reference the event specified in the Location field. This also can save a lot of memory as a single location could be referenced by EVERY event, but you would have to transfer and store in memory all that duplicate information.

[sull]

Great response guys, thanks!

[Andres_Rodrigue]

Is there a reason why this is the case? It seems rather inconsistent that when filtering on a linked field you have to search by the value, yet whenever you do practically anything else, you do so with the record ID. As evident by this post, this creates confusion. It would be nice to be able to filter by record IDs (via some sort of formatting standard) without having to create those extra record ID fields.

Either way, you should make it clear in the API documentation what users should do in order to accomplish this.

[Eric_Ferrer-Vau]

@Chester_McLaughlin @Kasra This doesn’t make much sense . I have a lot of questions. Here is my records schema

{
“records”: [
{
“id”: “recR6gmhuyqdGom4k”,
“fields”: {
“Account”: “Apple ID”,
“Category”: [
“recheGQTsNkOv2iSd”
]
},
“createdTime”: “2017-12-07T17:55:47.000Z”
},
…
}

Category is my linked table. I simply want to only return accounts that have a specific category. I have tried FIND, SEARCH, RECORDID(), ARRAYJOIN and I still get back no results.

why cant i just do ?filterByFormula=(Category = “recheGQTsNkOv2iSd”)

FYI, I am trying to do this as an HTTP request, (do I have to encode?)

Any help would be gladly appreciated

[IT_BeeTee]

Hi Eric,

In case you still need the answer, I think your request is incorrect because recheGQTsNkOv2iSd is the specific Category’s technical ID; thus if you filter it by such value, nothing will be returned unless you have a Category with the exact value of recheGQTsNkOv2iSd. Therefore, it only makes sense if you use that id to fire a request back to Category table in order to obtain its corresponding value. However, I personally find it extremely inconvenient as well as resource consuming especially when your database is quite large. Thus I would suggest a neat workaround that I have been using which is to create a new Formula column named Category Text for example, then simply enter Category as its’ formula to obtain the Category’s value from the current table. From now on you would be able to request based on that value.

Good luck with your work,

Alex

[Michael_Cetrulo]

These solutions do not work, filtering by record id should be the right approach but no results are being returned and yes the values are correct.

Can someone provide an actual working example?

[Michael_Cetrulo]

To be more specific when linking tables the API returns an array of record ids, filtering by such records returns zero results.