How to handle duplicate users(same name users) in Advance configuration of Email generation Rule

[Community_User]

Originally posted on February 21 2022 at 11:43 UTC

Hi Team,

We have a requirement to configure two different domain name email for two diff. company code.

So for this we are using advance query in Email generation rule.

We have two queries-

1) How to configure unique email for each user (even if they have same name).

2) Also how to ignore special characters in the name while generating email.

Thanks

Jaya

[Community_User]

Originally posted on February 23 2022 at 09:26 UTC

Hi Jaya,

Please follow the below:

For unique email :
Advance Email Generation Rule (Under Global Config -> Identity Life Cycle -> Email Generation) where you can write advance SQL queries to support your use case. 

For removing special characters :
https://saviynt.freshdesk.com/a/solutions/articles/43000613673 

Thanks

Nikita

[Community_User]

Originally posted on February 24 2022 at 06:40 UTC

Hi Nikita

Thanks for the information.

Yes we are using this Email generation path & have applied below query:

(case when (users.customproperty1 = 'ABC') THEN lower(concat(substring(users.firstname,1,1) , users.lastname,substring('@ABCco.uk',1,8))) ELSE lower(concat(substring(users.firstname,1,1) , users.lastname,substring('dev@testco.uk',1,13))) END)

But this query is only working for different name users not for duplicate name users, can you suggest something that we can apply for duplicate users in query so it will generate unique email for everyone.

Also, I am not able to access this link that you provided for special character-

https://saviynt.freshdesk.com/a/solutions/articles/43000613673 
ThanksJaya

[Community_User]

Originally posted on February 28 2022 at 13:19 UTC

Hi Nikita/Team,

With above query, we are able to generate Email Id for multi domains. Here, the requirement is how to handle duplicate name users. Can you please suggest how to handle this requirement.

Please let us know if more clarification required on this.

Regards,

Rohan Pandit

[Community_User · Accepted solution]

Originally posted on March 2 2022 at 00:37 UTC

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