Click HERE to see how Saviynt Intelligence is transforming the industry. |
09/27/2024 04:59 AM
MODIFYUSERDATAJSON in workday connection | Update User query not working
In the MODIFYUSERDATAJSON of workday connection (which is a source of truth) for user identities, under PREPROCESSQUERIES section, we have added the below query:
"UPDATE NEWUSERDATA SET customproperty40 = 'TRUE' WHERE startdate <= now() AND statuskey ='1' AND customproperty40 IS NULL".
After running the import for this connection, customproperty40 is getting set to value 'TRUE' for almost all users except for a few users. We're not getting the reason for the same.
These 28 user records are present and other attributes are getting updated for these users from this import. Any further help to resolve this issue would be appreciated.
09/27/2024 05:25 AM
Validate below
select username,startdate,customproperty40,statuskey from users where customproperty40 != 'TRUE'
09/27/2024 05:42 AM
There are 0 records with this query
select username,startdate,customproperty40,statuskey from users where customproperty40 != 'TRUE'
------------------------------------------------------------------------------------------------------------------
However, there are 28 records with below query
select username,startdate,customproperty40,statuskey from users WHERE startdate <= now() AND statuskey = 1 AND customproperty40 IS NULL
------------------------------------------------------------------------------------------------------------------
Even if I have configured the connection to update the records as per the below query, it is not working
"UPDATE NEWUSERDATA SET customproperty40 = 'TRUE' WHERE startdate <= now() AND statuskey ='1' AND (customproperty40 IS NULL OR customproperty40 != 'TRUE')
09/27/2024 06:02 AM - edited 09/27/2024 06:02 AM
select username,startdate,customproperty40,statuskey ,now() as today from users where customproperty40 is null
09/27/2024 06:05 AM
09/27/2024 06:08 AM
Does this user coming from latest import ?
UPDATE NEWUSERDATA
SET customproperty40 =
CASE
WHEN startdate <= now() AND statuskey = '1' AND customproperty40 IS NULL THEN 'TRUE'
ELSE 'FALSE'
END;
09/27/2024 06:38 AM
Yes, we tried this as well, In fact I tried this again, but it is not processing with this option either.
09/27/2024 06:41 AM
Add jobid in query
Does this user coming from latest import ?
09/27/2024 06:44 AM
Yes, it is a full upload every time it runs. It's not incremental.