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

User Creation Workflow - Custom Query using Custom Property

sab2
Regular Contributor
Regular Contributor

Hi,

We are using the user creation form. In my workflow, I am using a custom assignment > custom query to assign the approval to the users customproperty19. We are populating customproperty19 with the users organization manager email address on the creation form. I have read a few different forums posts to get ideas and have tried the below queries, but Saviynt keeps assigning the workflow approval to admin and I see in the logs the query is not working. Can someone tell me if my syntax is wrong or if I am missing anything? Much appreciated!

tested queries:

select u.customproperty19 as userkey from users u where userkey in (select customproperty19 from users where username ='${user.username}') 


select customproperty19 as userkey from users u where userkey in (select customproperty19 from users where username ='${user.username}') 


select customproperty19 as userkey from users where userkey= ${user.customproperty19}

select u.customproperty19 as userkey from users u where userkey in (select customproperty19 from users where username ='${user.username}')

 

6 REPLIES 6

sk
All-Star
All-Star

As per you statement I see you are storing organization manager email address in User CP19. Also what the Dynamic attributes name you have used on User request Form to capture that information?

select userkey from users where email ={<Dynamic attribute Name of Organization Manager Email address>}


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

Share screenshot of User Request form and dynamic attribute name


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

sab2
Regular Contributor
Regular Contributor

Thank you!

I tried the below and none worked. It assigns the approval to the admin every time. This is for the user create form.

select userkey from users where email = {TestEmail}
select userkey from users where email = ${TestEmail}
select userkey from users where email = ${users.TestEmail}
select userkey from users where email = {users.TestEmail}
select userkey from users where email = {users.customproperty19}
select userkey from users where email = ${users.customproperty19}

Error:
DEBUG println.PrintlnToLogger - Println :: DID NOT GET USERS FROM QUERY ADMIN WILL BE ASSIGNEE

select u.userkey from users u where lower(u.email) in (select lower(raa.ATTRIBUTE_VALUE) 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='DA_NAME')

 

Update dynamic attribute name


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

In that case try below query

select userkey from users where email = (select raa.ATTRIBUTE_VALUE 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='DA_NAME')


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

sab2
Regular Contributor
Regular Contributor

Thank you both. We were able to get it working using the query below.

select userkey from users where FIND_IN_SET(users.'value', (select raa.ATTRIBUTE_VALUE from request_access_attrs raa , ars_requests ar, request_access ra WHERE ra.REQUESTKEY = ar.REQUESTKEY and raa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY and raa.ATTRIBUTELABLE= 'label name' and ar.REQUESTKEY = ${ARSREQUEST.id})) !=0