Search Whole Workspace using API

Hello there,

I was wondering if it is possible to search a whole workspace using the Airtable API.

The reason I need this is below:

  • I have 5 x bases full of LinkedIn profile IDs all with the identical base structure and column headers
  • Using the API I want to search across all the 5 x bases to check if there are any matches
  • I need the results returned in the output from the API request

Many thanks,

Karan

Welcome Karan!

By default, the Airtable API does not provide the kind of inter-base calls you’re looking to make, but what you’re looking for should be possible with some development work.

A high-level overview of how this could work, looks something like:

  1. Have a list accessible with all Airtable base IDs you would like to read from (these are the app***** ids listed on the API documentation).
  2. Write a script/app that iterates over all 5 base IDs making the appropriate call to list records (either the GET request if using cURL, or base('{Table Name}').select() if using the Javascript library.
  1. Handle the results as needed, either stopping after the first result, or compiling results from all bases if needed.

Since all of your bases have an identical base structure and column headers, this should reduce the amount of custom code needed to parse different bases—the same API call should work for all bases, just passing in the unique base ID for each one.

Here are some potential examples for both the cURL and Javascript flavors of the API. If using the cURL API, you might find Airtable’s URL encoder to be handy for creating the filterByFormula to select your LinkedIn records.

curl "https://api.airtable.com/v0/app*****/{Table Name}?maxRecords=3&filterByFormula=%7BLinkedIn%7D%3D%22myLinkedInId%22&view={View} \
  -H "Authorization: Bearer YOUR_API_KEY"
var base = new Airtable({apiKey: 'YOUR_API_KEY'}).base('app****');

base({Table Name}).select({
    // Selecting the first 3 records in View:
    maxRecords: 3,
    view: "View",
    filterByFormula: '{LinkedIn ID} = "myLinkedInId"'
})

If this answers your question, please consider marking it as “solution”. If not, I’m happy to work with you further. Thanks!

Hey Matthew,

Many thanks for your detailed response.

If you are open to the idea of building this logic.

Would you be able to build this function for me taking into consideration that in the future I may want to change 2 variable which are:

  1. The number of bases to search
  2. The airtable base IDs of tables to search

Let me know if you are open to the idea and we can discuss further

Many thanks,

Karan

Hey Karen,

Here is a very simple shell script that you should be able to use to get started. It has a few variables to define up top, including an array of BASES that you can add to or remove from as your needs change. It’ll then loop over these bases and fetch the records, writing them to a baseId.json file that you can parse/work with as needed. If you have other command line tools, you should be able to remove the ‘write to file’ bit, and pipe the results from stdout as needed.

The one thing you’ll need to add for your specific use case is the filterByFormula bit I mentioned above. You can tack this on to the URL variable such that the records retrieved match whatever search criteria for LinkedIn IDs you want.

#!/bin/sh
#

fatal()
{
	echo $1
	exit $2
}

AIRTABLE_KEY="key***"
BASES[0]="app***"
BASES[1]="app***"
TABLE="My Table"

# Iterate through all courses listed above
for BASE in "${BASES[@]}"
do
    # Assemble URL, and retrieve JSON data
    URL="https://api.airtable.com/v0/$BASE/$TABLE"

    STATUS=$(curl "$URL" \
    	-H "Authorization: Bearer $AIRTABLE_KEY" \
    	-o "$BASE.json" \
    	-s \
    	-w "%{http_code}")

    # Check the HTTP response
    if [ $STATUS -ne 200 ]
    then
    	rm "$BASE.json"
    	fatal "Airtable returned a $STATUS error. Try again."
    fi

done

exit 0

Hi Karan,

I think this is kind’a what you’re looking for and here’s more about my approach.

image

I’ve built four of these for various clients. One was a custom block (used as the search UI) which the client built and I simply integrated the search backend. The others were either simple script block search bars or web apps. One of them even indexed multiple workspaces.

You can also read the paper that served as the requirements for tips on how you might approach this. Doing it is far more complicated than you might think, and you’ll probably be disappointed with the results unless and until you implement ranked results based on relevance.

From this point forward, I’m showing off - this Airtable search system integrates a map as the search UI…

Hey @Karan_Setia,

If one of the answers Bill or I posted above solves your question, would you mind marking it as the “solution” so others searching the forums can easily find the information?

Thanks!

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