ModifyUserDataJson - Workday REST Connection

[smadabh]

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"
]
}

[Saathvik]

@smadabh : Is customproperty45 is mapped in colsToPropsMap in ImportUserJSON? 

[smadabh]

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

[Saathvik]

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? 

[rushikeshvartak]

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"
] }

[smadabh]

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

[rushikeshvartak]

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

[smadabh]

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

[Saathvik]

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

[smadabh]

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]

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

[rushikeshvartak]

use alias

[smadabh]

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"