Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

PREPROCESSQUERIES - to set manager

PuspanjaliM
New Contributor II
New Contributor II

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.

PuspanjaliM_0-1716189967939.png

PuspanjaliM_1-1716190413442.png

 

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

6 REPLIES 6

vivek9237
Regular Contributor
Regular Contributor

@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 "
  ]
}
Regards,

Vivek Mohanty


If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

NM
Valued Contributor
Valued Contributor

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
Regular Contributor
Regular Contributor

@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

Regards,

Vivek Mohanty


If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

PremMahadikar
Valued Contributor
Valued Contributor

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
All-Star
All-Star

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 != '');


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Saathvik
All-Star
All-Star

@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",


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.