Workday RaaS - UserModifyJSON

[Alex_Terry]

Hello, 

We have a requirement to store a user's manager's manager in a custom property. We wish to achieve this using the ModifyUserJSON as part of the connection so that we dont have to schedule an additional job to achieve this. 

With the below we get the error also specified below.

ModifyUserJSON:

{
"ADDITIONALTABLES": {
	"USERS": "SELECT USERKEY,EMAIL,USERNAME,MANAGER,STATUSKEY,EMPLOYEEID FROM USERS"},
	
"COMPUTEDCOLUMNS": [
	"USERNAME", "STATUSKEY", "MANAGER", "ENDDATE", "STARTDATE","CUSTOMPROPERTY8" ,"CUSTOMPROPERTY9", "CUSTOMPROPERTY14", "CUSTOMPROPERTY15", "CUSTOMPROPERTY18", "EMAIL", "CUSTOMPROPERTY20"],
	
"PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA SET NEWUSERDATA.STATUSKEY=1","UPDATE NEWUSERDATA SET NEWUSERDATA.EMAIL = CONCAT(SUBSTRING_INDEX(EMAIL,'@',1),'@XXX.uk')",
	"UPDATE NEWUSERDATA SET NEWUSERDATA.MANAGER = (select CURRENTUSERS.USERNAME FROM CURRENTUSERS WHERE CURRENTUSERS.EMPLOYEEID = NEWUSERDATA.CUSTOMPROPERTY4 AND CURRENTUSERS.STATUSKEY = 1)",
	"UPDATE NEWUSERDATA SET NEWUSERDATA.ENDDATE = DATE_FORMAT(NEWUSERDATA.CUSTOMPROPERTY15, '%b %d, %Y %H:%i:%s')",
	"UPDATE NEWUSERDATA SET NEWUSERDATA.STARTDATE = DATE_FORMAT(NEWUSERDATA.CUSTOMPROPERTY14, '%b %d, %Y %H:%i:%s')",
	"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY8 = CASE WHEN NEWUSERDATA.CUSTOMPROPERTY8 = 0 THEN 'FALSE' WHEN NEWUSERDATA.CUSTOMPROPERTY8 = 1 THEN 'TRUE' ELSE NEWUSERDATA.CUSTOMPROPERTY8 END",		
	"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY9 = CASE WHEN NEWUSERDATA.CUSTOMPROPERTY9 = 0 THEN 'FALSE' WHEN NEWUSERDATA.CUSTOMPROPERTY9 = 1 THEN 'TRUE' ELSE NEWUSERDATA.CUSTOMPROPERTY9 END",
	"UPDATE NEWUSERDATA SET NEWUSERDATA.STATUSKEY = CASE WHEN NEWUSERDATA.CUSTOMPROPERTY5 < CURDATE() THEN 0 ELSE 1 END",
	"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY20 = (SELECT USERS.manager FROM USERS WHERE USERS.userkey = NEWUSERDATA.manager)"
]
}

Error: 

2023-11-17T12:24:02.213796243Z stdout F 2023-11-17 12:24:02,213 [quartzScheduler_Worker-2] ERROR services.ImportSAvDataUserService  - Error while processing data: 
2023-11-17T12:24:02.213808643Z stdout F com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: '123546'

Please can you advise?

[Saathvik]

@Alex_Terry : First of all I want to check if you are able to see manager for user properly? I see a conflicting statements

Here you are setting user manager with username which is string

	"UPDATE NEWUSERDATA SET NEWUSERDATA.MANAGER = (select CURRENTUSERS.USERNAME FROM CURRENTUSERS WHERE CURRENTUSERS.EMPLOYEEID = NEWUSERDATA.CUSTOMPROPERTY4 AND CURRENTUSERS.STATUSKEY = 1)",

 where as below you are comparing with userkey with manager column which you are setting username in above statement

"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY20 = (SELECT USERS.manager FROM USERS WHERE USERS.userkey = NEWUSERDATA.manager)"

 Adjust your query accordingly

[Alex_Terry]

Hi @Saathvik 

Thanks for your reply.

In the user page we can see the managers as expected. When I query this in the data analyzer I see that the manager is returned as  the userkey for that manager and as such is why i'm matching on that in the second query. Is that the incorrect way to view this?

[Saathvik]

@Alex_Terry : I think once you assign manager internally it is translating to key of the manager. Here in this case you compare the data with newuserdata which is not yet translated so try to compare with username instead of userkey and see if that works

[Alex_Terry]

Hey @Saathvik thanks for your help, this hasn't resolved the issue but we no longer have need for this use case. I will spend some time at a later date and validate this potential fix.