Query Element Properties

Use the Query element to query a data source, including sources such as ODBC, OLEDB, or LDAP.

The query can be run when the form is submitted or when a field changes.

Results of the query can be displayed in a repeating element if it contains more than one item.

Note:

Provide the Query with brackets around the connection column name, when working with Solution Data Library or Reference Data connection types.
Filter property of Query Control has been disabled for userfromgroup Data Connection.


Property	Description
Name	A descriptive name for the element.
Data Connection	The name of the data connection. It can be, for example, SQL Server, ODBC, OLEDB, or LDAP. In case LDAP data connection used, then LDAP settings will be picked from Evolve server.
Query	Query to execute on the data source. See help below. If a query is not provided, all data in the data connection is returned.
Run at	Start: Runs the query when the form is started. End: Runs the query when the form is submitted. When field changes: Runs when a form field changes. Invoked by rule: Runs when a rule triggers it, such as when a button is clicked.
Field	Runs the query when the specified field is changed.
Set Fields	Comma-separated list of query return values and fields to set in the form. The format is: <Form field name>=<Column in Data Connection>, <Form field name>=<Column in Data Connection>, …
Append To Repeating Group	Appends results to an existing repeating group.
Ordinal	The order (1, 2, etc.) in which the query should be performed (when multiple Query elements are used).
Clear Fields When Empty Return	If the query returns no rows, selecting this option will cause the form fields to be cleared. This property works with Run At: 'On Field Change' and 'Invoked by Rule' only.
Empty Message	The message to displays when the query returns no rows. If this field is left empty, no message is displayed. This property works with Run At: 'On Field Change' and 'Invoked by Rule' only.

Examples of using the query element with SQL

Some examples of Query being used for SQL queries are provided below.

Example 1


select top 1 Profession from [Job Title Taxonomy] where ProfessionID = [/my:myFields/my:PO13_Descriptions/my:PO13_Descriptions_Input/my:Search_Term]

This query returns just one row and one field, where a table column equals the value in a form field. Notice the square brackets around the table name (because it has spaces in it) and the full xpath of the form field name with square brackets.

Note:

“top 1” in this example is SQL Server-specific; other databases may use different syntax.

Example 2


select Profession from [Job Title Taxonomy] where EmployeeName like '[/my:myFields/my:PO13_Descriptions/my:PO13_Descriptions_Input/my:Search_Term]%'

Single quotes must be used around the xpath because this value returned is a string value with potential spaces. The percentage sign is used as a wild card after the xpath value to ensure the return of all results starting with the value that comes from EmployeeName.

Note:

Single quotes are required around string values.

Example 3


select distinct ProfessionID from [Job Title Taxonomy]

This one returns unique values of ProfessionID from the table. The results would then be mapped to a repeating section in a form, which is then used as input for drop-down values. Because this can result in very large tables, using SQL for this type of operation is faster and more efficient.

Example 4

With a Solution Data Library and Reference Data, you have a select and a filter statement as shown in the query below.

Value from Form field: [VendorName]=[/my:myFields/my:Vendor_Name]

This returns data where vendorname is equal to the value in the form field Vendor_Name.

Direct Value: [VendorName]='Vendor1'

This returns data where vendorname is equal to Vendor_1.

Example 5

Note:

If the value is numeric, single quotes around string values are not required.

[Country]=='[/my:myFields/my:Country]' AND [Region]=='[/my:myFields/my:Region]'