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