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

Accountname Rule via advanced query

Anurag08
New Contributor
New Contributor

Hi Team,

I need to establish an AccountName rule using an advanced query with the following conditions. Could you please provide suggestions for this?

When EmployeeType = EMPLOYEE:
Then
ABC.FirstName.Lastname
If the total count exceeds 20:
Then
ABC.FirstLetterInitialLastname

For Duplicate users with same name: It should be incremental..

Note: "ABC" represents the hardcoded company name.

Thanks in advance.

5 REPLIES 5

rushikeshvartak
All-Star
All-Star


IF(EmployeeType = 'EMPLOYEE',
CONCAT('ABC.', FirstName, '.', Lastname),
IF((SELECT COUNT(*) FROM users WHERE EmployeeType = 'EMPLOYEE') > 20,
CONCAT('ABC.', LEFT(FirstName, 1), Lastname),
CONCAT('ABC.', FirstName, '.', Lastname)
)


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Anurag08
New Contributor
New Contributor

Hi Rushikesh,

Thank you for your prompt response.

Could you also advise on how to handle duplicate user scenario by modifying this query to generate account name in incremental order?
For example, if a second "Anurag Vats" is hired, the account name should be generated as "abc.anurag.vats1" and so on..
and if the total count is more than 20 than it should be "abc.avats1"

 

You can use concat

sample

substring(upper(users.username),1,6)#concat(substring(upper(users.username),1,5),'1')#concat(substring(upper(users.username),1,5),'2')#concat(substring(upper(users.username),1,5),'3')#concat(substring(upper(users.username),1,5),'4')#concat(substring(upper(users.username),1,5),'5')#concat(substring(upper(users.username),1,5),'6')#concat(substring(upper(users.username),1,5),'7')#concat(substring(upper(users.username),1,5),'8')#concat(substring(upper(users.username),1,5),'9')#concat(substring(upper(users.username),1,4),'10')#concat(substring(upper(users.username),1,4),'11')#concat(substring(upper(users.username),1,4),'12')#concat(substring(upper(users.username),1,4),'13')#concat(substring(upper(users.username),1,4),'14')#concat(substring(upper(users.username),1,4),'15')#concat(substring(upper(users.username),1,4),'16')#concat(substring(upper(users.username),1,4),'17')#concat(substring(upper(users.username),1,4),'18')#concat(substring(upper(users.username),1,4),'19')#concat(substring(upper(users.username),1,4),'20')#concat(substring(upper(users.username),1,4),'21')#concat(substring(upper(users.username),1,4),'22')#concat(substring(upper(users.username),1,4),'23')#concat(substring(upper(users.username),1,4),'24')#concat(substring(upper(users.username),1,4),'25')#concat(substring(upper(users.username),1,4),'26')#concat(substring(upper(users.username),1,4),'27')#concat(substring(upper(users.username),1,4),'28')#concat(substring(upper(users.username),1,4),'29')#concat(substring(upper(users.username),1,4),'30')#concat(substring(upper(users.username),1,4),'31')#concat(substring(upper(users.username),1,4),'32')#concat(substring(upper(users.username),1,4),'33')#concat(substring(upper(users.username),1,4),'34')#concat(substring(upper(users.username),1,4),'35')#concat(substring(upper(users.username),1,4),'36')#concat(substring(upper(users.username),1,4),'37')#concat(substring(upper(users.username),1,4),'38')#concat(substring(upper(users.username),1,4),'39')#concat(substring(upper(users.username),1,4),'40')


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Anurag08
New Contributor
New Contributor

Hi Rushikesh,

Thank you for your suggestion.

I've adjusted the query by integrating a case statement to encompass all potential scenarios. However, it's not yielding any results. Could you kindly advise if there's something I may have missed ?

case when (users.employeeType.equals('EMPLOYEE')) & length(concat(users.firstname,'.',users.lastname)) <16 then concat(SUBSTRING(users.companyname,1,4),'.',users.firstname,'.',users.lastname)
when (length(concat(users.firstname,'.',users.lastname)) >15 & length(users.lastname)<14) then concat(SUBSTRING(users.companyname,1,4),'.',SUBSTRING(users.firstname, 1, 1),'.',users.lastname)
when (length(concat(users.firstname,'.',users.lastname)) >15 & length(users.lastname)>13) then concat(SUBSTRING(users.companyname,1,4),'.',SUBSTRING(users.firstname, 1, 1),'.',SUBSTRING(users.lastname,1,13))

 

Anurag08_0-1713526941059.png

 

Did you checked logs?

Does user have properties mentioned in query and not null ?


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.