Email Generation Rule advanced query when user with no lastname and max characters of 30

[aundreb]

Hello,

I'm using the below email generation rule to meet the criteria of when a user has no lastname then email should be firstname@domain and if they have a lastname firstname.lastname@domain with incrementing if the email exists. The firstname.lastname portion works but if a user doesn't have a lastname an email is not being generated. I've tried with doing users.lastname='' as well same issue.

How would I get a user with no lastname email to generate as well as prevent each email from being more than 30 characters long via advanced query?

CASE WHEN (users.lastname=null) THEN concat(users.firstname,'@domain.com') ELSE concat(users.firstname,'.',users.lastname,'@domain.com') END #

CASE WHEN (users.lastname=null) THEN concat(users.firstname,'1','@domain.com') ELSE concat(users.firstname,'.',users.lastname,'1','@domain.com') END #

CASE WHEN (users.lastname=null) THEN concat(users.firstname,'2','@domain.com') ELSE concat(users.firstname,'.',users.lastname,'2','@domain.com') END #

CASE WHEN (users.lastname=null) THEN concat(users.firstname,'3','@domain.com') ELSE concat(users.firstname,'.',users.lastname,'3','@domain.com') END

  Error-

[Sivagami]

Hi @aundreb 

Can you try below and let us know if it works?

CASE WHEN (users.lastname is null OR users.lastname='') THEN concat(users.firstname,'@domain.com') ELSE concat(users.firstname,'.',users.lastname,'@domain.com') END #

CASE WHEN (users.lastname is null OR users.lastname='') THEN concat(users.firstname,'1','@domain.com') ELSE concat(users.firstname,'.',users.lastname,'1','@domain.com') END #

CASE WHEN (users.lastname is null OR users.lastname='') THEN concat(users.firstname,'2','@domain.com') ELSE concat(users.firstname,'.',users.lastname,'2','@domain.com') END #

CASE WHEN (users.lastname is null OR users.lastname='') THEN concat(users.firstname,'3','@domain.com') ELSE concat(users.firstname,'.',users.lastname,'3','@domain.com') END

-Siva

[aundreb]

Thanks Sivgami, this works for the checking if last name is null.

[rushikeshvartak · Accepted solution]

CASE WHEN users.lastname IS NULL THEN CASE WHEN LENGTH(users.firstname) <= 27 THEN CONCAT(users.firstname, '@domain.com') ELSE CONCAT(SUBSTRING(users.firstname, 1, 27), '@domain.com') END ELSE CASE WHEN LENGTH(users.firstname) + LENGTH(users.lastname) <= 27 THEN CONCAT(users.firstname, '.', users.lastname, '@domain.com') ELSE CONCAT(SUBSTRING(users.firstname, 1, 27 - LENGTH(users.lastname)), '.', users.lastname, '@domain.com') END END

[aundreb]

Hi Rushikesh,

This is a bit hard to read, can you provide in a more human readable format. Also would this account for having to auto increment as well? I don't see the case where you would have to add an additional number if the email exists already.

[aundreb]

For those curious, final email generation rule with both checking if no lastname and length of email looked like the below:

CASE WHEN (users.lastname is null) THEN CASE WHEN (LENGTH(users.firstname) <= 21) THEN CONCAT(users.firstname, '@domain') ELSE CONCAT(SUBSTRING(users.firstname, 1, 21), '@domain') END ELSE CASE WHEN (LENGTH(users.firstname) + LENGTH(users.lastname) <= 20) THEN CONCAT(users.firstname, '.', users.lastname, '@domain') ELSE CONCAT(users.firstname, '.', SUBSTRING(users.lastname, 1, 20 - LENGTH(users.firstname)), '@domain') END END #
CASE WHEN (users.lastname is null) THEN CASE WHEN (LENGTH(users.firstname) <= 20) THEN CONCAT(users.firstname,'1','@domain') ELSE CONCAT(SUBSTRING(users.firstname, 1, 20),'1','@domain') END ELSE CASE WHEN (LENGTH(users.firstname) + LENGTH(users.lastname) <= 19) THEN CONCAT(users.firstname, '.', users.lastname,'1','@domain') ELSE CONCAT(users.firstname, '.', SUBSTRING(users.lastname, 1, 19 - LENGTH(users.firstname)),'1','@domain') END END #
CASE WHEN (users.lastname is null) THEN CASE WHEN (LENGTH(users.firstname) <= 20) THEN CONCAT(users.firstname,'2','@domain') ELSE CONCAT(SUBSTRING(users.firstname, 1, 20),'2','@domain') END ELSE CASE WHEN (LENGTH(users.firstname) + LENGTH(users.lastname) <= 19) THEN CONCAT(users.firstname, '.', users.lastname,'2','@domain') ELSE CONCAT(users.firstname, '.', SUBSTRING(users.lastname, 1, 19 - LENGTH(users.firstname)),'2','@domain') END END #
CASE WHEN (users.lastname is null) THEN CASE WHEN (LENGTH(users.firstname) <= 20) THEN CONCAT(users.firstname,'3','@domain') ELSE CONCAT(SUBSTRING(users.firstname, 1, 20),'3','@domain') END ELSE CASE WHEN (LENGTH(users.firstname) + LENGTH(users.lastname) <= 19) THEN CONCAT(users.firstname, '.', users.lastname,'3','@domain') ELSE CONCAT(users.firstname, '.', SUBSTRING(users.lastname, 1, 19 - LENGTH(users.firstname)),'3','@domain') END END #
CASE WHEN (users.lastname is null) THEN CASE WHEN (LENGTH(users.firstname) <= 20) THEN CONCAT(users.firstname,'4','@domain') ELSE CONCAT(SUBSTRING(users.firstname, 1, 20),'4','@domain') END ELSE CASE WHEN (LENGTH(users.firstname) + LENGTH(users.lastname) <= 19) THEN CONCAT(users.firstname, '.', users.lastname,'4','@domain') ELSE CONCAT(users.firstname, '.', SUBSTRING(users.lastname, 1, 19 - LENGTH(users.firstname)),'4','@domain') END END #
CASE WHEN (users.lastname is null) THEN CASE WHEN (LENGTH(users.firstname) <= 20) THEN CONCAT(users.firstname,'5','@domain') ELSE CONCAT(SUBSTRING(users.firstname, 1, 20),'5','@domain') END ELSE CASE WHEN (LENGTH(users.firstname) + LENGTH(users.lastname) <= 19) THEN CONCAT(users.firstname, '.', users.lastname,'5','@domain') ELSE CONCAT(users.firstname, '.', SUBSTRING(users.lastname, 1, 19 - LENGTH(users.firstname)),'5','@domain') END END

 Adjust the length checks to match the length of your domain and additional characters.