List Query Criteria

List Query Criteria


List Queries is a common method to load results from a SharePoint list.

List Queris can be specified using the LIST-QUERIES section of the runtime designer or when defining parameters for lookup controls (inside the Lookup Details tab or the control properties editor).

For lookup controls, you can use the following option :

 


 

For List Queries, the specific editor contains the same option :


 

 

Please note that you can now use the {url} keyword instead of the full site/sub-site url path in the Site Url parameter. See here for more details

 

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

 

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)



NOTE : Nested parenthesis are not supported when building complex queries.

 

 

Clause

 

The clause is the operator that binds criteria together.

Availble values : AND, OR