Complex Field Select

Normally the value in row 2 of the map workbook specifies the name of the database column. It equates to the database field that is populated with the configuration data during the import process or extracted in the export process. During the import process, the value from the configuration worksheet is set directly into the database table. This simple setting handles the vast majority of fields.

However some database fields are references to other tables. The reference is usually a number and in many cases an identity value assigned by the database. In this case the value in the configuration worksheet is a name or description that is used to look up the reference. This is where the complex field select comes into play.

The basic form is "name,(SELECT getname FROM &schemaid.tabname WHERE matchname = ?)" where

  • name - The database column name being imported into
  • getname - The field in table tabname whose value is to be used in the insert.
  • tabname - The table being searched
  • matchname - The field in table tabname to match
  • ? - Use value from configuration worksheet with same attribute name.

It is sometimes necessary to apply more than one value to isolate the record. For example,

"name,(SELECT getname FROM &schemaid.tabname WHERE matchname1 = ? AND matchname2 = ?)"

The DSU detects multiple '?' and obtains the values for the first ? from the configuration worksheet with the same Attribute Name. Subsequent ? are automatically obtained from adjacent column(s) to the right.

It is also possible to direct the DSU to a specific configuration column using the &attribute control. This control specifies the Attribute Name of the target column.

"name,(SELECT getname FROM &schemaid.tabname WHERE matchname1 = ? AND matchname2 = &attribute'attrname')"

"name,(SELECT getname FROM &schemaid.tabname WHERE matchname1 = &attribute'attrname1' AND matchname2 = &attribute'attrname'2)"

In the first example, matchname1 is satisfied with the value from the configuration worksheet with the same Attribute Name.

Use this method when the configuration worksheet can not be organized to group the columns together or if two or more field selects must pull from the same value.

The complex field requires a dedicated export query in the cross reference Export worksheet to restore the original value in the configuration worksheet.