How do I fix an Invalid Cross Reference ID error?

Issue

I'm seeing errors in my _trigger_log where the sf_message is invalid cross reference id. What does this mean and how do I fix it?

Resolution

You get the invalid cross reference id error when Heroku Connect attempts to insert or update a row in Salesforce that has a field value which references another Salesforce record that either doesn't exist, or isn't accessible by the API user.

You can get more details about what Heroku Connect attempting to write by querying the _trigger_log table and inspecting the values field:

SELECT values FROM salesforce._trigger_log WHERE table_name = 'my_table__c' AND state != 'SUCCESS' ORDER BY id;

In the query results, look for fields that reference other records by their Salesforce ID, such as parentid or userorgroupid.

If the referenced record is something that can be viewed in the Salesforce UI, such as a User or an Account, you can verify whether or not your Salesforce integration user can access the record by:

1. Logging in to your Salesforce org as the Salesforce integration user (that is, the user that you authorized Heroku Connect to sync data to Salesforce with).
2. Attempting to view the record. You can usually do this by visiting a URL that's composed of your base Salesforce URL and the record ID. For example, if your base Salesforce URL is https://na64.salesforce.com and your record ID is 00124000001sJP2AAM, try to access https://na64.salesforce.com/00124000001sJP2AAM. If you're unable to view the record while logged in as the Salesforce integration user, you've identified the problem: you're referring to a record that either doesn't exist or isn't accessible by your user. You may need to update object or record-level permissions. If you can successfully view the record, more investigation will be required.

You can also verify that you're writing exactly the columns to Salesforce that you expect. An example query of how to check this is:

SELECT
  id, record_id, sf_message, ARRAY(
    SELECT
        key
    FROM
        each(values::hstore)
    WHERE
        value IS NOT NULL
        AND
        key != 'id'
  )
FROM
  salesforce._trigger_log_archive
WHERE
  action = 'INSERT'
  AND
  table_name = 'opportunity'
  AND
  state = 'FAILED'
  AND
  processed_at >= '2018-07-13'::date;

In some cases, this query may help identify a subset of fields that may be more likely to be successful. Following that you can follow our documentation to resolve write errors