Applies to:

Winshuttle Foundation

Note: Winshuttle is restricted to access SAP tables directly as per their SAP certification terms.

Query

Use the Winshuttle Composer Query element to query a data source, including sources such as ODBC, OLE DB, LDAP, or a SharePoint list.

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.

Query element properties

Property	Description
Name	A descriptive name for the element.
Data Connection	The connection string to a data source, which can be an ODBC connection string, LDAP, OLEDB, or a SharePoint site for SharePoint list queries. Click to select a data connection (SQL, SharePoint, SOAP Web Service, or Other) for the Query, or click Add to add a new one. See Adding Data connections for additional information about adding data connections.
LDAP Query Connection String	Select LDAP (Lightweight Directory Access Protocol), and then supply the connection string to the LDAP server.
User Profile Name Field (Portal Only)	The name of the field that contains the current user’s name. This functionality works in conjunction with the Current User element, where the element populates a given field with the current user’s login name. NOTE: This option is used only when using SharePoint Portal Server (SPS) or Microsoft Office SharePoint Server (MOSS).
Portal Root	URL to the root of the SPS or MOSS.
Query	SQL or SharePoint query to execute on the data source.
Run at	Start: Runs the query when the form is started. End: Runs the query when the form is submitted. When field changes: Run 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	Sets values in the XML to query return values. The format is <field name>=<query field name>,<field name>=<query field name>,....
Append To Repeating Group	Appends results to an existing repeating group.
Ordinal	The order (1, 2, etc.) in which the query should be performed (if 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 display to the End User if the query returns no rows. If this field is left empty, no message will be displayed. This property works with Run At: 'On Field Change' and 'Invoked by Rule' only.

Adding a Query

The following procedure shows you how to add and configure a query.

On the Elements Tab, click Advanced Elements.
Click Query.
The Query element is inserted in the upper left corner of the form:
Click the Query button to open its properties in the Properties Pane.
Under Data Connection, select the Data Connection for the query from the drop menu. (Note: You can click Add to add a data connection if necessary).

To add a query, under Query paste the query code into the query field. For example, it might look something like this:

composer-query-properties-query-example-code

Tip: If the Query is querying a SharePoint list, you only need the "filter=" value because a Composer SharePoint data connection already has the SharePoint List name and the SharePoint List fields maintained in its properties.

Example: SharePointColumn==@[myFieldXPath], which can look like this in a real scenario: Code==@[/my:myFields/my:CurrencyCode]

Sorting: When using query fields in Composer, the query is performed prior to Composer sorting fields. For instance, if your query only pulls back certain currencies, and the data connection is set to sort on Currency Rate, the data set will be sorted by currency rate only for the currencies the query field returns.

Under Run At, select when you want the Query to run:
- Start: Run when the form is started
- End: Run when the form is completed
- When field changes: Run the Query when the field is changed
Below Set Fields, click Choose.Select the field(s) and the corresponding column(s) that will display the data returned by the Query.
Fill out the remaining information if needed. (See above for a description of each option).
Append Repeating Group
Ordinal
Clear Fields When Empty Return
Empty Message

Configuring the Query element for SharePoint User Profile Services

The SharePoint User Profile Service needs the Login Name of the user before it can return anything. You can use the Current User element to obtain the login name for the currently logged in user and populate a field in your form with the Login Name.

Note: See Microsoft Technet article Default user profile properties table as a reference for when looking up SharePoint User Profile properties. Note that some properties appear to have spaces in them, but this is for display purposes only. The Query control requires the internal name of the property and not the display name to pass the value of the 'User Profile Property' column within the table.

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 wildcard 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 dropdown values. Because this can result in very large tables, using SQL for this type of operation is faster and more efficient than an equivalent operation in SharePoint.

Alternatively: With a SharePoint list you have a select and a filter statement as shown in the query below.

select VendorNumber, POC; filter=[VendorName]==@[/my:myFields/my:Vendor_Name]

This returns 2 SharePoint list columns – Vendor Number and POC, with a filter where vendorname = form field name.

Note: Notice the @ and square brackets around the xpath.