Email generation rule for duplicate email.

[aminahasware]

Hi,

We have requirement that some users have multiple first names or last names (i.e., Lara-Rodriguez, Pyaram-Shashidhar, Cotto-Sediles, etc).

In this case, the email generation rule should be as follows-

• If the first name or last name contains a hyphen (- ) then use it as it is.
• If the first name or last name contains space ( ) then replace it with an underscore (_).
• If a duplicate email is found, autoincrement it with 1 starting with number 2.

i.e. – if Marina_Cotto-Sediles@domain.com  is found

then the new mail ID will be Marina_Cotto-Sediles2@domain.com 

We have used advanced config and used below query.

CONCAT(
        REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''),
        '_',
        REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''),
        '@domain.com'
    )

This query is working as expected but the problem is if a duplicate email ID is found then how to auto-increment in advanced config?

[This message has been edited by moderator to disable email hyperlink]

[rushikeshvartak · Accepted solution]

Refer https://forums.saviynt.com/t5/saviynt-knowledge-base/achieve-email-uniqueness-check-during-email-gen...

https://forums.saviynt.com/t5/saviynt-knowledge-base/how-to-escaping-special-characters-in-user-attr... 

CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '1@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '2@domain.com' )

[yatishtiwari]

Thank you, @rushikeshvartak . It worked.

[rushikeshvartak]

Please click the 'Accept As Solution' button on the reply (or replies) that best answered your original question.

[Miko]

If there are more email addresses in the sequence of 

Marina_Cotto-Sediles@domain.com

Marina_Cotto-Sediles1@domain.com

Marina_Cotto-Sediles2@domain.com

Marina_Cotto-Sediles3@domain.com

.....

how can we auto increment the index on advanced email generation rule?

Thanks,

Michael

...

[rushikeshvartak]

In this case you need to maintain sequence manually in email generation rule.

CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '1@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '2@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '3@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '4@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '5@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '6@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '7@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '8@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '9@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '10@domain.com' )

[yatishtiwari]

Hi Michael,

below is the sample to achieve the same. 

Please note- using the rule you can not have a perpetual rule. like 1 to n. But you can have finite numbers like 1 to 10 or 15 based on what is suitable to you.

CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '2@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '3@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '4@domain.com' )#CONCAT( REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '5@domain.com' )

[Poornima]

I am trying to combine  2 conditions:

Condition 1 : Add email domain based on department number

Condition 2: auto increment if email ID exists.

case
WHEN users.departmentnumber = 100 THEN
concat(
case
when users.firstname like '%.%' and users.firstname like '%,%' then replace(substring_index(users.firstname, ',', 1), '.', '')
when users.firstname like '%.%' then replace(users.firstname, '.', '')
when users.firstname like '%,%' then substring_index(users.firstname, ',', 1)
else users.firstname
end,
'.',
case
when users.lastname like '%.%' and users.lastname like '%,%' then replace(substring_index(users.lastname, ',', 1), '.', '')
when users.lastname like '%.%' then replace(users.lastname, '.', '')
when users.lastname like '%,%' then substring_index(users.lastname, ',', 1)
else users.lastname
end,
'@emaildomain1.org'
)#concat(
case
when users.firstname like '%.%' and users.firstname like '%,%' then replace(substring_index(users.firstname, ',', 1), '.', '')
when users.firstname like '%.%' then replace(users.firstname, '.', '')
when users.firstname like '%,%' then substring_index(users.firstname, ',', 1)
else users.firstname
end,
'.',
case
when users.lastname like '%.%' and users.lastname like '%,%' then replace(substring_index(users.lastname, ',', 1), '.', '')
when users.lastname like '%.%' then replace(users.lastname, '.', '')
when users.lastname like '%,%' then substring_index(users.lastname, ',', 1)
else users.lastname
end,
'2@emaildomain1.org'
)#concat(
case
when users.firstname like '%.%' and users.firstname like '%,%' then replace(substring_index(users.firstname, ',', 1), '.', '')
when users.firstname like '%.%' then replace(users.firstname, '.', '')
when users.firstname like '%,%' then substring_index(users.firstname, ',', 1)
else users.firstname
end,
'.',
case
when users.lastname like '%.%' and users.lastname like '%,%' then replace(substring_index(users.lastname, ',', 1), '.', '')
when users.lastname like '%.%' then replace(users.lastname, '.', '')
when users.lastname like '%,%' then substring_index(users.lastname, ',', 1)
else users.lastname
end,
'3@emaildomain1.org'
)
WHEN users.departmentnumber = 200 THEN
concat(
case
when users.firstname like '%.%' and users.firstname like '%,%' then replace(substring_index(users.firstname, ',', 1), '.', '')
when users.firstname like '%.%' then replace(users.firstname, '.', '')
when users.firstname like '%,%' then substring_index(users.firstname, ',', 1)
else users.firstname
end,
'.',
case
when users.lastname like '%.%' and users.lastname like '%,%' then replace(substring_index(users.lastname, ',', 1), '.', '')
when users.lastname like '%.%' then replace(users.lastname, '.', '')
when users.lastname like '%,%' then substring_index(users.lastname, ',', 1)
else users.lastname
end,
'@emaildomain2.org'
)#concat(
case
when users.firstname like '%.%' and users.firstname like '%,%' then replace(substring_index(users.firstname, ',', 1), '.', '')
when users.firstname like '%.%' then replace(users.firstname, '.', '')
when users.firstname like '%,%' then substring_index(users.firstname, ',', 1)
else users.firstname
end,
'.',
case
when users.lastname like '%.%' and users.lastname like '%,%' then replace(substring_index(users.lastname, ',', 1), '.', '')
when users.lastname like '%.%' then replace(users.lastname, '.', '')
when users.lastname like '%,%' then substring_index(users.lastname, ',', 1)
else users.lastname
end,
'2@emaildomain2.org'
)#concat(
case
when users.firstname like '%.%' and users.firstname like '%,%' then replace(substring_index(users.firstname, ',', 1), '.', '')
when users.firstname like '%.%' then replace(users.firstname, '.', '')
when users.firstname like '%,%' then substring_index(users.firstname, ',', 1)
else users.firstname
end,
'.',
case
when users.lastname like '%.%' and users.lastname like '%,%' then replace(substring_index(users.lastname, ',', 1), '.', '')
when users.lastname like '%.%' then replace(users.lastname, '.', '')
when users.lastname like '%,%' then substring_index(users.lastname, ',', 1)
else users.lastname
end,
'3@emaildomain2.org'
)
ELSE
concat(
case
when users.firstname like '%.%' and users.firstname like '%,%' then replace(substring_index(users.firstname, ',', 1), '.', '')
when users.firstname like '%.%' then replace(users.firstname, '.', '')
when users.firstname like '%,%' then substring_index(users.firstname, ',', 1)
else users.firstname
end,
'.',
case
when users.lastname like '%.%' and users.lastname like '%,%' then replace(substring_index(users.lastname, ',', 1), '.', '')
when users.lastname like '%.%' then replace(users.lastname, '.', '')
when users.lastname like '%,%' then substring_index(users.lastname, ',', 1)
else users.lastname
end,
'@emaildomain3.org'
)#concat(
case
when users.firstname like '%.%' and users.firstname like '%,%' then replace(substring_index(users.firstname, ',', 1), '.', '')
when users.firstname like '%.%' then replace(users.firstname, '.', '')
when users.firstname like '%,%' then substring_index(users.firstname, ',', 1)
else users.firstname
end,
'.',
case
when users.lastname like '%.%' and users.lastname like '%,%' then replace(substring_index(users.lastname, ',', 1), '.', '')
when users.lastname like '%.%' then replace(users.lastname, '.', '')
when users.lastname like '%,%' then substring_index(users.lastname, ',', 1)
else users.lastname
end,
'2@emaildomain3.org'
)#concat(
case
when users.firstname like '%.%' and users.firstname like '%,%' then replace(substring_index(users.firstname, ',', 1), '.', '')
when users.firstname like '%.%' then replace(users.firstname, '.', '')
when users.firstname like '%,%' then substring_index(users.firstname, ',', 1)
else users.firstname
end,
'.',
case
when users.lastname like '%.%' and users.lastname like '%,%' then replace(substring_index(users.lastname, ',', 1), '.', '')
when users.lastname like '%.%' then replace(users.lastname, '.', '')
when users.lastname like '%,%' then substring_index(users.lastname, ',', 1)
else users.lastname
end,
'3@emaildomain3.org'
)END

ERROR :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as property from users users where users.USERKEY=8693' at line 18"
"2024-08-02T19:33:44.930+00:00","ecm-worker","services.WorkflowService","quartzScheduler_Worker-6-z2mbv","ERROR","Error in running Advanced Config Query rule: "
"2024-08-02T19:33:44.954+00:00","ecm-worker","","null-z2mbv","","java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as property from users users where users.USERKEY=8693' at line 18 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) at com.saviynt.ecm.services.WorkflowService.getPropertyFromRule(WorkflowService.groovy:8156) at com.saviynt.ecm.services.WorkflowService$_generateUserProperty_closure96.doCall(WorkflowService.groovy:8684) at com.saviynt.ecm.services.WorkflowService.generateUserProperty(WorkflowService.groovy:8673) at com.saviynt.ecm.services.WorkflowService.$tt__generateEmail(WorkflowService.groovy:8804) at com.saviynt.ecm.services.ImportSAvDataUserService$_importDataFromFile_closure25_closure75.doCall(ImportSAvDataUserService.groovy:2518) at com.saviynt.ecm.services.ImportSAvDataUserService$_importDataFromFile_closure25.doCall(ImportSAvDataUserService.groovy:2298) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:2297) at com.saviynt.provisoning.rest.RestProvisioningService.processUsers(RestProvisioningService.groovy:3161) at com.saviynt.provisoning.rest.RestProvisioningService.importUsers(RestProvisioningService.groovy:3013) at com.saviynt.ecm.integration.ExternalConnectionCallService.importUserUsingExternalConnection(ExternalConnectionCallService.groovy:1258) at UserImportJob.execute(UserImportJob.groovy:108) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)"
"2024-08-02T19:33:44.930+00:00","ecm-worker","services.WorkflowService","quartzScheduler_Worker-6-z2mbv","DEBUG","Error in running Advanced Config Query rule: "
"2024-08-02T19:33:44.954+00:00","ecm-worker","","null-z2mbv","","java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as property from users users where users.USERKEY=8693' at line 18 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) at com.saviynt.ecm.services.WorkflowService.getPropertyFromRule(WorkflowService.groovy:8156) at com.saviynt.ecm.services.WorkflowService$_generateUserProperty_closure96.doCall(WorkflowService.groovy:8684) at com.saviynt.ecm.services.WorkflowService.generateUserProperty(WorkflowService.groovy:8673) at com.saviynt.ecm.services.WorkflowService.$tt__generateEmail(WorkflowService.groovy:8804) at com.saviynt.ecm.services.ImportSAvDataUserService$_importDataFromFile_closure25_closure75.doCall(ImportSAvDataUserService.groovy:2518) at com.saviynt.ecm.services.ImportSAvDataUserService$_importDataFromFile_closure25.doCall(ImportSAvDataUserService.groovy:2298) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:2297) at com.saviynt.provisoning.rest.RestProvisioningService.processUsers(RestProvisioningService.groovy:3161) at com.saviynt.provisoning.rest.RestProvisioningService.importUsers(RestProvisioningService.groovy:3013) at com.saviynt.ecm.integration.ExternalConnectionCallService.importUserUsingExternalConnection(ExternalConnectionCallService.groovy:1258) at UserImportJob.execute(UserImportJob.groovy:108) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)"

[rushikeshvartak]

CASE
WHEN users.departmentnumber = 100 THEN
CASE
WHEN (EXISTS (SELECT 1 FROM users u WHERE u.email =
CONCAT(
CASE
WHEN users.firstname LIKE '%.%' AND users.firstname LIKE '%,%' THEN REPLACE(SUBSTRING_INDEX(users.firstname, ',', 1), '.', '')
WHEN users.firstname LIKE '%.%' THEN REPLACE(users.firstname, '.', '')
WHEN users.firstname LIKE '%,%' THEN SUBSTRING_INDEX(users.firstname, ',', 1)
ELSE users.firstname
END,
'.',
CASE
WHEN users.lastname LIKE '%.%' AND users.lastname LIKE '%,%' THEN REPLACE(SUBSTRING_INDEX(users.lastname, ',', 1), '.', '')
WHEN users.lastname LIKE '%.%' THEN REPLACE(users.lastname, '.', '')
WHEN users.lastname LIKE '%,%' THEN SUBSTRING_INDEX(users.lastname, ',', 1)
ELSE users.lastname
END,
'@emaildomain1.org'
)
)) THEN
CONCAT(
CASE
WHEN users.firstname LIKE '%.%' AND users.firstname LIKE '%,%' THEN REPLACE(SUBSTRING_INDEX(users.firstname, ',', 1), '.', '')
WHEN users.firstname LIKE '%.%' THEN REPLACE(users.firstname, '.', '')
WHEN users.firstname LIKE '%,%' THEN SUBSTRING_INDEX(users.firstname, ',', 1)
ELSE users.firstname
END,
'.',
CASE
WHEN users.lastname LIKE '%.%' AND users.lastname LIKE '%,%' THEN REPLACE(SUBSTRING_INDEX(users.lastname, ',', 1), '.', '')
WHEN users.lastname LIKE '%.%' THEN REPLACE(users.lastname, '.', '')
WHEN users.lastname LIKE '%,%' THEN SUBSTRING_INDEX(users.lastname, ',', 1)
ELSE users.lastname
END,
'2@emaildomain1.org'
)
ELSE
CONCAT(
CASE
WHEN users.firstname LIKE '%.%' AND users.firstname LIKE '%,%' THEN REPLACE(SUBSTRING_INDEX(users.firstname, ',', 1), '.', '')
WHEN users.firstname LIKE '%.%' THEN REPLACE(users.firstname, '.', '')
WHEN users.firstname LIKE '%,%' THEN SUBSTRING_INDEX(users.firstname, ',', 1)
ELSE users.firstname
END,
'.',
CASE
WHEN users.lastname LIKE '%.%' AND users.lastname LIKE '%,%' THEN REPLACE(SUBSTRING_INDEX(users.lastname, ',', 1), '.', '')
WHEN users.lastname LIKE '%.%' THEN REPLACE(users.lastname, '.', '')
WHEN users.lastname LIKE '%,%' THEN SUBSTRING_INDEX(users.lastname, ',', 1)
ELSE users.lastname
END,
'@emaildomain1.org'
)
END
WHEN users.departmentnumber = 200 THEN
-- Similar logic for department 200 with @emaildomain2.org
ELSE
-- Default case logic with @emaildomain3.org
END