Blog Elements

Half Sized Blog Element (Single Author Style)

Half Sized Blog Element (Multi Author Style)

Knowledge Base, List Queries, PowerForms Siverlight

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 […]

August 15, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms Siverlight

Querying SharePoint Lists and displaying results

Users can execute queries to the Sharepoint Lists and show the result in a DataGrid control. To enter the List Query editor, first enter PowerForms designer mode and select LIST_QUERIES from the drop down menu. The selection menu is highlighted red on the picture below. A form similar to the one below will appear. This is where […]

August 15, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms Siverlight

Complex List Query Examples

August 15, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms Siverlight

Querying SharePoint Lists and displaying results

August 15, 2017/by Konstantinos

Full Sized Blog Element (Big Preview Pic)

Knowledge Base, List Queries, PowerForms Siverlight

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)

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.

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 :

August 15, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms Siverlight

Querying SharePoint Lists and displaying results

Users can execute queries to the Sharepoint Lists and show the result in a DataGrid control. To enter the List Query editor, first enter PowerForms designer mode and select LIST_QUERIES from the drop down menu. The selection menu is highlighted red on the picture below. A form similar to the one below will appear. This is where List Queries are created and found using the editor.

To better understand how to create a List Query, we will try to draw data from an existing list using the editor above. Our example list is called ‘Projects’ and contains, as no surprise, various company projects.

The list contains information such as: the Project’s name, the Customer, an Estimated Start and End Date, the Actual Start and Finish Date, the Active Start and Finish Date, the Baseline Cost and Effort, the project’s Owner and finally, a short description of the project itself.

The Projects list along with some sample data is shown below:

In order to create a list query for the Project’s List, first we enter LIST_QUERIES part of our PowerForms designer and then we press the New button highlighted below:

By Default a new row appears in our available list queries, named ListQuery1, containing the information shown below:

First, we will change the Name to something more suitable. In our case, we will call our new query ‘GetProjects‘.

Then we will enter our Sharepoint’s site on the Site Url and press the Apply button. This causes the form to load the available site lists in the dropdown combobox directly below, named Lists.

Therefore, we enter our site’s url and press Apply. The lists appear when we click on the List menu. We select the list Projects and the All Items view. Ignoring all other parameters, we select the Executebutton. Data loads and is shown on the grid below the execute button.

A lot of information loads on the grid, some of which may not be needed.

There are too many columns loaded by default and in our query, only the ID, the project name, the customer and a short description of the project are required.

To limit the information, we will select to display only those columns mentioned above. To do so, we will press on the Gear Icon next to Columns.

The Setup Attributes form appears. To select data only from the columns mentioned above, we press on the New button as highlighted below. This will in turn creates a new row which will represent one of the columns we’d like to draw data from.

Pressing on the field menu reveals all of the columns contained within our Projects Sharepoint list. Browsing through, we select the field ID and press the Set button. Our action sets the Value field to display the name of our selected list column.

We follow the actions above to select the other three columns we are interested in, Title (Project name), Customer, Description. Finally, we press OK.

Going back to our designer, we select the Execute button to see the changes on our list query’s displayed data.

The information looks acurate, but we’re still not satisfied because the column titles are not what we’d like them to be.

To change the headers, we select the Gear Icon next to Headers:

The Setup Attribute Headers form appears. Selecting a row and typing a new description on the Item box changes that column’s header. Pressing the OK button validates our changes.

Again, pressing the Execute button reloads the information on the grid.

Criteria is used to filter the list query’s output. To add a new criteria, press the blue criteria label or the gear icon to enter the wizard:

NOTE : The same criteria editor can be used when defining criteria inside the “Lookup Details” tab of a lookup control (combobox, lookuppicker, etc).

The Setup Criteria wizard appears.

To start filtering the outcome, press the button. A blank Criteria appears having only the default values.

There you can enter multiple criteria combining them with the AND and OR clauses and also use parenthesis to combine criteria together and build complex queries.

For example a query like the following could be build using the available parameters :

Code

(Status = Open OR Status = Pending) AND (Priority = High OR Priority = Normal) AND Customer = ‘C1’

Ignore Blank property

There are some cases where we want to ignore the criteria defined only if the value specified is blank.

For example you have a Country combobox and a City combobox on your form.

You want to filter the City combobox with the Cities that belong to the Country inside the Country combo.

BUT, you want to retrieve ALL cities in case the Country combobox is blank.

In that case, you would have to specify lookup criteria inside the cities combo, that would filter using the Country but you would check the “Ignore Blank” property to display all cities when the country combo is blank.

We’ll try to filter the output by showing only the project with ID=’1′. To do so, we start by selecting the Target column which is ID. We set Operator as ‘Equals’ and Value as ‘1’. Finally, we press OK.

Yet again, pressing the Execute button reloads the information on the grid.

At the Lookup fields (displaying ID;#TITLE) there is a choice to show the selected Value of the lookup by typing in the Attributes field {value:COLUMN_NAME} or the selected Title by typing {name:COLUMN_NAME} instead of COLUMN_NAME that would bring both.

August 15, 2017/by Konstantinos