Create and Update User Form Query to return the manager

[AshishDas]

Hi,

We have configured a workflow for the create and update user form wherein we have written a custom query which returns the manager to whom the request should go for approval.

Use Case :

1. Whenever a create user request is submitted, it should go to the manager that has been selected by the requestor while filling the create user form.

2. When a user is updated via the update user form and a new manager is selected, the approval should route to the new manager selected in the update user form

3. When any other property of a  user is updated via the update user form (eg: mobile number or termdate) the approval should route t the old manager.

In our current query, point 1 and point 3 is achieved. For point 2, the approval is going to the old manager instead of the new one.

Please let me know if i am missing something in the query and the query can be improvised. Any help is appreciated.

select distinct case when (select distinct raa3.attribute_value as source
from request_access_attrs raa3 , ars_requests ar3, request_access ra3
where ar3.REQUESTKEY = ra3.REQUESTKEY and ra3.REQUEST_ACCESSKEY = raa3.REQUEST_ACCESS_KEY
and ar3.requestkey=${ARSREQUEST.id} and raa3.ATTRIBUTE_NAME ='sourceaction')='createUserRequest' 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' )) when raa1.ATTRIBUTE_VALUE ='updateUserRequest' then (select u1.manager
from users u1, request_access_attrs raa2,ars_requests ar1,request_access ra1
where ar1.REQUESTKEY = ra1.REQUESTKEY AND ra1.REQUEST_ACCESSKEY = raa2.REQUEST_ACCESS_KEY and ar1.requestkey=${ARSREQUEST.id} and ra1.userkey=u1.userkey AND ra1.status=1 AND raa2.ATTRIBUTE_VALUE ='updateUserRequest') else null END as userkey from request_access_attrs raa1 

[sudeshjaiswal]

Hello @AshishDas ,

You May try the below query and check if it works,

SELECT DISTINCT
                CASE
                                WHEN
                                                (
                                                                SELECT DISTINCT raa3.attribute_value AS source
                                                                FROM            request_access_attrs raa3 ,
                                                                                ars_requests ar3,
                                                                                request_access ra3
                                                                WHERE           ar3.requestkey = ra3.requestkey
                                                                AND             ra3.request_accesskey = raa3.request_access_key
                                                                AND             ar3.requestkey=${ARSREQUEST.id}
                                                                AND             raa3.attribute_name ='sourceaction')='createUserRequest' 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' ))
                                WHEN raa1.attribute_value ='updateUserRequest' THEN
                                                (
                                                       SELECT u1.manager
                                                       FROM   users u1,
                                                              request_access_attrs raa2,
                                                              ars_requests ar1,
                                                              request_access ra1
                                                       WHERE  ar1.requestkey = ra1.requestkey
                                                       AND    ra1.request_accesskey = raa2.request_access_key
                                                       AND    ar1.requestkey=${ARSREQUEST.id}
                                                       AND    ra1.userkey=u1.userkey
                                                       AND    ra1.status=1
                                                       AND    raa2.attribute_value ='updateUserRequest')
                                WHEN
                                                (
                                                                SELECT DISTINCT raa4.attribute_value AS source
                                                                FROM            request_access_attrs raa4 ,
                                                                                ars_requests ar4,
                                                                                request_access ra4
                                                                WHERE           ar4.requestkey = ra4.requestkey
                                                                AND             ra4.request_accesskey = raa4.request_access_key
                                                                AND             ar4.requestkey=${ARSREQUEST.id}
                                                                AND             raa4.attribute_name ='sourceaction')='updateUserRequest' THEN
                                                (
                                                       SELECT u2.userkey
                                                       FROM   users u2
                                                       WHERE  u2.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 NULL
                END AS userkey
FROM            request_access_attrs raa1


You may need to trace the logs and do the query modification as necessary.

Thanks.

[AshishDas]

Hi Sudesh,

Thank you for the help and effort.

However, for point number two (When a user is updated via the update user form and a new manager is selected, the approval should route to the new manager selected in the update user form), it is not working.

It is returning both the old and the new manager userkey. It should return only the new manager's user key.

Attached screenshot of the result of the query:

[yogesh]

Assuming you have mapped the manager field on the form to user.owner attribute , you can use below condition:

This will return true if owner attribute is updated on the form, and false if not. You can handle approvals accordingly in each branch.

[AshishDas]

Hi yogesh,

We followed both the format given by you as well as the format given in Help section of workflows:

There is an error parsing the syntax. Seems the keyword null does not work

This is what has been configured:

[yogesh]

This exact condition has been working for us from v2021 and all upgrades till now (v23.3)

dynamicAttributes.get('owner') != null and dynamicAttributes.get('owner') != '' and user.owner != dynamicAttributes.get('owner')

Not sure why it is not working for you (v23.4)

Maybe something is different in v23.4 but I dont have access to this version so I can not test.

[rushikeshvartak]

There is one extra closing bracket

[sudeshjaiswal]

Hello @AshishDas,

Can you please try to use the below query, 
SELECT DISTINCT
CASE
WHEN (
SELECT DISTINCT raa3.attribute_value AS source
FROM request_access_attrs raa3,
ars_requests ar3,
request_access ra3
WHERE ar3.requestkey = ra3.requestkey
AND ra3.request_accesskey = raa3.request_access_key
AND ar3.requestkey = ${ARSREQUEST.id}
AND raa3.attribute_name = 'sourceaction'
) = 'createUserRequest' 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'
)
)
WHEN raa1.attribute_value = 'updateUserRequest' THEN (
SELECT DISTINCT
CASE
WHEN EXISTS (
SELECT 1
FROM request_access_attrs raa4
WHERE raa4.attribute_name = 'Manager'
AND raa4.request_access_key = ra1.request_accesskey
) THEN (
SELECT raa4.attribute_value
FROM request_access_attrs raa4
WHERE raa4.attribute_name = 'Manager'
AND raa4.request_access_key = ra1.request_accesskey
)
ELSE u1.manager
END AS manager
FROM users u1,
request_access_attrs raa2,
ars_requests ar1,
request_access ra1
WHERE ar1.requestkey = ra1.requestkey
AND ra1.request_accesskey = raa2.request_access_key
AND ar1.requestkey = ${ARSREQUEST.id}
AND ra1.userkey = u1.userkey
AND ra1.status = 1
AND raa2.attribute_value = 'updateUserRequest'
)
END;

Thanks,