Configure Cascading Value Lists

Cascading value lists are configured by writing a VBScript function for each property that will retrieve only the values related to the value already selected for a different property. The functions are based on the Meridian GetValues methods. The functions are typed as a validation rule for each property.

The following example procedure creates four levels of cascading value lists and assumes that a standard value table named Clients is used to store the data. The example data is shown in the table in Cascading Value Lists. A standard value query may also be used by specifying the Vault.Query property instead.

To configure cascading value lists:

In Configurator, on the Edit menu, select Edit Events.

The Meridian Enterprise Script Editor appears, showing the vault's existing VBScript event procedures, functions, and other code. Learn more about custom scripting.
Scroll to the bottom of the existing code, and create a new function similar to the following code to present a list of values for the first (top) level property (Name) of the cascading value lists:
```
Function GetClients
  GetClients = Vault.Table ("Clients").GetValues (Null, Null, "Name", True, "Name")
End Function
```
This function retrieves all values from the Name column of the Clients table.
Click OK.
Select This expression and type GetClients on the Validation page of the ClientName property.
On a new empty line in the Meridian Enterprise Script Editor, create a new function similar to the following code to present a list of values for the next lower (second) level property (ProjectNumber) of the cascading value lists:
```
Function GetClientProjects
  GetClientProjects = Vault.Table ("Clients").GetValues ("Name", Document.ClientName, "Project", True, "Project")
End Function
```
This function retrieves only values from the Project column of the Clients table based on the value selected for Document.ClientName at that moment.
Click OK.
Select This expression and type GetClientProjects on the Validation page of the ProjectNumber property.
On a new empty line in the Meridian Enterprise Script Editor, create a new function similar to the following code to present a list of values for the next lower (third) level property (ProjectNumber) of the cascading value lists:
```
Function GetClientProjectContracts
  GetClientProjectContracts = Vault.Table ("Clients").GetValues (Array ("Name", "Project"), Array (Document.ClientName, Document.ProjectNumber), "Contracts", True, "Contracts")
End Function
```
This function retrieves only values from the Contracts column of the Clients table based on the value selected for Document.ClientName and Document.ProjectNumber at that moment.
Click OK.
Select This expression and type GetClientProjectContracts on the Validation page of the ContractNumber property.
On a new empty line in the Meridian Enterprise Script Editor, create a new function similar to the following code to present a list of values for the next lower (fourth and final) level property (ContactName) of the cascading value lists:
```
Function GetClientProjectContractContacts
  GetClientProjectContractContacts = Vault.Table ("Clients").GetValues (Array ("Name", "Project", "Contract"), Array (Document.ClientName, Document.ProjectNumber, Document.ContractNumber), "ContactName", True, "ContactName")
End Function
```
This function retrieves only values from the ContractName column of the Clients table based on the value selected for Document.ClientName, Document.ProjectNumber, and Document.ContractNumber at that moment.
Click OK.
Select This expression and type GetClientProjectContractContacts on the Validation page of the ContactNames property.

In all of the preceding example functions, the fourth argument of the GetValues method is True. This is to ensure that the [Distinct] SQL query argument is also True, which ignores duplicate values.

Note:

When using column names that contain spaces, you must enclose the column name within square brackets, for example: "[Column name with spaces]".