Username not getting updated from SystemUsername

[ag420]

Hello Experts 🙂

I am importing users from Workday. As a manadate, i have to pull username from Workday but the its should be based on a logic. The logic has been defined in SystemUsername generation rule and username generation rule is to use systemusername -

Now, when i tested this from csv file upload, it worked. But when i trigger Workday import, systemusername is generating but the same is not getting updated as username.

Job is configured properly - 

Any help here would be great.

Thank you

[indra_hema_95]

Hi @ag420 You are correct it doesn't from import. We have also faced the similar issue. But it works fine through csv.

We had to use pre processor for username to sort it.

Regards,

Indra

[ag420]

I did that as well, but username was not getting updated. Can you explain your preprocessor approach or share if that possible.

[indra_hema_95 · Accepted solution]

Hi @ag420 This is for fresh user import

"UPDATE NEWUSERDATA SET NEWUSERDATA.USERNAME = CASE WHEN NEWUSERDATA.preferedfirstname is NOT NULL AND CONCAT(NEWUSERDATA.preferedfirstname,'.',NEWUSERDATA.LASTNAME) NOT IN (SELECT DISTINCT USERNAME FROM CURRENTUSERS) THEN CONCAT(NEWUSERDATA.preferedfirstname,'.',NEWUSERDATA.LASTNAME) WHEN NEWUSERDATA.preferedfirstname is NOT NULL AND CONCAT(NEWUSERDATA.preferedfirstname,'.',NEWUSERDATA.LASTNAME) IN (SELECT DISTINCT USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.preferedfirstname,'.',NEWUSERDATA.LASTNAME,1) NOT IN (SELECT DISTINCT USERNAME FROM CURRENTUSERS) THEN CONCAT(NEWUSERDATA.preferedfirstname,'.',NEWUSERDATA.LASTNAME,1) WHEN NEWUSERDATA.preferedfirstname is NOT NULL AND CONCAT(NEWUSERDATA.preferedfirstname,'.',NEWUSERDATA.LASTNAME) IN (SELECT DISTINCT USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.preferedfirstname,'.',NEWUSERDATA.LASTNAME,1) IN (SELECT DISTINCT USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.preferedfirstname,'.',NEWUSERDATA.LASTNAME,2) NOT IN (SELECT DISTINCT USERNAME FROM CURRENTUSERS) THEN CONCAT(NEWUSERDATA.preferedfirstname,'.',NEWUSERDATA.LASTNAME,2) WHEN NEWUSERDATA.preferedfirstname is NOT NULL AND CONCAT(NEWUSERDATA.preferedfirstname,'.',NEWUSERDATA.LASTNAME) IN (SELECT DISTINCT USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.preferedfirstname,'.',NEWUSERDATA.LASTNAME,1) IN (SELECT DISTINCT USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.preferedfirstname,'.',NEWUSERDATA.LASTNAME,2) IN (SELECT DISTINCT USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.preferedfirstname,'.',NEWUSERDATA.LASTNAME,3) NOT IN (SELECT DISTINCT USERNAME FROM CURRENTUSERS) THEN CONCAT(NEWUSERDATA.preferedfirstname,'.',NEWUSERDATA.LASTNAME,3) END"

This is for update

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.CUSTOMPROPERTY1 = CURRENTUSERS.CUSTOMPROPERTY1 SET NEWUSERDATA.USERNAME = CASE WHEN NEWUSERDATA.preferedfirstname is NULL AND CURRENTUSERS.preferedfirstname is NOT NULL AND (NEWUSERDATA.firstname != CURRENTUSERS.firstname or NEWUSERDATA.firstname = CURRENTUSERS.firstname) AND (NEWUSERDATA.lastname != CURRENTUSERS.lastname or NEWUSERDATA.lastname = CURRENTUSERS.lastname) AND CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname) NOT IN (SELECT DISTINCT CURRENTUSERS.USERNAME FROM CURRENTUSERS) THEN CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname) WHEN NEWUSERDATA.preferedfirstname is NULL AND CURRENTUSERS.preferedfirstname is NOT NULL AND (NEWUSERDATA.firstname != CURRENTUSERS.firstname or NEWUSERDATA.firstname = CURRENTUSERS.firstname) AND (NEWUSERDATA.lastname != CURRENTUSERS.lastname or NEWUSERDATA.lastname = CURRENTUSERS.lastname) AND CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname) IN (SELECT DISTINCT CURRENTUSERS.USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,1) NOT IN (SELECT DISTINCT CURRENTUSERS.USERNAME FROM CURRENTUSERS) THEN CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,1) WHEN NEWUSERDATA.preferedfirstname is NULL AND CURRENTUSERS.preferedfirstname is NOT NULL AND (NEWUSERDATA.firstname != CURRENTUSERS.firstname or NEWUSERDATA.firstname = CURRENTUSERS.firstname) AND (NEWUSERDATA.lastname != CURRENTUSERS.lastname or NEWUSERDATA.lastname = CURRENTUSERS.lastname) AND CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname) IN (SELECT DISTINCT CURRENTUSERS.USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,1) IN (SELECT DISTINCT CURRENTUSERS.USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,2) NOT IN (SELECT DISTINCT CURRENTUSERS.USERNAME FROM CURRENTUSERS) THEN CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,2) WHEN NEWUSERDATA.preferedfirstname is NULL AND CURRENTUSERS.preferedfirstname is NOT NULL AND (NEWUSERDATA.firstname != CURRENTUSERS.firstname or NEWUSERDATA.firstname = CURRENTUSERS.firstname) AND (NEWUSERDATA.lastname != CURRENTUSERS.lastname or NEWUSERDATA.lastname = CURRENTUSERS.lastname) AND CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname) IN (SELECT DISTINCT CURRENTUSERS.USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,1) IN (SELECT DISTINCT CURRENTUSERS.USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,2) IN (SELECT DISTINCT CURRENTUSERS.USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,3) NOT IN (SELECT DISTINCT CURRENTUSERS.USERNAME FROM CURRENTUSERS) THEN CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,3) WHEN NEWUSERDATA.preferedfirstname is NULL AND CURRENTUSERS.preferedfirstname is NOT NULL AND (NEWUSERDATA.firstname != CURRENTUSERS.firstname or NEWUSERDATA.firstname = CURRENTUSERS.firstname) AND (NEWUSERDATA.lastname != CURRENTUSERS.lastname or NEWUSERDATA.lastname = CURRENTUSERS.lastname) AND CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname) IN (SELECT DISTINCT CURRENTUSERS.USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,1) IN (SELECT DISTINCT CURRENTUSERS.USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,2) IN (SELECT DISTINCT CURRENTUSERS.USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,3) IN (SELECT DISTINCT CURRENTUSERS.USERNAME FROM CURRENTUSERS) AND CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,4) NOT IN (SELECT DISTINCT CURRENTUSERS.USERNAME FROM CURRENTUSERS) THEN CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,4) ELSE CURRENTUSERS.USERNAME END",

We had to use 2 connections, 1 is for importing users another one is just for updating existing users

Regards,

Indra

[rushikeshvartak · Accepted solution]

Refer https://docs.saviyntcloud.com/bundle/EIC-Admin-v24x/page/Content/Chapter03-User-Management/User-Impo...