02/02/2023 01:40 AM
Hi.
I am trying to add users to an organization during user import, in the preprocessing section.
I have tried using both the CUSTOMERKEY value and the CUSTOMERNAME value of the organization, to the users CUSTOMER column, but none of them works (the user is not added).
02/02/2023 01:46 AM
Share query
02/02/2023 01:50 AM - edited 02/02/2023 01:50 AM
{
"ADDITIONALTABLES": {
"USERS": "SELECT employeeid,firstname,middlename,startdate,location,customproperty6,customproperty40,employeetype,costcenter,enddate,customproperty60,customer FROM USERS",
"CUSTOMER": "SELECT CUSTOMERKEY, CUSTOMERNAME, CUSTOMPROPERTY20, CUSTOMPROPERTY1, LOCATION FROM CUSTOMER"
},
"COMPUTEDCOLUMNS": [
"firstname", "middlename", "displayname", "customproperty6", "costcenter", "customproperty11", "enddate", "customer"
],
"PREPROCESSQUERIES": [
"DELETE FROM NEWUSERDATA WHERE EMPLOYEETYPE is null or EMPLOYEETYPE = ''",
"UPDATE NEWUSERDATA SET MIDDLENAME = TRIM(SUBSTR(NEWUSERDATA.firstname,LOCATE(' ',NEWUSERDATA.firstname)))",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.ENDDATE = case when NEWUSERDATA.ENDDATE is not NULL and NEWUSERDATA.ENDDATE != '' and (CURRENTUSERS.CUSTOMPROPERTY60 is NULL or CURRENTUSERS.CUSTOMPROPERTY60='') THEN NEWUSERDATA.ENDDATE when CURRENTUSERS.CUSTOMPROPERTY60 is not NULL and CURRENTUSERS.CUSTOMPROPERTY60 != '' and (NEWUSERDATA.ENDDATE is NULL or NEWUSERDATA.ENDDATE = '') then DATE(CURRENTUSERS.CUSTOMPROPERTY60) when CURRENTUSERS.CUSTOMPROPERTY60 is not NULL and CURRENTUSERS.CUSTOMPROPERTY60 != '' and DATE(NEWUSERDATA.ENDDATE) > DATE(CURRENTUSERS.CUSTOMPROPERTY60) then DATE(CURRENTUSERS.CUSTOMPROPERTY60) when (CURRENTUSERS.CUSTOMPROPERTY60 is not NULL and CURRENTUSERS.CUSTOMPROPERTY60 != '') and (DATE(NEWUSERDATA.ENDDATE) < DATE(CURRENTUSERS.CUSTOMPROPERTY60)) then NEWUSERDATA.ENDDATE when (NEWUSERDATA.ENDDATE is NULL or NEWUSERDATA.ENDDATE = '') and (CURRENTUSERS.CUSTOMPROPERTY60 is NULL or CURRENTUSERS.CUSTOMPROPERTY60 = '') then '' else NULL end",
"UPDATE NEWUSERDATA SET CUSTOMER = case when ((NEWUSERDATA.LOCATION is not null or NEWUSERDATA.LOCATION != '') AND (select CURRENTCUSTOMER.CUSTOMERNAME from CURRENTCUSTOMER where CURRENTCUSTOMER.CUSTOMPROPERTY1 = NEWUSERDATA.LOCATION AND (CURRENTCUSTOMER.CUSTOMPROPERTY20 is not null and CURRENTCUSTOMER.CUSTOMPROPERTY20 != '')) is not null or (select CURRENTCUSTOMER.CUSTOMERNAME from CURRENTCUSTOMER where CURRENTCUSTOMER.CUSTOMPROPERTY1 = NEWUSERDATA.LOCATION AND (CURRENTCUSTOMER.CUSTOMPROPERTY20 is not null and CURRENTCUSTOMER.CUSTOMPROPERTY20 != '')) != '') then (select CURRENTCUSTOMER.CUSTOMERNAME from CURRENTCUSTOMER where CURRENTCUSTOMER.CUSTOMPROPERTY1 = NEWUSERDATA.LOCATION AND (CURRENTCUSTOMER.CUSTOMPROPERTY20 is not null and CURRENTCUSTOMER.CUSTOMPROPERTY20 != '')) else '' end",
"UPDATE NEWUSERDATA SET FIRSTNAME = SUBSTRING_INDEX(firstname, ' ', 1)",
"UPDATE NEWUSERDATA SET DISPLAYNAME = CONCAT(firstname,' ', lastname)",
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY11 = case when NEWUSERDATA.EMPLOYEETYPE = 'External Consultant' then 'OU=External Users,' when ((NEWUSERDATA.LOCATION is not null or NEWUSERDATA.LOCATION != '') AND (select CURRENTCUSTOMER.CUSTOMPROPERTY20 from CURRENTCUSTOMER where CURRENTCUSTOMER.CUSTOMPROPERTY1 = NEWUSERDATA.LOCATION AND (CURRENTCUSTOMER.CUSTOMPROPERTY20 is not null and CURRENTCUSTOMER.CUSTOMPROPERTY20 != '')) is not null or (select CURRENTCUSTOMER.CUSTOMPROPERTY20 from CURRENTCUSTOMER where CURRENTCUSTOMER.CUSTOMPROPERTY1 = NEWUSERDATA.LOCATION) != '') then (select CURRENTCUSTOMER.CUSTOMPROPERTY20 from CURRENTCUSTOMER where CURRENTCUSTOMER.CUSTOMPROPERTY1 = NEWUSERDATA.LOCATION AND (CURRENTCUSTOMER.CUSTOMPROPERTY20 is not null and CURRENTCUSTOMER.CUSTOMPROPERTY20 != '')) else '' end",
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY6 = case when ((NEWUSERDATA.LOCATION is not null or NEWUSERDATA.LOCATION != '') AND (select CURRENTCUSTOMER.LOCATION from CURRENTCUSTOMER where CURRENTCUSTOMER.CUSTOMPROPERTY1 = NEWUSERDATA.LOCATION AND (CURRENTCUSTOMER.CUSTOMPROPERTY20 is not null and CURRENTCUSTOMER.CUSTOMPROPERTY20 != '')) is not null or (select CURRENTCUSTOMER.LOCATION from CURRENTCUSTOMER where CURRENTCUSTOMER.CUSTOMPROPERTY1 = NEWUSERDATA.LOCATION AND (CURRENTCUSTOMER.CUSTOMPROPERTY20 is not null and CURRENTCUSTOMER.CUSTOMPROPERTY20 != '')) != '') then (select CURRENTCUSTOMER.LOCATION from CURRENTCUSTOMER where CURRENTCUSTOMER.CUSTOMPROPERTY1 = NEWUSERDATA.LOCATION AND (CURRENTCUSTOMER.CUSTOMPROPERTY20 is not null and CURRENTCUSTOMER.CUSTOMPROPERTY20 != '')) else '' end",
"DELETE FROM NEWUSERDATA WHERE COSTCENTER = '9999'",
"DELETE FROM NEWUSERDATA WHERE ENDDATE !='' AND ENDDATE <= DATE_SUB(now(),INTERVAL 1092 DAY)"
]
}
It is this in particular in above complete JSON which is causing issues. The rest is working as expected:
"UPDATE NEWUSERDATA SET CUSTOMER = case when ((NEWUSERDATA.LOCATION is not null or NEWUSERDATA.LOCATION != '') AND (select CURRENTCUSTOMER.CUSTOMERNAME from CURRENTCUSTOMER where CURRENTCUSTOMER.CUSTOMPROPERTY1 = NEWUSERDATA.LOCATION AND (CURRENTCUSTOMER.CUSTOMPROPERTY20 is not null and CURRENTCUSTOMER.CUSTOMPROPERTY20 != '')) is not null or (select CURRENTCUSTOMER.CUSTOMERNAME from CURRENTCUSTOMER where CURRENTCUSTOMER.CUSTOMPROPERTY1 = NEWUSERDATA.LOCATION AND (CURRENTCUSTOMER.CUSTOMPROPERTY20 is not null and CURRENTCUSTOMER.CUSTOMPROPERTY20 != '')) != '') then (select CURRENTCUSTOMER.CUSTOMERNAME from CURRENTCUSTOMER where CURRENTCUSTOMER.CUSTOMPROPERTY1 = NEWUSERDATA.LOCATION AND (CURRENTCUSTOMER.CUSTOMPROPERTY20 is not null and CURRENTCUSTOMER.CUSTOMPROPERTY20 != '')) else '' end"
I have tried using CURRENTCUSTOMER.CUSTOMERKEY and CURRENTCUSTOMER.CUSTOMERNAME
02/02/2023 01:58 AM
I think customer table is not exposed
02/02/2023 02:01 AM - edited 02/02/2023 02:39 AM
.
02/02/2023 02:38 AM
It really does seems like it. Yet another decision I finds very odd. Why can we not import users in an onboarding flow, to an organization object? It makes no logical sense.
02/03/2023 01:10 PM
Raise Idea ticket
02/05/2023 10:56 PM
Already did it. 🙂
02/06/2023 06:01 PM
Share here
02/09/2023 12:03 AM