Email Generation Rule by Advance query

[anujapawar]

Hi,

I need the email generation Logic for the follow use case with the help of advance query.

Email Address creation logic: -

a.     Firstname + ‘. ‘+ lastname@domain.com

              Example: Firstname= John, Lastname= Doe, Email= john.doe@domain.com

b.     Duplicates will have incrementing numbers starting from 1.

                Example: john.doe1@domain.com , john.doe2@domain.com , john.doe3@domain.com.. .

c.       If Firstname is missing, use Lastname (without period).

             Example: Lastname= Doe, email= doe@domain.com 

d.     If Lastname is missing, use Firstname (without period).

             Example: Firstname= John, email= john@domain.com

e.     Special characters (_@.-') allowed in e-mail.

1. Accented characters except apostrophe's will be converted in email generation.

NOTE:

1. Preferred Firstname and Lastname will be taken into consideration for creation of     

         Email Address if available in place of the Firstname and Lastname.

[This message has been edited by moderator to mask sensitive information]

[Manu269]

Refer Sample :

CASE
when ((users.customproperty19 = 'ABC' or users.customproperty19 = 'DEF') and users.lastname is not null and users.customproperty2= 'DC') THEN concat(users.firstname,'.',replace(users.lastname,' ',''),'.dc', '@test.org')
when ((users.customproperty19 = 'ABC' or users.customproperty19 = 'DEF') and users.lastname is null and users.customproperty2= 'DC') THEN concat(users.firstname,'.dc', '@test.org')
when ((users.customproperty19 = 'ABC' or users.customproperty19 = 'DEF') and users.lastname is not null and users.customproperty2 not in ('XYZ','PQR')) THEN concat(users.firstname,'.',replace(users.lastname,' ',''), '@test.org')
when ((users.customproperty19 = 'ABC' or users.customproperty19 = 'DEF') and users.lastname is null and users.customproperty2 not in ('XYZ','PQR')) THEN concat(users.firstname, '@test.org')
when (users.customproperty19 = 'MNO' and users.lastname is not null and users.customproperty2= 'DC') THEN concat(users.firstname,'.',replace(users.lastname,' ',''),'.dc', '@test.com')
when (users.customproperty19 = 'MNO' and users.lastname is null and users.customproperty2= 'DC') THEN concat(users.firstname,'.dc', '@test.com')
when (users.customproperty19 = 'MNO' and users.lastname is not null) THEN concat(users.firstname,'.',replace(users.lastname,' ',''), '@test.com')
when (users.customproperty19 = 'MNO' and users.lastname is null) THEN concat(users.firstname,'@test.com')
END #

[anujapawar]

Hi,

Please, can you check the problem statement and let me know the appropriate query for it?

[This post has been edited by a Moderator. We discourage the @ mention of other forum users or employees unless they have already involved themselves on the forum post.]

[rushikeshvartak]

Based on sample provided you can generate email generation rule. If you face issue let us know, please prepare base rule 

[PremMahadikar · Accepted solution]

Hi @anujapawar ,

Use below query as reference: (It as 2 iterations)

CASE
WHEN (preferedfirstname!='' OR preferedfirstname is not null) AND (preferedlastname!='' OR preferedlastname is not null) THEN  concat(FN_EIC_REPLACE(concat(preferedfirstname,'.',preferedlastname),null),'@domain.com')
WHEN (preferedfirstname='' OR preferedfirstname is null) AND (preferedlastname!='' OR preferedlastname is not null) THEN  concat(FN_EIC_REPLACE(preferedlastname,null),'@domain.com')
WHEN (preferedfirstname!='' OR preferedfirstname is not null) AND (preferedlastname='' OR preferedlastname is null) THEN  concat(FN_EIC_REPLACE(preferedfirstname,null),'@domain.com')
WHEN (firstname!='' OR firstname is not null) AND (lastname!='' OR lastname is not null) THEN  concat(FN_EIC_REPLACE(concat(firstname,'.',lastname),null),'@domain.com')
WHEN (firstname='' OR firstname is null) AND (lastname!='' OR lastname is not null) THEN  concat(FN_EIC_REPLACE(lastname,null),'@domain.com')
WHEN (firstname!='' OR firstname is not null) AND (lastname='' OR lastname is null) THEN  concat(FN_EIC_REPLACE(firstname,null),'@domain.com')
END
#
CASE
WHEN (preferedfirstname!='' OR preferedfirstname is not null) AND (preferedlastname!='' OR preferedlastname is not null) THEN  concat(FN_EIC_REPLACE(concat(preferedfirstname,'.',preferedlastname),null),'1','@domain.com')
WHEN (preferedfirstname='' OR preferedfirstname is null) AND (preferedlastname!='' OR preferedlastname is not null) THEN  concat(FN_EIC_REPLACE(preferedlastname,null),'1','@domain.com')
WHEN (preferedfirstname!='' OR preferedfirstname is not null) AND (preferedlastname='' OR preferedlastname is null) THEN  concat(FN_EIC_REPLACE(preferedfirstname,null),'1','@domain.com')
WHEN (firstname!='' OR firstname is not null) AND (lastname!='' OR lastname is not null) THEN  concat(FN_EIC_REPLACE(concat(firstname,'.',lastname),null),'1','@domain.com')
WHEN (firstname='' OR firstname is null) AND (lastname!='' OR lastname is not null) THEN  concat(FN_EIC_REPLACE(lastname,null),'1','@domain.com')
WHEN (firstname!='' OR firstname is not null) AND (lastname='' OR lastname is null) THEN  concat(FN_EIC_REPLACE(firstname,null),'1','@domain.com')
END
#
CASE
WHEN (preferedfirstname!='' OR preferedfirstname is not null) AND (preferedlastname!='' OR preferedlastname is not null) THEN  concat(FN_EIC_REPLACE(concat(preferedfirstname,'.',preferedlastname),null),'2','@domain.com')
WHEN (preferedfirstname='' OR preferedfirstname is null) AND (preferedlastname!='' OR preferedlastname is not null) THEN  concat(FN_EIC_REPLACE(preferedlastname,null),'2','@domain.com')
WHEN (preferedfirstname!='' OR preferedfirstname is not null) AND (preferedlastname='' OR preferedlastname is null) THEN  concat(FN_EIC_REPLACE(preferedfirstname,null),'2','@domain.com')
WHEN (firstname!='' OR firstname is not null) AND (lastname!='' OR lastname is not null) THEN  concat(FN_EIC_REPLACE(concat(firstname,'.',lastname),null),'2','@domain.com')
WHEN (firstname='' OR firstname is null) AND (lastname!='' OR lastname is not null) THEN  concat(FN_EIC_REPLACE(lastname,null),'2','@domain.com')
WHEN (firstname!='' OR firstname is not null) AND (lastname='' OR lastname is null) THEN  concat(FN_EIC_REPLACE(firstname,null),'2','@domain.com')
END

If you find the above response useful, Kindly Mark it as Accept As Solution and hit Kudos