We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

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

piyushm
Regular Contributor II
Regular Contributor II

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

8 REPLIES 8

piyushm
Regular Contributor II
Regular Contributor II

Below is the error

piyushm_2-1668703405266.png

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


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

piyushm
Regular Contributor II
Regular Contributor II

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

piyushm_1-1668800434139.png

Does query return from data analyzer


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

piyushm
Regular Contributor II
Regular Contributor II

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)

Share wf zip


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

piyushm
Regular Contributor II
Regular Contributor II

@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}

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


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.