Situation
This is an example situation for the solution below. The solution also applies to different situations.
Let’s say you have workers that can have multiple working relationships with your organization. For example, employees and contingent workers. You also have different authoritative sources for each: The HR system for employees and another system for contingent workers.
A single person can have no, one, or multiple work relationships. For example, a worker can start as an Employee, then also signs up as a Contingent Worker doing a different job. Maybe after a while quitting as the Employee but maintaining the job as Contingent Worker.
You need to keep track of each work relationship and its status. You also want to set the EmployeeType attribute to Employee if the worker is an Employee, to Contingent Worker if the worker only is a Contingent Worker and no Employee. The Title attribute should work the same way. If the worker is an Employee, then list the Employee’s title, otherwise the Contingent Worker’s title.
Solution
The solution involves 4 custom properties on the User object:
- Custom Property 11: Employee Status
- Custom Property 12: Employee Title
- Custom Property 13: Contingent Worker Status
- Custom Property 14: Contingent Worker Title
We also apply the following pre-processing script to the HR import:
{
"ADDITIONALTABLES" :
{
"USERS" : "select username, title, customproperty11, customproperty12, customproperty13, customproperty14 from users"
},
"COMPUTEDCOLUMNS" :
[
"employeetype", "title", "customproperty11", "customproperty12"
],
"PREPROCESSQUERIES" :
[
"update NEWUSERDATA nu left join CURRENTUSERS cu on nu.username = cu.username set nu.customproperty11 = case when nu.statuskey = 1 then 'Active' else 'Inactive' end, nu.customproperty12 = nu.title, nu.title = case when (nu.statuskey = 0 and cu.customproperty13 = 'Active') then cu.customproperty14 else nu.title end, nu.employeetype = case when nu.statuskey = 1 then 'Employee' when nu.statuskey = 0 and cu.customproperty13 = 'Active' then 'Contingent Worker' else null end, nu.enddate = case when nu.enddate > cu.enddate then nu.enddate else cu.enddate end, nu.statuskey = case when (nu.statuskey = 0 and (cu.customproperty13 is null or cu.customproperty13 = 'Inactive')) then 0 else 1 end"
]
}The following sections are included in the script:
- ADDITIONALTABLES: Here, we query for existing data in the Identity Repository. In this case, user data. This way, we can look up how the user record looks before we import the new data. “USERS” gets translated to “CURRENTUSERS” in the PREPROCESSQUERIES section.
- COMPUTEDCOLUMNS: Here, we just define which columns we are potentially changing. Those can be columns included in the data feed or other columns. Based on our situation, we want to compute the values for the user’s title and employeetype. As this is the pre-processing script for Employees, we also add logic for the Employee’s Status and Title, Represented by Custom Properties 11 and 12.
- PREPROCESSQUERIES: Here is where the computation happens. We are basically checking for the logic described for this situation and applying this to the imported Employee data.
A very similar pre-processing script will be applied to the Contingent Workers import:
{
"ADDITIONALTABLES" :
{
"USERS" : "select username, title, customproperty11, customproperty12, customproperty13, customproperty14 from users"
},
"COMPUTEDCOLUMNS" :
[
"employeetype", "title", "customproperty13", "customproperty14"
],
"PREPROCESSQUERIES" :
[
"update NEWUSERDATA nu left join CURRENTUSERS cu on nu.username = cu.username set nu.customproperty13 = case when nu.statuskey = 1 then 'Active' else 'Inactive' end nu.customproperty14 = nu.title, nu.title = case when cu.customproperty11 = 'Active' then cu.customproperty12 when nu.statuskey = 1 then nu.title else nu.title end, nu.employeetype = case when cu.customproperty11 = 'Active' then 'Employee' when nu.statuskey = 1 then 'Contingent Worker' else null end"
]
}Note, that for the Contingent Workers we have a slightly different logic to accommodate for the attribute precedence of the Employee.
