You are here: Reference Guides > Designer Form Controls (A-Z) > Ws.Lookup

Ws.Lookup

The Ws.Lookup control enables you to run a query against a data source and enter the returned values into a form.

Ws.Lookup Properties

Property	Description
Allow Multiple Selections	Allows you to select multiple listings from the returned query results when the pop-up option is selected.
Append To Repeating Group	Appends results to an existing repeating group.
Auto Run At End	Query runs when the form is submitted.
Auto Run At Start	Query runs when the form starts.
Auto Wildcard End	Automatically appends a SQL wildcard character to the end of the where clause.
Auto Wildcard Start	Automatically appends a SQL wildcard character to the beginning of the where clause.
Autorun Lockouts	When using multiple Ws.Lookup controls that run when field values change, circular references can occur if one Ws.Lookup control is attempting to update a field that is being used by another Ws.Lookup control, which can cause infinite loops. This property can be set to a comma-separated list of field names that are referenced by other Ws.Lookup controls, and when this Ws.Lookup control runs, it will not update the fields specified, which would cause other Ws.Lookup controls to run.
Data Source	Name of the InfoPath data source or ODBC connection string.
Display Columns	Comma-separated list of columns to display in the pop-up selection window. If a Web Service data source is configured, this value also serves to name the columns of data returned from the Web Service.
Height	The height of the pop-up window.
Image URL	The URL to an image to use when displaying the Ws.Lookup control button in the form.
Interactive	Presentation of query data: Popup: data is presented in a pop-up window. Drop Down List: data is presented in a specified InfoPath list box control. Note: The properties "Auto Run At Start”, "Auto Run At End" and "Run When Field Changes” are not applicable to the "Interactive" option.
Minimum Search Key Size	Minimum character size for the query term.
Ordinal	The order to use when running this Ws.Lookup control.
Raw "Where" Clause	Allows you to specify a custom where clause for a SQL statement. If you are using Search Column, Search Operator, and Search Key Field XPath, it will result in a where clause that includes only a single condition. If you want more conditions, you must specify a custom where clause.
Run Query Only	Runs only the query portion of a Ws.Lookup control when a form field value is changed, skipping the step where another form field’s value is set. The default value is off.
Run When Field Changes	Reruns the lookup query when a specific field is changed.
Search Column	Name of the database column that is being queried.
Search Key Field XPath	InfoPath field containing the value to use in the constructed query.
Search Operator	Operator to use in the query (valid values are <, >, =, <=, >=, like).
Set Fields	Comma-separated list of query return values and fields to set in the form. The format is <display column name>=<field name>,<display column name>=<field name>,... .
Table	Name of the table if the data source is an ODBC connection string.
Table Title	The title to give the table within the pop-up window.
Width	The width of the pop-up window.
Window Title	The title to give the pop-up window.

Configuring Ws.Lookup for SharePoint data connections

Add the Data Source you would like to query against to InfoPath using the Manage Data Connections link.
Add the Ws.Lookup control to your form.
Right-click the Ws.Lookup control and select Properties.
In the Data Source Field, enter the name of your Data Source (exactly as it is displayed in InfoPath).
For the Display Columns field, enter the names of the SharePoint Columns you would like to display to the user. If you would like to display more than one column, separate the columns by commas. Do not include spaces unless the column name itself has a space in it.
For the Search Operator field, enter something appropriate for your query. Possible operators are found in the table at the beginning of the document in the Search Operator line.
In the Search Key Field XPath field, enter the name of the field in the form which will hold the value of what you are searching/querying for. If you have multiple inputs in which you would like to use, leave this field blank and use the Raw "Where" Clause option.
In the Set Fields field, enter a comma separated list that will set the fields in your form with the results returned from the query. The correct syntax for this is InfoPathField=SharePointColumn.
If you would like a user to interact and select their results, select the Interactive Option. You then have two choices (see below for examples):
- Popup: Opens a new window and the user can select an option from a list.
- Drop Down List: Results populate a Drop Down List Box in the form.

Drop down list example

Pop-up example

Using the Raw "Where" Clause

The Raw "Where" Clause Option field can be used to type a specific query for either a SharePoint List or SQL server database and extract only records that fulfill the search criteria.

Note:

To use the Raw Where Clause field, leave Search Column, Search Operator, Search Key Field XPath fields blank.
SQL requires single quotes around text values, but not around Numeric fields.

Where Clause Operators

The following table describes the operators that can be used in the Raw WHERE clause with SQL Queries and SharePoint Lists.

SQL Operators		SharePoint Operators
Operator	Description	Operator	Description
=	Equal	==	Equal
<>	Not equal	!=	Not equal
>	Greater than	>	Greater than
<	Less than	<	Less than
>=	Greater than or equal	>=	Greater than or equal
<=	Less than or equal	<=	Less than or equal
BETWEEN	Between an inclusive range	CONTAINS	Contains
LIKE	Search for a pattern	LIKE	BeginsWith
IN	To specify multiple possible values for a column	&&	And
		\|\|	Or

Examples

The following basic examples were constructed based upon the sample data in the following table (which can represent either a SharePoint List or SQL database).

ID	Name	Profile	Address	City	PostalCode	Country
1	John Moreno	Software Tester	abc	Abc	1111	xyz
2	Bryan	Software Engineer	Address2	city2	12345	abc
3	Paul	Software Engineer	address3	city3	14141	xyz
4	Marya	Software Tester	address4	city4	56565	bbb
5	John Davidson	Software Engineer	address5	city5	12678	ccc

Using Raw Where Clause with SQL

Extract the records where Profile is not equal to value of my: Profilefield:
Profile <> '[/my:myFields/my:Profilefield]'
Extract the records where Profile is equal to value of my: Profilefield and City is equal to value of my: Cityfield:
Profile = '[/my:myFields/my:Profilefield]' and city= '[/my:myFields/my:Cityfield]'
Extract the records where Profile is equal to value of my: Profilefield , City is equal to value of my: Cityfield and Country is in abc and xyz:
Profile = '[/my:myFields/my:Profilefield]' and city= '[/my:myFields/my:Cityfield]' And country in('abc', 'xyz')

Using Raw Where Clause with SharePoint list

Extract the records where Profile contains value of my: Profilefield:
[Profile]CONTAINS@[/my:myFields/my: Profilefield]
Extract the records where Profile Begins With value of my: Profilefield and Country equal to abc:
[Profile]like@[/my:myFields/my: Profilefield] &&[ Country] == 'abc'
Extract the records where Profile contains value of my: Profilefield and order by ID Desc:
[Profile]contains@[/my:myFields/my: Profilefield] order by [ID] desc