Error in Users Import - Error while processing data: Table 'ssminlp.customer' doesn't exist

[Roua]

I'm working department name in Saviynt, The goal is to correctly populate the departmentname and displayname fields during the user import process.

Implemented Logic:
departmentname is mapped to d:Short~#~char.
orgunitid and departmentnumber are mapped to d:Orgunitid~#~char.
Display Name Construction:
The MODIFYUSERDATAJSON has logic to construct the displayname using attributes like lastname, firstname, and departmentname.
It also appends "(EXT)" for external employees, ensuring proper differentiation in display names.

Missing or Incomplete Logic:

Secondary Condition for users.departmentname:
The current logic only populates users.departmentname from d:Short~#~char.
what we need is if d:Short is empty, the logic to fetch organizations.displayname based on HCMSYSTEM-ORGUNITID.
and If users.departmentname is set before users.displayname, there should be a conditional check to ensure that users.departmentname is used in constructing the displayname. The logic should prevent re-evaluating based on previous conditions, ensuring the precedence of users.departmentname.
Problem Encountered: i am using the customer table since it represent the organizations, I am encountering the following error during the user import process:
Error in Users Import - Error while processing data: Table 'ssminlp.customer' doesn't exist
This error persists even after several attempts to correct the configuration.
i am implementing the logic in SAV4SAV connection but i also use the customer table in users as well and i also get the same error when i run the import job
SAV4SAV MODIFYUSERDATAJSON: 

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT customproperty5, customproperty6, customproperty48, USERNAME, departmentname, Orgunitid, lastname, firstname, employeeType FROM users",
    "CUSTOMER": "SELECT DISPLAYNAME, CUSTOMERCODE FROM customer"
  },
  "COMPUTEDCOLUMNS": [
    "customproperty6",
    "departmentname",
    "displayname"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA NU JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME SET NU.customproperty6 = CU.customproperty48 WHERE CU.customproperty5 = 'P'",
    "UPDATE NEWUSERDATA NU JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME SET NU.customproperty6 = NULL WHERE CU.customproperty5 <> 'P'",
    "UPDATE NEWUSERDATA NU JOIN CUSTOMER C ON NU.Orgunitid = C.CUSTOMERCODE SET NU.departmentname = C.DISPLAYNAME WHERE NU.departmentname IS NULL OR NU.departmentname = ''",
    "UPDATE NEWUSERDATA NU SET NU.displayname = CONCAT(UPPER(NU.lastname), ' ', NU.firstname, ' (', NU.departmentname, IF(NU.employeeType = 'E', ' (EXT)', ''), ')') WHERE NU.departmentname IS NOT NULL"
  ]
}

Any insights or suggestions on how to resolve this issue would be greatly appreciated.

i will link the forums that i already tried their solution but didn't work
https://forums.saviynt.com/t5/identity-governance/preprocessor-table-ssminlp-currentusers-doesn-t-ex...
https://forums.saviynt.com/t5/identity-governance/query-another-tables-on-user-import-preprocessor/m...
https://forums.saviynt.com/t5/application-access-governance/error-in-users-import-error-while-proces...
https://forums.saviynt.com/t5/identity-governance/preprocessor-error-quot-table-ssminlp-currentusers...

[Amit_Malik]

@Roua ,

tables in additional tables must be prefixed by current.

users --> currentusers

for customer try currentcustomer

[Roua]

@Amit_Malik i tried your solution and still got the same error 

Failure msg

Error in Users Import - Error while processing data: Table 'ssminlp.currentcustomer' doesn't exist

here is the new one :

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT customproperty5, customproperty6, USERNAME, departmentname, Orgunitid, lastname, firstname, employeeType FROM users",
    "CURRENTCUSTOMER": "SELECT DISPLAYNAME, CUSTOMERCODE FROM customer",
 "ACCOUNTS": "SELECT NAME, customproperty49 FROM accounts"
  },
  "COMPUTEDCOLUMNS": [
    "customproperty6",
    "departmentname",
    "displayname"
  ],
  "PREPROCESSQUERIES": [
     "UPDATE NEWUSERDATA NU JOIN CURRENTACCOUNTS CA ON NU.USERNAME = CA.NAME SET NU.customproperty6 = CA.customproperty49 WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 = 'P')",
    "UPDATE NEWUSERDATA NU SET NU.customproperty6 = NULL WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 <> 'P')",
     "UPDATE NEWUSERDATA NU JOIN CURRENTCUSTOMER C ON NU.Orgunitid = C.CUSTOMERCODE SET NU.departmentname = C.DISPLAYNAME WHERE NU.departmentname IS NULL OR NU.departmentname = ''",
    "UPDATE NEWUSERDATA NU SET NU.displayname = CONCAT(UPPER(NU.lastname), ' ', NU.firstname, ' (', NU.departmentname, IF(NU.employeeType = 'E', ' (EXT)', ''), ')') WHERE NU.departmentname IS NOT NULL"
  ]
  
}

@ 

[Amit_Malik]

In additional table section it is customer but while using it in queries , it is currentcustomer

{
"ADDITIONALTABLES": {
"USERS": "SELECT customproperty5, customproperty6, USERNAME, departmentname, Orgunitid, lastname, firstname, employeeType FROM users",
"CUSTOMER": "SELECT DISPLAYNAME, CUSTOMERCODE FROM customer",
"ACCOUNTS": "SELECT NAME, customproperty49 FROM accounts"
},
"COMPUTEDCOLUMNS": [
"customproperty6",
"departmentname",
"displayname"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU JOIN CURRENTACCOUNTS CA ON NU.USERNAME = CA.NAME SET NU.customproperty6 = CA.customproperty49 WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 = 'P')",
"UPDATE NEWUSERDATA NU SET NU.customproperty6 = NULL WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 <> 'P')",
"UPDATE NEWUSERDATA NU JOIN CURRENTCUSTOMER C ON NU.Orgunitid = C.CUSTOMERCODE SET NU.departmentname = C.DISPLAYNAME WHERE NU.departmentname IS NULL OR NU.departmentname = ''",
"UPDATE NEWUSERDATA NU SET NU.displayname = CONCAT(UPPER(NU.lastname), ' ', NU.firstname, ' (', NU.departmentname, IF(NU.employeeType = 'E', ' (EXT)', ''), ')') WHERE NU.departmentname IS NOT NULL"
]

}

https://docs.saviyntcloud.com/bundle/EIC-Admin-v24x/page/Content/Chapter03-User-Management/User-Impo...

Read this section - 

Setting an organization value using inline preprocessor during user import:

Resolution:

To provide user the permission to import users in a specific organization, configure the following parameters.

• Go to Admin > SAV Role

• Open a SAV role assigned to a user
  This can be a logged in user who is uploading user CSV or an admin user in case of user import job execution.

• On the SAV Role Details page, select All or a specific organizations under Show Users in Request Access based on Organizations that is managed by the user.

Note: After the user has a SAV role with organization configured, you can set that customer or organization value using inline processor. For a sample JSON, refer to the JSON provided in the problem statement.

[Amit_Malik]

if it still throws error. You need to raise a Saviynt Freshdesk ticket.

[Roua]

i did as you said i stil get the error of the CUSTOMER TABLE
but also now i am getting the following error: 

Failure msg

Error in Users Import - Error while processing data: Unknown column 'NU.Orgunitid' in 'on clause'

i tried to use DEPARTMENTNUMBER as well it doesnt work and i tried many other ways to do it but i don't get why exactly it doesn't work 

Orgunitid is an official attribute in the users table.

[rushikeshvartak]

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT customproperty5, customproperty6, USERNAME, departmentname, Orgunitid, lastname, firstname, employeeType FROM users",
    "CUSTOMER": "SELECT DISPLAYNAME, CUSTOMERCODE FROM customer",
    "ACCOUNTS": "SELECT NAME, customproperty49 FROM accounts"
  },
  "COMPUTEDCOLUMNS": [
    "customproperty6",
    "departmentname",
    "displayname",
    "Orgunitid"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA NU JOIN CURRENTACCOUNTS CA ON NU.USERNAME = CA.NAME SET NU.customproperty6 = CA.customproperty49 WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 = 'P')",
    "UPDATE NEWUSERDATA NU SET NU.customproperty6 = NULL WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 <> 'P')",
    "UPDATE NEWUSERDATA NU JOIN CURRENTCUSTOMER C ON NU.Orgunitid = C.CUSTOMERCODE SET NU.departmentname = C.DISPLAYNAME WHERE NU.departmentname IS NULL OR NU.departmentname = ''",
    "UPDATE NEWUSERDATA NU SET NU.displayname = CONCAT(UPPER(NU.lastname), ' ', NU.firstname, ' (', NU.departmentname, IF(NU.employeeType = 'E', ' (EXT)', ''), ')') WHERE NU.departmentname IS NOT NULL"
  ]
}

[Roua]

@rushikeshvartak 
Thank you 

i figured out that whn i dont basically put all of them in COMPUTEDCOLUMNS it will give this error:

Failure msg

Error in Users Import - Error while processing data: Unknown column 'NU.lastname' in 'field list'

but when i do and run the job it is making them empty and i am not sure how to address this ..

i tried to also include them in the ImportUserJSON it also made them empty 
NOTE: SAV4SAV connector 

and for example when i am doint these two requirements: 

"UPDATE NEWUSERDATA NU JOIN CURRENTACCOUNTS CA ON NU.USERNAME = CA.NAME SET NU.customproperty6 = CA.customproperty49 WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 = 'P')",

"UPDATE NEWUSERDATA NU SET NU.customproperty6 = NULL WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 <> 'P')",

in "COMPUTEDCOLUMNS" i have only "customproperty6"
and it worked 

do you know how to address the issue ? 

[Amit_Malik]

send your import user JSON. All the fields that you are using as NU. MUST be in you import user JSON.

The fields that you want to calculate sud be in computed column. Dont put any field in computed column if you are not calculationg it , else it will make those null. Instead have those in import user JSON .

[rushikeshvartak]

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT customproperty5, customproperty6, USERNAME, departmentname, Orgunitid, lastname, firstname, employeeType FROM users",
    "CUSTOMER": "SELECT DISPLAYNAME, CUSTOMERCODE FROM customer",
    "ACCOUNTS": "SELECT NAME, customproperty49 FROM accounts"
  },
  "COMPUTEDCOLUMNS": [
    "customproperty6",
    "departmentname",
    "displayname",
    "Orgunitid",
    "lastname",
    "firstname",
    "employeeType"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA NU JOIN CURRENTACCOUNTS CA ON NU.USERNAME = CA.NAME SET NU.customproperty6 = CA.customproperty49 WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 = 'P')",
    "UPDATE NEWUSERDATA NU SET NU.customproperty6 = NULL WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 <> 'P')",
    "UPDATE NEWUSERDATA NU JOIN CUSTOMER C ON NU.Orgunitid = C.CUSTOMERCODE SET NU.departmentname = C.DISPLAYNAME WHERE NU.departmentname IS NULL OR NU.departmentname = ''",
    "UPDATE NEWUSERDATA NU SET NU.displayname = CONCAT(UPPER(NU.lastname), ' ', NU.firstname, ' (', NU.departmentname, IF(NU.employeeType = 'E', ' (EXT)', ''), ')') WHERE NU.departmentname IS NOT NULL"
  ]
}

[Roua]

hi @rushikeshvartak 
thank you but i already tried this and it made them empty

[Amit_Malik]

Okay so your old issues are resolved at least.

Error while processing data: Unknown column 'NU.Orgunitid' in 'on clause'

This generally means , in your import user JSON or csv file , you dont have Orgunitid attribute mapped

[Roua]

@Amit_Malik 
thank you for your answer but even when i added them to import json it was given a null value "made them empty"
i did it like this: 

{ 

  "connection": "acctAuth", 

  "successResponses": { 

    "statusCode": [ 

      200, 

      201, 

      202, 

      203, 

      204, 

      205 

    ] 

  }, 

"url": "https://xxxxxxx.saviyntcloud.com/ECM/api/v5/user?q=username:AT0056z73&fields=username,email,displayname",


  "httpMethod": "GET", 

  "httpHeaders": { 

    "Authorization": "${access_token}", 

    "Accept": "application/json" 

  }, 

  "httpContentType": "application/json", 

  "userResponsePath": "results", 

  "colsToPropsMap":{ 
"username": "username~#~char", 
"customproperty6": "customproperty6~#~char",
"displayname":"displayname~#~char",
"lastname":"lastname~#~char",
"firstname":"firstname~#~char",
"employeeType":"employeeType~#~char",
"CUSTOMER":"CUSTOMER~#~char",
"departmentname":"departmentname~#~char"

} ,
"pagination": {
    "offset": {
      "offsetParam": "offset",
      "batchParam": "max",
      "batchSize": 10000,
      "totalCountPath": "completeResponseMap.total"
    }
  }

}

[Amit_Malik]

@Roua , add attributes in URL also

url": "https://xxxxxxx.saviyntcloud.com/ECM/api/v5/user?q=username:AT0056z73&fields=username,email,displayname",

[Roua]

.

[Roua]

@Amit_Malik  thank you so much for answering!
so basically  i am trying what you said, i am just confused how it is working for cp6 and the first two conditions without assigning them or anything, but anyways i tried this solution and it kept giving me this error: 

Error while getting User Import Response WebService call failed with responseStatusCode null	Failed url-https://xxxxxxxxxx.saviyntcloud.com/ECM/api/v5/user?q=username:AT0003546&fields=username,email,displayname, lastname, firstname,employeeType,CUSTOMER,departmentname with Error Message-null
Error In Getting Response In pullObjectsByRest	NullResponseFromTarget

so to debug it i tried many things then i tried to include only the attributes that are showing in the postman results
when i used the following url in postman : 
https:/xxxxxxxx.saviyntcloud.com/ECM/api/v5/user?q=username:AT0003546&fields=username,email,displayname , lastname, firstname,employeeType,CUSTOMER,departmentname
results: 
{
"msg": "Successful",
"count": 1,
"errorCode": "0",
"results": [
{
"employeeType": "I",
"displayname": "xxxxxx ()",
"userKey": 26063,
"username": "AT0003546"
}
],
"order": "ASC"

2) https://xxxxxxxxxxx.saviyntcloud.com/ECM/api/v5/user?q=username:AT0003546&fields=username,customer 
{
"msg": "Successful",
"count": 1,
"errorCode": "0",
"results": [
{
"userKey": 26063,
"username": "AT0003546"
}
],
"order": "ASC"
}

so i run the job only with the following url to try: 
https://xxxxxxxxxxxxsaviyntcloud.com/ECM/api/v5/user?q=username:AT00033391&fields=username,displayname,employeeType 
i got the following error that that the temporary table is trying to create or access during the user import process doesn't exist or basically failing to be created: 

Failure msg

Error in Users Import - Error while processing data: Table 'ssminlp.tempcustomer_45189_users' doesn't exist

logs: 

Spoiler

2024-09-06T10:11:06+02:00-ecm-worker--null-frbjs--06-Sep-2024 08:11:05.416 WARNING [quartzScheduler_Worker-3] groovy.sql.Sql.executeUpdate Failed to execute: UPDATE TEMPNEWUSERS_45197 NU JOIN TEMPCUSTOMER_45197_USERS CUU ON NU.USERKEY = CUU.USERKEY JOIN TEMPCUSTOMER_45197 C ON CUU.CUSTOMERKEY = C.CUSTOMERKEY SET NU.departmentname = C.DISPLAYNAME WHERE NU.departmentname IS NULL OR NU.departmentname = '' because: Table 'ssminlp.tempcustomer_45197_users' doesn't exist

2024-09-06T10:11:06+02:00-ecm-worker--null-frbjs--java.sql.SQLSyntaxErrorException: Table 'ssminlp.tempcustomer_45197_users' doesn't exist at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) 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.provisoning.rest.RestProvisioningService.processUsers(RestProvisioningService.groovy:3127) at com.saviynt.provisoning.rest.RestProvisioningService.importUsers(RestProvisioningService.groovy:2979) at com.saviynt.ecm.integration.ExternalConnectionCallService.importUserUsingExternalConnection(ExternalConnectionCallService.groovy:1251) at UserImportJob.execute(UserImportJob.groovy:108) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)

current import json : 

{ 

  "connection": "acctAuth", 

  "successResponses": { 

    "statusCode": [ 

      200, 

      201, 

      202, 

      203, 

      204, 

      205 

    ] 

  }, 



 "url": "https://xxxxxxx.saviyntcloud.com/ECM/api/v5/user?q=username:AT000345231&fields=username,displayname,employeeType",
  "httpMethod": "GET", 

  "httpHeaders": { 

    "Authorization": "${access_token}", 

    "Accept": "application/json" 

  }, 

  "httpContentType": "application/json", 

  "userResponsePath": "results", 

  "colsToPropsMap":{ 
"username": "username~#~char", 
    "displayname": "displayname~#~char",
    "employeeType": "employeeType~#~char"
}

}

current modify json: 

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT customproperty5, customproperty6, USERKEY, USERNAME, departmentname, lastname, firstname, employeeType FROM users",
    "CUSTOMER": "SELECT CUSTOMERKEY, DISPLAYNAME FROM customer",
    "CUSTOMER_USERS": "SELECT USERKEY, CUSTOMERKEY FROM customer_users",
    "ACCOUNTS": "SELECT NAME, customproperty49 FROM accounts"
  },
  "COMPUTEDCOLUMNS": [
    "customproperty6",
    "departmentname",
    "displayname"
  ],
  "PREPROCESSQUERIES": [
    
    "UPDATE NEWUSERDATA NU JOIN CURRENTACCOUNTS CA ON NU.USERNAME = CA.NAME SET NU.customproperty6 = CA.customproperty49 WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 = 'P')",
    "UPDATE NEWUSERDATA NU SET NU.customproperty6 = NULL WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 <> 'P')",
    
     "UPDATE NEWUSERDATA NU JOIN CURRENTCUSTOMER_USERS CUU ON NU.USERKEY = CUU.USERKEY JOIN CURRENTCUSTOMER C ON CUU.CUSTOMERKEY = C.CUSTOMERKEY SET NU.departmentname = C.DISPLAYNAME WHERE NU.departmentname IS NULL OR NU.departmentname = ''",
    
    "UPDATE NEWUSERDATA NU SET NU.displayname = CONCAT(UPPER(NU.lastname), ' ', NU.firstname, ' (', NU.departmentname, IF(NU.employeeType = 'E', ' (EXT)', ''), ')') WHERE NU.departmentname IS NOT NULL"
  
  ]
}

the logic of the last two queries: 
if a user doesn't have a departmentname already set, the system tries to fill in that information using the corresponding organization's displayname. "customer"
After the departmentname is either populated (from the previous step) or already exists, it uses this departmentname to build the user's displayname.
The displayname is constructed by combining the user's lastname, firstname, and the departmentname. If the user is an external employee (employeeType = 'E'), it appends "(EXT)" to the display name.

[This message has been edited by moderator to disable URL hyperlink]

[Amit_Malik]

"url": "https://xxxxxxx.saviyntcloud.com/ECM/api/v5/user?q=username:AT0056z73&fields=username,email,displayn...",

user?q=username:AT0056z73&fields=username,email,displayname,departmentname,employeeType,firstname,lastname,userkey"

"colsToPropsMap":{
"username": "username~#~char",
"customproperty6": "customproperty6~#~char",
"displayname":"displayname~#~char",
"lastname":"lastname~#~char",
"firstname":"firstname~#~char",
"employeeType":"employeeType~#~char",
"departmentname":"departmentname~#~char",

"userkey":"userkey~#~char"

Modifydatajson

{
"ADDITIONALTABLES": {
"USERS": "SELECT customproperty5, customproperty6, USERKEY, USERNAME, departmentname, lastname, firstname, employeeType FROM users",
"CUSTOMER": "SELECT CUSTOMERKEY, DISPLAYNAME FROM customer",
"CUST_USERS": "SELECT USERKEY, CUSTOMERKEY FROM customer_users",
"ACCOUNTS": "SELECT NAME, customproperty49 FROM accounts"
},
"COMPUTEDCOLUMNS": [
"customproperty6",
"departmentname",
"displayname"
],
"PREPROCESSQUERIES": [

"UPDATE NEWUSERDATA NU JOIN CURRENTACCOUNTS CA ON NU.USERNAME = CA.NAME SET NU.customproperty6 = CA.customproperty49 WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 = 'P')",
"UPDATE NEWUSERDATA NU SET NU.customproperty6 = NULL WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 <> 'P')",

"UPDATE NEWUSERDATA NU JOIN CURRENTCUST_USERS CUU ON NU.USERKEY = CUU.USERKEY JOIN CURRENTCUSTOMER C ON CUU.CUSTOMERKEY = C.CUSTOMERKEY SET NU.departmentname = C.DISPLAYNAME WHERE NU.departmentname IS NULL OR NU.departmentname = ''",

"UPDATE NEWUSERDATA NU SET NU.displayname = CONCAT(UPPER(NU.lastname), ' ', NU.firstname, ' (', NU.departmentname, IF(NU.employeeType = 'E', ' (EXT)', ''), ')') WHERE NU.departmentname IS NOT NULL"

]
}

[Roua]

Hello @Amit_Malik, Thank you for your time and help 
the tables now are correct there is no error, the job status is Success  but the logic for : 

"UPDATE NEWUSERDATA NU JOIN CURRENTCUST_USERS CUU ON NU.USERKEY = CUU.USERKEY JOIN CURRENTCUSTOMER C ON CUU.CUSTOMERKEY = C.CUSTOMERKEY SET NU.departmentname = C.DISPLAYNAME WHERE NU.departmentname IS NULL OR NU.departmentname = ''",

is not working. i tried to debug an use many logic but didn't work. 
last thing i tried is :

"UPDATE NEWUSERDATA NU JOIN CURRENTCUST_USERS CUU ON NU.USERKEY = CUU.USERKEY JOIN CURRENTCUSTOMER C ON CUU.CUSTOMERKEY = C.CUSTOMERKEY SET NU.departmentname = C.DISPLAYNAME WHERE NU.departmentname IS NULL OR NU.departmentname = ''",
"UPDATE NEWUSERDATA NU SET NU.departmentname = CONCAT('DEBUG-', NU.departmentname) WHERE NU.departmentname IS NOT NULL"

and i got the following logs, the user is not updated also the departmentname is not being populated from the customer table.

Do you have an idea ? i am still trying some other ways

i also tried to get the values directly it didn't work: 

"UPDATE NEWUSERDATA NU LEFT JOIN CURRENTCUST_USERS CUU ON NU.USERKEY = CUU.USERKEY LEFT JOIN CURRENTCUSTOMER C ON CUU.CUSTOMERKEY = C.CUSTOMERKEY SET NU.displayname = CONCAT(UPPER(NU.lastname), ' ', NU.firstname, ' (', CASE WHEN NU.departmentname IS NOT NULL AND NU.departmentname <> '' THEN NU.departmentname ELSE C.DISPLAYNAME END, IF(NU.employeeType = 'E', ' (EXT)', 'TEST'), ')')"

[Roua]

i also tried the following in data analyzer to make sure my table joining is working: 
SELECT users.USERKEY, users.USERNAME, customer.DISPLAYNAME
FROM users
LEFT JOIN customer_users ON users.USERKEY = customer_users.USERKEY
LEFT JOIN customer ON customer_users.CUSTOMERKEY = customer.CUSTOMERKEY
WHERE users.USERNAME = 'AT00xxxx';
and yes it is working i got the right result: 

USERKEY USERNAME DISPLAYNAME

4xxxx

AT00xxxx

AxxxxxxE "customer displayname"

[Amit_Malik]

In below Query can you try hardcoding username 

"UPDATE NEWUSERDATA NU JOIN CURRENTCUST_USERS CUU ON NU.USERKEY = CUU.USERKEY JOIN CURRENTCUSTOMER C ON CUU.CUSTOMERKEY = C.CUSTOMERKEY SET NU.departmentname = C.DISPLAYNAME WHERE NU.USERNAME ='ABC',

Also, logs are not complete , could you send it all. As it seems , you are only running it for one user, wont be a problem to remove sensitive information 

Add two more computed fields 

"COMPUTEDCOLUMNS": [
"customproperty6",
"departmentname",
"displayname",

"customproperty30",

"customproperty31",
],

Use two more queries in preprocessor

"UPDATE NEWUSERDATA NU  SET NU.customproperty30 in (select count(*) from CURRENTCUST_USERS CU where cu.userkey= <your user userkey> ),

"UPDATE NEWUSERDATA NU  SET NU.customproperty31 in (select count(*) from CURRENTCUSTOMER CC where cc.CUSTOMERKEY= <your customer key> ),

[Roua]

@Amit_Malik  Thank you for helping out! 
i did add what you said 
results as the following 
cp33 and cp34 "i used them because 30 and 31 were not empty"

Full logs from this job run : 

[Amit_Malik]

What are the outcomes as the logs doesn't show , preprocessor query running and how many records updated etc,...

SELECT COUNT(*) FROM CURRENTCUSTOMER CC WHERE CC.CUSTOMERKEY = '')

It seems you have user userkey in updating cp34, can you use customerkey? 

and if this give cp34 as 1. Can you change query to 

"UPDATE NEWUSERDATA NU  SET NU.customproperty34 in (select CC.displayname from CURRENTCUSTOMER CC where cc.CUSTOMERKEY= <your customer key> ),

[Roua]

@Amit_Malik 
as i posted the outcome is just these two cp changed 

and no for the cp34 i am using customerkey: 
"UPDATE NEWUSERDATA NU SET NU.customproperty33 = (SELECT COUNT(*) FROM CURRENTCUST_USERS CU WHERE CU.USERKEY = '45994')",
"UPDATE NEWUSERDATA NU SET NU.customproperty34 = (SELECT COUNT(*) FROM CURRENTCUSTOMER CC WHERE CC.CUSTOMERKEY = '45994')",

i tried the following as you said
"UPDATE NEWUSERDATA NU SET NU.customproperty34 = (select CC.displayname from CURRENTCUSTOMER CC where cc.CUSTOMERKEY = '4238')"

I'm giving you this update while also going with the process of working around with it if you have any feedback / solution suggestion please let me know. Thank you a lot!

_____
i tried the following: "instead of specifying the CUSTOMERKEY "
"UPDATE NEWUSERDATA NU JOIN CURRENTCUST_USERS CUU ON NU.USERKEY = CUU.USERKEY JOIN CURRENTCUSTOMER CC ON CUU.CUSTOMERKEY = CC.CUSTOMERKEY SET NU.customproperty34 = CC.DISPLAYNAME"
did't work it gave empty value
__________________________
in data analyzer : 
SELECT CUU.USERKEY, C.DISPLAYNAME, C.CUSTOMERKEY
FROM customer_users CUU
JOIN customer C ON CUU.CUSTOMERKEY = C.CUSTOMERKEY
WHERE CUU.USERKEY = (SELECT USERKEY FROM users WHERE USERNAME = 'AT00939275')
this works and join the tables correctly : 

USERKEY DISPLAYNAME CUSTOMERKEY

45994

AZyyxxxxE

4238

so i tried to use the same for the modify json : 
"UPDATE NEWUSERDATA NU SET NU.customproperty34 = (SELECT C.DISPLAYNAME FROM CURRENTCUST_USERS CUU JOIN CURRENTCUSTOMER C ON CUU.CUSTOMERKEY = C.CUSTOMERKEY WHERE CUU.USERKEY = NU.USERKEY)"

it doesn't really get updated 

[Amit_Malik]

Is below one working?

"UPDATE NEWUSERDATA NU SET NU.customproperty34 = (SELECT C.DISPLAYNAME FROM CURRENTCUSTOMER C  WHERE C.CUSTOMERKEY=4238)"

[Roua]

@Amit_Malik 
yes since i am specifying the C.CUSTOMERKEY=4238 it worked: 

[Amit_Malik]

"UPDATE NEWUSERDATA NU SET NU.customproperty34 = (SELECT C.userkey FROM CURRENTCUST_USERS C  WHERE C.CUSTOMERKEY=4238)"

this also please once #

[Roua]

i really appreciate you replies and time!

here is the result it was mapped since it is hardcoded 

[Amit_Malik]

Try this

UPDATE NEWUSERDATA NU
INNER JOIN CURRENTCUST_USERS CUU ON NU.USERKEY = CUU.USERKEY
INNER JOIN CURRENTCUSTOMER C ON CUU.CUSTOMERKEY = C.CUSTOMERKEY
SET NU.customproperty34 = C.DISPLAYNAME;

If this doesn't work, then share the logs including preprocessor execution. Last time the logs ended before the execution of preprocessor queries 

[rushikeshvartak]

@Roua  If above solution does not work please raise support ticket for further troubleshooting 

[rushikeshvartak]

Share full JSON

[Roua]

in this i am testing in cp34 originally it should be depertement name:
in sav4sav connection: 
import user json : 

{ 

  "connection": "acctAuth", 

  "successResponses": { 

    "statusCode": [ 

      200, 

      201, 

      202, 

      203, 

      204, 

      205 

    ] 

  }, 



 "url": "https://xxxxxxxxxxxxxxxxapi/v5/user?q=username:AT0xxxx1&fields=username,email,displayname,departmentname,employeeType,firstname,lastname,userkey",
  "httpMethod": "GET", 


  "httpHeaders": { 

    "Authorization": "${access_token}", 

    "Accept": "application/json" 

  }, 

  "httpContentType": "application/json", 

  "userResponsePath": "results", 

  "colsToPropsMap":{
"username": "username~#~char",
"customproperty6": "customproperty6~#~char",
"displayname":"displayname~#~char",
"lastname":"lastname~#~char",
"firstname":"firstname~#~char",
"employeeType":"employeeType~#~char",
"departmentname":"departmentname~#~char",
"customproperty35":"customproperty35~#~char",

"userkey":"userkey~#~char"
}

}

modify user json + the logic that is not working:  "to get the display name of the customer an assign it tp cp34 "

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT customproperty5, customproperty6, USERKEY, USERNAME, departmentname, lastname, firstname, employeeType, customproperty33, customproperty35 FROM users",
    "CUSTOMER": "SELECT CUSTOMERKEY, DISPLAYNAME FROM customer",
    "CUST_USERS": "SELECT USERKEY, CUSTOMERKEY FROM customer_users",
    "ACCOUNTS": "SELECT NAME, customproperty49, displayname  FROM accounts"
  },
  "COMPUTEDCOLUMNS": [
    "customproperty6",
   "departmentname",
"displayname",
"customproperty34"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA NU JOIN CURRENTACCOUNTS CA ON NU.USERNAME = CA.NAME SET NU.customproperty6 = CA.customproperty49 WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 = 'P')",
    "UPDATE NEWUSERDATA NU SET NU.customproperty6 = NULL WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 <> 'P')",

"UPDATE NEWUSERDATA NU SET NU.customproperty34 = (SELECT C.DISPLAYNAME FROM CURRENTCUST_USERS CUU JOIN CURRENTCUSTOMER C ON CUU.CUSTOMERKEY = C.CUSTOMERKEY WHERE CUU.USERKEY = NU.USERKEY)"

  ]
}

[rushikeshvartak]

UPDATE NEWUSERDATA NU
JOIN CURRENTCUST_USERS CUU ON NU.USERKEY = CUU.USERKEY
JOIN CURRENTCUSTOMER C ON CUU.CUSTOMERKEY = C.CUSTOMERKEY
SET NU.customproperty34 = C.DISPLAYNAME;

[Roua]

Hello @rushikeshvartak ,
i tried to use the query you wrote but it didn't work 

{ 

  "connection": "acctAuth", 

  "successResponses": { 

    "statusCode": [ 

      200, 

      201, 

      202, 

      203, 

      204, 

      205 

    ] 

  }, 



 "url": "https://xxxxxxxxxxxxxxxxxxxxxx.com/ECM/api/v5/user?q=username:AT0006457&fields=username,email,displayname,departmentname,employeeType,firstname,lastname,userkey",
  "httpMethod": "GET", 


  "httpHeaders": { 

    "Authorization": "${access_token}", 

    "Accept": "application/json" 

  }, 

  "httpContentType": "application/json", 

  "userResponsePath": "results", 

"colsToPropsMap":{
"username": "username~#~char",
"customproperty6": "customproperty6~#~char",
"customproperty34 ":"customproperty34 ~#~char",
"userkey":"userkey~#~char"
}

}

MODIFYJSON

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT customproperty5, customproperty6, USERKEY, USERNAME, departmentname, lastname, firstname, employeeType, customproperty34 FROM users",
    "CUSTOMER": "SELECT CUSTOMERKEY, DISPLAYNAME FROM customer",
    "CUST_USERS": "SELECT USERKEY, CUSTOMERKEY FROM customer_users",
    "ACCOUNTS": "SELECT NAME, customproperty49, displayname  FROM accounts"
  },
  "COMPUTEDCOLUMNS": [
    "customproperty6",
"customproperty34"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA NU JOIN CURRENTACCOUNTS CA ON NU.USERNAME = CA.NAME SET NU.customproperty6 = CA.customproperty49 WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 = 'P')",
    "UPDATE NEWUSERDATA NU SET NU.customproperty6 = NULL WHERE EXISTS (SELECT 1 FROM CURRENTUSERS CU WHERE NU.USERNAME = CU.USERNAME AND CU.customproperty5 <> 'P')",
 "UPDATE NEWUSERDATA NU JOIN CURRENTCUST_USERS CUU ON NU.USERKEY = CUU.USERKEY JOIN CURRENTCUSTOMER C ON CUU.CUSTOMERKEY = C.CUSTOMERKEY SET NU.customproperty34 = C.DISPLAYNAME"

  ]
}

the logs found: 

[Roua]

@rushikeshvartak @Amit_Malik 

from the logs i can see that issue is that cp34 is getting overwriten with null since: 
this is getting excuted first 

2024-09-11T12:19:28+02:00-ecm-worker--null-rxfk8--"UPDATE NEWUSERDATA NU JOIN CURRENTCUST_USERS CUU ON NU.USERKEY = CUU.USERKEY JOIN CURRENTCUSTOMER C ON CUU.CUSTOMERKEY = C.CUSTOMERKEY SET NU.customproperty34 = C.DISPLAYNAME"

an then the rest (please correct me if i am wrong, can you please tell me why it is not getting excuted in order but reversed ?)

then this : 

2024-09-11T12:19:28+02:00-ecm-worker--null-rxfk8--"COMPUTEDCOLUMNS": [
2024-09-11T12:19:28+02:00-ecm-worker--null-rxfk8--"customproperty6",
2024-09-11T12:19:28+02:00-ecm-worker--null-rxfk8--"customproperty34 "

i tried to remove it from COMPUTEDCOLUMNS from modify json it gave an error: 

Failure msg

Error in Users Import - Error while processing data: Unknown column 'NU.customproperty34' in 'field list'

also when i added it in the COMPUTEDCOLUMNS  in import json code it gave null value and when i remove it from both it result in this error: Error in Users Import - Error while processing data: Unknown column 'NU.customproperty34' in 'field list'
even thought it is in the link:
"url": "https:/xxxxxxxxx/ECM/api/v5/user?q=username:AT000453&fields=username,email,displayname,departmentname,employeeType,firstname,lastname,userkey,customproperty34",
and in the select table: 
"USERS": "SELECT customproperty5, customproperty6, USERKEY, USERNAME, departmentname, lastname, firstname, employeeType, customproperty34 FROM users",



any idea? thank you!

[rushikeshvartak]

Please raise support ticket for further troubleshooting

[Roua]

@Amit_Malik @rushikeshvartak ,
Do you have an idea what could be the issue, this must work but it is not still, i did debug it all but couldn't fins a way. 
Thank you !

[Roua · Accepted solution]

after further debugging and testing this worked for me: 
customer mapping:

"UPDATE NEWUSERDATA NU JOIN CURRENTCUSTOMER C ON NU.Orgunitid = C.CUSTOMERNAME SET NU.CUSTOMER= C.CUSTOMERNAME"

mapping customer displayname to users departmentname name: 

"UPDATE NEWUSERDATA NU JOIN CURRENTCUSTOMER C ON NU.Orgunitid = C.CUSTOMERNAME SET NU.departmentname = C.displayname",

displayname fo the user:

"UPDATE NEWUSERDATA NU SET NU.displayname = CONCAT(UPPER(NU.lastname), ' ', NU.firstname, ' (', NU.departmentname, IF(NU.employeeType = 'E', ' (EXT)', ''), ')')"

full json: 

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT customproperty2, customproperty11, customproperty9, lastname, firstname, departmentname, employeeType, Orgunitid, customproperty4, country, statuskey, customproperty5, username, startdate, enddate, customproperty3, manager, customproperty14, customproperty21, userkey, customproperty13, customer, secondaryManager, owneronterminate, customproperty30, owner, customproperty25, customproperty34 FROM USERS",
"CUSTOMER": "SELECT CUSTOMERKEY, customername, DISPLAYNAME FROM customer",
"CUST_USERS": "SELECT USERKEY, CUSTOMERKEY FROM customer_users"
  },
  "COMPUTEDCOLUMNS": [
    "customproperty2",
    "customproperty11",
    "customproperty9",
    "displayname",
    "Orgunitid",
    "username",
    "statuskey",
    "startdate",
    "customer",
    "enddate",
    "statuskey",
    "manager",
    "customproperty14",
    "customproperty15",
    "customproperty13",
    "secondaryManager",
    "userkey",
    "owneronterminate",
    "customproperty30",
    "customproperty3",
     "customproperty25",
"customproperty34"
  ],
  "PREPROCESSQUERIES": [
   
"UPDATE NEWUSERDATA NU SET NU.departmentname = CASE WHEN COALESCE(NU.departmentname, '') <> '' THEN NU.departmentname ELSE NU.departmentname END",
"UPDATE NEWUSERDATA NU JOIN CURRENTCUSTOMER C ON NU.Orgunitid = C.CUSTOMERNAME SET NU.CUSTOMER= C.CUSTOMERNAME",
"UPDATE NEWUSERDATA NU JOIN CURRENTCUSTOMER C ON NU.Orgunitid = C.CUSTOMERNAME SET NU.departmentname = C.displayname",
 "UPDATE NEWUSERDATA NU SET NU.displayname = CONCAT(UPPER(NU.lastname), ' ', NU.firstname, ' (', NU.departmentname, IF(NU.employeeType = 'E', ' (EXT)', ''), ')')"
  ]
}