UNIQUE RECORD_ID's linked to email address only

Topic Labels: Automations Formulas
585 2
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator

So, I have over 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 this data is pulled in from other platforms (Active Campaign, google forms etc etc) and I have zapier populating my CRM everytime a new form is submitted.  This means I currently have 3 records for a 'Mr' as he offered to be a 'volunteer' and a 'sponsor' and he as subscribed to our newsletter.  So he now has 3 different record ID's as my CRM creates an ID for each new entry. 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 the ID field with the same ID as the other three if that makes any sense at all!! 

2 Replies 2

Maybe you could build an automation that triggers whenever a record is created and then looks up records in the same table based upon the email field. Add a conditional (when number of records found in lookup action >= 1) action that updates the email field in the new record with the email value from the found record. You could add an email action that lets you know if more than 1 record was found in the lookup action (in which case the update will have failed). 

Challenge: your unique ID is probably a formula field (correct?) which you can't edit via an automation.

Just my first thought here...

6 - Interface Innovator
6 - Interface Innovator

Hiya, it doesn't look like my screenshot loaded in my orignal post, but the primary field is an IF forumula that pulls in the rollup email addresses from the various sources which is simply so I could group the view into email addresses.... the current ID column is also a forumula with an autonumber and prefixs eg HU0100. What I need instead of one contact having 3 HU numbers is one ID number generated per unique email address if thats possible? whether through a forumla or an automation or a script?