Auto-Lookup > AgilePoint NX Data Entities > Multiple Columns

This topic shows how to create a lookup with Data Entities as the data source based on multiple columns for the Auto-Lookup form control.

Background and Setup

Prerequisites

Good to Know

How to Start

  1. On the Auto-Lookup form control configuration screen, on the Configure tab, click Add Lookup.

    For information about how to open this screen, refer to Auto-Lookup form control.


    Click Add Lookup
  2. On the Select Data Source Type screen, select Data Entities.

    Select Data Entities
  3. Click Next.

Lookup Details

Specifies the basic configuration for a lookup.

Figure: Lookup Details screen

Lookup Details screen

Good to Know

Fields

Field NameDefinition

Lookup Name

Description:
Specifies the name you want to give to your lookup procedure.
Allowed Values:
One line of text (a string).

Accepted:

  • Letters
  • Numbers
  • Spaces
Default Value:
None
Accepts Variables:
No
Example:
Refer to:

Lookup Type

Description:
Specifies the type of lookup procedure to do.
Allowed Values:
  • Name/Value - The lookup gets data for specified name/value pairs.
  • Multiple Columns - The lookup gets data for more than one column.
Default Value:
Name/Value
Example:
Refer to:

Configure Lookup > Quick Config tab

Configures a lookup with more than one column from your data source.

Figure: Configure Lookup > Quick Config tab

Quick Config tab

Fields

Field NameDefinition

Show Custom Entities

Description:
Specifies whether to show only custom entities in the Select Entity list.
Allowed Values:
  • Selected - Shows only custom entities in the Select Entity list.
  • Deselected - Shows only standard entities in the Select Entity list.
Default Value:
Deselected

Entity

Description:
Specifies the name for your entity.
Allowed Values:
The name of your entity.
Default Value:
None
Example:
Refer to:

Source Column

Description:
Shows the column names for the selected entity.
To Open this Field:
  1. On the Configure Lookup screen, in the Entity list, select an entity.
Allowed Values:
Read only.

The values are completed when you select the entity name.

App Variable

Description:
Specifies the value to update for the column.
To Open this Field:
  1. On the Configure Lookup screen, in the Entity list, select an entity.
Allowed Values:
None.

But when you add a column in the list, the value field is populated with the column's name. Set the actual value.

Display Name

Description:
Specifies the name for the column that shows in your lookup.
To Open this Field:
  1. On the Configure Lookup screen, in the Entity list, select an entity.
Allowed Values:
One line of text (a string).

Accepted:

  • Letters
  • Numbers
  • Spaces
  • Special characters
Default Value:
None
Limitations:

This field is available in these releases:

Expand Expand icon

Function:
Shows the field list options.
To Open this Field:
  1. On the Configure Lookup screen, in the Entity list, select an entity.

Collapse Collapse icon

Function:
Closes the field list.
To Open this Field:
  1. On the Configure Lookup screen, in the Entity list, select an entity.

Add expression Add Expression icon

Function:
Creates a condition row.
To Open this Field:
  1. On the Configure Lookup screen, in the Entity list, select an entity.
Limitations:

This field is available in these releases:

Add group Add Group icon

Function:
Creates a new conditional expression group.
To Open this Field:
  1. On the Configure Lookup screen, in the Entity list, select an entity.
Limitations:

This field is available in these releases:

Select Nest Type

Description:
Specifies the AND and OR logical operators to filter the lookup.
To Open this Field:
  1. On the Configure Lookup screen, in the Entity list, select an entity.
Allowed Values:
  • AND - Specifies the AND operator.
  • OR - Specifies the OR operator.
Default Value:
And

Column Name

Description:
Specifies the name of the entity column.
To Open this Field:
  1. On the Configure Lookup screen, in the Entity list, select an entity.
  2. Click Add expression Add Expression icon.
Allowed Values:
A column name from the list.
Default Value:
None

Operator

Description:
Specifies the operator to use in the filter.
To Open this Field:
  1. On the Configure Lookup screen, in the Entity list, select an entity.
  2. Click Add expression Add Expression icon.
Allowed Values:
  • !=
  • =
  • >
  • <
  • >=
  • <=
  • Like
  • Not Like
  • Contains
  • Does Not Contain
  • Begins with
  • Does Not Begin With
  • Ends With
  • Does Not End With
  • In
  • Is Null
  • Is Not Null
Default Value:
=
Limitations:

Value

Description:
Specifies the value for an entity field to use in filter.
To Open this Field:
  1. On the Configure Lookup screen, in the Entity list, select an entity.
  2. Click Add expression Add Expression icon.
Allowed Values:
One line of text (a string).

Accepted:

  • Letters
  • Numbers
  • Spaces
  • Special characters
Default Value:
None
Accepts Variables:
Yes

Delete Delete icon

Function:
Deletes the condition row.
To Open this Field:
  1. On the Configure Lookup screen, in the Entity list, select an entity.
  2. Click Add expression Add Expression icon.

Sort By

Description:
Specifies the name of the column to sequence the query results.
To Open this Field:
  1. On the Configure Lookup screen, in the Entity list, select an entity.
Allowed Values:
A column name from the list.
Default Value:
None

Sort Order

Description:
Specifies the sort order for the result set.
To Open this Field:
  1. On the Configure Lookup screen, in the Entity list, select an entity.
Allowed Values:
  • Ascending
  • Descending
Default Value:
Ascending

Configure Lookup > Advanced tab

Configures a WHERE clause for your query.

Figure: Configure Lookup > Advanced tab

Advanced tab

Prerequisites

Fields

Field NameDefinition

Query

Description:
Specifies the WHERE clause for your query with the conditions and logical operators
Allowed Values:
  • A valid SQL WHERE clause.
  • A valid stored procedure.
  • A valid SQL view.
Default Value:
None
Example:
  • SELECT [Account Owner] "Name", [Account Number] "Value" FROM Account
  • SELECT PositionCode, FirstName, LastName, Email, City, StateCode, ZipCode FROM [appApplicants] WHERE PositionCode = ${PositionCode}

Perform SQL Query Escaping

Description:
Specifies whether the query condition value escapes the apostrophe (') character.
Allowed Values:
  • Selected - Apostrophe (') characters are escaped in your query condition value.
  • Deselected - The query condition value does not escape apostrophe (') characters.
Default Value:
Deselected
Example:
  • SELECT [Account Owner] "Name", [Account Number] "Value" FROM Account
  • SELECT PositionCode, FirstName, LastName, Email, City, StateCode, ZipCode FROM [appApplicants] WHERE PositionCode = ${PositionCode}

Validate Query

Function:
Does a test to make sure the query is correct, and displays the error message if an error occurs.

Result

Function:
Shows the result of the WHERE clause query.

Configure Columns

Opens this Screen:
Configure Lookup > Configure Columns
Function:
Configures a data type for more than one column to show in your lookup.

Configure Lookup > Configure Columns

Configures a data type for more than one column to show in your lookup.

Figure: Configure Lookup > Configure Columns

Configure Columns

Prerequisites

Fields

Field NameDefinition

Display Name

Description:
Specifies the name for the column that shows in your lookup.
Allowed Values:
One line of text (a string).

Accepted:

  • Letters
  • Numbers
  • Spaces
  • Special characters
Default Value:
Different for different columns

Data Type

Description:
Specifies the data type for the column.
Allowed Values:
A data type from the list.
Default Value:
None

Format

Description:
Specifies the date format that the column shows.
To Open this Field:
  1. In the Data Type field, select Date.
Allowed Values:
  • MM/dd/yyyy - Shows the date in the format Month/day/year.
  • yyyy/MM/dd - Shows the date in the format year/Month/day.
  • yyyy-MM-dd - Shows the date in the format year-Month-day.
  • dd-MM-yyyy - Shows the date in the format day-Month-year.
  • dd MMM yyyy - Shows the date in the format day Month year.
  • MMM dd yyyy - Shows the date in the format Month day year.
Default Value:
None
Example:
  • 09/21/1978
  • 1978/09/21

Format

Description:
Specifies the date-time format that the column shows.
To Open this Field:
  1. In the Data Type field, select DateTime.
Allowed Values:
  • MM/dd/yyyy - Shows the date in the format Month/day/year.
  • MM/dd/yyyy HH:mm:ss - Shows the date-time in the format Month/day/year hour:minute:second.
  • yyyy/MM/dd - Shows the date in the format year/Month/day.
  • yyyy-MM-dd HH:mm:ss - Shows the date-time in the format year-Month-day hour:minute:second.
  • yyyy-MM-dd - Shows the date in the format year-Month-day.
  • dd-MM-yyyy - Shows the date in the format day-Month-year.
  • dd-MM-yyyy HH:mm:ss - Shows the date-time in the format day-Month-year hour:minute:second.
  • dd MMM yyyy - Shows the date in the format day Month year.
  • dd MMM yyyy HH:mm:ss - Shows the date-time in the format day Month year hour:minute:second.
  • MMM dd yyyy - Shows the date in the format Month day year.
  • MMM dd yyyy HH:mm:ss - Shows the date-time in the format Month day year hour:minute:second.
Default Value:
None
Example:
  • 09/21/1978 18:35:50

Reset

Description:
Restores the default display name and data type for the column.

Collapse All

Description:
Specifies whether to close the Display Name and Data Type fields for all columns on the screen.
Allowed Values:
  • On - Closes the Display Name and Data Type fields for all columns.
  • Off - By default, the Display Name and Data Type fields stay open for all columns.
Default Value:
Deselected

Configure Lookup > Error Messages tab

Configures the message to show if the lookup does not return any data.

Figure: Configure Lookup > Error Messages tab

Error Messages tab

Fields

Field NameDefinition

No Items Retrieved

Description:
Specifies the error message to show if the lookup does not return any data.
Allowed Values:
One line of text (a string).

Accepted:

  • Letters
  • Numbers
  • Spaces
Default Value:
None

Lookup Failed

Description:
Specifies the message to show if the data fails to load.
Allowed Values:
More than one line of text.
Default Value:
Failed to load data.