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.

Download Solution example (.wssln file)

On this page






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

Back to top

  1. Add the Data Source you would like to query against to InfoPath using the Manage Data Connections link.
  2. Add the Ws.Lookup control to your form.
  3. Right-click the Ws.Lookup control and select Properties.

  4. In the Data Source Field, enter the name of your Data Source (exactly as it is displayed in InfoPath).

  5. 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.
  6. 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.
  7. 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.

  8. 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.

  9. 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

Back to top

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

Back to top

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

Back to top

  • 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

Back to top

  • 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