Skip to main content

Hello, 

I am having a table in Airtable with Subscriptions. Each subscription record has its subscriber data containing the following fields:

  • First Name
  • Last Name
  • Address
  • Zip Code
  • City

But each subscription can be assigned to a person more than once. So persons can have several subscriptions at the same time. 

I want to create unique IDs based on the subscriber fields from above (first name, last name, address, zip code, city). If there are subscriptions with the same person, the Unique ID (of the subscriber) should be the same. 

So basically I want to avoid duplicate people having a different ID. They should have the same ID.

How can I do this with a formula? 

Thanks a lot for your help, 
Michael

This is how I'd do it:

https://airtable.com/shrD9g6cp1IjoZ0ne 

You just need to use a formula with an autonumber field to give each record in the subscriptions table its own ID.  Then use a formula in the primary field to give, as in this example an ID for the record of SUB1, SUB2, etc.  Link the subscription to a subscriber any number of times an the rollup field using ARRAYUNIQUE(values) will show you the subscription ID for the person.

Feel free to the copy the base above if you want.

This is how I'd do it:

https://airtable.com/shrD9g6cp1IjoZ0ne 

You just need to use a formula with an autonumber field to give each record in the subscriptions table its own ID.  Then use a formula in the primary field to give, as in this example an ID for the record of SUB1, SUB2, etc.  Link the subscription to a subscriber any number of times an the rollup field using ARRAYUNIQUE(values) will show you the subscription ID for the person.

Feel free to the copy the base above if you want.


Hi @JonathanB thanks for your details. 

The problem is that both data (Subscriber and Subscriptions) are in the same table. So I can't use the Rollup field.

And I do have 10k subscriptions! So it would be a bit complicated to split the data. 

Is there no other option possible?

Something like Compare the combination of "First Name + Last Name + Address + Zip Code + City" of all records. If they are the same in some rows then use the same ID/autonumber.  

Hi @JonathanB thanks for your details. 

The problem is that both data (Subscriber and Subscriptions) are in the same table. So I can't use the Rollup field.

And I do have 10k subscriptions! So it would be a bit complicated to split the data. 

Is there no other option possible?

Something like Compare the combination of "First Name + Last Name + Address + Zip Code + City" of all records. If they are the same in some rows then use the same ID/autonumber.  


I don't think there is another option I'm afraid.  As subscribers and subscriptions are two different categories of data, each needs its own table.  You could set up a single select field to designate each record as either "Subscriber" and "Subscription", then an automation that adds a new record to your new tables for either option.  Unfortunately then I think you'd have to link them back up manually.  Is it obvious from the records which type each one is?  If so you might be able to automate that too.

I don't think there is another option I'm afraid.  As subscribers and subscriptions are two different categories of data, each needs its own table.  You could set up a single select field to designate each record as either "Subscriber" and "Subscription", then an automation that adds a new record to your new tables for either option.  Unfortunately then I think you'd have to link them back up manually.  Is it obvious from the records which type each one is?  If so you might be able to automate that too.


Thanks @JonathanB for your update.

I guess I will have to look for another solution (i.e. adding pre-defined autonumbers from Excel and look with the Dedupe extension https://support.airtable.com/docs/dedupe-extension and give them another ID manually. I saw that there are "only" 100 duplicates. 

Email address can sometimes be considered a unique ID depending on the use case.

You could create an automation that searches for email address or a combination of fields exists using Find Records action and either update a linked record field to the same table or use a conditional action to insert an existing/create a new ID.

Using linked records to the same table or another table let’s you count/filter duplicates. it may even be worth an automation that finds or creates a subscriber record in a second table. 

Email address can sometimes be considered a unique ID depending on the use case.

You could create an automation that searches for email address or a combination of fields exists using Find Records action and either update a linked record field to the same table or use a conditional action to insert an existing/create a new ID.

Using linked records to the same table or another table let’s you count/filter duplicates. it may even be worth an automation that finds or creates a subscriber record in a second table. 


Hi @Stephen_Orr1 , thanks again. 

The problem is, that not every record has an email address. But I will look into your last idea with the automation. 

Thanks, M.

@micha281sth np! Your post made me think about how forms really should be able to populate linked tables in a way that is invisible to the submitter but encourages Airtable best practices (like storing data relationally) to the base owner. So I submitted the idea earlier 🙂 Automations to duplicate data for the sake of "using Airtable efficiently" should not be necessary IMO.

Accessible linked record fields on a form view 

@micha281sth np! Your post made me think about how forms really should be able to populate linked tables in a way that is invisible to the submitter but encourages Airtable best practices (like storing data relationally) to the base owner. So I submitted the idea earlier 🙂 Automations to duplicate data for the sake of "using Airtable efficiently" should not be necessary IMO.

Accessible linked record fields on a form view 


Yes @Stephen_Orr1 , that is a absolutely great idea. Hope to see this feature soon 🙂 . 

Thanks again for help!

Email address can sometimes be considered a unique ID depending on the use case.

You could create an automation that searches for email address or a combination of fields exists using Find Records action and either update a linked record field to the same table or use a conditional action to insert an existing/create a new ID.

Using linked records to the same table or another table let’s you count/filter duplicates. it may even be worth an automation that finds or creates a subscriber record in a second table. 


Hi @Stephen_Orr1 , you don't happen to have a step by step guide to create this automation do you? It is EXACTLY what i am looking for.  I have 3,000 people in my database that people can opt in to help the charity in numerous ways like being: volunteers or subscribers or sponsors or trainers etc etc but some people has said they can help by doing a few of those options. My problem is that the data is pulled in from other platforms so I will have 'Mr Smith' 3 times in my master list as he offered to be a volunteer and a sponsor and a trainer.  So currently he therefore has 3 record ID's. I need a formula or an automation that creates a Unique ID that is linked to an email address so if he registers for a 4th option his Unique ID will automatically populate with the same ID as the other three if that makes any sense at all!! 

Reply


Terms & Conditions