Workflow to Assign approval Request only to Rank1 ServiceAccountowner for any create/modify requests

[piyushm]

Need the request to be assigned only to the Rank 1 Service Account owner (current owner if its modify request and to the new owner if its update owner request). This is to prevent request going to any other Rank owner for any type of request.

For this I wrote custom query to get the ownerkey from the request attribute USERRANKJSON. But I am unable to save workflow when i am using custom assignment block with custom query. What i noticed if the query is having nested functions then workflow cannot be saved. If the query is non-nested then it works. The same query works in data analyzer.

select userkey from users where FIND_IN_SET(users.userkey,(select distinct TRIM('"' from ( TRIM('{' from SUBSTRING_INDEX(attribute_value,':1',1))))
from request_access_attrs raa , ars_requests ar, request_access ra WHERE ar.REQUESTKEY = ra.REQUESTKEY and
ra.REQUEST_ACCESSKEY = raa.REQUEST_ACCESS_KEY and ar.requestkey=${ARSREQUEST.id} and raa.ATTRIBUTE_NAME='USERRANKJSON'))!=0

[piyushm]

Below is the error

[rushikeshvartak]

select userkey from users where FIND_IN_SET(users.userkey,(select distinct TRIM('"' from ( TRIM('{' from SUBSTRING_INDEX(attribute_value,':1',1))))
from request_access_attrs raa , ars_requests ar, request_access ra WHERE ar.REQUESTKEY = ra.REQUESTKEY and
ra.REQUEST_ACCESSKEY = raa.REQUEST_ACCESS_KEY and ar.requestkey=${ARSREQUEST.id} and raa.ATTRIBUTE_NAME='USERRANKJSON'))!=0

[piyushm]

Thanks @rushikeshvartak , but I am still getting same error when trying to save the workflow.

[rushikeshvartak]

Does query return from data analyzer

[piyushm]

Yes, both my query and the one you posted work in Data analyzer (only change is I am giving static requestkey instead of ${ARSREQUEST.id} in data analyzer)

[rushikeshvartak]

Share wf zip

[piyushm]

@rushikeshvartakPlease find attached the workflow.

Looking at the way the data is stored, its stored as json but column type is longtext.

{"34620":1,"3724":2}

The query/logic above will work if the rank 1 owner is stored as the first entry. But the logic fails for below example or if the rank 1 owner is not the first entry.

{"34620":2,"3724":1}

[rushikeshvartak]

Its tricky i am trying to query, data is getting stored randomly