Deleting rows from Table A when form completed with same email in Table B

[Pierre_Delannoy]

Hi guys,
I don’t know if the title is clear. So here’s some context.
I am currently using Airtable as a CRM. Thus, I have 3 important Tables : Leads, Unpaid Leads and Clients.

As of now, people can pre-subscribe to my service (not open yet). When filling the form, they will go to “Leads”.
Once the service will open, I will manually validate them to send them a form for Subscription. If they fill the form, they will go to “Unpaid Leads”. If they do not, they will stay in Leads and receive a followup at D+3.
If they do fill the subscription form, I’d like to erase them from “Leads” in order not to follow up with them if not needed.

Do you know if there is a script for that ? Or anyway to work around it ?
If there isn’t, is there a script that can help me update the status in the 1st Table when a form is submitted in the 2nd one ?

PS: FI, I first wanted to keep everything in the same Table ‘Leads’ until they became clients but I need more info between pre-sub. and sub. and I couldn’t find a way to add the info to existing record from the same Table so I had to create another one for this step.

Willing to hear all your ideas! thanks a lot in advance!
Pierre

[Alexey_Gusev]

Hi,
it’s a question of table design. I would store all data in a single table with different views, where ‘Lead’ / ‘Unpaid lead’ is a single-select status. Thus, you can update status by automation ‘when form submitted’
You can also store them in 2 tables, Client & (paid/unpaid leads)

[Pierre_Delannoy]

Thanks @Alexey_Gusev,
So do you mean I should put the 2 forms in one table ?

Because when doing so, I will have duplicates (as the second form requests more info than the 1st one) and as I can not find a way to update my record even if they have the same email or name.

Thanks in advance,
Pierre

[WilliamPorter]

Pierre,

I don’t fully understand what you’re trying to do, but my pretty experienced gut tells me that @Alexey_Gusev is right: You should be using a single table with, say, a single-select field called “LeadStatus” or something like that, which you can change from Lead, to Unpaid, to Client as needed.

Database design does not necessarily conform precisely to the way we human beings think about something. In an invoicing app, for example, if you have an ACTIVITY or ORDERS table that you use to create line items on your invoices, you are keenly aware of the practical difference between “billables” and “receivables” and “paid orders”. But when you’re modeling your data, you design the containers for the data — the tables — based not (well, not just) on how your users think about things, but on the structural properties of the things. And structurally, a billable order is identical to a receivable and a paid order. The only difference is their paid status.

I’m working this week on a school app. There’s a STUDENTS table. Parents call to ask about registration and at that time the school creates a record in STUDENTS, enters just basic info (name, address, DOB, sex, etc) and clicks into a single-select RegistrationStatus field and marks the student as “Inquired”. Later that will change to “Registered”, then “Enrolled”, and eventually to “Alum”. Everybody stays in the same table. In this example, it remains an open question whether, periodically, to purge records whose status never progressed beyond “Inquired”. But I’m considering that only because of Airtable’s limit on the count of records in a table.

Similarly, the things or “entities” that you’re working here seem to me identical in terms of basic data structure: a Client is just a Lead who has developed into a Client. Yes, you’ll want to gather more info about a client, but that just means you create a client form that displays more fields. The basic structural properties of the two “roles” are the same: name, company, contact info, whatever else. So create a status field, make views for your different status categories, and revise your forms based on those different views.

I might change my mind if I knew a lot about your project. There aren’t very many absolutely hard-and-fast rules. But I’ve been doing this since the last century and as I said, my gut tells me Alexey is right. And if so, you’ll end up finding it MUCH easier to manage all this data if you put it in a single table.

William

[Alexey_Gusev]

There is an existing workaround to update existing record via form submission, but it consists of steps: person submits a form in other table => automation finds that person in the first table and updates existing record with the values provided in a form.
So, it needs the same amount of efforts as you described: delete rows from table A when form completed in table B.
You can do it via automation script step (because there is no ‘delete record’ action):

const table=base.getTable(‘table_name’)
await table.deleteRecordsAsync(list_of_ids)

there is a single delete action, table.deleteRecordAsync(id) but you should find that id, and likely you will do it by ‘Find Records’ action, which always return array of IDs. Even if it finds a single record, it’s an array containing 1 element. You can’t use an array in a place, which expect single value input.

I suggest you to do additional check, because for example, if a person will submit a form with empty email, automation will remove all founded records with empty email

just a kind of example how Find records result can be used with scripting step

[Pierre_Delannoy]

Dear @WilliamPorterTech and @Alexey_Gusev,

First of all thanks a lot for the time taken to help me out on my issue. I really appreciate it.

In fact, I tried to put all the data in one table with different views and this is in fact much easier for me to manage. I don’t know why I made it separate table at first, I think I was too deep in the subject and didn’t have an overall view…

I still need to erase the duplicates, but I think I will prefill the whole form at the exception of the infos that will be added by the client within the second form. Therefore I am (almost) sure that the email (that I will use as reference) will be the same in both records and I’ll be able to use the script shown in the screenshots to delete them.

I’ll keep you posted,
Thanks again for your great help to both of you!

Pierre

[WilliamPorter]

Pierre_Delannoy:

In fact, I tried to put all the data in one table with different views and this is in fact much easier for me to manage. I don’t know why I made it separate table at first, I think I was too deep in the subject and didn’t have an overall view…

“Too deep in the subject” is precisely why DIY development is so difficult!

I still need to erase the duplicates, but I think I will prefill the whole form at the exception of the infos that will be added by the client within the second form. Therefore I am (almost) sure that the email (that I will use as reference) will be the same in both records and I’ll be able to use the script shown in the screenshots to delete them.

Two suggestions.

1. Duplicate this base right now. Or at least, make a snapshot of it. For this purpose, I think duplicating the base is the better idea. I am spending a lot of time cleaning up data for a client, including deduplication. When I get ready to delete a bunch of records, I copy them and paste the data into a Google Sheets doc I’m using for this purpose. I hate deleting records. In FileMaker I very seldom did it, but in FileMaker I wasn’t worried about record limits. (I have databases with millions of records in them.) In Airtable on the other hand, deleting is a necessary evil. Still, backup before deleting.
2. Remember that you can copy and paste whole ranges of data in Airtable, just as you can in a spreadsheet. Might help you here. I think this is one of Airtable’s best features.

Good luck.

William

[Pierre_Delannoy]

Yes, I guess after a time it’s difficult to have the whole picture…

Thanks for the suggestions, I always work on a separate table before deleting the other, less risks of loosing the data :slightly_smiling_face:

Pierre

[CristianCG]

Hi there!

At miniExtensions, we've created a third-party form designed exclusively to integrate with Airtable that can easily solve the 'duplicates issue' you face when using a single table. Our form can edit already submitted records through a unique link to the record or a login field, so only one record per user will be created. Here's how it would work in two scenarios:

• Edit link: If you’d like to send users a link to edit their record immediately after submission, they’ll be able to reopen it later to add more information.
  

• Login: Alternatively, you can set up a field (or combination of fields) as a login credential, enabling users to access and edit their unique record directly with the form.

Both options help keep your table organized while allowing users to update their records as needed.