Clarity

  • 1.  Restrict the values in the filter section of an attribute, based on Group of User.

    Posted Dec 03, 2018 01:46 AM

    Hello All,

     

    I have a need where in, I have to restrict the values in the filter section of a Team Object attribute, based on the group that the user is in.

     

    Suppose if a user is in Group A, he should be able to see the values X and Y in the filter section of the Attribute.

    If the user is in Group B, he should only be able to see the value X.

     

    Can anyone provide me the query on how to do this?

     

    Thanks and Regards,

    Sai.



  • 2.  Re: Restrict the values in the filter section of an attribute, based on Group of User.

    Posted Dec 03, 2018 03:48 AM

    You can use "@WHERE:PARAM:USER_ID@" in NSQL to pick up the cmn_sec_groups.id of the logged in user, you can then join that to CMN_SEC_USER_GROUPS and then to CMN_SEC_GROUPS to check user group membership.

     

    If you are using the lookup on an object*, then you will probably want a BROWSE-ONLY clause in the query too, otherwise it will display blank for validation that the current user doe snot have (i.e. if a different user has set the value, you still want that value to display even when a user who would not be able to set the value looks at the screen - that is what "BROWSE-ONLY" does).

     

    Ref : BROWSE-ONLY Construct for Dynamic Query Lookups in the https://docops.ca.com/ca-ppm/15-5/en/administration/configure-lookups#ConfigureLookups-DefineQueries  area of the documentation

     

    * : if you are only using the lookup in a FILTER (i.e. a @WHERE:PARAM:USER_DEF field - you can ignore the comment about BROWSE-ONLY 



  • 3.  Re: Restrict the values in the filter section of an attribute, based on Group of User.

    Posted Dec 10, 2018 11:37 PM

    Hello David,

     

    I was able to write the Lookup Code successfully, but when I configured the Field from Admin Side, in the Filter Section, the filter does not work.

    where as the filter is working fine when i configured from personalize section to myself. Below is the screenshot as mentioned.

     

    Filter not working from Admin Side: -

     

    Filter working from Configure Section:-

    Below is the Lookup Code used for the "Select Team Member Type Attribute" mentioned above:-

     

    select NAME B2,lookup_code c2 from cmn_lookups_v
    where lookup_type='BUSINESS_IT' and language_code='en'
    @BROWSE-ONLY:

    AND (NOT EXISTS
    (SELECT 'business_user' B1
    FROM CMN_SEC_GROUPS SEC_GRPS
    JOIN CMN_SEC_USER_GROUPS USER_GRPS
    ON USER_GRPS.GROUP_ID=SEC_GRPS.ID AND SEC_GRPS.GROUP_CODE='unum_bus_users'
    WHERE USER_GRPS.USER_ID=@WHERE:PARAM:USER_ID@ )
    OR lookup_code='business_user')
    :BROWSE-ONLY@
    AND @FILTER@

     

    Static BUSINESS_IT lookup used in the above lookup query:-

    Thank you in Advance.

     

    /Sai.



  • 4.  Re: Restrict the values in the filter section of an attribute, based on Group of User.

    Posted Dec 11, 2018 03:41 AM

    Well the lookup loos like its working in both cases (the only difference being the admin config it is set as a drop-down but the user-config as a browse, but this does not affect the functionality).

    Would need to see the NSQL behind the query (not the lookup) to understand where it might not be working in the query (which does look strange)