Check for duplicates before creating user name

Topic Labels: Formulas
842 2
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I am completely out of my depth here, so I apologize for dumbing what I’m doing down. Also, talk to me like I’m a child when explaining what’s going on :grinning_face_with_big_eyes:

I have built a simple website with Webflow and use Wized as the backend. Wized uses Airtable as the database. One of the functions of my website is that it gives the user a unique email address where they can send messages. I want this unique address to be created by Airtable.

I’m assuming i use the first and last name tables with this formula {first} & {last} to get the first and last name into a new cell. Now i have two questions:

  • how would i add to this first and last name the “” part of the email? i know this is something super simple, but just can’t seem to find the right one

  • more importantly, how would i get airtable to check the same column for duplicate email addresses before creating this one? and if it encounters a duplicate, get it to ad a number (1, 2, 3…) to the end of the “firstlast” part before the domain? i can’t do this manually every time because the email will be displayed to the user in the user dashboard on the website

2 Replies 2

In a formula field, doing the following should get you what you want:

{first} & {last} & “”

Hmm, I think I would handle this with two tables:

  1. Holding Table
  • Contains records that have the formatted email address
  1. Emails
  • Contains all your user’s emails

We would create a new record in Holding Table that contains the first section of the user’s email (i.e. {first} & {last}), and have an automation that triggers from that. The automation would do a “Find Records” action on the Emails table for any emails that contained that combination of {first} & {last}, and append the number of records it found to that name before creating it in the Emails table if anything was found.

This means that, in a scenario where you have an email in your system with the address, and a new user signs up with the first and last name combination of BobMort, the new user would end up with as their email due to the text BobMort being found within the email address, and I’m not sure if you’re okay with that

I’ve created a version of the above here. To view the setup (formulas, automations etc), duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

Adam’s basis system should work. I suggest a few variations.

  • Don’t use two tables. It can be done in one table. If you don’t want the email address to be display until after the automation has run to check for duplicates, use a checkbox field that the automation sets.
  • Use formula fields to convert the names to lowercase when matching them. Email addresses usually are not case sensitive.
  • Use formula fields to remove any spaces or special characters from the names. Sometimes people do things like put a first name and a middle name together in the {first name} field, and sometimes people put suffixes like “jr.” in the last name field. In both of these cases you might have spaces which are not allowed in email addresses.
  • You probably don’t want the 1 in for the second BobMort. Instead you want a 2. This works better having everything in one table. When the second was is created, the Find Records action will find 2 records and can update the numeric field with that 2. If the Find Records finds only 1, don’t update the sequence number. You would use conditional actions for this logic.