Topic
  • 6 replies
  • Latest Post - ‏2013-01-04T15:54:10Z by SystemAdmin
SystemAdmin
SystemAdmin
5842 Posts

Pinned topic Completely resrict visibility of a set of records?

‏2013-01-02T21:12:08Z |
This is a challenge that I am addressing in 6.2 using QuickPick, but would like to use native functionality (Conditional UI, Data Restrictions, Automation Scripting) when we move to 7.5.0.3:

We have company records that are really employees (companies.nstype = 'EMPLOYEE').
We need to restrict access to these records based on membership in a Person or Security Group ('AP' or 'FINANCE').
Only members of these groups can even see that records exist for these companies (Companies, Invoice, PO, PR, Contract).

This is not a need to hide fields on a record, but to appear that there is no record.

My "Restrict" rule in QuickPick looks like this:
select 1 from dummy_table
where <USERNAME> not in
(select respparty from persongroupteam where persongroup in ('AP','ADMIN','PURCH'))

Any ideas?
Thanks, Brian
Updated on 2013-01-04T15:54:10Z at 2013-01-04T15:54:10Z by SystemAdmin
  • SASHULL
    SASHULL
    281 Posts

    Re: Completely resrict visibility of a set of records?

    ‏2013-01-03T01:50:07Z  
    This sounds like a good example of a using data restrictions in 7.1 and later. I would create a global data restriction at the object level for the companies table and make it qualified (if you make it hidden, records that match the criteria are shown as masked characters (XXXX for instnace), whereas qualified means only records that meet that criteria are accessible).

    Then associate a condition with it like :nstype'EMPLOYEE' or exists(SELECT 1 FROM groupuser WHERE groupname IN('AP', 'FINANCE') and userid=:&USER&)

    The :&USER& in a conditional expression will get the userid of the current user dynamically at run time.

    Global Data Restrictions are configured in the security groups application (Select Action->Global Data Restrictions) or you could associate it with individual security groups if desired (you just have to remember that as new groups are created that restriction is copied as well).
  • SystemAdmin
    SystemAdmin
    5842 Posts

    Re: Completely resrict visibility of a set of records?

    ‏2013-01-03T16:32:41Z  
    • SASHULL
    • ‏2013-01-03T01:50:07Z
    This sounds like a good example of a using data restrictions in 7.1 and later. I would create a global data restriction at the object level for the companies table and make it qualified (if you make it hidden, records that match the criteria are shown as masked characters (XXXX for instnace), whereas qualified means only records that meet that criteria are accessible).

    Then associate a condition with it like :nstype'EMPLOYEE' or exists(SELECT 1 FROM groupuser WHERE groupname IN('AP', 'FINANCE') and userid=:&USER&)

    The :&USER& in a conditional expression will get the userid of the current user dynamically at run time.

    Global Data Restrictions are configured in the security groups application (Select Action->Global Data Restrictions) or you could associate it with individual security groups if desired (you just have to remember that as new groups are created that restriction is copied as well).
    Thanks Steve, let me give it a shot.
  • SystemAdmin
    SystemAdmin
    5842 Posts

    Re: Completely resrict visibility of a set of records?

    ‏2013-01-03T18:05:15Z  
    Thanks Steve, let me give it a shot.
    OK, here we go (rather than confusing everyone with my custom data, this refers to maxDemo):

    -Global data restriction on "COMPANIES"
    -Against a Conditional Expression (1003):
    :company 'BURSAW'
    OR
    exists(SELECT 1 FROM groupuser WHERE groupname IN ('FINANCE') and userid=:USER)
    -Works great in "COMPANY" application, but has no impact in "PO" application

    -Global data restriction on "PO"
    -Against a Conditional Expression (1004):
    :vendor 'BURSAW'
    OR
    exists(SELECT 1 FROM groupuser WHERE groupname IN ('FINANCE') and userid=:USER)
    -Works great in "PO" application

    BUT, when I attempt to use the relationship "VENDOR" from PO to COMPANIES
    -Global data restriction on "PO"
    -Against a Conditional Expression (1004):
    :vendor.type 'V'
    OR
    exists(SELECT 1 FROM groupuser WHERE groupname IN ('FINANCE') and userid=:USER)

    I throw the following exceptions:

    BMXAA4019E - The expression
    :vendor.type 'V'
    OR
    exists(SELECT 1 FROM groupuser WHERE groupname IN ('FINANCE') and userid=:USER)
    cannot be converted to a WHERE clause. Change the expression so that it will properly convert to a WHERE clause.

    BMXAA4018E - The bind variable vendor.type could not be converted to a field name in a WHERE clause. The relationship name or owner keyword cannot be used in a condition for the QUALIFIED
    restriction.

    The condition passes validation in the Conditional Expression Manager against "PO" just fine. Simplifying the condition doesn't address the issue either.

    Thanks, Brian
  • SystemAdmin
    SystemAdmin
    5842 Posts

    Re: Completely resrict visibility of a set of records?

    ‏2013-01-04T15:15:46Z  
    OK, here we go (rather than confusing everyone with my custom data, this refers to maxDemo):

    -Global data restriction on "COMPANIES"
    -Against a Conditional Expression (1003):
    :company 'BURSAW'
    OR
    exists(SELECT 1 FROM groupuser WHERE groupname IN ('FINANCE') and userid=:USER)
    -Works great in "COMPANY" application, but has no impact in "PO" application

    -Global data restriction on "PO"
    -Against a Conditional Expression (1004):
    :vendor 'BURSAW'
    OR
    exists(SELECT 1 FROM groupuser WHERE groupname IN ('FINANCE') and userid=:USER)
    -Works great in "PO" application

    BUT, when I attempt to use the relationship "VENDOR" from PO to COMPANIES
    -Global data restriction on "PO"
    -Against a Conditional Expression (1004):
    :vendor.type 'V'
    OR
    exists(SELECT 1 FROM groupuser WHERE groupname IN ('FINANCE') and userid=:USER)

    I throw the following exceptions:

    BMXAA4019E - The expression
    :vendor.type 'V'
    OR
    exists(SELECT 1 FROM groupuser WHERE groupname IN ('FINANCE') and userid=:USER)
    cannot be converted to a WHERE clause. Change the expression so that it will properly convert to a WHERE clause.

    BMXAA4018E - The bind variable vendor.type could not be converted to a field name in a WHERE clause. The relationship name or owner keyword cannot be used in a condition for the QUALIFIED
    restriction.

    The condition passes validation in the Conditional Expression Manager against "PO" just fine. Simplifying the condition doesn't address the issue either.

    Thanks, Brian
    :vendor.type 'V'
    OR
    exists(SELECT 1 FROM groupuser WHERE groupname IN ('FINANCE') and userid=:USER)

    Should be
    :vendor.type='V'
    OR
    exists(SELECT 1 FROM groupuser WHERE groupname IN ('FINANCE') and userid=:USER)
  • SystemAdmin
    SystemAdmin
    5842 Posts

    Re: Completely resrict visibility of a set of records?

    ‏2013-01-04T15:43:34Z  
    :vendor.type 'V'
    OR
    exists(SELECT 1 FROM groupuser WHERE groupname IN ('FINANCE') and userid=:USER)

    Should be
    :vendor.type='V'
    OR
    exists(SELECT 1 FROM groupuser WHERE groupname IN ('FINANCE') and userid=:USER)
    Sorry, typo in by post.
  • SystemAdmin
    SystemAdmin
    5842 Posts

    Re: Completely resrict visibility of a set of records?

    ‏2013-01-04T15:54:10Z  
    Sorry, typo in by post.
    Thanks for the help!

    I got it to work by burying the relationship in the SQL:

    exists(SELECT 1 FROM companies WHERE isnull(taxexemptnum,'x') '111' and company=:vendor)
    OR
    exists(SELECT 1 FROM groupuser WHERE groupname IN ('FINANCE') and userid=:USER)