Filtering lists (pickers) based on field value

Facebooktwitterpinterestlinkedinmail
Applies to v8.0; author: Marek Suchowiejko

 

Description of functionality:

Filtering of available elements is a useful functionality in forms with multiple selection boxes. WEBCON BPS – Designer Studio allows you to configure the filtering of selection box elements depending on the value of form fields.

Example of implemented functionality:

An example of such filtering is entering the details of a vehicle (for example, when settling business trip expenses). When you select the car make in the “Vehicle Make” box, available models in the “Vehicle Model” box will be narrowed down to models of the selected make only.

Configuration:

First, make sure that the set of possible selection box values has been correctly defined. For example, the “VehicleMake” and “VehicleModel” SharePoint lists may look like this:

vehicles

To enable filtering vehicles by their makes, you need to add a column to the “VehicleModel” list that will link each model to its make. In order to do this, create a Lookup column which will take the user to information already present on the site (in particular to the elements of other lists).

To configure the Lookup column, you need to select the name of the SharePoint List you want to refer to, and a column in which values will be searched. It is a good idea to mark the column as mandatory, so that you prevent a situation in which a newly added car model does not have a make assigned to it.

list-marka

Once you save the changes, when adding new models to the “VehicleModel” list you will also have to select the correct make (Marka):

vectra-select

The next configuration step is to create “VehicleMake” and “VehicleModel” boxes where the value selected in the former will determine the elements available in the latter. For this purpose, select

„New Form Field” in the “Form fields” tab.

Next, name it accordingly and set as “Selection box”.

To display the desired values on the form, you need to associate the attributes with previously created SharePoint lists. In order to do this, go to the Data Sources” tab and create new sources which refer to the corresponding lists.

After you click on the button “New” and select the “SharePoint List” source, you must specify the server, website and the name of the list for which the source has been created. In our scenario, these are the “VehicleMake” and “VehicleModel” lists.

vehicle-data-source

Once you save the changes, you can assign the existing data sources to appropriate fields in the form field configuration:

vehicle-data-source-select

The next step in configuring the narrowing down of the lists is to define a CAML query responsible for filtering the results. You must enter the query in the “SQL or CAML Query” window in the “Data Source” section of the general settings of the form field that is to be filtered (here it is the “VehicleModel“).
The syntax of the CAML query that filters vehicle models by their makes is presented below:

<Where>

<Eq>

<FieldRef Name=”Coulmn name we want to filter with” />

<Value Type=”Text”>Value, we want to search for</Value>

</Eq>

</Where>

In our scenario the correct query should look like this:

CAML-filter

By using the editor and form fields tree, you can easily parameterize the query so that it takes into account any of the form fields existing in the form.

vehicle-postback

It is very important that you click “Generate postback after change of value” in the parent form field’s settings (here it is “VehicleMake“). As a result, when you select a specific make, the form will re-load and correct items will become available in the “Model” box.

Verification of operation:

Presented below is the functioning of the “Model” box before the configuration of filtering. As you can see, despite the selection of make it contains all the models from the “VehicleModel” list:

vehicle-ver1

 

The next two figures present the correct functioning of filtering the list of vehicle models by selected makes. The only models available in the “Model” field are the ones with makes assigned to them on the “VehicleModel” list which havebeen selected earlier in the “VehicleMake“box :

vehicle-ver2

vehicle-ver3

Summary:

Using CAML queries to filter SharePoint list items is very useful when you need to detail one form field with the help of another. Such a functionality is not only convenient for the users, but also guarantees they will enter correct values.

CAML queries make it possible to define various filtering criteria of SharePoint list items, including:

  • Filtering itemsby means of severallogical conditionsconnected with operatorsand or or
    • Filtering items created in a specified period of time
    • Filtering items created (modified) by a specific person
    • Using various logical conditions, such as equal, greater than, lower than, is null, contains etc.
    • Sorting the returned results by the values ​​of SharePoint list columns

In CAML queries you can dynamically refer to attributes entered in the form and that is why it is more intuitive and accessible to the user.

Leave a Reply

Your email address will not be published. Required fields are marked *