Blog Elements

Half Sized Blog Element (Single Author Style)

Half Sized Blog Element (Multi Author Style)

Knowledge Base, List Queries, PowerForms HTML

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 13, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms HTML

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

August 13, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms HTML

Querying SharePoint Lists and displaying results

August 13, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms HTML

List Query Criteria

August 13, 2017/by Konstantinos

Full Sized Blog Element (Big Preview Pic)

Knowledge Base, List Queries, PowerForms HTML

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 ListQuery2, containing the information shown below:

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

Then we will enter our Sharepoint’s site on the Site Url and press the Apply button. Alternatively, we can just enter {url} as the Site Url and it will automatically use the current site to retrieve information. 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 (or simply use {url}) and press Apply. The lists appear when we click on the List menu. We select the list Cases and the All Items view. Ignoring all other parameters, we select the Execute button. 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 case name, the customer and a short description of the case 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 (Case 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.

Max Rows

You may also stumble upon a case where you should only return the top 1, 2, maybe 3 rows from a list query. You can now define the maximum number of rows returned in a list query, by changing the number appearing in the the Max Rows parameter. 0 returns all rows, with no restriction applied, a positive number will return the respective number of results.

For example, we only need 1 result returned in our list query. Entering 1 in Max Rows produces the following results:

August 13, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms HTML

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 Urlparameter. 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 :

Equals
BeginsWith
Contains
Greater
GreaterOrEqual
Less
LessOrEqual
NotEquals
IsNull
IsNotNull

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.

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)

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)

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 :

Code

(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

August 13, 2017/by Konstantinos