BPC-Components

Home > Products > BPC PowerForms > Knowledge Base > List Queries > Complex List Query Examples

This article contains some more advanced examples on query building :

You can read some basic instructions on how to build list query criteria HERE :

For our examples, we will use a list of Cases having the following columns :

  • Title (Single line of text)
  • Customer (Lookup to the Customers list)
  • StartDate (Datetime)
  • Status (Lookup to the Status list)
  • Priority (Choice combo)
  • Category (Lookup to the Categories list)
  • Sub Category (Lookup to the Sub Categories list)

 

Example 1 :

 

Retrieving Cases where

Code

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

We are going to use the ListQueries editor to build our list query. Open the designer, go to the List Queries section and add a new List Query. Provide a name, add the site url and list name and add the following criteria :

Code

Priority Equals High (Type = Text, Left Parentesis = Yes) OR

Priority Equals Normal (Type = Text, Right Parenthesis = Yes) AND

Status Equals Resolved (Type = Text, Left Parenthesis = Yes) OR

Status Equals Canceled (Type = Text, Right Parentesis = Yes) AND

The steps to add the criteria follow :

1st :

 

2nd

3rd

 4th

Then press OK to close the criteria editor and test your query results. 

Example 2 :

We want from inside the Customer form, to query the Cases list and retrieve the cases connected with the current Customer record.

In order to achieve this, we will have to build a List Query and bind it to a ListDataGrid control.

Moreover, we want to filter the cases by Status, so we must provide an unbound combobox that the user will use to filter results.

So we open our customer form and we add a ListDataGrid control and a ComboBox (named c_StatusFilter) to help us filter the list.

For the c_StatusFilter control, provide static values (inside the Lookup Details tab) : Assigned;Canceled;Resolved;Submitted (which are the actual display values of the Status column)

 

Then we must define the List Query that will provide data to the ListDataGrid.

Open the designer, go to the List Queries and add a new one.

Set the criteria to the following :

Code

Customer Equals {c_ID} (Type=Lookup, IgnoreBlank=No)

Status Equals {c_StatusFilter} (Type=Text, IgnoreBlank=Yes)

The above criteria have the following effect :

If we are opening an existing customer, the c_ID control will contain a value (the ID of the record) and the filter fill be applied. If we are in a new customer record, the c_ID control will be empty but the IgnoreBlank property will force the criteria to be applied and the query will not return any results.

Moreover, the c_StatusFilter combobox will also be used during the query, limiting the retrieved results, but the IgnoreBlank property set to Yes, will exclude the Status criteria when no value is selected in the combobox.

Building the List Query Criteria :

An the complete query :


NOTE : Each criteria item has a separate IgnoreBlank property which should be set according to user needs. The query itself has a separate IgnoreBlank property that is applied to every criteria item inside the query (only if set to True)

 

Binding the ListQuery to the ListDataGrid on the form :

And we have the final results :