Announcing the SAVIYNT KNOWLEDGE EXCHANGE unifying the Saviynt forums, documentation, training, and more in a single search tool across platforms. Click HERE to read the Announcement.

MODIFYUSERDATAJSON not working for Workday RaaS connection

gwagh
Regular Contributor
Regular Contributor

Hi Team,

 

I am trying to update statuskey while user import so I used MODIFYUSERDATAJSON  to do that. But while importing users its getting below error.

Error in Users Import - Error while processing data: Column count doesn't match value count at row 1

Below is my Modifyuserdatajson.

{
"ADDITIONALTABLES": {
"USERS": "SELECT STATUSKEY,CUSTOMPROPERTY6 FROM USERS" },
"COMPUTEDCOLUMNS": ["STATUSKEY"],
"PREPROCESSQUERIES": ["UPDATE NEWUSERDATA SET NEWUSERDATA.STATUSKEY = CASE WHEN NEWUSERDATA.CUSTOMPROPERTY6 = '0' THEN '1' ELSE '0' END" ]
}

 

Thanks,

Gaurav

18 REPLIES 18

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @gwagh ,

Can you please try with the below query,
"UPDATE NEWUSERDATA SET STATUSKEY = CASE WHEN CUSTOMPROPERTY6 = 0 THEN 1 ELSE 0 END"

Thanks,

If you find the above response useful, Kindly Mark it as "Accept As Solution".

gwagh
Regular Contributor
Regular Contributor

Not working still getting same issue. and One more thing i haven't passed any statusthresholdconfig but still it getting trigger while import users. Below is the screenshot for your reference.

gwagh_0-1685705097908.png

Thanks,

Gaurav

"USERS": "SELECT CUSTOMPROPERTY6 FROM USERS" },

Can you just add above (removed statuskey from the query) and try ?

 

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

gwagh
Regular Contributor
Regular Contributor

@dgandhi , No luck still facing same issue.

Paste the entire JSON that you are trying.

 

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

gwagh
Regular Contributor
Regular Contributor

{
"ADDITIONALTABLES": {
"USERS": "SELECT CUSTOMPROPERTY6 FROM USERS" },
"COMPUTEDCOLUMNS": ["STATUSKEY"],
"PREPROCESSQUERIES": ["UPDATE NEWUSERDATA SET STATUSKEY = CASE WHEN CUSTOMPROPERTY6 = 0 THEN 1 ELSE 0 END" ]
}

 

Just cosmetic change, but try this and see if it works

{
"ADDITIONALTABLES": {
"USERS": "SELECT CUSTOMPROPERTY6 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"STATUSKEY"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET STATUSKEY = (CASE WHEN CUSTOMPROPERTY6 = 0 THEN 1 ELSE 0 END)"
]
}

 

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

gwagh
Regular Contributor
Regular Contributor

@dgandhi still not working. I am confused why query showing statusthresholdconfig value in Job history. Please refer the below screenshot of job logs.

shivmano
Regular Contributor III
Regular Contributor III

Have you tried checking the logs to see where the status_threshold config is being taken from? 

Please ensure that the value 0 in the CASE statement is treated as a string by enclosing it in single quotes '0'. Here's the modified version:

{
"ADDITIONALTABLES": {
"USERS": "SELECT CUSTOMPROPERTY6 FROM USERS"
},
"COMPUTEDCOLUMNS": ["STATUSKEY"],
"PREPROCESSQUERIES": ["UPDATE NEWUSERDATA SET STATUSKEY = CASE WHEN CUSTOMPROPERTY6 = '0' THEN '1' ELSE '0' END"]
}

By enclosing 0 and 1 within single quotes, the values are treated as strings, which may resolve the "Column count doesn't match value count" error.

Hi @DixshantValecha , As its string value so I tried with single quotes and without quotes . 

shivmano
Regular Contributor III
Regular Contributor III

@gwagh this seems to me like an issue with your USERIMPORT json. Please can you verify the following:

1) Check the column mappings for all the attributes you are pulling from source into Saviynt

2) Especially you need to check the datatype that you are specifying for each column mapping in your import json. For ex: if there are attributes of datatype 'number' or 'Date' on your source application, then the datatype should be specified accordingly for your sourceproperty in the  USERIMPORT json. 

3) Try to perform an import without anything in the Modifyuserdatajson and check if the import works or not

gwagh
Regular Contributor
Regular Contributor

Hi Shiv, USERIMPORT is working fine without Modifyjuserdatajson and all attributes are populating properly.

sai_sp
Saviynt Employee
Saviynt Employee

Looks like status threshold config is being picked up from the job trigger. Can you please check and clear it and try again?

gwagh
Regular Contributor
Regular Contributor

Here I am confused I haven't configured any status threshold json so from where its taking reference?? Apart from connection do I need to check in other things for statusthresholdjson ??

Rishi
Saviynt Employee
Saviynt Employee

Also while running the keep statuskey in the additionaltables Json
{
"ADDITIONALTABLES": {
"USERS": "SELECT STATUSKEY,CUSTOMPROPERTY6 FROM USERS" },
"COMPUTEDCOLUMNS": ["STATUSKEY"],
"PREPROCESSQUERIES": ["UPDATE NEWUSERDATA SET NEWUSERDATA.STATUSKEY = CASE WHEN NEWUSERDATA.CUSTOMPROPERTY6 = '0' THEN '1' ELSE '0' END" ]
}

gwagh
Regular Contributor
Regular Contributor

Tried that as well still same error.. 

Hi @gwagh ,

Can you please share the userimportjson here?

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