cancel
Showing results for 
Search instead for 
Did you mean: 

Approval Workflows: select approver for a create user request based on a request attribute

JustSalva
Regular Contributor
Regular Contributor

Hi all, 

we have an use case for which we have implemented a create user request form in which the user manager can be selected from a dropdown list.

Is there a way to use that manager field (containing the manager username) to have him/her approve the user create request?

We tried to use the manager approval task but, being the user not created yet before the approval, the request is re-routed to the admin user instead.

I guess that we have to use the custom assignment task but I don't know how to access the form attribute.

Kind regards,

Matteo

15 REPLIES 15

rushikeshvartak
All-Star
All-Star

You can use Custom Assignment block & use Custom query

yes, but how can I access the form attribute in the custom query?

e.g. the attribute is called managerUsername

Use Below Query

select userkey from users where FIND_IN_SET(users.username,(select distinct 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='managerUsername'))!=0

@JustSalva,

You can try with the query shared below.

select userkey from users where username in (select distinct attribute_value as username 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='managerUsername');

 

 

Regards,
Avinash Chhetri

JustSalva
Regular Contributor
Regular Contributor

Hi all, 

Thank you! I had to do some debugging but it worked!

The 'managerUsername' variable, being mapped to the user table 'manager' column is automatically changed with the userkey of the manager, instead of the displayed manager username.

My final query is the following:

select userkey as userkey 
from users
where userkey in
(select distinct raa.attribute_value as userkey
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='managerUsername')

JustSalva_0-1664441313965.png

I've also tried to remove the outer query to remove the overhead, but it only works if the userkey is selected from the users table and not from other tables

Mapping the dynamic attribute to the owner user attribute will save the manager user name instead of the userkey.

 

 

Regards,
Avinash Chhetri

AshishDas
Regular Contributor
Regular Contributor

Hi,

JustSalva,

Does this workflow also work for Update user request. For me, it routes the request to the admin.

Do you have managerUsername field on update user request form.

Yes we have managerUsername on update user form as well. and even managers can be updated via the same form

AshishDas
Regular Contributor
Regular Contributor

Hi,

Any help on this would be highly appreciated

What is error

Hi Rushikesh,

Since there is a single workflow for both create user form and update user form.

The OOTB workflow for manager approval when used for create user form, routes the request to admin, however for update user, it goes to the actual manager.

When I use the query as mentioned above,

select userkey as userkey
from users
where userkey in
(select distinct raa.attribute_value as userkey
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='Manager')

This query when used in custom assignment, routes the create user to actual manager but does not work for update user and routes it to the admin instead.

Requirement is to route the approvals for both create and update user to the actual manager.

AshishDas_0-1669269601121.png

Use some read only field which will redirect manager block in case of create and update user

h_sapkota
New Contributor II
New Contributor II

Hi Ashish,

Can you try below query. It should work for both create user and update user.

Let me know if anything is required.

select distinct case when raa1.ATTRIBUTE_NAME='Manager' then (select userkey
from users
where userkey in
(select distinct raa.attribute_value as userkey
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='Manager')) else users.manager END as userkey
from users, request_access_attrs raa1,ars_requests ar1,request_access ra1
where ar1.REQUESTKEY = ra1.REQUESTKEY AND ra1.REQUEST_ACCESSKEY = raa1.REQUEST_ACCESS_KEY and ar1.requestkey=${ARSREQUEST.id} and ra1.userkey=users.userkey AND ra1.status=1

Regards,

Hitesh Sapkota

AshishDas
Regular Contributor
Regular Contributor

Thanks Hitesh.

This worked!