Search non-primary key function

Topic Labels: Base design
1080 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hey, just have a quick question that I have not been able to solve otherwise

So we have a pretty complete base with the following flow :
1.Client fills an Airtable form to book a service;
2. Internal team of operators have different views related to the state of the bookings ( in transit, current, done)
3. Each major client would have a filtered shared view of their bookings, grouped by status

Here is a quick idea of how the base is setup :

  • Booking
  • Customer / Client

Customer table having the customer ID as their main identifier (primary key of the Customer table), Full company name being the second column, to make sure in the Airtable Form that the customers cannot see the other customer other than their randomized Customer IDs. We communicate with each client their Customer ID and with a prefilled URL we prefill the form shared with the customer.

So the current problem is that if the operator needs to fill manually a new booking, they need to search the client by Customer ID (primary key of the linked table), which they do not know by heart having thousands of entries.

We want to see if there is a way to search by the second column and not only by the primary key. So if they start writing " Company XYZ inc" it would currently return no result, since the search field looks into the randomized Customer ID being something like “9166 7484”. Is there any way to configure the search field so it searches a designated column ( in our case : Full Company Name).

Currently, the solution is to find the company from the Customer table and create a new booking directly in the related booking column (Linked to Bookings) having a pop-up of the created booking and it permits that the booking is directly linked to the right customer.

1 Reply 1

Welcome to the community, @LudoP!

Unfortunately, one of Airtable’s biggest limitations is that it only lets us search by the primary field in linked record fields. Please email to let them know how important this feature is to you, and explain your situation to them.

To take care of your users, you can use a formula field as your primary field, and your formula should combine all the searchable fields that you want your users to search.

Then, for your clients, you could still prefill the form for them with their Client ID, but just make the Client ID a normal text field, and tell the Clients not to edit their ID on the form.

This is actually the same methodology that Airtable uses with their own support article forms… for example, go to this support article, scroll to the very bottom, and click on “Not really”. The form asks you to not alter that Article ID.

After your client submits the form, you can use an Airtable Automation to move the client ID from the text field into the linked record field.