MODIFYUSERDATAJSON in workday connection | Update User query not working

[darshanmandhane]

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.

[rushikeshvartak]

Validate below

select username,startdate,customproperty40,statuskey from users where customproperty40 != 'TRUE'

[darshanmandhane]

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')

[rushikeshvartak]

select username,startdate,customproperty40,statuskey ,now() as today from users where customproperty40 is null

[darshanmandhane]

[rushikeshvartak]

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;

[darshanmandhane]

Yes, we tried this as well, In fact I tried this again, but it is not processing with this option either.

[rushikeshvartak]

Add jobid in query

Does this user coming from latest import ?

[darshanmandhane]

Yes, it is a full upload every time it runs. It's not incremental.