Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

How to set a unique email address for each company

JPMac
Regular Contributor
Regular Contributor

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.

JPMac_0-1716803172356.png


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? 

6 REPLIES 6

PremMahadikar
Valued Contributor
Valued Contributor

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
Regular Contributor
Regular Contributor

@Prem 

Thanks for your response!

JPMac_0-1716804599460.png

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
Valued Contributor
Valued Contributor

@JPMac , 

The general format of working case statement is below:

2018-08-image2-9-1024x342.jpg

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:

PremMahadikar_1-1716810012945.png

  • 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.

PremMahadikar_2-1716810536831.png

 

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

JPMac
Regular Contributor
Regular Contributor

M12827136 belongs to DEF company.

PremMahadikar
Valued Contributor
Valued Contributor

@JPMac ,

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

 

JPMac
Regular Contributor
Regular Contributor

@PremMahadikar 

Thanks. It makes me clear.