Project and Portfolio Management Practitioners Forum
cancel

Rule/Validation to Get Users based on Business Units

Highlighted
Raka_1
Super Contributor.

Rule/Validation to Get Users based on Business Units

I have 2 fields User Field and Business Units. What I want is when some user selects a business unit the User field just shows 6 users to be picked from. Like i will Pick unit 'A' then it should show the names of only 6 users and then i can select any user from the list of 6. I am trying to use user data but the relation is not working. How would I create a validation list that shows only those users that are tied to a business unit. I can do it for other non user fields but not for user fields.
3 REPLIES
Mahen M
Acclaimed Contributor.

Re: Rule/Validation to Get Users based on Business Units

Can you share the Query.

What happens if you use User fields? Any errors?

Please send the snap shots.

Regards,
Mahendran M
Raka_1
Super Contributor.

Re: Rule/Validation to Get Users based on Business Units

What I have done is used USER USER_data with the validation of Business Units now so every user gets associated with a Business Unit in there profile this information is in KNTA_USERS table, similarly i used USER_DATA for the validation list of Business Units and with every validation value i associated a USER. What I am not able to get is how to consolidate everything into one validation which is driven by the field value of Business Unit. It should work like if the User selects Business Unit 'A' then in the Approver field only user 1,2,3 should be available and when the user selects Business unit 'B' then in the Approver field only users 4,5,6 should be available to be selected.
Jim Esler
Acclaimed Contributor.

Re: Rule/Validation to Get Users based on Business Units

We usually do this kind of thing based on security group membership. The validation query, derived from the standard user validations, looks something like this:

SELECT t2.username, t2.username, t2.full_name, t2.first_name,t2.last_name,t2.email_address, t2.phone_number, t1.security_group_name
FROM knta_security_groups t1, knta_users t2 ,knta_user_security t3
WHERE (UPPER(first_name) like UPPER('?' || '%')
OR UPPER(last_name) like UPPER('?' || '%')
OR UPPER(full_name) like UPPER('?' || '%')
OR UPPER(username) like UPPER('?' || '%'))
AND t1.security_group_id=t3.security_group_id
AND t2.user_id=t3.user_id AND t1.enabled_flag='Y' AND t2.end_date is null
AND security_group_name=''
[FILTER_FIELD_SQL]
ORDER BY full_name

In order to use a user data field on the user record, replace all of the security table stuff with the field name where the data is stored in the user record.