Pre Processor Does not work while creating a user but while updating a user

[AashishD]

Hi,

We have written a pre processor code under ModifyUserJSON. We are importing users via REST API.

Use Case:

Employee Type on target is 'User ID with External Email' . When a new user is imported in Saviynt, the value 'User ID with External mail' should be set as 'External Email' in employeetype attribute of Saviynt. This is not working while creation.

The pre processor query works when we are updating an existing user in Saviynt though

Also, in our user import json we have mapped UserType (target) to employeetype attribute in Saviynt.

We tested the same via csv, and during create it does not work. It works while updating a user.

Here is the pre processor code:

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,employeeType FROM USERS"
},
"COMPUTEDCOLUMNS": [
"username","employeeType"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA, CURRENTUSERS SET NEWUSERDATA.employeeType = case when NEWUSERDATA.employeeType = 'User ID with external mail' then 'ExternalMail' when NEWUSERDATA.employeeType ='User ID with Internal mail only' then 'InternalMail' when NEWUSERDATA.employeeType = 'User ID without email' then 'ADOnly' else NEWUSERDATA.employeeType end where NEWUSERDATA.USERNAME=CURRENTUSERS.USERNAME"
]
}

Am I missing something? Please advice

[naveenss]

Any error in the logs? Also, in the computedcolumns, you can just mention 'employeeType'. Username is not required. 

[AashishD]

Hi Naveen,

I have made the changes, still the same. I could not see error logs too. Can see in logs that the query is getting executed.

[naveenss · Accepted solution]

can you try the below query?

update newusers n left join currenusers c on n.username=c.username set n.employeeType=case when n.employeeType = 'User ID with external mail' then 'ExternalMail' when n.employeeType ='User ID with Internal mail only' then 'InternalMail' when n.employeeType = 'User ID without email' then 'ADOnly' else n.employeeType end

[AashishD]

Thank you naveen,

correcting the table names NEWUSERDATA, currentusers in your above query. It worked

[naveenss]

Perfect. thanks for the confirmation. 

[AashishD]

Could you tell me what was the mistake? 

Instead of "where NEWUSERDATA.USERNAME=CURRENTUSERS.USERNAME" you have used joins. Was the 'where' condition causing an issue?

[naveenss]

That's correct. since for new users, the data will not exist in the currentusers table. hence the condition you had originally wouldn't pick up the new users. Hence in this case we need to use 'left join' which will satisfy for both new and existing users. 

[AashishD]

Thank you for the info