Is it possible to search across the multiple tables?

Petar_Brkovic · ‎Mar 15, 2020

Hey guys, I host my own pub quiz and since I’ve done around 80 of them I start forgetting and sometimes I add question I had before. I’m looking to make a database which I’d update after each quiz so I can quickly search for keywords or answers.

This tool does seem really useful and I’m willing to explore it. Now I’m curious if I’d be able to somehow search for the answer across all the different quiz tables I make?

Thanks for your help!

Screenshot_1|690x201

kuovonne · ‎Mar 15, 2020

I believe the native “search” feature only works on the current table.

If you want to be able to search all the quizzes, I recommend putting all of the questions in a single [Questions] table.

In order to tell which questions are used in each quiz, I recommend creating a new [Quizzes] table, with one row per quiz. This table could have additional fields for information like when and where the quiz was used. Then create a new linked record field in the [Questions] table that links to the appropriate quiz in the [Quizzes] table.

Then, if you want to see questions for a particular quiz. you would filter the [Questions] table for the particular quiz in the linked record field.

If you are used to spreadsheets, using linked records in a database can be a very different way of looking at data and can take some getting used to.

See Solution in Thread

kuovonne · ‎Mar 15, 2020

I believe the native “search” feature only works on the current table.

If you want to be able to search all the quizzes, I recommend putting all of the questions in a single [Questions] table.

In order to tell which questions are used in each quiz, I recommend creating a new [Quizzes] table, with one row per quiz. This table could have additional fields for information like when and where the quiz was used. Then create a new linked record field in the [Questions] table that links to the appropriate quiz in the [Quizzes] table.

Then, if you want to see questions for a particular quiz. you would filter the [Questions] table for the particular quiz in the linked record field.

If you are used to spreadsheets, using linked records in a database can be a very different way of looking at data and can take some getting used to.

Petar_Brkovic · ‎Mar 17, 2020

Thank you,

I’m actually using this method now, but I was wondering if I could shift it around a bit, and have one table for each of the Categories I have (General Knowledge, Geography, Sport etc.), since I only need to search during that category for particular questions and as some questions don’t have exactly the same fields. Would I then be able to see all the questions linked to a particular quiz somehow?

kuovonne · ‎Mar 17, 2020

If you keep everything in one big table, you can have a single select field for the category.

If you want to restrict searching to a particular field, you then use a filter with two conditions joined by “and”: on condition on that field that “contains” the text you want, and another condition on the category field.

However, if you want to search across multiple fields for a particular category, you will need a different approach. Create a view for each category that filters records to show only questions in that category. When you want to search only in that category, look at the view for that category and the do the search within that view.

You also mention that different categories have different fields. If the different categories have very different fields, it might make sense to have multiple tables. You could use linked record fields, lookups, and formula fields to bring all the questions into a single table. It would be a bit messy to setup, but it is quite doable.

Petar_Brkovic · ‎Mar 17, 2020

The only one that differs from the rest is the category Song quest (Where they need to guess the artists and song) so instead of Answer and question, It would have Artist, featuring artist and song title, but I guess since all 7 other categories can go in the same table I’ll make it work that way. Thanks a lot again you’ve really helped me!

Bill_French · ‎Mar 17, 2020

Yeah, um - read this paper and tell me if that’s what you are asking for.

kuovonne · ‎Mar 17, 2020

Is this available yet? Is it a custom block?

Bill_French · ‎Mar 17, 2020

No - this is probably never going to be released as a product because there’s not really much demand for it. I have used it in three client engagements - each one is a custom one-off intended to index a lot of Airtable content as well as other content external from Airtable.

I do have a prototype running in a Custom Block and I’m currently looking for a partner to take it and build it out as a full product because with my other work, I just don’t have a lot of time to build this into what it really could be.

Kamille_Parks · ‎Mar 17, 2020

If you have a Pro account, you can use the Search Block, which can search the whole base.

Bill_French · ‎Mar 17, 2020

Indeed, and that’s one (of many) reason’s why I’ve chosen not to build yet another one. :winking_face:

There are exceptions of course, such as:

Search across multiple workspaces
Search across multiple bases
Results that must include full-text pointers into attachments
Search results that include data and document sources in addition to Airtable
Search results that need to be instantiated in tables (the results are actually records in a results table)

But these are special requirements and relatively costly to build for an enterprise, let alone a small business. And because these requirements extend far beyond the scope of Airtable and why people choose Airtable, Airborne is not ideal as a base-level search solution.