Property

Description

Fields

Sets the names of the fields in the data set. Browse the field to display the LookupField Collection Editor dialog where you add the fields, define their types, and select the key field for the data set.

Note: Use the AutoIncrement property to set a key field as an AutoIncrement field, which is analogous to an identity field in a database table.

Follow these steps to add a field to the table:

1. Click Add. The system displays the LookupField Collection Editor and creates a new field in the table.

2. In the AutoIncrement property, select True if you want the system to automatically increment the value for the key field each time a new record is added. If you set AutoIncrement to True, the system selects System.Int32 as the field type.  The system also sets the ReplaceOnDuplicateAdd and AddOnMissingReplace properties to False when you set the AutoIncrement property to True.

 Select False if it is not a key field.

3. In the FieldName property, enter a name for the field.

4. In the Key property, enter True if this field is the key field for the table. The system uses the key field to index the table. You cannot modify the key field. Only one key field can be present in the table.

5. In the Type property, select the field  type. Some common field types are listed here:

• System.String

• System.Int32

• System.Boolean

KeyName

Shows the name of the field that you chose as the key field in the LookupField Collection Editor.

AddOnMissingReplace

Set this property to True if you want the automation to add a record when it performs  a replace operation in the table and the matching key field does not exist. For example, if the automation tries to replace a record that is not in the lookup table, your entry for this property determines whether to add it as a new record.

The default is False.

ReplaceOnDuplicateAdd

Set this property to True if you want the LookupTable component to replace an existing record when the data set receives an add record that duplicates an existing record. For example, if you try to add a record that is already there, your entry for this property determines whether to replace the existing record.

The default is False.

RowCount

Shows the number of rows in the lookup table.

Method

Description

Parameters

Result Type

AddRecord

Adds a record to the lookup table. You must provide the key field value. You must enter the rest of the fields too,  but you can leave those fields blank or null.

This method works with the ReplaceOnDuplicateAdd property. If you enable the AutoIncrement property, this method has no key field parameter.

key field, table fields

key field

Clear

Removes all data from the table while retaining the table structure.

none

Void

Contains

Returns True if the system finds in the lookup table the key value that you specify.

key value

Boolean

GetKeys

Returns a list of the values for the key fields in the lookup table.

none

Object[]

GetRecord

Retrieves a record from the lookup table based on the entry of the key field value as a parameter. If the system finds the record, the method outputs the values of the rest of the fields in the record and a True result.

This method only takes integers when you set the AutoIncrement property to True.

key field

Table fields and Boolean result

GetTable

Retrieves the entire lookup table as a DataTable.

none

DataTable

GetTableWithoutKey

Returns the data table without the key field. Use to return only the data from a lookup table without the automatically generated key value.

none

DataTable

ImportDelimitedFile

Imports the delimited file that you specify. For example, use this method to import a comma-delimited file.

filePath - Enter the path to the delimited file that you want to import.

delimiter - Select the character that is used as the delimiter. You can choose from these options: Tab, Whitespace, Comma, QuestionMark, Caret (^), Pipe (|), or Other. If you choose Other, specify the delimiter character in the customDelimiter parameter.

customDelimiter - (Optional) If you choose Other in the delimiter parameter, specify the character that is used as the delimiter.

mappingMethod - Specify how the data is imported into the lookup table. Select UseRowHeader if  the first line in the file determines the column names and the file data is mapped to the lookup table by column name. Select UseColumnIndex to map the columns in the file data to the lookup table based on the column position. For example, if you select UseColumnIndex, the system maps the first column in the file to first column in the lookup table and so on.

clearTable - Enter True if the system should clear the table prior to operation. The default is False.

Message - This property returns an error message if the operation fails, but the system does not throw an exception.

filePath, deliniter, customerDelimiter, mappingMethod, clearTable

Boolean
Message

ImportFixedWidthFile

Imports the fixed-width file that you specify. This file should have data in a fixed column width format, for example a mainframe report.

filePath - Enter the path to the delimited file that you want to import.

mappingMethod - Specify how the data is imported into the lookup table. Select UseRowHeader if  the first line in the file determines the column names and the file data is mapped to the lookup table by column name. Select UseColumnIndex to map the columns in the file data to the lookup table based on the column position. For example, if you select UseColumnIndex, the system maps the first column in the file to first column in the lookup table and so on.

startLine - Enter a number to indicate the first line in the file to process.  Use this parameter to skip lines that contain information that you do not want to import, such as report headings.  If the report has column headings, the column heading line should be your start line.

clearTable - Enter True if the system should clear the table prior to operation. The default is False.

message - This property returns an error message if the operation fails, but the system does not throw an exception.

columnWidths - Enter an integer array that contains the width of each column in the text file. All values must be greater than zero except the last column. The last column can contain the actual column width or the special value of -1 , which indicates that the column may be of variable (such as ragged text at the end of a line). The -1 value instructs the system to process all of the remaining characters in the line as the last column.

The following example reads the ParserText.txt file, chooses UseRowHeader as the mappingMethod, starts the import on line 4 and uses the data on that line as column headers to match the data to the lookup table. This example also specifies the column widths, with the fifth row being of variable width.

filePath, mappingMethod, startLine, clearTable, message, columnWidths

Boolean
Message

Remove

Removes a record from the table based on the key field value that you specify.

key field

Boolean

ReplaceRecord

Replaces an entire record in a lookup table by matching the value in the key field. When the system finds a matching record, it overwrites the values of the fields with the new data.

This method works with the AddOnMissingReplace property. This method only takes integers when you set the AutoIncrement property to True.

key field, table fields

key field

ReplaceTable

Replaces the lookup table with the contents of the DataTable that you specify.

The schema of the DataTable must match the schema of the lookup table.

DataTable

none

ReplaceTableAutoKey

Replaces the lookup table with the contents of the DataTable that you specify.

The lookup table must have a key with the AutoIncrement property set to True. The schema of the DataTable must match the schema of the lookup table with the exception that the Key column can not be present.

DataTable

none

UpdateField

Returns True if the update is successful. Returns False if the key is not found or if there is a mismatch between the Column Name and the newValue.

Key - The key of the  record to update.

Column Name\Index - The name or index of the column to update.

newValue - The new value of the field.

key, object, Boolean