Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Username Generation Using Preprocessor

indra_hema_95
New Contributor II
New Contributor II

I am trying to generate user name through preprocessor, the condition is like firstname.lastname, now if next user with the same first name and last name comes it should pick firstname.lastname1. Now I have given the iterations in the preprocessor. But if I upload 2 users with the same first name and last name through csv then first one is getting imported then if I upload the sheet again then only it is taking the next one, otherwise not. How to fix it? 

Regards,

Indra

2 REPLIES 2

PremMahadikar
Valued Contributor
Valued Contributor

Hi @indra_hema_95 ,

Solution would be to also check the temp table (NEWUSERDATA) for uniqueness.

Below is the sample JSON: (Please use it as reference)

{ 
 "ADDITIONALTABLES": {
"USERS" : "SELECT userkey,systemusername,username,firstname,lastname FROM USERS"
},
"COMPUTEDCOLUMNS" :[ 
"username",
],
"PREPROCESSQUERIES" : [
"UPDATE NEWUSERDATA set NEWUSERDATA.username=case 

when (CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname) in (select u.username from currentusers u)=0) AND (CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname) in (select CONCAT(n.firstname,'.',n.lastname) from NEWUSERDATA n group by CONCAT(n.firstname,'.',n.lastname) having count(*)>1)=1) then CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname)

when (CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,'1') in (select u.username from currentusers u)=0) AND (CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,'1') in (select CONCAT(n.firstname,'.',n.lastname,'1') from NEWUSERDATA n group by CONCAT(n.firstname,'.',n.lastname,'1') having count(*)>1)=0) then CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname)

when (CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,'2') in (select u.username from currentusers u)=0) AND (CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,'2') in (select CONCAT(n.firstname,'.',n.lastname,'2') from NEWUSERDATA n group by CONCAT(n.firstname,'.',n.lastname,'2') having count(*)>1)=0) then CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname)

ELSE CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,'3') END"
]
}

 

If this helps your question, please consider select Accept As Solution and hit Kudos

Hi @PremMahadikar getting below error.

 

2024-05-30T02:32:16+05:30-ecm-services.ImportSAvDataUserService-http-nio-8080-exec-147-j6sq2-ERROR-Error while processing data:
 
2024-05-30T02:32:16+05:30-ecm--null-j6sq2--java.sql.SQLException: You can't specify target table 'TEMPNEWUSERS_59245' for update in FROM clause at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335) at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2108) at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245) at com.saviynt.ecm.services.ImportSAvDataUserService$_doImportDataPreprocessing_closure10.doCall(ImportSAvDataUserService.groovy:416) at com.saviynt.ecm.services.ImportSAvDataUserService.doImportDataPreprocessing(ImportSAvDataUserService.groovy:405) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:733) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:686) at com.saviynt.ecm.services.UsersService.uploadUserFinalStep(UsersService.groovy:8806) at com.saviynt.ecm.identitywarehouse.controllers.UsersController$_closure26.doCall(UsersController.groovy:1720) at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53) at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:158) 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)
 
Regards,
Indra