Saviynt Forums

Roua · ‎09/12/2024

Hello everyone,

I’m encountering an issue with the a requirement, we have customproperty3 that is getting its value from username, but we need to check that once it is set it doesn't get updated again (CP3 is changed from UI- it should't be back to username value during update) so i made a query to check and update CP3 only if it was empty/NULL.
the field is being updated despite it already having a value. or it will automatically be set to NULL. and i am facing this issue in modifyuserdatajson in both SAP connector and SAV4SAV connector"with another requirement"
Example of the process:
username: test1
changing CP3 from test1 -> test2 in the UI
Running the user import job
CP3 is reset to test1 (checking that it is not null or empty didn't work).

i tried many solutions posted here but it didn't work i will provide the links at the end.
here is my modifydatajson:

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT customproperty2, customproperty11, customproperty9, lastname, firstname, departmentname, employeeType, Orgunitid, customproperty4, country, statuskey, customproperty5, username, startdate, enddate, customproperty3, manager, customproperty14, customproperty21, userkey, customproperty13, customer, secondaryManager, owneronterminate, customproperty30, owner, customproperty25, customproperty34 FROM USERS",
"CUSTOMER": "SELECT CUSTOMERKEY, customername, DISPLAYNAME FROM customer",
"CUST_USERS": "SELECT USERKEY, CUSTOMERKEY FROM customer_users"
  },
  "COMPUTEDCOLUMNS": [
    "customproperty2",
    "customproperty11",
    "customproperty9",
    "displayname",
    "Orgunitid",
    "username",
    "statuskey",
    "startdate",
    "customer",
    "enddate",
    "statuskey",
    "manager",
    "customproperty14",
    "customproperty15",
    "customproperty13",
    "secondaryManager",
    "userkey",
    "owneronterminate",
    "customproperty30",
    "customproperty3",
     "customproperty25",
"customproperty34"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA NU SET NU.customproperty2 = DATE_FORMAT(STR_TO_DATE(NU.customproperty2, '%Y%m%d'), '%d.%m.%Y')",
    "UPDATE NEWUSERDATA NU SET NU.customproperty11 = CASE WHEN NU.customproperty11 = '00000000' THEN NULL ELSE DATE_FORMAT(STR_TO_DATE(NU.customproperty11, '%Y%m%d'), '%d.%m.%Y') END",
    "UPDATE NEWUSERDATA NU SET NU.customproperty9 = CASE WHEN NU.customproperty9 = '00000000' THEN NULL ELSE NU.customproperty9 END",
"UPDATE NEWUSERDATA NU SET NU.departmentname = CASE WHEN COALESCE(NU.departmentname, '') <> '' THEN NU.departmentname ELSE NU.departmentname END",
   "UPDATE NEWUSERDATA NU SET NU.displayname = CONCAT(UPPER(NU.lastname), ' ', NU.firstname, ' (', NU.departmentname, IF(NU.employeeType = 'E', ' (EXT)', ''), ')') WHERE NU.departmentname IS NOT NULL",

"UPDATE NEWUSERDATA SET CUSTOMER=(select CURRENTCUSTOMER.CUSTOMERNAME from CURRENTCUSTOMER where CURRENTCUSTOMER.CUSTOMERNAME=NEWUSERDATA.Orgunitid)",

  "UPDATE NEWUSERDATA NU SET NU.customer = (SELECT customername FROM currentcustomer WHERE customername = NU.Orgunitid)", 

    "UPDATE NEWUSERDATA NU SET NU.Orgunitid = CONCAT(NU.customproperty4, '-', NU.Orgunitid, '')",
 "UPDATE NEWUSERDATA NU SET NU.username = CASE WHEN NU.username IS NULL OR NU.username = '' THEN CONCAT(NU.country, NU.employeeId) ELSE NU.username END",
    "UPDATE NEWUSERDATA NU SET NU.manager = CONCAT(NU.country, NU.manager)",
	"UPDATE NEWUSERDATA NU SET NU.customproperty30 = CONCAT(NU.country, NU.customproperty30)",
    "UPDATE NEWUSERDATA NU SET NU.secondaryManager = NU.customproperty30",
    "UPDATE NEWUSERDATA NU SET NU.statuskey = CASE WHEN NU.customproperty5 IN ('P', 'A') THEN 1 WHEN NU.customproperty5 = 'I' THEN 0 END",
    "UPDATE NEWUSERDATA NU SET NU.startdate = DATE_FORMAT(STR_TO_DATE(NU.startdate, '%Y%m%d'), '%b %d, %Y')",
  "UPDATE NEWUSERDATA NU SET NU.enddate = CASE WHEN NU.customproperty5 = 'I' THEN DATE_FORMAT(NU.enddate, '%m/%d/%Y') WHEN NU.customproperty5 IN ('P', 'A') THEN NULL ELSE NU.enddate END",
 "UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS CU ON NU.username = CU.username SET NU.customproperty3 = NU.username WHERE NU.customproperty3 IS NULL OR NU.customproperty3 = '' ",

"UPDATE NEWUSERDATA NU SET NU.OWNERONTERMINATE = CASE WHEN NU.manager IS NOT NULL THEN NU.manager ELSE '' END",
    "UPDATE NEWUSERDATA NU SET NU.customproperty14 = CASE WHEN NU.customproperty15 = '50404368' THEN 'Poolberater' WHEN NU.customproperty15 = '50404373' THEN 'Projektberater' WHEN NU.customproperty15 = '50404374' THEN 'Fachberater' ELSE NULL END",
    "UPDATE NEWUSERDATA NU SET NU.customproperty13 = CONCAT(NU.customproperty13)",
"UPDATE NEWUSERDATA NU SET NU.customproperty25 = CASE WHEN NU.customproperty4 LIKE '%.92' AND NU.employeeId LIKE '40%' THEN 'ro' WHEN NU.customproperty4 LIKE '%.92' THEN 'de' WHEN NU.customproperty4 LIKE '%.H2' THEN 'hu' WHEN NU.customproperty4 LIKE '%.K2' THEN 'hr' WHEN NU.customproperty4 LIKE '%.S2' THEN 'sl' WHEN NU.customproperty4 LIKE '%.I2' THEN 'it' WHEN NU.customproperty4 LIKE '%.C2' THEN 'cs' ELSE 'en' END"

  ]
}

Importjson:

{
    "connection": "acctAuth",
    "url": "https://xxxxxxxxxxxxxxxxxx44352/xxxxxxxxxxxxxxxxxxxRSDATA_SRV/ZZIDM_PERSONDATASet?$filter=Perid%20eq%20%27ID03%27",
    "httpMethod": "GET",
    "httpHeaders": {
        "Authorization": "${access_token}",
        "Accept": "application/xml",
        "NamespaceM": "hxxxxxxxxxxxxxxxs/metadata",
        "NamespaceD": "http://xexxxxxxxxxxxxxxxxxxxxxxxes"
    },
    "userResponsePath": "feed.entry.content.m:properties",
    "colsToPropsMap": {
        "secondaryemail": "d:Alternativeemail~#~char",
        "email": "d:email~#~char",
        "employeeType": "d:Type~#~char",
        "employeeclass": "d:Acctype~#~char",
        "customproperty18": "d:Btrtl~#~char",
        "customproperty13": "d:Bwplant~#~char",
        "city": "d:Compcity~#~char",
        "companyname": "d:Compname~#~char",
        "customproperty12": "d:Comppostalcode~#~char",
        "street": "d:Compstreet~#~char",
        "costcenter": "d:Costcenter~#~char",
        "country": "d:Countrycode~#~char",
        "customproperty2": "d:Dateofbirth~#~char",
        "customproperty7": "d:Emphier~#~char",
        "startdate": "d:Entrydate~#~char",
        "enddate": "d:Exitdate~#~char",
        "job_function": "d:Function~#~char",
        "customproperty30": "d:Futurelinemanagerpernr~#~char",
        "customproperty9": "d:Futureorgunitid~#~char",
        "customproperty21": "d:Perid~#~char",
        "employeeId": "d:Persno~#~char",
        "customproperty4": "d:Hcmsystem~#~char",
        "manager": "d:Linemanagerpernr~#~char",
        "phonenumber": "d:Mobnr~#~char",
        "lastname": "d:Nachn~#~char",
        "customproperty23": "d:Noprov~#~char",
        "customproperty8": "d:Orgpath~#~char",
        "customproperty11": "d:Orgunitchangedate~#~char",
        "departmentname": "d:Short~#~char",
        "customproperty10": "d:Futurelinemanagerperid~#~char",
        "middlename": "d:Midnm~#~char",
        "orgunitid": "d:Orgunitid~#~char",
        "departmentnumber": "d:Orgunitid~#~char",
        "displayname": "d:Short~#~char",
        "customproperty22": "d:Zasesclnt~#~char",
        "customproperty19": "d:Persg~#~char",
        "customproperty20": "d:Persk~#~char",
        "customproperty16": "d:Plans~#~char",
        "customproperty5": "d:Status~#~char",
        "secondaryPhone": "d:Telnr~#~char",
        "title": "d:Title1~#~char",
        "customproperty1": "d:Title2~#~char",
        "firstname": "d:Vorna~#~char",
        "customproperty17": "d:Werks~#~char",
        "customproperty15": "d:Zstelle~#~char"
    },
    "pagination": {
        "offset": {
            "offsetParam": "$skip",
            "batchParam": "$top",
            "batchSize": 1000000,
            "totalCountPath": 10000
        }
    },
    "statuskey": {
        "1": "A",
        "0": "I",
        "1": "P"
    },
    "successResponses": {
        "statusCode": [
            200
        ]
    }
}

https://forums.saviynt.com/t5/identity-governance/issues-setting-organization-customer-during-api-im...
https://forums.saviynt.com/t5/identity-governance/customproperty-converting-to-null-from-sav4sav-res...
https://forums.saviynt.com/t5/identity-governance/preprocessqueries-sets-value-to-null-without-match...

solutions i tried:

UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS CU ON NU.username = CU.username SET NU.customproperty3 = NU.username WHERE NU.customproperty3 IS NULL OR NU.customproperty3 = ''
________________________________________
UPDATE NEWUSERDATA NU 
LEFT JOIN CURRENTUSERS CU ON NU.username = CU.username 
SET NU.customproperty3 = NU.username 
WHERE (NU.customproperty3 IS NULL OR TRIM(NU.customproperty3) = '');
________________________________________
UPDATE NEWUSERDATA NU 
LEFT JOIN CURRENTUSERS CU ON NU.username = CU.username 
SET NU.customproperty3 = CU.customproperty3 
WHERE (CU.customproperty3 IS NULL OR TRIM(CU.customproperty3) = '') 
AND (NU.customproperty3 IS NULL OR TRIM(NU.customproperty3) = '')
________________________________________
UPDATE NEWUSERDATA NU 
LEFT JOIN CURRENTUSERS CU ON NU.username = CU.username 
SET NU.customproperty3 = CASE 
    WHEN CU.customproperty3 IS NULL OR TRIM(CU.customproperty3) = '' 
    THEN NU.username 
    ELSE CU.customproperty3 
END
WHERE (CU.customproperty3 IS NULL OR TRIM(CU.customproperty3) = '') 
AND (NU.customproperty3 IS NULL OR TRIM(NU.customproperty3) = '')
________________________________________
 "UPDATE NEWUSERDATA NU SET NU.customproperty3 = NU.username WHERE EXISTS (SELECT 1 FROM currentusers U WHERE U.username = NU.username AND (U.customproperty3 IS NULL OR TRIM(U.customproperty3) = ''));"

rushikeshvartak · ‎09/12/2024

UPDATE NEWUSERDATA NU
LEFT JOIN CURRENTUSERS CU ON NU.username = CU.username
SET NU.customproperty3 = COALESCE(NU.customproperty3, NU.username)
WHERE CU.customproperty3 IS NULL;

Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Roua · ‎09/13/2024

@rushikeshvartak
Thank you
but this is also not working and resulted in empty value like the following:

and i am having this problem with other attributes as well that updating only when checking if it is empty or null is not working
if you have an idea how to solve it i appreciate it because i already tried a lot of methods..

JonasSallstrom · ‎09/13/2024

Can you try something like: Case WHEN cu.customproperty3 is not null then cu.customproperty3 ELSE nu.username

Roua · ‎09/13/2024

thank you @JonasSallstrom ,
yes i actually already tried it with

"UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS CU ON NU.username = CU.username SET NU.customproperty3 = CASE WHEN CU.customproperty3 IS NULL THEN NU.username ELSE CU.customproperty3 END"

and this one is working now!

Saviynt Forums

custom property being updated despite having a value / NULL- Empty