Accountname generation rules

[Saviynt_learner]

Hi all,

I need to create account name rule which will have users firstname +lastname.

But if there's a duplicate / users with same name, it should include 1st letter of middle name and if  there's 3 users then 1st 2 letters of middlename at the end.

How can I do this using advanced query??

[rushikeshvartak]

concat(users.firstname,users.lastname)#concat(users.firstname,substring(users.middlename,1,1),users.lastname)#concat(users.firstname,users.lastname,substring(users.middlename,1,2))

[Saviynt_learner]

Thanks for the response @rushikeshvartak . may i know why you used '#' in the query?

sometimes I have also see 3#(###) also . Why we use this. and what does it mean here?

[rushikeshvartak]

  # is internally used to seprator two account name rule. different places different logic is used in account name rule seprator is single hash #

[Saviynt_learner]

Understood, but where we are mentioning if its duplicate and use second one?? will it understand by default?

[rushikeshvartak · Accepted solution]

Its saviynt logic if first does not satisfy it will evaulte next using seprator #

[Saviynt_learner]

@rushikeshvartak Instead of checking duplicate username and going for next rule. can we do the same if users have same first name then go for next rule???

[rushikeshvartak]

You can write case when then logic of SQL.

[Saviynt_learner]

Can you please provide a general syntax, I'm not getting how to check even when we use case when .

[rushikeshvartak]

concat(users.firstname, users.city, (case when (users.lastname = 'Admin')
THEN
concat(users.firstname,users.lastname)
ELSE
users.lastname
END))

https://docs.saviyntcloud.com/bundle/EIC-Admin-v23x/page/Content/Chapter02-Identity-Repository/Creat...

[Saviynt_learner]

I used Case when condition for some other condition. If i use it for 1 time its working, but there is a duplicate i need to ad few more character from username, But its not working, and showing account name blank. 

can we use case when multiple times like 

concat(case when..... end, substring(users.username,1,1) # concat(case when..... end, substring(users.username,1,2)

[rushikeshvartak]

You can test query from data analyzer before adding & check

[Saviynt_learner]

Yes we can but Its having a Saviynt format like we have to separate the conditions with # , to remove duplicate and next condition etc.This cannot be done using Data analyzer.

Basically if I test each query separately its working, but I i use it after # its not working  that too if i include case when condition in both query else it works flawlessly.

[rushikeshvartak]

Use case when then end

[Saviynt_learner]

Yes..used the same

[rushikeshvartak]

share query