Issue with Register User Form || Manager Approval

[Manu269]

Hello Team,

We are working on Register user form and we have an attribute called manager on the form.

• Attribute Type: Single Select From SQL Query

• Values: select username as ID, CONCAT_WS(', ',IFNULL(customproperty8,lastname),IFNULL(preferedFirstName,firstname)) as inlinedescription from users where username in (select distinct owner from users where owner is not null and statuskey=1);

• We are using this manager which is selected on the form to trigger approval flow.
• This works very well when we are creating the user.
• The problem happens when we are trying to update the same user via update user request form.
• We see that workflow is unable to resolve the manager for the same and assigning the same to admin.
• Please note there is a possibility that manager may get modified during the update process.
• We are using custom assignment block with the query : select userkey from users where userkey=${manager}

[rushikeshvartak]

select u.userkey from request_access_attrs raa, request_access ra, Users u where u.username = raa.ATTRIBUTE_VALUE and raa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY and ra.REQUEST_ACCESSKEY = ${REQUESTACCESS.id} and raa.attribute_name = 'manager'

[Manu269]

It now got assigned to admin at creation stage only

[rushikeshvartak]

I hope you updated dynamic attribute name in query

[Manu269]

Yes, my DA name is manager

[rushikeshvartak]

Run query from data analyzer and validate. What is value whether its username or userkey accordingly update query 

select u.userkey from request_access_attrs raa, request_access ra, Users u where u.userkey = raa.ATTRIBUTE_VALUE and raa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY and ra.REQUEST_ACCESSKEY = ${REQUESTACCESS.id} and raa.attribute_name = 'manager'

[Manu269 · Accepted solution]

I was able to fix the issue @rushikeshvartak .

We have to user userkey instead of userame.

Adding updated query :

select u.userkey from request_access_attrs raa, request_access ra, Users u where u.userkey= raa.ATTRIBUTE_VALUE and raa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY and ra.REQUEST_ACCESSKEY = ${REQUESTACCESS.id} and raa.attribute_name = 'manager'

[rushikeshvartak]

Good base query worked and you got way forward 

[Manu269]

@rushikeshvartak there is few more issue as we see.

In my form there are more than 7-8 attributes apart from manager which can be updated.

Now the probelm is when any other atrribute is modified apart from manager the request goes for admin approval.

Can you assist how to handle?

[rushikeshvartak]

Use union and get values from users table when manager attributes not exists in request access table 

[Manu269]

any sample that can be referred?

[rushikeshvartak]

select u.userkey from request_access_attrs raa, request_access ra, Users u where u.userkey= raa.ATTRIBUTE_VALUE and raa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY and ra.REQUEST_ACCESSKEY = ${REQUESTACCESS.id} and raa.attribute_name = 'manager' union select userkey from users where userkey=${manager}