VPA Restrictions Entered in the Archibus Roles Table

You specify VPAs per-role, that is, in the Archibus Roles table. There are a number of options to VPAs that are summarized below.

The use of these options will become clearer when you view the examples in these "how to" topics:

• How to Enter an SQL-Type VPA Restriction on a Table 
• How to Enter a VPA Restriction for a Validated Table
• How to Enter a VPA Restriction for a Field

Archibus Role VPA Restriction Format

When specifying a restriction in the VPA Restriction field of the Archibus Roles table you use an XML format.

The VPA has three forms:

• A restriction with type sql specific to a particular table. This is used when:
• the restriction is on a single table
• the restriction must compound restrictions using OR
• the restriction must relate tables (and so must state the tablename qualifier and field names explicitly).
• A restriction with type ForValidatedTables template for a restriction that can be expanded for all tables with a given name or that hold fields that validate on the table with the given name. This is the most common form of VPA restriction.
  • The restriction is applied to all fields in the table that validate of the specified reference table.
  • If there is more than one such field in the table, the restrictions on each field are joined with an OR. For example, the em table contains bl_id and contingency_bl_id, both of which validate off the bl table, so a VPA restriction forValidatedTables on bl would be applied to the em table like this:
    ((em.bl_id = 'HQ') OR (em.contingency_bl_id = 'HQ'))
    
    
• A restriction with type ForFields template for a restriction that can be expanded for all fields with a given name. This is typically used for "generic" restrictions on non-validated fields, such as the tc_service enumeration field.
• If more than one restriction is needed, you can enter multiple restrictions, provided the restrictions are enclosed in a <restrictions> tag.

The following examples describe how to work with these VPA restrictions:

sqlRestriction VPA

Syntax

<restriction type="sql" sql="strWhereClause">
 <title translatable="true">strTitle</title>
 <field table="strTable"/>
</restriction>

Parameters

title. This is an optional description of the restriction, such as "Restrict to Archibus User Table’s Building List."

table. The table name for the VPA.

sql. The restriction for the VPA.

Example

<restriction type = "sql" sql =
"eq.site_id IN (#ASQL_GetAfmUserCachedValue('vpa_option1' ))
OR
eq.bl_id IN (#ASQL_GetAfmUserCachedValue( 'vpa_option2' ))))">
<title translatable="true">
  "Restrict Equipment to Site or Building List"</title>
<field table="eq" />
</restriction>

Usage

• For sqlRestrictions, the restriction SQL statement contains the entire set of clause or clauses including the tablename qualifier and field names (e.g. "bl.bl_id LIKE ‘HQ%'").
• The clause should be suitable for being enclosed in parentheses and AND’ed with any other view, Select Value, or program restriction.
• For sqlRestrictions,the sql can include compound conditions (e.g. "bl.bl_id = 'HQ' or bl.bl_id='WARE').
• sqlRestrictions never need to use the #ASQL_VPAField() (and cannot use this macro), since the field name is specified in the restriction (rather than being generated by the program when it applies a generic VPA to all relevant fields).

sqlRestrictionForValidatedTables VPA

Syntax

<restriction type="forValidatedTables" sql="strWhereClause ">  <title translatable="true"> strTitle </title>  <validatingTable name="site"/></restriction>

Parameters

validatingTable. The table name for the VPA. The program will apply this VPA to:

• the last primary key field of the specified table (e.g. bl.bl_id)
  
  and to
  
• all fields that validate on this table (e.g. eq.bl_id, mo.bl_id_from)

Example

<restriction type="forValidatedTables" sql="#ASQL_VPAField()LIKE 'HQ%'OR #ASQL_VPAField()LIKE 'JFK%'" >   <title translatable="true"> </title> <validatingTable name="bl"/> </restriction>

Usage

For sqlRestrictionForValidatedTables, the restriction SQL statement contains the entire set of clause or clauses; however, instead of the tablename qualifier and field name, the statement uses the #ASQL_VPAField() macro.

The program will expand this macro at runtime based on each table and field name it is applying the VPA macro to.

sqlRestrictionForFieldsNamed VPA

Syntax

<restriction type="forFields" sql="strWhereClause">
  <title translatable="true">strTitle</title> <field name="strTable"/>
</restriction>

Parameters

field. The field name for the VPA. The program will apply this VPA to all fields named identically. For instance, a VPA on field tc_service would match ca.tc_service and eq.tc_service but not a (hypothetical) field named eq.tc_service_one.

Example

#ASQL_VPAField() = 'V'"> /  <title translatable="true">Restrict access to Voice Service</title> <field name="rc_service"/> </restriction>

Usage

For sqlRestrictionForFieldsNamed, the restriction SQL statement contains the entire set of clause or clauses; however, instead of the tablename qualifier and field name, the statement uses the #ASQL_VPAField() macro.

The program will expand this macro at runtime based on each table and field name it is applying the VPA macro to (based on an exact match of the field name).

Multiple Restrictions

<restrictions>
<restriction type="forFields"
sql="#ASQL_VPAField() NOT IN (#ASQL_GetAfmUserCachedValue('vpa_option1'))">
<title translatable="true">Fields-Named Restriction on bl_id</title>
<field name="bl_id"/>
</restriction>
<restriction
type="forFields"
sql="#ASQL_VPAField() NOT IN (#ASQL_GetAfmUserCachedValue('vpa_option2'))">
<title translatable="true">Fields-Named Restriction on site_id</title>
<field name="site_id"/>
</restriction>
</restrictions>