Description
Searches a column of a target table for a value and returns the corresponding value from another column.
Syntax
FKEYSUBST ( TextSourceField, "connection", "table", "lookup_field", "substitution_field" )
| Parameter | Description |
|---|---|
| TextSourceField | The value for which to search in the lookup_field column. |
| connection | The connection to the target database that contains the columns storing the keys. |
| table | The name of the lookup table that contains the lookup and substitution field values. |
| lookup_field | The name of the column in the lookup table that stores the key value to search for. |
| substitution_field | The name of the column in the lookup table that stores the key value to return. |
Remarks
Useful for looking up and substituting one foreign key value for another. Required when transferring data between two databases that use different fields as foreign keys.
If Support Unicode is enabled and the target table contains a column of type Float, an update operation in the target table may fail. To resolve this, do one of the following in the table:
Examples
The following example uses this table named CUSTOMER in the target database.
| CUSTOMER_ID | MKTG_CUSTOMER_KEY |
|---|---|
| 1012356 | 598564 |
| 1012357 | 658742 |
| 1012358 | 587412 |
| 1012359 | 625478 |
| 1012360 | 645214 |
This function returns 587412:
FKEYSUBST("1012358", "Customer", "CUSTOMER", "CUSTOMER_ID", "MKTG_CUSTOMER_KEY")
See also