Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Automation Help

Solved
Jump to Solution
48 1
cancel
Showing results for 
Search instead for 
Did you mean: 

I have a client who has submissions to an AT base via jotform that populate into a master 'customer' database. With the business, there are different approvals that occasionally happen that need to be noted. There are multiple events throughout the year, and the client is asking if there is a way to timestamp when they were approved, so that when they are approved, it updates the master database. Furthermore, they would like to be able to search the main database, and when they search for that customer, it will automatically show the approval, and the date of that approval.

They would also like to be able to see all events that they have participated in. Now, to throw a wrench in it, they only have 2 events open each registration period, which uses a different jotform for each event, but they all currently populate to the main database. So, there are duplicate customer records with the same name, if they've registered for multiple events (in the main database). We would like to find a solution to avoid duplicate customer records, and have them just update the customer record when they register for other events, rather than having duplicate records. Really what they want, is to have customer profiles, where they can upload their documents, mark if they're passenger certified, and when something is updated in their user profile, it updates on the master database. Then when the customer registers for future events, all of that information is linked to their registration. I know this is incredibly complicated and specific, but if we can figure these things out in airtable (with possibly adding an extension that does member profiles or something), that would be great. My client LOVES airtable, and wants to stay with it, but we need to figure out how to handle this issue.


So to conclude: customers register and pay via jotform, the information populates to a master customer database. (right now) it creates duplicate records for each submission if they've registered for an event previously. What we need: to have the submission of passenger approvals, solo drive certification, and which event they've participated in, all to update in the master database with these items. So when they register for a future event, all of this information is attached to their name in the main database.

If I need to set up different tabs for each event, and have an automation to the master database that says: when customer name matches, update the matching master record with the event information and approval? I'm just unsure on how to work around this to get the client what they're asking for. I have about 4 months of AT experience and was able to create this much for them, along with building a website for them through wix and taking payments/uploading info to airtable via jotform, but I've run into something I'm not educated on and just need some guidance on how to set up what they're asking for.

1 Solution

Accepted Solutions

Hm, would it be right to call the table that the JotForm creates records in the "Registrations" table or some such? 

Assuming you have a unique ID of some sort for the customer (Email, phone number?) you could create a "Customers" table, link it to "Registrations" and paste that unique ID into the linked field, which would populate the "Customers" table for you as needed, or link registrations to it

Handling the approval bits is more subjective.  I think if it were me I'd just use rollup fields in the "Customers" table with an `ARRAYCOMPACT()` and `ARRAYUNIQUE()` as that would give me the details each customer provided in their previous registrations, and I'd then have an automation to do the approval as needed

See Solution in Thread

1 Reply 1

Hm, would it be right to call the table that the JotForm creates records in the "Registrations" table or some such? 

Assuming you have a unique ID of some sort for the customer (Email, phone number?) you could create a "Customers" table, link it to "Registrations" and paste that unique ID into the linked field, which would populate the "Customers" table for you as needed, or link registrations to it

Handling the approval bits is more subjective.  I think if it were me I'd just use rollup fields in the "Customers" table with an `ARRAYCOMPACT()` and `ARRAYUNIQUE()` as that would give me the details each customer provided in their previous registrations, and I'd then have an automation to do the approval as needed