Pre Processor JSON is making attributes empty

[Varshi_Balaji]

Hi Team,

We have requirement where we need to update display name based on first name and last name condition.

{ "ADDITIONALTABLES":
{ "USERS": "SELECT DISPLAYNAME, EMPLOYEETYPE, CUSTOMPROPERTY3, FIRSTNAME, LASTNAME, FROM USERS" },
"COMPUTEDCOLUMNS":
[ "DISPLAYNAME", "EMPLOYEETYPE", "CUSTOMPROPERTY3", "FIRSTNAME", "LASTNAME" ],
"PREPROCESSQUERIES":
[
"UPDATE NEWUSERDATA SET displayName = CONCAT(firstname,' ',lastname) where ((employeetype='Permanent Employee' and username='user.newjoiner11') and (displayName is null or displayName=''))",
"UPDATE NEWUSERDATA SET displayName = CONCAT(firstname,' ',lastname,'External') where ((customproperty3='89' and employeetype!='Permanent Employee' and username='user.newjoiner11') and (displayName is null or displayName=''))",
"UPDATE NEWUSERDATA SET displayName = CONCAT(firstname,' ',lastname,'Externe') where ((employeetype!='Permanent Employee' and username='user.newjoiner11') and (displayName is null or displayName=''))"
] }

we added this query in the pre processor json and when we triggered the job the attributes present in the query(firstname,lastname,cp3,employeetype) evrything became empty.

Is this happening because of the version, we are currently in 23.4 and we are facing this issue.

Can you please help us here.

Regards,

Varshitha

[naveenss]

Hi @Varshi_Balaji ,

I see you have defined firstname,lastname,cp3,employeetype in the COMPUTEDCOLUMNS. But there is no query defined to set this value under the PREPROCESSQUERIES. 

[AmitM]

Hi @Varshi_Balaji , is displayname coming from connector mappings? if yes then dont add it in computed columns again.

No need to have firstname and lastname also as computed columns. Same for cp3. Keep computed column blank if all attributes are part of mapping.

Thanks,

Amit

[saimeghana]

Hi AmitM

We tried by keeping the only one computed value that displayname still we are facing the error.

We are using the below preprocessor query:

{
"ADDITIONALTABLES": {
"USERS": "SELECT DISPLAYNAME,EMPLOYEETYPE FROM USERS"},
"COMPUTEDCOLUMNS": [
"DISPLAYNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET displayName = CONCAT(firstname,' ',lastname) where ((employeetype='Permanent Employee' and username='user.newjoiner11') and (displayName is null or displayName=''))"
]
}

"Error in Users Import - Error while processing data: Unknown column 'employeeType' in 'where" clause' 

Thanks,

Sai Meghana

[AmitM · Accepted solution]

In your connector (user import mapping), have you mapped employeetype? 

can you share you user import mapping?

Also it will help if you can explain your use case , why have you hard coded username? You expect it to run only for one user?

You are also not using additional tables but are having additional tables in preprocessor

[saimeghana]

No, we didn't mapped the emloyeetype

{

"connection": "userAuth",

"url": "https://<domain>.saviyntcloud.com/ECM/api/v5/getUser",

"httpHeaders": {

        "Authorization": "${access_token}",

"contentType": "application/json"

},

"httpMethod": "POST",

"userResponsePath": "userdetails",

"colsToPropsMap": {

"username": "username~#~char",

"customproperty56": "BU~#~char"

}, 

"pagination": { 

"offset": 

{ 

"offsetParam": "offset", 

"batchParam": "max", "batchSize": 500, 

"totalCountPath": 999999 

} 

}

}

[rushikeshvartak · Accepted solution]

Make sure all columns exists in import file / from hr source which are used for display name population 

[Varshi_Balaji]

Hi All,

We added the columns in the import user json and it is working as expected. 

Thank you so much for the support.

Regards,

Varshitha