User Creation Workflow - Custom Query using Custom Property

[stephbres]

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

[sk]

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

[rushikeshvartak]

Share screenshot of User Request form and dynamic attribute name

[stephbres]

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

[rushikeshvartak]

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

[sk]

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

[stephbres · Accepted solution]

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