When Creating User through User Form, username with condition, username unable to incremental

[CLeong]

Hi,

Have the use case where after username define in access form, additional checking require, if the username exist, it will auto incremental a numeric to the username

when fill in user form, requestor insert first name and last name

username will based on this SQL values:
SELECT CONCAT(LEFT(${lastname}, 1), CASE WHEN LOCATE(' ', ${firstname}) > 0 THEN LEFT(${firstname}, LOCATE(' ', ${firstname}) - 1) ELSE ${firstname} END) AS ID;

how to add the additional rules to check if ID exist will auto incremental with numeric?
- test to add register user rules with auto increment but not works

Or use SQL? Any better solution for this?

Thank you

[rushikeshvartak]

Check in where clause 

sample

SELECT
CASE
WHEN EXISTS (
SELECT 1
FROM users
WHERE username = CONCAT(LEFT(${lastname}, 1),
CASE WHEN LOCATE(' ', ${firstname}) > 0 THEN LEFT(${firstname}, LOCATE(' ', ${firstname}) - 1)
ELSE ${firstname}
END)
)
THEN CONCAT(
LEFT(${lastname}, 1),
CASE WHEN LOCATE(' ', ${firstname}) > 0 THEN LEFT(${firstname}, LOCATE(' ', ${firstname}) - 1)
ELSE ${firstname}
END,
(
SELECT COALESCE(MAX(CAST(SUBSTRING(username, LENGTH(CONCAT(LEFT(${lastname}, 1),
CASE WHEN LOCATE(' ', ${firstname}) > 0 THEN LEFT(${firstname}, LOCATE(' ', ${firstname}) - 1)
ELSE ${firstname}
END)) + 1) AS UNSIGNED)), 0) + 1
FROM users
WHERE username REGEXP CONCAT('^', LEFT(${lastname}, 1),
CASE WHEN LOCATE(' ', ${firstname}) > 0 THEN LEFT(${firstname}, LOCATE(' ', ${firstname}) - 1)
ELSE ${firstname}
END, '[0-9]*$')
)
)
ELSE CONCAT(LEFT(${lastname}, 1),
CASE WHEN LOCATE(' ', ${firstname}) > 0 THEN LEFT(${firstname}, LOCATE(' ', ${firstname}) - 1)
ELSE ${firstname}
END)
END AS unique_username
FROM users
LIMIT 1;

[CLeong]

Hi,

the use case will be based on the first name and last name

will generate the login username

using the code get error when i click the username

can further advise?

regards

[rushikeshvartak]

Could you kindly provide a detailed snapshot of the information extracted from the logs, encompassing errors and other pertinent functionality details encountered during the execution of this process? Your assistance in furnishing this information would greatly aid in the analysis and resolution of any issues .



‼️‼️⚠️Do not upload any attachments that contain sensitive information, such as IP Addresses, URLs, Company/Employee Names, Email Addresses, etc.⚠️‼️‼️

[CLeong]

1.user fill in first name and last name
2.username will based on this SQL values: --> to generate the username
SELECT CONCAT(LEFT(${lastname}, 1), CASE WHEN LOCATE(' ', ${firstname}) > 0 THEN LEFT(${firstname}, LOCATE(' ', ${firstname}) - 1) ELSE ${firstname} END) AS ID;

3.additional requirement : require to add checking, if the generated username already exists from user table, it will automatically append additional number. In the example , the username will be "afiq"

4.username afiq already exist, how to make the condition checking in backend and appear "afiq1"?

Or got any other way to archive this use case?

[rushikeshvartak]

Query users table and check generated username already exists or not and suggest next possible username

[CLeong]

Hi Rushikesh,

put in the same ar form with different attribute? i tried your suggestion but it encounter error to generate the username

[rushikeshvartak]

Could you kindly provide a detailed snapshot of the information extracted from the logs, encompassing errors and other pertinent functionality details encountered during the execution of this process? Your assistance in furnishing this information would greatly aid in the analysis and resolution of any issues .



‼️‼️⚠️Do not upload any attachments that contain sensitive information, such as IP Addresses, URLs, Company/Employee Names, Email Addresses, etc.⚠️‼️‼️

[CLeong]

Hi,

how about use the "system username generation rule" -> Advanced config?

[rushikeshvartak]

You can use it.

[CLeong]

Hi,

I refer to documentation example and another forum...
use the "Add Register User Rule" with Advance Config and make it work

https://docs.saviyntcloud.com/bundle/SSM-Admin-v55x/page/Content/Chapter06-Configuring-EIC/Configure...

concat(users.firstname , substring('.',1,1) , users.lastname)
### CASE WHEN (users.middlename is NOT NULL) THEN concat(users.firstname , substring('.',1,1) , substring(users.middlename,1,1) , substring('.',1,1) , users.lastname) END
### concat(users.firstname , substring('.',1,1) , users.lastname , substring('1',1,1))
### concat(users.firstname , substring('.',1,1) , users.lastname , substring('2',1,1))
### concat(users.firstname , substring('.',1,1) , users.lastname , substring('3',1,1)

https://forums.saviynt.com/t5/identity-governance/forward-slash-in-system-username-generation-rule/m...

The limitation is unable to have infinite auto increment by itself. Probably because the advance config unable to put in a full sql query with function attach together. Not sure whether saviynt can make the basic rules/advanced config can have more flexibility on it

Thanks a lot

Regards

[rushikeshvartak]

You can raise idea ticket for same. for now you need to add incremental value manually