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

[JustSalva]

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

[rushikeshvartak]

You can use Custom Assignment block & use Custom query

[JustSalva]

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

e.g. the attribute is called managerUsername

[rushikeshvartak]

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

[avinashchhetri]

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

[JustSalva · Accepted solution]

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

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

[avinashchhetri]

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

[AshishDas]

Hi,

JustSalva,

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

[rushikeshvartak]

Do you have managerUsername field on update user request form.

[AshishDas]

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

[AshishDas]

Hi,

Any help on this would be highly appreciated

[rushikeshvartak]

What is error

[AshishDas]

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.

[rushikeshvartak]

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

[h_sapkota · Accepted solution]

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]

Thanks Hitesh.

This worked!