Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

validation based on security group(s)

SOLVED
Go to solution
Highlighted
Andy McMahon_1
Collector

validation based on security group(s)

Hello guys. 

How would I create a validation based on a security group?  

For instance, in my 'project manager' field, I'd love it to only show project managers...

Also, is it possible to do a validation on 2 security groups?  

8 REPLIES
Erik Cole
Honored Contributor
Solution

Re: validation based on security group(s)

Here's one way...an ACL with sql:

 

SELECT u.user_id, u.full_name, u.username, u.email_address FROM knta_users_v u, knta_user_security_v kus
      where u.user_id = kus.user_id
and kus.security_group_name = 'Project Manager'
     AND (   UPPER (u.first_name) LIKE UPPER ('?' || '%')
          OR UPPER (u.last_name) LIKE UPPER ('?' || '%')
          OR UPPER (u.full_name) LIKE UPPER ('?' || '%')
          OR UPPER (u.username) LIKE UPPER ('?' || '%')
         )
         [FILTER_FIELD_SQL]
     AND NVL (u.end_date, CURRENT_DATE) >= CURRENT_DATE
ORDER BY u.full_name

 

 

For two, you could do something like:

 

and kus.security_group_name IN ('Group A','Group B')

Andy McMahon_1
Collector

Re: validation based on security group(s)

So would I replace 

and kus.security_group_name IN ('Group A','Group B')

with

and kus.security_group_name IN ('Group A','Group B')

 

but keep the 

    AND (   UPPER (u.first_name) LIKE UPPER ('?' || '%')
          OR UPPER (u.last_name) LIKE UPPER ('?' || '%')
          OR UPPER (u.full_name) LIKE UPPER ('?' || '%')
          OR UPPER (u.username) LIKE UPPER ('?' || '%')

)

 

Under it, or would I get rid of that? 

Erik Cole
Honored Contributor

Re: validation based on security group(s)

Keep it - that's what lets it do incremental search in the text box when you type a partial name.
Andy McMahon_1
Collector

Re: validation based on security group(s)

Thank you!

 

and, this is the SQL I'm using: 

SELECT  

u.user_id, 

u.full_name, 

u.username, 

u.email_address

 FROM

knta_users_v u, 

knta_user_security_v kus      

where u.user_id = kus.user_id

and kus.security_group_name IN ('OPHPR Project Managers (C)','OPHPR Project Managers (FTE)')

AND (UPPER (u.first_name) LIKE UPPER ('?' || '%')         

  OR UPPER (u.last_name) LIKE UPPER ('?' || '%')         

  OR UPPER (u.full_name) LIKE UPPER ('?' || '%')              

  OR UPPER (u.username) LIKE UPPER ('?' || '%')         

)        

[FILTER_FIELD_SQL]     

AND NVL (u.end_date, CURRENT_DATE) >= CURRENT_DATE

ORDER BY u.full_name

 

however I'm getting  a "SQL Command not ended properly error when I try to run it in SQL Runner, and it also errors out when I create the validation and test it in a form.   

Erik Cole
Honored Contributor

Re: validation based on security group(s)

Have to remove [FILTER_FIELD_SQL] when you run it in SQL Runner. What error does it throw in PPM? You might remove it from there as well if you don't add any other filter fields to the validation...

Andy McMahon_1
Collector

Re: validation based on security group(s)

That did it.  The only issue is the column headers are 'no value', but I think if I search deep in my SQL skills I can remember that.  Thanks a lot! 

Andy McMahon_1
Collector

Re: validation based on security group(s)

Dang, I accepted the wrong thing as solution. Can this be corrected? 

dirkf
Honored Contributor

Re: validation based on security group(s)

Hi Andy,

 

I removed the 'solution'-mark. You can set it correctly now.

 

Best regards,

Dirk

//Add this to "OnDomLoad" event