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.
Back to Form Elements
Related topic: Element Properties
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. Note: 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.
|
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.
Note: 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. Note: 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]'