I am using below pre-processor to populate owner.
{ "ADDITIONALTABLES" : { "USERS": "SELECT userkey,employeeid,username,costcenter,employeetype,customproperty10,customproperty25,displayname,manager,customproperty60,customproperty29,customproperty18,customproperty28,systemusername,customproperty27,companyname,departmentname from users","ACCOUNTS": "select accountid,endpointkey, name, accountkey, customproperty15 from accounts","ENDPOINTS":"Select endpointkey, endpointname from endpoints","ACCOUNT_ENTITLEMENTS1": "SELECT accountkey,entitlement_valuekey FROM account_entitlements1","ENTITLEMENT_VALUES":"Select entitlement_value,entitlement_valuekey from entitlement_values ","USER_ACCOUNTS":"select userkey, accountkey from user_accounts " }, "COMPUTEDCOLUMNS" : [ "employeeid","accountid","costcenter","employeetype","customproperty10","customproperty60","accountkey","entitlement_valuekey","customproperty29","name","customproperty18","owner","customproperty27","systemusername","customproperty28","companyname","departmentname","endpointkey","endpointname","customproperty25","displayname","userkey","manager" ], "PREPROCESSQUERIES" : [ "UPDATE NEWUSERDATA SET owner = (Select u.username from CURRENTUSERS u where u.systemusername = NEWUSERDATA.customproperty27)","UPDATE NEWUSERDATA SET costcenter = CASE when NEWUSERDATA.customproperty30 = 'Model User' then (select cu.costcenter from CURRENTUSERS cu where cu.username=NEWUSERDATA.owner) else (select u.costcenter from CURRENTUSERS u, CURRENTACCOUNTS a, CURRENTUSER_ACCOUNTS ua, CURRENTENDPOINTS e where a.accountkey = ua.accountkey and u.userkey = ua.userkey and a.name = NEWUSERDATA.customproperty29 and a.endpointkey=e.endpointkey and e.endpointname = 'Active Directory') END","UPDATE NEWUSERDATA SET customproperty28 = concat('Contractor - ',(NEWUSERDATA.companyname),' - ',(Select departmentname from CURRENTUSERS where systemusername = NEWUSERDATA.customproperty27))","UPDATE NEWUSERDATA SET customproperty10= CASE when NEWUSERDATA.customproperty30 = 'Model User' then (select replace(accountID, substring_index(accountid, 'OU', 1),'') from CURRENTACCOUNTS a,CURRENTENDPOINTS e where a.name = NEWUSERDATA.customproperty29 and a.endpointkey=e.endpointkey and e.endpointname = 'Active Directory') else NEWUSERDATA.customproperty10 END","UPDATE NEWUSERDATA SET customproperty18= CASE when NEWUSERDATA.customproperty30 = 'Model User' then (select customproperty15 from CURRENTACCOUNTS a,CURRENTENDPOINTS e where a.name = NEWUSERDATA.customproperty29 and a.endpointkey=e.endpointkey and e.endpointname = 'Active Directory') else NEWUSERDATA.customproperty18 END","UPDATE NEWUSERDATA SET employeetype = 'Contractor'","UPDATE NEWUSERDATA SET customproperty60= CASE when NEWUSERDATA.customproperty30 = 'Model User' then (select group_concat(ev.ENTITLEMENT_VALUE) as ID from CURRENTACCOUNTS a, CURRENTACCOUNT_ENTITLEMENTS1 ae1 , CURRENTENTITLEMENT_VALUES ev,CURRENTENDPOINTS e where a.accountkey = ae1.accountkey and a.endpointkey=e.endpointkey and e.endpointname = 'Active Directory' and ev.ENTITLEMENT_VALUEKEY = ae1.ENTITLEMENT_VALUEKEY and a.name = NEWUSERDATA.customproperty29 group by a.name) else NEWUSERDATA.customproperty60 END","UPDATE NEWUSERDATA SET customproperty25 = (select m.displayname from CURRENTUSERS u, CURRENTUSERS m where u.manager=m.userkey and u.systemusername = NEWUSERDATA.customproperty27)"] }
It works fine if I remove manager from the additionaltable and the computed columns. Otherwise owner fields comes as blank.
Our requirement:
1. Populate the Owner field based on the cp27 value
2. Populate cp25 based on the cp27(Get manager's manager displayname)
