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

ModifyUserDataJson - Workday REST Connection

smadabh
New Contributor II
New Contributor II

Hi, 

We are looking to set customproperty45 to different values to trigger certain user update rules for relevant actions upon import from Workday

This is the current syntax for the query:

"UPDATE NEWUSERDATA SET customproperty45 = CASE WHEN customproperty45=1 THEN 2 WHEN customproperty45=2 THEN 3 END"

**We've also tried this with the values in single quotes. In both scenarios, when customproperty45=1, the actual result has been null when the expected result is 2. 

Any suggestions on how to fix this? It seems like the import is always seeing the value for customproperty45 as NULL.

The Json part for this query:

 

 

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY, customproperty45 FROM USERS" // tried this w/o including CP45 and "userkey" is needed for the other queries that we are calling

},
"COMPUTEDCOLUMNS": [
"customproperty45"

],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET customproperty45 = CASE WHEN customproperty45=1 THEN 2 WHEN customproperty45=2 THEN 3 END"
]
}

 

 

 

 

12 REPLIES 12

sk
All-Star
All-Star

@smadabh : Is customproperty45 is mapped in colsToPropsMap in ImportUserJSON


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

smadabh
New Contributor II
New Contributor II

No. We are not importing anything from Workday for this customproperty.

Then it will be always null because from import you are not getting CP45 so newuser data will always have null for CP45 and won't get updated as your case statement is also not matching.

What is is your actual use case and what is that you want achieve? 


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

rushikeshvartak
All-Star
All-Star

Please try below

 

{ "ADDITIONALTABLES": { "USERS": "SELECT username , USERKEY, CUSTOMPROPERTY45 FROM USERS"},



"COMPUTEDCOLUMNS": [ "CUSTOMPROPERTY45"], 

"PREPROCESSQUERIES": [ 
"ALTER TABLE NEWUSERDATA ADD INDEX `idx_USERNAME` (`USERNAME` ASC)", 
"ALTER TABLE NEWUSERDATA ADD INDEX `idx_CP45` (`CUSTOMPROPERTY45` ASC)", 

"UPDATE NEWUSERDATA NU1 INNER JOIN CURRENTUSERS CU1 ON CU1.USERNAME=NU1.USERNAME SET NU1.CUSTOMPROPERTY45 =  CASE WHEN customproperty45=1 THEN 2 WHEN customproperty45=2 THEN 3 END"
] }

 


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

I got the following error when I tried the above in the job log for failure message - Error in Users Import - Error while processing data: BLOB/TEXT column 'CUSTOMPROPERTY45' used in key specification without a key length

Use any customproperty with varchar(255) datatype such as CP19


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

Is there a list somewhere of custom properties and their data types?

We are already using the following for different purposes:  1 to 24, 39, 42, 43, 44, 46, 47, 49, 50, 61, 65

@smadabh : You can identify the column data type from data analyzer as shown below

sk_0-1697637220617.png

 


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

smadabh
New Contributor II
New Contributor II

I tried it with customproperty38 which is of varchar(255) type and got the following error - 

Error in Users Import - Error while processing data: Column 'customproperty38' in field list is ambiguous

smadabh
New Contributor II
New Contributor II

Hello, any suggestion on how to proceed on this? Not sure why we are seeing this error. 

use alias


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

smadabh
New Contributor II
New Contributor II

Modifying the Update statement to make sure its looking for the custom property on the current user worked. Highlighted the change. 

UPDATE NEWUSERDATA NU1 INNER JOIN CURRENTUSERS CU1 ON CU1.USERNAME=NU1.USERNAME SET NU1.CUSTOMPROPERTY38 = CASE WHEN cu1.customproperty38=1 THEN 2 WHEN cu1.customproperty38=2 THEN 3 END"