PREPROCESSQUERIES - to set manager

[PuspanjaliM]

Hi team,

We use User schema import to create new users and modify user attributes as per the HR file.

we have PREPROCESSQUERIES to SET the manager for the user.

This is the query we are using to set the manager value.

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.customproperty1=CURRENTUSERS.employeeid SET NEWUSERDATA.manager = case when (CURRENTUSERS.employeeid IS NOT NULL and CURRENTUSERS.employeeid != '') and (NEWUSERDATA.customproperty1 IS NOT NULL and NEWUSERDATA.customproperty1 != '') then CURRENTUSERS.username end",

HERE customproperty1 is the field where we store the manager's employeeID of the user.

The above query is working properly setting the manager field but with each import, there is an update history it is leaving on the user's UPDATE HISTORY like below although there is no change on the value of manager on the imported file.

Could the team help with some suggestions, to stop this dummy updation?

[vivek9237 · Accepted solution]

@PuspanjaliM Instead of setting the manager column, you have to set the owner column - 

Try with this MODIFYUSERDATAJSON - 

{
  "ADDITIONALTABLES": {},
  "COMPUTEDCOLUMNS": [
    "OWNER"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA N INNER JOIN NEWUSERDATA M ON(N.CUSTOMPROPERTY1=M.EMPLOYEEID) SET N.OWNER=M.EMPLOYEEID "
  ]
}

[NM]

Hi @vivek9237, i believe this will only work .. if manager record is also getting pulled from import and in the case of incremental import it wouldn't be the scenario everytime ..

[vivek9237]

@NM you are correct. you have to also consider the existing manager in users table.

you have to include the user table in CURRENTUSERS. 
And then you have to also include the CURRENTUSERS.EMPLOYEEID in the update statement

[PremMahadikar]

Hi @PuspanjaliM ,

Can you also add 'Else' statement in your code?

Currently its always updating when it sees the condition is matching. After you add Else, I think this won't be recorded in user history.

(Assuming its 'ELSE NEWUSERDATA.manager END')

If this helps your question, please consider selecting Accept as Solution and hit Kudos

[rushikeshvartak]

UPDATE NEWUSERDATA
LEFT JOIN CURRENTUSERS
ON NEWUSERDATA.customproperty1 = CURRENTUSERS.employeeid
SET NEWUSERDATA.manager = CASE
WHEN (CURRENTUSERS.employeeid IS NOT NULL
AND CURRENTUSERS.employeeid != ''
AND NEWUSERDATA.customproperty1 IS NOT NULL
AND NEWUSERDATA.customproperty1 != ''
AND NEWUSERDATA.manager != CURRENTUSERS.username)
THEN CURRENTUSERS.username
END
WHERE (CURRENTUSERS.employeeid IS NOT NULL
AND CURRENTUSERS.employeeid != ''
AND NEWUSERDATA.customproperty1 IS NOT NULL
AND NEWUSERDATA.customproperty1 != '');

[Saathvik]

@PuspanjaliM : Manager column is key column where you are trying to update with username so internally it is trying to convert the username to key which might be the reason every time you are seeing updates.

I would suggest to update manager column with userkey or owner column with username like below. Also make sure the in ADDITIONALTABLES section you have owner column and userkey column respectively in users table according to the query you have used.

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.customproperty1=CURRENTUSERS.employeeid SET NEWUSERDATA.owner= case when (CURRENTUSERS.employeeid IS NOT NULL and CURRENTUSERS.employeeid != '') and (NEWUSERDATA.customproperty1 IS NOT NULL and NEWUSERDATA.customproperty1 != '') then CURRENTUSERS.username end",

or

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.customproperty1=CURRENTUSERS.employeeid SET NEWUSERDATA.manager = case when (CURRENTUSERS.employeeid IS NOT NULL and CURRENTUSERS.employeeid != '') and (NEWUSERDATA.customproperty1 IS NOT NULL and NEWUSERDATA.customproperty1 != '') then CURRENTUSERS.userkey end",