Ws.Query

With the Ws.Query control you can query a data source, including ODBC, OLE DB, LDAP, or a SharePoint list/column.

Note: If you reference a SharePoint list/column, be aware that it is case sensitive.

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.

Download example (.wssln file)

On this page

• Configuring Ws.Query for a SharePoint list using a Connection String
• Configuring Ws.Query for SharePoint User Profile Services

Note: If you use the SharePoint User Profile Services option, the user must have the Enterprise version of SharePoint and have SharePoint User Profile Services Configured.

Ws.Query properties

Property	Description
Data Source	InfoPath Data Source: specifies an InfoPath data source to query. This will be the name of what you called your data source in InfoPath. Connection String: The connection string to data source, which can be an ODBC connection string, LDAP, OLEDB, or a SharePoint site for SharePoint list queries. 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 Ws.CurrentUser control, where the control 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 query to execute on the data source.
Invocation Settings	Run at Start: runs the query when the form is started. Run at End: runs the query when the form is submitted. Run When Field Changes: runs the query when the specified field is changed. Ordinal: order that the query should be performed (if multiple Ws.Query controls are used). 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. Clear Fields When Empty Return: if the query returns no rows, selecting this option will cause the form fields to be cleared. 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.

Configuring Ws.Query for a SharePoint list using a connection string

Back to top

1. Add the Ws.Query Control to your form, right-click it, and then click Properties.

   

2. Select Connection String.
3. In the Connection String text box, enter the SharePoint URL to the site where your list resides.

   If you are unsure about the correct link to insert into the Connection String Text Box, navigate to your SharePoint List in which you would like to use in your Query, and then get the Full URL to the list. In the example at left, the List Name is called WsQueryList. The Full URL is: http://server9/sites/marcwcentral/SVExLib/myexlib/Lists/WsQueryList/AllItems.aspx In the above link, the connection string will be everything prior to/Lists/WsQueryList/AllItems.aspx The final input into the Connection String Text Box will be: http://server9/sites/marcwcentral/SVExLib/myexlib

4. In the Query: section, type your query syntax.
   



For a Static Filter (i.e Query is for the same data source every time) list=YourListName;filter=[SharePointColumnToSearch]=="ValueYouAreSearchingFor";select[SharePointColumnThatHoldsResult] For a Variable Filter (The value that is queried will change) list=YourListName;filter=[SharePointColumnToSearch]==@[//my:InfoPathFieldThatHoldsQueryValue];select=[SharePointColumnThatHoldsResult] No Filter list=YourListName;select=[SharePointColumnThatHoldsResult] Query Syntax Explained: list=YourListName: YourListName: This is the name of your SharePoint List. filter=[SharePointColumnToSearch] =="ValueYouAreSearchingFor": [SharePointColumnToSearch] is the column within your SharePoint List that you will be using to query against. == is the identifier for a static filter. Use this if the value you are searching for will always be the same "ValueYouAreSearchingFor" is a static filter. If you are querying something where the query value never changes, type the value in between quotes. [//my:InfoPathFieldThatHoldsQueryValue] is the XPath to the form field which holds the value to be used in the query. select=[SharePointColumnThatHoldsResult]: [SharePointColumnThatHoldsResult] is the name of the SharePoint column that holds the actual result of your query. If you would like to return more than one value, use a comma-separated list of SharePoint Column Names, for example: select=[Column1],[Column2],[Column3]

5. Select when you would like to execute the query (Run At Start, Run At End, Run When Field Changes). See the above table at the beginning of the document for a description of each option.

   

6. In the Set Fields box, enter the form fields that you would like the Query Results to populate. The correct syntax is FormField=SharePointColumn.
   
   If you have more than one value to set, use a comma separated list. Example: FormField1=Column1,FormField2=Column2,FormField3=Column3

   

7. If you are using the Run When Field Changes option, enter the name of the field which will be changed (your trigger field).

   

Configuring Ws.Query for SharePoint User Profile Services

Back to top

The SharePoint User Profile Service needs the Login Name of the user before it can return anything. You can use the Ws.CurrentUser control to obtain the login name for the currently logged in user and populate a field in your form with the Login Name. See the Ws.CurrentUser Control example for further instruction on how to do this.

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, so pass the value of the 'User Profile Property' column within the table.

1. Add the Ws.Query Control to your form and right click to go inside of its properties.

   

2. Select the second tab labeled Query to configure the control.
3. In the User Profile Name Field (Portal Only) field, enter the name of the InfoPath field that will hold the value of the users login name.

   TIP: Use the Ws.CurrentUser Control to automate this.

4. In the Portal Root field, enter the Portal Root of your SharePoint site.
5. This will be the first portion of your link:
• FormEx Site link:
• Portal Root:
6. Depending on when you want to trigger this action, select Run At Start, Run At End, or Run When Field Changes. If you would like this to be automated upon form load, select Run At Start.
7. For the Set Fields field, enter a comma separated list of the values in which you would like to return. Possible field values (User Attributes) are:

   AboutMe FirstName PreferredName Office Title SPS-SipAddress SPS-School SPS-Interests SPS-PastProjects SPS-Skills SPS-Responsibility SPS-Peers PersonalSpace PictureURL UserName QuickLinks WebSite PublicSiteRedirect SPS-Dotted-line UserProfile_GUID

   AccountName LastName WorkPhone Department Manager SPS-Birthday SPS-MySiteUpgrade SPS-DontSuggestList SPS-ProxyAddresses SPS-HireDate SPS-LastColleagueAdded SPS-OWAUrl SPS-ResourceAccountName SPS-MasterAccountName Assistant WorkEmail CellPhone Fax HomePhone

8. For every User Attribute you would like to display in your form, you will need to create a Text box inside your form for each value. The correct syntax for the Set Fields field is:
   
   InfoPathFieldName=UserAttribute,InfoPathFieldName=UserAttribute

Example

The following example shows all possible User Attributes on Form Load.

Result