and more in a single search tool across platforms. Read the announcement here. |
04/11/2024 09:01 AM
Hii,
I have one use case,
In mover scenario some attributes are change to user profile, I want to store the attribute name like jobcodedesc, department name to users customproperty31 using preprocessor.
following some of sytax I try, but fails to achieve.
{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 = (', ',
CASE WHEN NEWUSERDATA.jobcodedesc != CURRENTUSERS.jobcodedesc THEN 'job profile change' ELSE NULL END
);"
]
}
{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 =
CASE WHEN NEWUSERDATA.jobcodedesc != CURRENTUSERS.jobcodedesc THEN 'job profile change' ELSE NULL END WHERE NEWUSERDATA.jobcodedesc != CURRENTUSERS.jobcodedesc;"
]
}
{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 = CONCAT_WS(', ',
CASE WHEN NEWUSERDATA.LOCATION != CURRENTUSERS.LOCATION THEN 'Location change' ELSE NULL END,
CASE WHEN NEWUSERDATA.MANAGER != CURRENTUSERS.MANAGER THEN 'Manager change' ELSE NULL END,
CASE WHEN NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC THEN 'Job profile change' ELSE NULL END,
CASE WHEN NEWUSERDATA.CUSTOMPROPERTY15 != CURRENTUSERS.CUSTOMPROPERTY15 THEN 'Functional group change' ELSE NULL END
);"
]
}
can you please suggest, how we can achieve this.
04/11/2024 09:15 AM - edited 04/11/2024 09:20 AM
@ankitalande try below:
{
"ADDITIONALTABLES": {
"USERS": "SELECT JOBCODEDESC FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31","JOBCODEDESC"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 = (', ',
CASE WHEN NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC THEN 'job profile change' ELSE NULL END
)"
]
}
{
"ADDITIONALTABLES": {
"USERS": "SELECT JOBCODEDESC FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31","JOBCODEDESC"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 =
CASE WHEN NEWUSERDATA.jobcodedesc != CURRENTUSERS.JOBCODEDESC THEN 'job profile change' ELSE NULL END WHERE NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC"
]
}
{
"ADDITIONALTABLES": {
"USERS": "SELECT LOCATION,MANAGER,JOBCODEDESC,CUSTOMPROPERTY15 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 = CONCAT_WS(', ',
CASE WHEN NEWUSERDATA.LOCATION != CURRENTUSERS.LOCATION THEN 'Location change' ELSE NULL END,
CASE WHEN NEWUSERDATA.MANAGER != CURRENTUSERS.MANAGER THEN 'Manager change' ELSE NULL END,
CASE WHEN NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC THEN 'Job profile change' ELSE NULL END,
CASE WHEN NEWUSERDATA.CUSTOMPROPERTY15 != CURRENTUSERS.CUSTOMPROPERTY15 THEN 'Functional group change' ELSE NULL END
)"
]
}
04/11/2024 09:33 AM
still not working, for testing purpose I upload the user through csv and check, User not updated and review history showing 0 as well.
04/11/2024 09:57 AM
try :
{
"ADDITIONALTABLES": {
"USERS": "SELECT JOBCODEDESC FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31","JOBCODEDESC"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 = CASE WHEN (NEWUSERDATA.JOBCODEDESC is not null and NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC) THEN 'job profile change' ELSE NULL END"
]
}
{
"ADDITIONALTABLES": {
"USERS": "SELECT JOBCODEDESC FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31","JOBCODEDESC"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 =
CASE WHEN (NEWUSERDATA.JOBCODEDESC is not null and NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC) THEN 'job profile change' ELSE NULL END WHERE NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC"
]
}
{
"ADDITIONALTABLES": {
"USERS": "SELECT LOCATION,MANAGER,JOBCODEDESC,CUSTOMPROPERTY15 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 = CONCAT_WS(', ',
CASE WHEN (NEWUSERDATA.LOCATION is not null and NEWUSERDATA.LOCATION != CURRENTUSERS.LOCATION) THEN 'Location change' ELSE NULL END,
CASE WHEN (NEWUSERDATA.MANAGER is not null and NEWUSERDATA.MANAGER != CURRENTUSERS.MANAGER) THEN 'Manager change' ELSE NULL END,
CASE WHEN (NEWUSERDATA.JOBCODEDESC is not null and NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC) THEN 'Job profile change' ELSE NULL END,
CASE WHEN (NEWUSERDATA.CUSTOMPROPERTY15 is not null and NEWUSERDATA.CUSTOMPROPERTY15 != CURRENTUSERS.CUSTOMPROPERTY15) THEN 'Functional group change' ELSE NULL END
)"
]
}
04/11/2024 08:07 PM
Are you updating preprocessor JSON while uploading user?
04/11/2024 09:38 PM
Yes, I updated preprocessor json while uploading user.
04/11/2024 09:52 PM
Is there any other way to achieve this or alternative preprocessor json.
04/11/2024 09:57 PM
Saviynt 4 saviynt
04/11/2024 10:54 PM
How we can define attribute change in saviynt 4 saviynt?
04/12/2024 07:45 PM
based on user update date and comparing value in actual attribute and Custom property