Assign users to organization in preproccessing

Kerasit
New Contributor III
New Contributor III

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).

9 REPLIES 9

rushikeshvartak
All-Star
All-Star

Share query 

 

{ 
	"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

I think customer table is not exposed

.

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.

Raise Idea ticket

Already did it. 🙂

Share here