Configure allowed values filtering for multi-column drop-downs

The procedure described in this sample configuration topic applies to the following modules: Downtime, Production, Maintenance, Quality, Knowledge, Planning, Energy.

This topic provides an example of how to set up custom fields with drop-down lists populated by an OLE-DB connection with allowed values filtering. Allowed values filtering makes it possible for values in the drop-down list to change depending on the values of other fields in that same record.

In the following screen, the selections of Body and Make determine that only fields with the selected values appear in the multi-column drop-down table.

Note: Only custom fields can be set up for allowed values filtering as only custom fields have the AllowedValuesFilterFields property. However, standard fields such as Cause and Location can be used as source fields into the custom field.

Sample configuration: Allowed values filtering for multi-column drop-downs

In this sample configuration, you are going to configure a multi-column lookup list with allowed values filtering. This procedure can be performed on a custom field in any supported module; however, this example uses a Production reporting point.

Note: The parameter names, fields, column names in the properties, and the returned columns from the Stored Proc are all case sensitive.

Studio configuration

1. Create a Production reporting point with three custom fields: Body, Make, Model.

   

2. On each of the fields, set the following properties:

   Property	Value
   DataType	String
   AllowedValuesType	OleDb

   The AllowedValuesFilterFields property appears.

   

3. Set the following properties for the Body field.

   Property	Value
   AllowedValuesFilterFields	Make, Model
   AllowedValuesKeyColumn	BodyTypeColumn
   AllowedValuesDisplayColumn	BodyTypeColumn, MakeColumn, ModelColumn

4. Set the following properties for the Make field.

   Property	Value
   AllowedValuesFilterFields	Body, Model
   AllowedValuesKeyColumn	MakeColumn
   AllowedValuesDisplayColumn	BodyTypeColumn, MakeColumn, ModelColumn

5. Set the following properties for the Model field.

   Property	Value
   AllowedValuesFilterFields	Body, Make
   AllowedValuesKeyColumn	ModelColumn
   AllowedValuesDisplayColumn	BodyTypeColumn, MakeColumn, ModelColumn

Setting up Ole-DB data source

In order for this field to populate its allowed values, point the AllowedValuesSource property to an OleDBAdapter.

6. Add an Ole-DB data source to the Studio project.

7. Under the data source, add an Ole-DB connection.

8. Assign permissions on the Ole-DB Connector to all users who access this function. The multi-column drop-down menu appears empty if the user does not have admin permissions or specific permissions to access the Ole-DB Connector.

   

9. Add three Ole-DB Adapter items and name them GetBody, GetMake, and GetModel.

10. For each adapter, under the SelectCommand property, next to Parameters, Click Browse (...) to open the Collection Editor and define the collections of parameters. It is important to check that the order of these parameters, as entered in the SelectCommand property, matches the order of the parameters in the stored procedure or SQL statement.

    For the GetBody adapter:

    Property	Value
    CommandText	EXEC SP_GetBody ?, ?
    Parameters	Make, Model

For the GetMake adapter:

For the GetModel adapter:

Setting up SQL database

The logic in determining the allowed values based on other field values is handled by means of a SQL query or stored procedure.

A simple example would be to set up a SQL table to be used as a matrix. In this example we have three fields; Make, Model and Body Type, where the allowed values of each field are dependent on the other two fields.

You can query the table directly with an SQL statement or more conveniently with a stored procedure. The stored procedure is called with the CommandText property in the adapter. The stored procedure or SQL query may also have to handle scenarios where one or more fields have an empty value.

View the Multi-Column List

After your project is properly configured, the allowed values of a drop-down list box change in accordance to the values of dependent fields.

Multi-column list without filtering applied

In this screen, there is no filtering applied to the data supplied by the external SQL database.

Multi-column list with filtering applied

In this screen, the selections made in the Body and Make fields applies filtering to the available data.

Related topics

• Add and configure an OLE-DB data source

• Add and configure an OLE-DB data connection

• About the OLE-DB adapter