Fetch NEWUSERDATA and count occurences in ModifyJson

[sdey_2023]

Hi team,

We are storing the concat of firstname and lastname in CP41 for all users. We have a requirement to update the count of the occurrences of same firstname, lastname in CP42 across all the User database of Saviynt, we are using the below query in the ModifyJson:

"update NEWUSERDATA as NU set NU.CUSTOMPROPERTY42 = select count(customproperty41)+1 from currentusers u1 where u1.customproperty41 like concat(NU.firstname,'.',NU.lastname)"

But the issue here is that if the same firstname and lastname user comes as part of same import, it is unable to correctly store the count as the above query is only considering the currentusers which was already imported.

So if as part of 1st import one user named John Doe comes and then in next import same named user comes, it is able to correctly store the count. But if as part of 1st import itself two users of name John Doe comes it is not able to store the count correctly.

Could you please advise on the query to be used so that we can fetch the current import users as well.

[NM]

Hi @sdey_2023 , try this

"update NEWUSERDATA as NU set NU.CUSTOMPROPERTY42 = select count(u1customproperty41)+count(NU.customproperty41)+1 from currentusers u1 where u1.customproperty41 like concat(NU.firstname,'.',NU.lastname)"

[Raghu]

@sdey_2023  try below

UPDATE NEWUSERDATA NU
SET NU.CUSTOMPROPERTY42 = (
SELECT COUNT(u1.customproperty41) + 1
FROM currentusers u1
WHERE u1.customproperty41 LIKE CONCAT(NU.firstname, '.', NU.lastname)
)

[sdey_2023]

Hi Raghu,

This is not considering the NEWUSERDATA users which are part of the current import but only the already existing users via the currentusers table.

[Raghu]

@sdey_2023  try

UPDATE NEWUSERDATA NU
SET NU.CUSTOMPROPERTY42 = (
SELECT COUNT(u1.customproperty41) + 1
FROM currentusers u1
WHERE u1.customproperty41 LIKE CONCAT(NU.firstname, '.', NU.lastname)
)
WHERE NU.USERNAME IN (
SELECT NU2.USERNAME
FROM NEWUSERDATA NU2
LEFT JOIN currentusers u2 ON u2.customproperty41 LIKE CONCAT(NU2.firstname, '.', NU2.lastname)
WHERE u2.USERNAME IS NULL)

[sdey_2023]

Hi Raghu, Thanks for your efforts but unfortunately this also does not work.

[Raghu]

@sdey_2023 

UPDATE NEWUSERDATA NU
SET NU.CUSTOMPROPERTY42 = (
    SELECT COUNT(*) + 1
    FROM currentusers u1
    WHERE u1.customproperty41 = CONCAT(NU.firstname, '.', NU.lastname)
)

[NM]

@sdey_2023 , did you got a chance to try this?

"update NEWUSERDATA as NU set NU.CUSTOMPROPERTY42 = select count(u1customproperty41)+count(NU.customproperty41)+1 from currentusers u1 where u1.customproperty41 like concat(NU.firstname,'.',NU.lastname)"