How to Auto Increment user id in Username Generation Rule

[sudeshjaiswal]

Use Case

The requirement is that Username Generation Rule should Autoincrement as per following scenario:

Scenario:

• Username creation needs to be like "OW000001, OW000002, OW000003 ~ ”
• Username should have a max length of 8. Characters 'OW' followed by 6-digit numbers autoincremented.
• After the 9th username, it doesn't add the increment as per the requirements -  the number of digits starts increasing. The digits length should remain at 6.'

Pre-requisites

N/A

Applicable Version(s)

 

All

Solution

We can use advanced dynamic attribute logic for username generation like below

select concat("OW",case when (length(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.username, 3, length(u.username)))+1)) = 1) then concat('00000', CONVERT(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.username, 3, length(u.username)))+1),CHAR)) when (length(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.username, 3, length(u.username)))+1)) = 2) then concat('0000', CONVERT(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.username, 3, length(u.username)))+1),CHAR)) when (length(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.username, 3, length(u.username)))+1)) = 3) then concat('000', CONVERT(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.username, 3, length(u.username)))+1),CHAR)) when (length(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.username, 3, length(u.username)))+1)) = 4) then concat('00', CONVERT(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.username, 3, length(u.username)))+1),CHAR)) when (length(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.username, 3, length(u.username)))+1)) = 5) then concat('0', CONVERT(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.username, 3, length(u.username)))+1),CHAR)) END) AS ID from request_access_attrs raa,users u where raa.attribute_name = 'Username' and raa.attribute_value like 'OW0%' and u.username like 'OW0%' and length(u.username) = 8

References

https://docs.saviyntcloud.com/bundle/EIC-Admin-v2021x/page/Content/Chapter06-EIC-Configurations/Conf... 

[Manu269]

@sudeshjaiswal can you please let me know what advanced dynamic attribute logic?