Question about automations

In a nutshell: You can create an automation that takes a New Record as a trigger; and then checks if any records exist with the same ID. If yes, the automation can then update the fields of the old records and delete the new one.

Alternatively, you can define a separate table for these form responses, and then use that the same way.

Best, Milan - Automable.AI Free Airtable consultation!-->

Hi, to be honest with you my speaking is not good for conversation, I do better with writing but I would like to get some help.

Did you try the automation I wrote?

I've got that so far, I don't know what the next step is.

Ok that looks like a good start!

In the Update Record, you can choose the “List of Record IDs” as the Record ID, from the ones you found in Find Record (the [+] sign at the end of the text box), and then you can add the values from the trigger as the field values.

Milan

Perhaps I’m misunderstanding, but wouldn’t this require the person submitting the form to know their ID number and input it in the form for matching purposes (which seems unlikely/unwieldy).

The way I’ve always handled this is using a field in the form for matching purposes that’s fairly unique but required (email address is a good one, you could do First/Last name but you get capitalization and nickname issues). Trigger is “When form is submitted”, first action is “Find records where Form Email = Contact Email”, next action is a conditional “If List is empty, create new record. If list is not empty, update record”

You can achieve this by implementing an "upsert" operation in your database. When a user submits the form, check if their ID already exists:

• If the ID exists, update the existing record.
• If the ID does not exist, insert a new record.

In SQL, you can use INSERT ... ON DUPLICATE KEY UPDATE (MySQL) or MERGE (SQL Server, Oracle). If using an ORM, look for an upsert or saveOrUpdate function.

​@Tim David Is this ChatGPT? This is an Airtable question, not an SQL database question.

Hey ​@Juan Camilo!

Even if you can acheive this via Airtable  scripting block within an automation, or with a dirty Airtable architecture (invlolving more tables that you’d actually need) I honestly think that it will be way easier for you to set this up using Zapier, Make.com, or n8n.

These platforms will allow you to have airtable modules/nodes which are specifically pre-set to this effect. They are usually called [“upsert” record]. You’ll try to find a record first. Then if found update it, if not found create it.

First question: Are you using Airtable native forms, or other form provider.
Second question: Are you familiar with any of the tools mentioned above? There are plenty of free online resources. Let me know if you’d like me to share these with you.

For last, if you have any specific question feel free to reach out. I’d be happy to help out -you can schedule a call using this link.

Mike, Consultant @ Automatic Nation 

Hi! ​@Mike_AutomaticN 

I’m currently using jotform as a form because of an autocomplete field option that is agile for registration (in this case it is to register attendance of people who constantly participate in our events).

I’m not familiar with the tools you mentioned, if you can share them I would be grateful.

Sure! Here you go.

Zapier
Make
n8n

One video you might find interesting (but you should use the Jotform form submission)

If you’d like to schedule a call I’d be happy to go through it.

Mike, Consultant @ Automatic Nation

The normal approach for this is to setup an “On creation” automation that find records in the same table with the same key value you need to use, normally email but you might use your primaryId. Then conditional steps If length of Find records = 0 then create new record, otherwise if length = 1 then update the fields, otherwise if length > 1 start whatever normal issue tracking you do.  I usually mark a checkbox that then send off notifications depending on conditions in the record that show who should look into the issue.

If you have a plan with scripting you can then write a delete script to remove the older or duplicate records as needed.