What should be done when Enterprise Integration Manager fails with message “This is a foreign key value in the base table and the values in the interface table did not resolve to existing values”?

Integration Manager (EIM) reports the following low-severity error when the foreign key value in the base table does not match with value in the interface table (Note: The following example is based on Siebel version 7.0.4 data model):

EIM_ORDER
------------
BL_ACCNT_BI
BL_ACCNT_LOC
BL_ACCNT_NAME
BL_ADDR_NAME

Base table:
S_ORDER
-----------
BL_ADDR_ID (Position)
This is a foreign key value in the base table and the values in the interface table did not resolve to existing values. Verify that the IF columns correspond to existing base table rows. This failure caused the rows to be eliminated from further processing for this secondary base table. However, processing of the rows WILL continue for other destination base tables.

Resolution:

The essential concept of resolving the foreign key value is to find the user key columns in the foreign key table. Based on multiple columns, user keys are used to uniquely identify a row in a table for EIM processing. The following guideline illustrates how the user key plays a role to identify the base column for corresponding EIM columns based on the above scenario.

Identify the foreign key table, which S_ORDER.BL_ADDR_ID points to. Siebel Tools or Siebel Interface Reference guide can be used to identify the foreign key table.

Using Siebel Tools, in the Object Explorer list, go to Table object and query for S_ORDER table

Navigate to Column object and query for BL_ADDR_ID column

Verify that the foreign key table value is S_ADDR_ORG.

Find the user key columns defined in the S_ADDR_ORG table.

Using Siebel Tools, in the Object Explorer list, go to Table object and query for S_ADDR_ORG table

Navigate to User Key object and select U1 index (S_ADDR_ORG_U1)

Navigate to User Key Column object and verify that the User Key columns for S_ADDR_ORG are ADDR_NAME and OU_ID (FK)

Identify the foreign key table, which S_ADDR_ORG.OU_ID points to using Siebel Tools.
S_ORG_EXT

Find the user key columns defined in the S_ORG_EXT table using Siebel Tools: NAME, LOC, BU_ID (FK)

Identify the foreign key table, which S_ORG_EXT.BU_ID points to using Siebel Tools: S_BU

Find the user key columns defined in the S_BU table using Siebel Tools: NAME

Based on the above result, following interface columns need to be populated correctly to resolve the S_ORDER.BL_ADDR_ID foreign key.

BL_ACCNT_BU: populated with S_BU.NAME value from step 6.
BL_ACCNT_LOC: populated with S_ORG_EXT.LOC value from step 4.
BL_ACCNT_NAME: populated with S_ORG_EXT.NAME value from step 4.
BL_ADDR_NAME: populated with S_ADDR_ORG.ADDR_NAME value from step 2.