How to search for specific value through API filterByFormula?

[Robert_Andrews1]

My “Published URL” column contains URLs of web pages.

Through API, I would like to be able to pull only the record containing a URL I supply in the call.

I am already successfully making a curl call with this…

  $base = 'appPtTr2kigmLlCP8';
  $table = 'Content';
  $view = urlencode('Published (Robert)');

  // $fields = urlencode('Published URL'); // Only return these fields, cf. https://community.airtable.com/t/using-fields-filter-in-list-of-records/940
  if (!empty($offset)) {
    $q_offset = '&offset='.$offset;
  }
  $airtable_url = 'https://api.airtable.com/v0/' . $base . '/' . $table;

  // construct the url query
  $url = 'https://api.airtable.com/v0/' . $base . '/' . $table . '?maxRecords=5&pagesize=1000&view='.$view . $q_offset;

But how can I make use of the relevant feature (is it filterByFormula?) to add

Elsewhere on the forum, I have seen reference like…

filterByFormula=SEARCH(‘riva’%2C+%7BLast+Name%7D)

But I have not crystallised how I would perform a query for the record with a specific “Published URL” value.

How do I do this, and what do I need to know about encoding a URL and the whole query parameter pair?

Thanks.

[Giovanni_Briggs]

filterByFormula allows you to apply an Airtable formula to limit the records that are returned. If I understand your question correctly, rather than trying to fetch a record by its Airtable record ID, you want to fetch it based on a different field value.

You can do this by doing filterByFormula={Field Name}="value". When your field names have a space in them, you have to wrap the name in curly brackets (you can also do this for field names that don’t have a space character, but it is not required). You should be aware that making a GET call with filterByFormula will always return a list of records, even if that list only has one value in it.

You can use this Airtable API Encoder to check how you should encode your data.

[Momen_Zakaria]

But how can I use filterByFormula to search for a specific containing string, i.e: If I’m searching for a book name like “UX design” and the search term was just ‘ux’ how to do this?

[Mark_Friedman1]

Reading the documentation at support.airtable.com/hc/en-us/articles/203255215-Formula-Field-Reference, your formula would look something like:

SEARCH("ux",{Field Name})

Using the Airtable API Encoder (at codepen.io/airtable/pen/rLKkYB) suggested above, the corresponding URL would look something like:

https://api.airtable.com/v0/YOUR_BASE_ID/Table%201?fields%5B%5D=Notes&filterByFormula=SEARCH(%22ux%22%2C+Notes)&api_key=YOUR_API_KEY

Hope this helps.

-Mark

[Felix_K]

Is it possible to somehow replace the Field Name {Field Name} part of the URL string by the AirTable Field ID fldnXGeRDd2r9SeRb?
I want to avoid that the code breaks in case the column is renamed.

You can find out the Field ID by opening Chrome or Firefox Code Inspector and searching for the column header’s parent div which looks like:
<div class="cell header primary read" data-columnid="fldnXGeRDd2r9SeRb" ...>

Thanks for your feedback!

Felix

[basanth_kumar_v]

The above Formula is not working for the below format
“loanFk”: [
“recazeHVfHg52dTFn”
],

[Alexey_Chekano1]

How can I compose a filterByFormula string to get only records from a set of record IDs? I tried filterByFormula=“OR(RECORD_ID() = ‘recRjdJSziwMjfhO8’, RECORD_ID() = ‘recdRonUzKAIMPOxb’)”, but id doesn’t work. Thank you in advance,

[Alexey_Chekano1]

The string filterByFormula=“OR(RECORD_ID() = ‘recRjdJSziwMjfhO8’, RECORD_ID() = ‘recdRonUzKAIMPOxb’)” works. Sorry. My bad. I had another issue.

[Pradhumna_Panch]

Hey @Momen_Zakaria Were you able to get it working?

[dilipborad]

Based on new available functions of Regular Expressions.

New: REGEX formula functions Announcements

[regex_functions] Hello everyone! We recently launched some exciting new additions to our formula field with three new regex (regular expression) functions: REGEX_MATCH(string, regex) REGEX_EXTRACT(string, regex) REGEX_REPLACE(string, regex, replacement) These functions can be used to match character combinations within text strings. For those interested, Airtable’s REGEX functions are implemented using the RE2 regular expression library. You can learn more about these functions in this ar…

Now you can use things like this.

New: REGEX formula functions Announcements

Thanks, Now it adds more power/command in search and replace data. These things are also working fine with API. while we fetch data from AirTable. Now we have less complexity in search records from the table. Previously : filterByFormula=AND({FIELD_NAME}=“FULL_VALUE”) But if there is a problem with this. Because it matches the exact string only. Now : REGEX_MATCH({FIELD_NAME}, ‘PARTIAL_VALUE’) If you add any word that contains that field, that’s also matches and returns in the result.