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 :
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.
With this property you define the kind of query you want to perform on the target column.
Available options are :
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 :
{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.
The Type property defines how the value provided will be handled.
Available values :
If the "Text" type is used a textual comparison will be performed.
for example a query like the following :
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 :
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 :
Country Equals {name:c_Country} (Query Type = Text)
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)
Country Equals {value:c_Country} (Type=Lookup, IgnoreBlank=YES)
or
Country Equals {name:c_Country} (Type=Text, IgnoreBlank=YES)
These properties are used to group criteria together and perform complex queries.
For example queries like the following can be performed using these properties :
(Priority = High OR Normal) AND (Status = Resolved OR Canceled)
NOTE : Nested parenthesis are not supported when building complex queries.
The clause is the operator that binds criteria together.
Availble values : AND, OR