Increment EmployeeID for bulk user upload

[Shubhamjain27]

Hi Everyone,

I was working on the csv user upload pre-procsssor to create employee id and map it to the employeeID attribute under user.

Format is - CO00001

Now, if we have empids filled till CO0003 in the user attributes and I have try to bulk upload multiple users through csv, it is assigned CO00004 to all the employees added through csv.

Pre-procssor used:

{
"ADDITIONALTABLES": {
"USERS": "SELECT userkey,employeeid,username from users",
"REQUEST_ACCESS_ATTRS": "select attribute_value,attribute_name from REQUEST_ACCESS_ATTRS where attribute_name = 'employeeid' and attribute_value like 'CO0%'"
},
"COMPUTEDCOLUMNS": [
"employeeid",
"attribute_value"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET employeeid=(select concat('CO',case when (length(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.employeeid, 3, length(u.employeeid)))+1)) = 1) then concat('0000', CONVERT(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.employeeid, 3, length(u.employeeid)))+1),CHAR)) when (length(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.employeeid, 3, length(u.employeeid)))+1)) = 2) then concat('000', CONVERT(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.employeeid, 3, length(u.employeeid)))+1),CHAR)) when (length(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.employeeid, 3, length(u.employeeid)))+1)) = 3) then concat('00', CONVERT(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.employeeid, 3, length(u.employeeid)))+1),CHAR)) when (length(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.employeeid, 3, length(u.employeeid)))+1)) = 4) then concat('0', CONVERT(greatest(max(substring(raa.attribute_value,3,length(raa.attribute_value)))+1 , MAX(substring(u.employeeid, 3, length(u.employeeid)))+1),CHAR)) END) AS ID from CURRENTREQUEST_ACCESS_ATTRS raa,CURRENTUSERS u where u.employeeid like 'CO0%' and length(u.employeeid) = 7)"
]
}

Any help would be appreciated

[PremMahadikar]

Hi @Shubhamjain27 ,

You are handling employeeid uniqueness for existing users using CURRENTUSERS u table only [ MAX(substring(u.employeeid, 3, length(u.employeeid)))+1) ]

Please do the same for NEWUSERDATA employeeid column.

While adding NEWUSERDATA inside select statement, remember to join the tables with any common value. ex: NEWUSERDATA.username=CURRENTUSERS u

This will help you to check the uniqueness with existing users and bulk users in your csv file.

(How it works: This would be uploaded in temp table in backend, and the updated employeeid per user based on the preprocessor will be stored back to temp table. Once all the records have updated employeeid, final temp table data is imported)

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