Why do I receive Invalid look-up value for field 'X' error during Data Import?
Data Import Wizard, Oracle B2C Service, all versions
Validation error "Invalid look-up value for field 'X'" can be returned during Data Import because of a specific Custom Objects setup. Lookup field is a field in a custom object designated to store values that are used as the names of the object’s records. These names display on editor tabs and the navigation pane’s Recent Items list.
For instance, the Recent Items list displays incident reference numbers since the Reference # field is designated as the lookup field for incidents. Values in lookup fields are also displayed in reports instead of the ID values from fields in other tables that link to the lookup field’s table. As an example, while the incidents table’s Assigned Account field stores account ID numbers, the values displayed for this field in reports are from the accounts table’s Full Name field.
Let's say we have Custom Object that has a custom attribute "i_id" (numeric type) and is connected to Incidents table as i_id = i_id.
During Data Import into Custom Object table we can pass either "ref_no" (lookup for Incidents) or "i_id" (actual Incident ID) into "i_id" field. "Invalid look-up value for field 'X'" error will never be returned because "ref_no" is a text field with a defined input mask and i_id is a number. Field ref_no will never get a number value and ID field will never get text value so Data Import Wizard can easily determine if we are passing a lookup field value or the actual ID.
Above is the example that demonstrates the correct way to setup Custom Objects relationships and a lookup field.
Now let's review another example:
We have Custom Object A with ID as lookup and "id_a" as a custom attribute and Custom Object B with "id_a" custom attribute. Objects connected via id_a = id_a.
ID field can hold numbers and "id_a" can only hold numbers so technically both fields can have the same value in different records.
During Data Import into Custom Object B we can pass either "ID" (lookup for Object A) or "id_a" (custom attribute for Object A) value; however, "Invalid look-up value for field 'X'" error will be returned because in Object A field "id_a" is an integer\numeric field and ID as well integer\numeric where Data Import Wizard cannot determine if the value is from lookup field or from "id_a".
The solution is to use a different lookup field.
For more information regarding Data Import errors, refer to the following resources: