Blog Single Author Fullwidth

List Queries is a common method to load results from a SharePoint list (either in the same or any other site of the current site collection).

List Queries can be specified using the LIST-QUERIES section of the runtime designer.

Name

Provide a name that will be used to identify the list query in other parts of the designer

Site Url

Set the url of the site that hosts the target list. Hit the “Apply” button in order to load all lists in the specified SharePoint site.

List

Select the target list

View

Optionally you may select a specific view to load data from.

In case a view is selected, the sort ordering defined for that view is used and the “Sort By” parameter is ignored.

Moreover, any criteria defined for the view and added to the criteria of the list query.

Criteria

To specify the records you want to retrieve, you have to setup your lookup criteria using the Criteria Editor.

In this criteria editor, you can specify multiple criteria for querying the target list.

For each one, you should specify the following properties :

Target Column

Represents the list column that you want the criteria to apply.

Every column of the target list is available in a dropdown list. The inetrnal names of the columns are used.

Operator

With this property you define the kind of query you want to perform on the target column.

Available options are :

• Equals
• BeginsWith
• Contains
• Greater
• GreaterOrEqual
• Less
• LessOrEqual
• NotEquals
• IsNull
• IsNotNull
• In (available in SP2010)

Value

In this property, you should type the value that should be used to compare column data with.

The value can be either static or calculated.

For calculated values, you can use control data like the following examples :

where the “value:” prefix returns the ID of a lookup control and the “name:” prefix returns the display member of that control.

Type

The Type property defines how the value provided will be handled.

Available values :

• Text
• Counter
• Number
• DateTime
• Lookup

If the “Text” type is used a textual comparison will be performed.

for example a query like the following :

will retrieve results with ID = 1 or 12 or 14, although we are dealing with a numeric data type.

The “Counter” and “Number” types perform numeric comparison.

The “DateTime” type uses Date comparison for available column data.

The “Lookup” type should be used when querying a Lookup column and the ID of the column is provided.

for example :

assuming that the c_Country control is a lookup control loading country data.

If you want to perform a query on the Title of the country, you should something like the following :

Ignore Blank

Use this property if you want to apply criteria only if the value provided is not blank.

If the value is blank, the criteria will be skipped.

For example, you may want to add a combobox with Cities (c_City) in your form and you want to filter cities be their country which is another combobox on the same form (c_Country)

The tricky part is that you want to load ALL available cities in the cities combobox when the country combo is blank.

In the above scenario, you should add the following criteria in the cities combobox (in the Lookupp Details tab)

LeftParenthesis – RightParenthesis

These properties are used to group criteria together and perform complex queries.

For example queries like the following can be performed using these properties :

Clause

The clause is the operator that binds criteria together.

Availble values : AND, OR

Columns

The columns that will be retrieved from the target list must be specified here, separated by semicolon.

The internal column names must be used.

Headers

Optionally, you can define column headers for the retrieved data.

These headers will be used when the list query will be bound to a DataGrid control and only if no specific Fields have been specified for that control.

Sort By

Define the column that will be used to sort data.

Ascending

Set if the sort order will be ascending or descending

Max Rows

Defines the number of results returned from a list query. 0 returns all records, all numbers greater than 0 will limit the displayed results respectively.

One of the most common scenarios when designing a custom form, is the requirement to enable/disable a specific control (or set of controls) based on a) the values of other controls, b) the user using the form.

For our example, we have a list with the following columns :

Customer (lookup column referencing the Customers list)

Priority (Choice column with values : Normal, High, Low)

The scenario requires that if a specific customer is selected, the Priority ComboBox should be set to Priority=High and should be disabled for the user.

When first opening the form, it should show something like the following :

You can enable or disable controls using 2 different approaches :

Enabled Formula

Open the designer and select the Priority ComboBox properties.

In the Enabled formula add the following expression :

NOTE : In the expression above, we only check for the display member of the combobox value. If we wanted to check for the value member (ID) we would have to write something like :

When the user selects a different customer, the Priority combo is enabled :

But if we select the customer appearing in the formula, the control is automatically disabled :

Now to completely meet the requirements, we have to set the value to “High”.

This can be accomplished using a simple script inside the Value-Change event of the Customer ComboBox.

The script should be something like the following :

The form.Loaded condition is used because we want this script to be executed only after the user changes the value and not during form initialization (the value-change events are raised during form loading).

Using script

The same thing can be accomplished without using the Enabled formula but by script alone.

Here is the changed script that we should use to have the same effect.

The script should handle the value-change event of the Customer ComboBox.

Notes :

In the above script, we check the customer value and we set the Enabled property of the Priority control.

We set the default value only when the event was raised from a user action and not due to form initialization.