Link to table using other than primary field

[Helmi]

Hi,

I guess this is not possible then let’s make this a request.

I would like to link tables using any other than the primary field.

Real life example using Airtable as a special, small biz CRM:

• table 1: customer database, customer account number is primary field, other fields like email
• table 2: orders, linking to customers via account number
• table 3: form submissions running in from a web form, customers don’t use their account numbers of course (mostly even don’t/need to know them). I know would want to query the customer DB using the email field to see if there’s an account for that email and then link it,

If it’s possible it would be great but it doesn’t look like.

I can imagine seveal other cases for this.

Cheers,
Frank

[Julian_Kirkness]

Hi Frank

I’m assuming your issue is with the form submissions?

What I would do here is to have the form user enter their email address in the form - and then use Zapier (triggered by a New Record) to find a matching record and if not create it. You could build some workflow into this such as a notification email etc. It would even be possible to create both a new order and new customer from an order form if you wanted to go that far.

Julian

[Helmi]

Well form submissions are not my problem basically. I’m already doing them through Zapier now. My problem indeed is the lookup. I don’t even want to create tables.

Of course I can do the lookup on Zapier but the feature request is because I think this is something Airtable should do. These cross table lookups are so important in my oppinion and you can’t always rely on external services to do so.

[Julian_Kirkness]

Hi Frank

My apologies - I’ve obviously misunderstood!

In relational databases generally, links between records really have to be to a primary key field because the link needs to be to a field which is guaranteed to return a specific record. This can make it difficult to capture new records in a customer facing environment.

CRM Systems often have a ‘Leads’ table to help resolve this situation - whether used to capture data direct from customers online or to make it simpler to record incoming enquiries on the phone. The idea is to capture all the information you need in one place and then, once captured, run a process which would update or create Company, Contact, Opportunity or Activity records in the database as appropriate.

This process can be replicated in Airtable - using Zapier to search for matching records (based on Company Name or Email for example) and then, if found update them and if not create them (it takes several Zap Steps!). It would obviously be much better if we were able to build this business logic within Airtable - but it’s not possible at the moment.

I’m not sure if this is relevant to your suggestion - if not I apologise once more!

Julian

[Helmi]

no need to apologize, I’m very thankful for any help. I visited your site and you seem to be quite expeirenced with everything business application. I’m only at the beginning of this things though having some technical background. I know the basic problems of uniqueness and also indexing for speed. And yes, if a field/column isn’t marked to be unique this would make it problematic but then I’d still wish to at least have lookup options like you described them with zapier. Of course Zapier could do that but then again I wish it would all be in Airtable as in my understanding Airtable is not only a database but also an application builder albeit a rather simple one.

I saw you are also into Zoho Creator. That is something I have on my shortlist for testing to do more advanced things. Maybe Airtable isn’t just right for us though.

[Julian_Kirkness]

I agree, it would be great to be able to build some business logic within Airtable - and I’m sure that this will come - or the integration with Zapier will be made more powerful.

I think regardless of the technology, collecting enquiries and then attaching them in some way to existing or new records is always a little problematic - for example, you may have enquiries from 2 companies with the same name (or the same name was entered). I also don’t think you can present external users with lookup type fields because you wouldn’t want to let them see other entries in your database in any way.

As you’ve seen from my website, I have chosen to work with 3 database / application builder / workflow tools - of which Airtable is one. It’s great strength is how visual and tactile it is - and this makes it really nice to use. It’s internal capabilities for building business logic / rules are limited at the moment but, having completed two small projects for a client a few months ago with Airtable/Zapier, I was very pleasantly surprised at what could be achieved.

This isn’t the right forum to discuss other options, obviously, but as always it’s a matter of ‘horses for courses’ and I have chosen to work with tools which each have their own unique strengths.

[Helmi]

I just tested the Zapier/Search way and can’t get it to work right. Basically everything works but if the Search fails the whole Zap fails which isn’t my intention.

Any idea how I can ignore the result from the search and still continue with my create action that follows afterwards?

Currently I do a seach using the email address from a Gravity Forms submission on one table. In the Create action I add another record to a different table using the data from Gravity Forms plus one field from the search results which could just be empty if nothing is found.

Any idea?

Thanks,
Frank