BPC-Components

Home > Products > BPC PowerForms > Knowledge Base > How To Articles > Define List Queries

Define List Queries

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 :

Code

{c_ID}

{value:c_Customer}

{name:c_Country}

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 :

Code

ID BeginsWith 1 (Query Type = Text)

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 :

Code

Country Equals {value:c_Country} (Query Type = Lookup)

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 :

Code

Country Equals {name:c_Country} (Query Type = Text)

 

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)

Code

Country Equals {value:c_Country} (Type=Lookup, IgnoreBlank=YES)

or

Country Equals {name:c_Country} (Type=Text, IgnoreBlank=YES)

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 :

Code

(Priority = High OR Normal) AND (Status = Resolved OR Canceled)

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.