Looking to generate Displayname post workday import as preferredFirstName + " " + preferredLastName

[mbh_it]

Hi All,

I am Looking to generate Displayname post workday import as preferredFirstName + " " + preferredLastName+number

so its basically : FN+" "+LN+index unique value as indext say, 

Lalit Patel1

Lalit Patel2

Lalit Patel3

Kindly let me know how to solve this , is there a way with ModifyUserData option at connector if yes, how?

Thank You,

Mahesh

[Saathvik]

@mbh_it : Yes it is possible to use inline processor and achieve this.

Use below JSON for MODIFYUSERDATAJSON 

{
    "ADDITIONALTABLES": {
        "USERS": "SELECT username,DISPLAYNAME,PREFEREDFIRSTNAME,LASTNAME from USERS "
    },
    "COMPUTEDCOLUMNS": [
        "DISPLAYNAME",
        "PREFEREDFIRSTNAME",
        "LASTNAME"
    ],
    "PREPROCESSQUERIES": [
        "UPDATE NEWUSERDATA SET NEWUSERDATA.DISPLAYNAME = CONCAT(NEWUSERDATA.PREFEREDFIRSTNAME,' ',NEWUSERDATA.LASTNAME,FN_EIC_SEQGEN('identifier'))"
    ]
}

 Also FN_EIC_SEQGEN is the default function to autoincrement the number which refer to data set SEQUENCE_GEN_MAPPING where you can define the auto increment value

So please replace FN_EIC_SEQGEN('identifier') with respective identifier name you defined in SEQUENCE_GEN_MAPPING dataset

For details refer Database Functions 

[mbh_it]

Hi Saathvik,

I added following and used your preprocessor as below:

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,DISPLAYNAME,PREFEREDFIRSTNAME,LASTNAME from USERS "
},
"COMPUTEDCOLUMNS": [
"DISPLAYNAME",
"PREFEREDFIRSTNAME",
"LASTNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET NEWUSERDATA.DISPLAYNAME = CONCAT(NEWUSERDATA.PREFEREDFIRSTNAME,' ',NEWUSERDATA.LASTNAME,FN_EIC_SEQGEN('ID'))"
]
}

Excel data has firstName & lastName along with other mandatory attributes, this is not working yet, I wonder where I am missing. Referred Database link, however not clear where I missed.

Checked logs as well, looks like product missing a Table Or schema itself.

services.ImportSAvDataUserService - Processing query.."
"2023-10-12T22:26:30.866+00:00","ecm","","","","2023-10-12T22:26:30.091708097Z stderr F 12-Oct-2023 22:26:30.091 WARNING [http-nio-8080-exec-17] groovy.sql.Sql.executeUpdate Failed to execute: UPDATE TEMPNEWUSERS_6080 SET TEMPNEWUSERS_6080.DISPLAYNAME = CONCAT(TEMPNEWUSERS_6080.PREFEREDFIRSTNAME,' ',TEMPNEWUSERS_6080.LASTNAME,FN_EIC_SEQGEN('ID')) because: Table 'ssminlp.DATASET_VALUES' doesn't exist"
"2023-10-12T22:26:30.866+00:00","ecm","","","","2023-10-12T22:26:30.092303697Z stdout F 2023-10-12 22:26:30,092 [http-nio-8080-exec-17] ERROR services.ImportSAvDataUserService - Error while processing data: "
"2023-10-12T22:26:30.866+00:00","ecm","","","","2023-10-12T22:26:30.092312397Z stdout F com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'ssminlp.DATASET_VALUES' doesn't exist"
"2023-10-12T22:26:30.866+00:00","ecm","","","","2023-10-12T22:26:30.092315797Z stdout F at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)"
"2023-10-12T22:26:30.866+00:00","ecm","","","","2023-10-12T22:26:30.092331197Z stdout F at com.mysql.jdbc.Util.getInstance(Util.java:383)"
"2023-10-12T22:26:30.866+00:00","ecm","","","","2023-10-12T22:26:30.092335097Z stdout F at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)"
"2023-10-12T22:26:30.866+00:00","ecm","","","","2023-10-12T22:26:30.092338097Z stdout F at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4208)"
"2023-10-12T22:26:30.866+00:00","ecm","","","","2023-10-12T22:26:30.092341397Z stdout F at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)"
"2023-10-12T22:26:30.866+00:00","ecm","","","","2023-10-12T22:26:30.092344597Z stdout F at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)"
"2023-10-12T22:26:30.866+00:00","ecm","","","","2023-10-12T22:26:30.092347697Z stdout F at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)"

Logs attached

It would be great if you could provide an input.

Thanks

Mahesh

[Saathvik]

@mbh_it : First remove entries for 2 and 3 in dataset. Entry with values as 1 is enough, Basically what it means is it increments the value by 1. And for the error you are seeing can you include below line in Additional Tables and see if that works?

"DATASET_VALUES": "SELECT ATTRIBUTE1,ATTRIBUTE2 FROM DATASET_VALUES WHERE DATASETNAME = 'SEQUENCE_GEN_MAPPING'"

After adding you block should look like below

"ADDITIONALTABLES": {
"USERS": "SELECT username,DISPLAYNAME,PREFEREDFIRSTNAME,LASTNAME from USERS ",

"DATASET_VALUES": "SELECT ATTRIBUTE1,ATTRIBUTE2 FROM DATASET_VALUES WHERE DATASETNAME = 'SEQUENCE_GEN_MAPPING'"
}

[mbh_it]

Hi Saathvik,

Thanks for your response and guidance so far. I am still getting same error, I tried preprocessor as below as suggested by you.

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,DISPLAYNAME,PREFEREDFIRSTNAME,LASTNAME from USERS ",
"DATASET_VALUES": "SELECT ATTRIBUTE1,ATTRIBUTE2 FROM DATASET_VALUES WHERE DATASETNAME = 'SEQUENCE_GEN_MAPPING'"
},
"COMPUTEDCOLUMNS": [
"DISPLAYNAME",
"PREFEREDFIRSTNAME",
"LASTNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET NEWUSERDATA.DISPLAYNAME = CONCAT(NEWUSERDATA.PREFEREDFIRSTNAME,' ',NEWUSERDATA.LASTNAME,FN_EIC_SEQGEN('ID'))"
]
}

Attached logs

services.ImportSAvDataUserService - Storing data in table TEMPNEWUSERS_6158"
"2023-10-13T14:41:05.968+00:00","ecm","","","","2023-10-13T14:41:05.902077781Z stdout F 2023-10-13 14:41:05,902 [http-nio-8080-exec-11] DEBUG services.ImportSAvDataUserService - Start adding indexes for temp tables"
"2023-10-13T14:41:05.968+00:00","ecm","","","","2023-10-13T14:41:05.902101081Z stdout F 2023-10-13 14:41:05,902 [http-nio-8080-exec-11] DEBUG services.ImportSAvDataUserService - End adding indexes for temp tables"
"2023-10-13T14:41:05.968+00:00","ecm","","","","2023-10-13T14:41:05.902104481Z stdout F 2023-10-13 14:41:05,902 [http-nio-8080-exec-11] DEBUG services.ImportSAvDataUserService - Executing the pre-processing queries"
"2023-10-13T14:41:05.968+00:00","ecm","","","","2023-10-13T14:41:05.902141181Z stdout F 2023-10-13 14:41:05,902 [http-nio-8080-exec-11] DEBUG services.ImportSAvDataUserService - Processing query.."
"2023-10-13T14:41:05.968+00:00","ecm","","","","2023-10-13T14:41:05.910069277Z stderr F 13-Oct-2023 14:41:05.909 WARNING [http-nio-8080-exec-11] groovy.sql.Sql.executeUpdate Failed to execute: UPDATE TEMPNEWUSERS_6158 SET TEMPNEWUSERS_6158.DISPLAYNAME = CONCAT(TEMPNEWUSERS_6158.PREFEREDFIRSTNAME,' ',TEMPNEWUSERS_6158.LASTNAME,FN_EIC_SEQGEN('ID')) because: Table 'ssminlp.DATASET_VALUES' doesn't exist"
"2023-10-13T14:41:05.968+00:00","ecm","","","","2023-10-13T14:41:05.910807177Z stdout F 2023-10-13 14:41:05,910 [http-nio-8080-exec-11] ERROR services.ImportSAvDataUserService - Error while processing data: "
"2023-10-13T14:41:05.968+00:00","ecm","","","","2023-10-13T14:41:05.910817377Z stdout F com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'ssminlp.DATASET_VALUES' doesn't exist"
"2023-10-13T14:41:05.968+00:00","ecm","","","","2023-10-13T14:41:05.910820377Z stdout F at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)"
"2023-10-13T14:41:05.968+00:00","ecm","","","","2023-10-13T14:41:05.910824177Z stdout F at com.mysql.jdbc.Util.getInstance(Util.java:383)"
"2023-10-13T14:41:05.968+00:00","ecm","","","","2023-10-13T14:41:05.910826277Z stdout F at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)"
"2023-10-13T14:41:05.968+00:00","ecm","","","","2023-10-13T14:41:05.910828077Z stdout F at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4208)"
"2023-10-13T14:41:05.968+00:00","ecm","","","","2023-10-13T14:41:05.910829677Z stdout F at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)"
"2023-10-13T14:41:05.968+00:00","ecm","","","","2023-10-13T14:41:05.910831377Z stdout F at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)"
"2023-10-13T14:41:05.968+00:00","ecm","","","","2023-10-13T14:41:05.910833177Z stdout F at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)"

[Saathvik]

@mbh_it : Looks like default DB functions doesn't work. Realized the same upon looking into documentation again. Apologies for misguiding.

[mbh_it]

Hello Saathvik,

That is Ok anyway you are trying to help me solve a problem. so what is next for solving this? Should I raise FD ticket?

Thanks

Mahesh

[mbh_it]

Hello Saathvik/ Team,

Is there a way I can achieve this, kindly suggest.

Thank You,

Mahesh

[rushikeshvartak]

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,DISPLAYNAME,PREFEREDFIRSTNAME,LASTNAME from USERS "
},
"COMPUTEDCOLUMNS": [
"DISPLAYNAME",
"PREFEREDFIRSTNAME",
"LASTNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET NEWUSERDATA.DISPLAYNAME = CONCAT(NEWUSERDATA.PREFEREDFIRSTNAME,' ',NEWUSERDATA.LASTNAME,Max(NEWUSERDATA.customproperty1)+1)"
]
}

Save any users CP1 = 1