Rohan/Jaya,

You can use the Advanced Config feature in the Email generation rule to check for duplicates. Currently the logic that you have is just generating a single email address, it does not have 'an option' to generate anything else in case it is a duplicate. For e.g If you use the OOB rules in email, you can add multiple rules to navigate past the duplicate issues, similarly you can do the same in an advanced query.

The format/example I am sharing below has the following contruct

case when <condition> then <do this> else <do this> end # case when <condition> then <do this 1> else <do this 2> end

Here, # marks the end of the rule. If you have multiple rules, you can keep adding # after every end keyword except the last as shown in my above example

Here's a sample :

case when(city like 'NYC%' or city Like 'LA%') then concat(substring(users.firstname,1,1) , substring('.',1,1),LASTNAME ,'@testemail.com')  else concat(users.firstname , substring('.',1,1),LASTNAME ,'@testemail.com') end # case when(city like 'NYC%' or city Like 'LA%') then concat(substring(users.firstname,1,1) , substring('.',1,1),LASTNAME ,FLOOR((RAND() * (68-10+1))+10),'@testemail.com') else concat(users.firstname , substring('.',1,1),LASTNAME ,FLOOR((RAND() * (68-10+1))+10),'@testemail.com') end

I'm checking if the user's city is either like LA or NYC, and I am generating an email based on it. so for user John Doe either it will be J.Doe@testemail.com or John.Doe@testemail.com. 

Now if both the email addresses taken for this combination, by second rule (the logic after after the #) kicks in and it appends a random two digit using the MYSQL RAND (). e.g It can be J.Doe12@testemail.com or John.Doe67@testemail.com based on the user's city.

Regards,

Avinash Chhetri