Create Record ID based on an existing field

[Guido_Cappa]

Hello, I have a table where I keep my website’s customers email addresses. I would like to create a random and Unique IDs for every email that I add to the table.

Also, if the same email is entered more then one time, the ID should be the same as the first email.

Is it possible to do something like it on airtable?

Thanks

[kuovonne]

This is possible with scripting. A variation is also possible with a secondary table.

Given all of the requirements, it is not possible with only a formula field.

---

A script can search records to see if the email address is a repeat. If the email address is a repeat, get the corresponding unique id and put it in this record. If it isn’t a repeat, the script can generate a random unique id.

On the other hand, some people are not comfortable with scripts.

---

A non-scripting possibility is to use a second linked table for storing email addresses and unique ids. In this second table, have the primary field be the email address as a single line text field. When a new email comes in, copy the email address to the linked record field. If the email already exists, the link will be made. If the email does not yet exist, a new record in the email addresses table will be created. Each email address will only appear once in the table. (You may need some tweaks to make email addresses lower case to ensure uniqueness regardless of case.)

To generate the unique id for the email address, use the Airtable record id, an autonumber, or the creation time (if emails are created infrequently enough). These unique ids will not be random, although a formula field can make them appear more random than they are.

Finally, bring the unique id to your original table with a rollup or lookup field.

[Guido_Cappa]

kuovonne:

In this second table, have the primary field be the email address as a single line text field. When a new email comes in, copy the email address to the linked record field. If the email already exists, the link will be made. If the email does not yet exist, a new record in the email addresses table will be created. Each email address will only appear once in the table. (You may need some tweaks to make email addresses lower case to ensure uniqueness regardless of case.)

Thanks for your answer. I appreciate the time. I am not sure I understand how to do this. Is it possible to sync a linked table automatically every time an email is entered to the first table? Also, I don’t understand how to avoid duplicates.

[kuovonne]

Guido_Cappa:

Is it possible to sync a linked table automatically every time an email is entered to the first table?

This technique does not use a synced table. You use an automation to copy the email address from the single line text field to the linked record field. If the email address already exists in the [Email Addresses] table, the existing record is linked. If the email address does not yet exist in the [Email Addresses] table, a new record is created and linked.

[Nikiska]

Hi @kuovonne - you couldn't give me a step-by-step for this please. This is what I have so far:
Trigger: when a record matches condition: email address not empty (from source table)
Action: Create record (in new table).
This works brilliantly, but I now have 4 records with the same email address and need to create a formula or something that gives those 4 records one unique ID number that I can link to the source base