Find an existing customer in a ticket database

[jmepok]

Hello everyone 🙂

I own a frame printing and painting renovation shop. I would like to use Airtable to track my shop services.
I have created a form when each customer arrives that identifies them if they are on our list of known customers and if not, that allows them to be registered without having to create a second form. The rest of the form goes normally to create their renovation ticket.

The problem I am having is the following:
At the beginning I wanted to have a single database (tickets and customers in a single view).
When I add a field to search for a customer in the database I created a field that looks up in the database and mentions the specific field to lookup (Customer ID). The problem is that this field only allows me to search in the first column of the database (the ticket ID).

I am asking for your help. Is it possible to have everything on one form ? Or do I have to separate the customer creation form and the ticket creation form? If so, how do I check the existence of a customer in my unique form?

Thank you very much for your help 😉

[TheTimeSavingCo]

Hmm, I think it's possible to have everything in one form but also have the customer data and ticket data as different tables

What do you think of creating those two tables and linking them together, and then creating an automation that triggers when a new ticket is created to link the ticket to the customer record? 

If your unique customer ID (e.g. the email) was the primary key, you could make the automation paste said ID into the linked field which would create a new record in the Customers table if one didn't already exist or would link it if it did

If it's not the primary key, you could do a "Find Record" action with conditionals to either create a new Customer record or link it

I feel like I don't fully understand your use case though, so apologies if this is going in the wrong direction!

[catz]

Hi there! it sounds like you may need a lookup field: https://support.airtable.com/docs/lookup-field-overview

Because it sounds like each form is creating something different, a customer vs a ticket, my instinct is to keep the forms separate. You should be able to use the lookup field to check for the existence of the customer.

hope this helps! 🙂

[Hannah_Wiginton]

Hey there,

You might look at On2Air Forms for Airtable. It would make this process pretty seamless.

You can have multiple tables on 1 form so you could essentially search for your customer or create a new one, then create a new ticket that's automatically linked to that customer, all on the same form.

Here's an example of a form with multiple tables:

https://on2air.com/form/4TRhSw/recpXkHwOC2dWN7zz

Feel free to let me know if you have questions.

[Hannah_Wiginton]

Another option might be to make your Primary field a Formula field that CONCATENATES (joins together) the Ticket ID and the Customer Name together so you can see it when searching.

https://support.airtable.com/docs/joining-field-values