How to Enter an SQL-Type VPA Restriction on a Table

Use this type of restriction when you wish to apply a restriction to a particular table in a view. The SQL VPA restriction can include compound conditions joined with an OR. This example shows how to restrict tradespersons to specific values in the Craftspersons table.

If you want more than one restriction clause, use: <restrictions></restrictions>

VPA applies to primary keys of the main table, and on fields in the main table which validate on the VPA table.

Example

  1. Add a new Role record or update an existing one. In this example, we use the Z-VPA-TBL role.
  2. In the VPA Restriction field for the role, enter the following

<restriction
type="sql"
sql="cf.tr_id IS #ASQL_GetAfmUserCachedValue('vpa_option1') OR
cf. tr_id LIKE #ASQL_GetAfmUserCachedValue('vpa_option2') OR
cf. tr_id IN (#ASQL_GetAfmUserCachedValue('vpa_option3'))">
<title translatable="true">Table-Specific Restriction on cf</title>
<field table="cf"/>
</restriction>

This restricts all tr_id fields in the cf table to be one of the following (based on entries in the afm_users VPA-Option fields):

VPA Option 1:  NULL
VPA Option 2:  M%
VPA Option 3 :  ELECTRICIAN-I,ELECTRICIAN-II

  1. Assign the Z-VPA-TBL role to a user. In the HQ sample data, this role is assigned to user Z-VPA-ROLE-TBL. Turn on the VPA Option 1, VPA Option 2, and VPA Option 3 fields and notice the values shown above appear in the fields.
  2. Open the Craftspersons view. Notice the view presents records that meet the criteria.

Note : You should not add your own quotes when editing the VPA Option 1, VPA Option 2, VPA Option 3. fields. You should not use an expression that does not expect the quotes. Notice that a "NULL" literal value evaluates to "NULL" -- that is, it has no quoting or translation.