We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

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 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

 

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


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

.

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


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Already did it. 🙂

Share here


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.