Nov 19, 2021 02:03 AM
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?
Nov 19, 2021 07:00 AM
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.
Nov 19, 2021 07:21 AM
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.
Nov 20, 2021 07:32 PM
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.