How to set a unique email address for each company

[JPMac]

We need to specify a different domain for each company.

For example:
- Company name ABC: domain: abc.com
- Company name DEF: domain: def.com

Each email must be unique.

We created the following advanced query and applied it to the email generation rule.

===generation rule===

CASE

    WHEN users.companyname = 'ABC' THEN

        concat(replace(users.firstname, ' ', ''), substring('.',1,1), replace(users.lastname, ' ', ''), substring('@abc.com',1,8))

        #concat(replace(users.firstname, ' ', ''), substring('.',1,1), replace(users.lastname, ' ', ''), substring('1',1,1), substring('@abc.com',1,8))

        #concat(replace(users.firstname, ' ', ''), substring('.',1,1), replace(users.lastname, ' ', ''), substring('2',1,1), substring('@abc.com',1,8))

    WHEN users.companyname = 'DEF' THEN

        concat(replace(users.firstname, ' ', ''), substring('.',1,1), replace(users.lastname, ' ', ''), substring('@def.com',1,8))

        #concat(replace(users.firstname, ' ', ''), substring('.',1,1), replace(users.lastname, ' ', ''), substring('1',1,1), substring('@def.com',1,8))

        #concat(replace(users.firstname, ' ', ''), substring('.',1,1), replace(users.lastname, ' ', ''), substring('2',1,1), substring('@def.com',1,8))

END

===================

When testing by registering three users with the same name and the same company, the results were as shown below.

Since all users have the company name ABC, my ideal outcome would be:
1. john.doe@abc.com
2. john.doe1@abc.com
3. john.doe2@abc.com

What changes should I make to the rule to achieve this ideal generation? 

[PremMahadikar]

Hi @JPMac ,

Can you try the below

CASE WHEN users.companyname = 'ABC' THEN concat(replace(users.firstname, ' ', ''), substring('.',1,1), replace(users.lastname, ' ', ''), substring('@abc.com',1,8)) END #
CASE WHEN users.companyname = 'ABC' THEN concat(replace(users.firstname, ' ', ''), substring('.',1,1), replace(users.lastname, ' ', ''), substring('1',1,1), substring('@abc.com',1,8)) END #
CASE WHEN users.companyname = 'ABC' THEN concat(replace(users.firstname, ' ', ''), substring('.',1,1), replace(users.lastname, ' ', ''), substring('2',1,1), substring('@abc.com',1,8)) END #
CASE WHEN users.companyname = 'DEF' THEN concat(replace(users.firstname, ' ', ''), substring('.',1,1), replace(users.lastname, ' ', ''), substring('@def.com',1,8)) END #
CASE WHEN users.companyname = 'DEF' THEN concat(replace(users.firstname, ' ', ''), substring('.',1,1), replace(users.lastname, ' ', ''), substring('1',1,1), substring('@def.com',1,8)) END #
CASE WHEN users.companyname = 'DEF' THEN concat(replace(users.firstname, ' ', ''), substring('.',1,1), replace(users.lastname, ' ', ''), substring('2',1,1), substring('@def.com',1,8)) END

If this helps, please consider selecting Accept As Solution and hit Kudos

[JPMac]

@Prem 

Thanks for your response!

It's working.

By the way, why does it fail when using "#" only inside the CASE WHEN statement, but succeed when "#" is included in every line? Could you explain your reasoning?

[PremMahadikar · Accepted solution]

@JPMac , 

The general format of working case statement is below:

You can't include # inside the case. When you include, it throws an error, not just in Saviynt but even in normal MySQL workbench. Thus, in Saviynt case statement follow the same syntax.

Using # is the syntax just in Saviynt (only inside advanced query) where it works like OR to execute line by line based on uniqueness check.

For Example:

• Here all the conditions are true for a user. But it only executes 1st condition, because the value 'xyz@test.com' is unique.
• Let's say, a new user is onboarded with cp='XYZ', then it won't generate 'xyz@test.com' - it will execute condition 2 because 'xyz@test.com' is already assigned to a user.
• Here order also matters
• Note: Condition 1, 2 ,3 can be MySQL general syntax format

FYI: If you select Basic Config, you can see 'Add Rule' where it adds another rule (Rule - 2) with OR. In advanced Config, to achieve OR, we need to use # and its Saviynt product syntax.

If this helps, please consider selecting Accept As Solution and hit Kudos

[JPMac]

M12827136 belongs to DEF company.

[PremMahadikar]

@JPMac ,

As the script is working. Please select 'Accept As Solution' for reply/replies which answered your question. Also hit Kudos!

[JPMac]

@PremMahadikar 

Thanks. It makes me clear.