Preprocessor not generating the right displayname

[TheSaviyntBoy]

Hi,

We are trying to generate displayName according to the following rule: If preferedfirstName is present then displayName should be preferedfirstName.lastname. 

If the preferedfirstName is not present then the displayName should be firstname.lastname. 

The issue we are facing is that preferedfirstName.lastname is generating correctly but when it is blank, the displayname isn't generating firstname.lastname. 

For example: We have two users John(preferedfirstName = Jon) Doe and John Doe(Blank preferedfirstName)

the displaynames generating respectively are Jon.Doe and .Doe

It is not able to parse the firstname field in Saviynt. 

Our preprocessor query is: 

{

    "ADDITIONALTABLES": {

        "USERS": "SELECT * FROM USERS"

    },

    "COMPUTEDCOLUMNS": [

"CUSTOMPROPERTY1",

"PREFEREDFIRSTNAME",

"LASTNAME",

"FIRSTNAME",

"DISPLAYNAME"

    ],

    "PREPROCESSQUERIES": [

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.CUSTOMPROPERTY1 = CURRENTUSERS.CUSTOMPROPERTY1 SET NEWUSERDATA.DISPLAYNAME = (CASE WHEN (NEWUSERDATA.PREFEREDFIRSTNAME != '' OR NEWUSERDATA.PREFEREDFIRSTNAME IS NOT NULL) THEN (CONCAT(NEWUSERDATA.PREFEREDFIRSTNAME, '.', NEWUSERDATA.LASTNAME)) ELSE (CONCAT(NEWUSERDATA.DEPARTMENTNAME, '.', NEWUSERDATA.LASTNAME)) END);"

    ]

}

The logs show:

2024-04-29T16:03:56+05:30-ecm--null-hztxn--java.lang.NullPointerException: Cannot get property 'firstname' on null object at com.saviynt.ecm.services.SaviyntCommonUtilityService.getUserDisplayName(SaviyntCommonUtilityService.groovy:7058) at gsp_ECM_usersshow_gsp$_run_closure2_closure5.doCall(gsp_ECM_usersshow_gsp.groovy:445) at gsp_ECM_usersshow_gsp$_run_closure2.doCall(gsp_ECM_usersshow_gsp.groovy:841) at gsp_ECM_usersshow_gsp.run(gsp_ECM_usersshow_gsp.groovy:1141) at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53) at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:159) at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62) at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59) at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69) at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82) at java.lang.Thread.run(Thread.java:750) 

[Raghu]

@TheSaviyntBoy  try below

{
"ADDITIONALTABLES": {
"USERS": "SELECT CUSTOMPROPERTY1,PREFEREDFIRSTNAME,LASTNAME,FIRSTNAME,DISPLAYNAME FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY1",
"PREFEREDFIRSTNAME",
"LASTNAME",
"FIRSTNAME",
"DISPLAYNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.CUSTOMPROPERTY1 = CURRENTUSERS.CUSTOMPROPERTY1 SET NEWUSERDATA.DISPLAYNAME = (CASE WHEN (NEWUSERDATA.PREFEREDFIRSTNAME != '' OR NEWUSERDATA.PREFEREDFIRSTNAME IS NOT NULL) THEN (CONCAT(NEWUSERDATA.PREFEREDFIRSTNAME, '.', NEWUSERDATA.LASTNAME)) ELSE (CONCAT(NEWUSERDATA.DEPARTMENTNAME, '.', NEWUSERDATA.LASTNAME)) END);"
]
}

[TheSaviyntBoy]

@Raghu 

Yeah, I already tried and the result is the same, nothing is coming before the '.' in the ELSE statement. 

[rushikeshvartak]

{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY1",
"PREFEREDFIRSTNAME",
"LASTNAME",
"FIRSTNAME",
"DISPLAYNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.CUSTOMPROPERTY1 = CURRENTUSERS.CUSTOMPROPERTY1 SET NEWUSERDATA.DISPLAYNAME = (CASE WHEN (NEWUSERDATA.PREFEREDFIRSTNAME != '' AND NEWUSERDATA.PREFEREDFIRSTNAME IS NOT NULL) THEN CONCAT(NEWUSERDATA.PREFEREDFIRSTNAME, '.', NEWUSERDATA.LASTNAME) ELSE CONCAT(NEWUSERDATA.PREFEREDFIRSTNAME, '.', NEWUSERDATA.LASTNAME) END)"
]
}

[PremMahadikar · Accepted solution]

Hi @TheSaviyntBoy ,

• The line which is not going for ELSE concat is 'NEWUSERDATA.PREFEREDFIRSTNAME IS NOT NULL'

The below is working, please try. Make sure columns mentioned in the 'NEWUSERDATA' are also in your csv file.

{
"ADDITIONALTABLES": {
	"USERS": "SELECT * FROM USERS"
},
"COMPUTEDCOLUMNS": [
	"CUSTOMPROPERTY1",
"PREFEREDFIRSTNAME",
"LASTNAME",
"FIRSTNAME",
"DISPLAYNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU SET NU.displayname = CASE WHEN (NU.preferedfirstname!= '' or NU.preferedfirstname!= null) THEN CONCAT(NU.preferedfirstname,'.',NU.lastname) ELSE CONCAT(NU.firstname,'.',NU.lastname) END"
]
}

If you find the above response useful, Kindly Mark it as Accept As Solution and hit Kudos