Specify Lookup query for meaningful strings instead of IDs

Management Pane > tab Extend Field Behavior, column Lookup


How it is used

If you use as the first field the ID, and the second field the string (e.g. name), then if the Source returns an ID, the value that is displayed in New Value, (and Old Value, if you are doing a replace) will be a string. In addition, the New Value combo box will show the strings returned by the query.

example: qryContactname

  1. Create a query called qryContactname: "SELECT  contact_id, contactname FROM tblContacts;
  2. In tab [Extend Field Behavior] check 'Specify Lookup query for meaningful strings instead of IDs'
  3. In row contact_id in the Transfer Pane, in column 'Lookup', insert value qryContactname

Now in the Old Value and New Value column names appear. Additionally, you can select another New Value from the combo box

Expressions allowed in the Lookup field

In the Lookup field  you can enter table or query names or SQL expressions:

Lookup semantics, stored and displayed value

From any query, table or SQL, Lookup will only concern itself with the first 2 fields:

If only 1 field is available, it will be both stored and displayed value.

Using WHERE and MatchId

The last SQL expression in the previous section shows the use of a WHERE-part and MatchId. The MatchId, in the example [113] tells the import processor to pick up the New Value of the row with MatchId 113.

example: SELECT  contact_id, contactname FROM tblContacts WHERE org_id = [113];