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

Use this type of restriction when you wish to apply a restriction to a particular validated table in a view. This example shows how to restrict Building Code foreign key values to entries that start with HQ or JFK.

1. Add a new Role record or update an existing one. This example uses the Z-VPA-VALTBLS role.

2. In the VPA Restriction field, enter the following. This restricts all Building Code foreign key values to those that start with HQ or JFK.

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

3. Assign this role to a user. In the HQ sample data, this restriction is assigned to user Z-VPA-ROLE-VALTBLS.

4. Log in as user Z-VPA-ROLE-VALTBLS.
5. Open a view that contains the Building Code field as a foreign key, such as wr.
6. Notice that only buildings that start with HQ and JFK are presented.

Note: If you want retrict the employee field against the BL table and you are not using Emergency Preparedness application, remove the Validating Table reference between the Contingency Building Code (em.contingency_bl_id) and the Buildings table. Otherwise, before the Employee record displays, the standard restriction will require that an employee is in both places -- an unlikely condition.

If you are using the Emergency Preparedness application, use an SQL VPA restriction instead to get the restriction you intend (typically between the em.bl_id field and not the em.contingency_bl_id field).