Document how to use `sf_upsert()` with Salesforce objects containing lookup fields with external IDs

[mccarthy-m-g]

Issue description

Thanks for this package---I'm using it for a work project and it's been a great benefit to my team!

We ran into some difficulties where we would get a "MALFORMED_ID" status code when using sf_upsert() with Salesforce objects containing lookup fields that contain external IDs. We were able to resolve this after contacting Salesforce's technical support, but the solution doesn't seem to be documented anywhere, and it's something most users would be unlikely to discover on their own. The solution is also API-specific; we were able to solve it for the REST API, but not the SOAP API.

I'm mainly filing this issue to document this somewhere, but if the solution is generalizable maybe it's worth turning into a feature in salesforcer?

REST API Solution

I can't (or am not not sure how to) share a reprex for this since it's private data, but I'll describe the solution we found, which will hopefully be generalizable and help others who run into this issue. Here's the response from technical support to our issue:

Yes, it is possible to use external IDs in lookup fields on custom objects in Salesforce. This approach can simplify your data integration processes by avoiding the need to maintain a separate table linking external IDs with Salesforce IDs.

Create External ID Fields: Ensure that your custom objects have fields marked as External IDs. These fields should be unique and can be used to match records during the upsert operation.

API Integration: When performing an upsert operation via API, you can reference the external ID fields directly in your lookup fields. This allows Salesforce to match and relate records based on the external ID values.

Assume you have two custom objects, CustomObjectA and CustomObjectB. CustomObjectB has a lookup field to CustomObjectA. Here’s how you can structure your API request (JSON):

{
  "CustomObjectB__c": {
    "External_Id__c": "external_id_value",
    "LookupField__r": {
      "External_Id__c": "related_external_id_value"
    }
  }
}

For the MALFORMED ID error typically occurs when the ID format is incorrect so verify that your API request correctly maps the external ID fields and that the values provided are valid and unique. Please ensure that the external IDs are unique across all records and correctly formatted according to Salesforce external ID requirements.

There are two important things of note here:

• The lookup field column needs to have a __r suffix, not a __c suffix.
• The values in the lookup field have a key and a value.

For the latter point, the solution is to transform the data in this column from raw values into named lists. Here's an example of how you might make that transformation. Note that setting verbose = TRUE in sf_upsert() was helpful for troubleshooting the transformation I needed to make, and getting it to match what technical support shared above.

dplyr::mutate(
  my_data,
  LookupField__r = unlist(
    purrr::map(LookupField__r, \(.x) list(External_Id__c = .x)),
    recursive = FALSE
  )
)

After making this transformation, I was able to successfully sf_upsert() with the REST API.

SOAP API

As noted, the above solution is specific to the REST API. If you try this with the SOAP API you will get the following error: "Missing entity type information. sObject requires a separate 'type' field be sent."

We were able to get another example from technical support for this, but the relationship between their example and the verbose output of sf_upsert() was less obvious, so I wasn't sure how to proceed. Here's technical support's response:

The error you are experiencing ("Missing entity type information. sObject requires a separate 'type' field be sent.") is because in the SOAP API, Salesforce demands that you explicitly declare the type of each object being referenced.

In your case, the lookup field that uses an external ID needs to include both the type of the referenced object and the external ID.

Example:-
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:enterprise.soap.sforce.com">
soapenv:Header
urn:SessionHeader
urn:sessionId[YOUR_SESSION_ID]</urn:sessionId>
</urn:SessionHeader>
</soapenv:Header>
soapenv:Body
urn:upsert
urn:externalIDFieldNameExternal_Id__c</urn:externalIDFieldName>
urn:sObjects

urn:typeCustomObject__c</urn:type>
urn:NameTest Record</urn:Name>
urn:LookupField__r

urn:typeReferencedObject__c</urn:type>
urn:External_Id__crelated_object_external_id</urn:External_Id__c>
</urn:LookupField__r>
urn:Custom_Field__cValue</urn:Custom_Field__c>
</urn:sObjects>
</urn:upsert>
</soapenv:Body>
</soapenv:Envelope>

Always include the urn:type field for both the main object and the referenced object. Also verify that LookupField__r and External_Id__c are the correct API names for the fields.

[StevenMMortimer]

First, thank you @mccarthy-m-g for a really detailed writeup! It's a great reference on the issue.

Second, I can't quite grasp this specific use case or the solution. Could you help clarify?

I understand that your org has 2 objects, each configured to have external ids. One object has a lookup field to the other object. Is the upsert creating a record in one or both objects if one or both of the external ids don't exist in a record? If so, I wasn't aware you could do that in just one API call. For example, a multi-step approach could be:

Step 1: Pull the Salesforce ids and external field ids of all records in CustomObjectB
Step 2: For every record you're going to upsert into CustomObjectA, check if the CustomObjectB external id on that record exists in CustomObjectB, if not then create it.
Step 3: Upsert into CustomObjectA using its external id and populate the lookup field with the Salesforce Ids of records in CustomObjectB

Also, your solution mentioned unnesting the LookupField__r column, which I assume came from a query, but wouldn't you need to nest it back into a list for the upsert? Or maybe you're mentioning the unnest you're performing on the response returned after executing sf_upsert().

In any case, thanks for your help. Really interested in hearing more since this is a topic I'm not super familiar with.

[mccarthy-m-g]

I understand that your org has 2 objects, each configured to have external ids. One object has a lookup field to the other object.

Correct.

Is the upsert creating a record in one or both objects if one or both of the external ids don't exist in a record?

The upsert is just creating a record in one of the objects, the one with the lookup field (we are also upserting to the other object in a separate call, but that works just fine).

Also, your solution mentioned unnesting the LookupField__r column, which I assume came from a query, but wouldn't you need to nest it back into a list for the upsert? Or maybe you're mentioning the unnest you're performing on the response returned after executing sf_upsert().

Here's a reprex demonstrating the processing we had to do prior to upserting. wide_tbl__c and long_tbl__c would be passed to separate sf_upsert() calls. Does this make it clearer?

library(dplyr)
library(purrr)
library(stringr)

# This is the main table in Salesforce, the external ID is "model". We can use
# sf_upsert() on this table without any problems after adding the "__c" suffixes.
wide_tbl <- tribble(
  ~model, ~cyl,
  "civic", 4,
  "ranger", 4,
  "miata", 4
)

wide_tbl__c <- wide_tbl |> dplyr::rename_with(\(.x) paste0(.x, "__c"))
wide_tbl__c
#> # A tibble: 3 × 2
#>   model__c cyl__c
#>   <chr>     <dbl>
#> 1 civic         4
#> 2 ranger        4
#> 3 miata         4

# This is a lookup table in Salesforce that connects to the main table. The
# lookup field is "model", the external ID is "model_extid". This table needs
# special handling of the lookup field to use sf_upsert(), and the upsert
# needs to use the REST API.
long_tbl <- tribble(
  ~model, ~model_extid, ~year,
  "civic", "civic_1", 1998,
  "civic", "civic_2", 1997,
  "ranger", "ranger_1", 2002,
  "miata", "miata_1", 1990,
  "miata", "miata_2", 1991
)

long_tbl__c <- long_tbl |>
  rename(model__r = model) |>
  mutate(
    model__r = unlist(
      map(model__r, \(.x) list(model__c = .x)),
      recursive = FALSE
    )
  ) |>
  rename_with(\(.x) paste0(.x, "__c"), .cols = -model__r)

long_tbl__c
#> # A tibble: 5 × 3
#>   model__r     model_extid__c year__c
#>   <named list> <chr>            <dbl>
#> 1 <chr [1]>    civic_1           1998
#> 2 <chr [1]>    civic_2           1997
#> 3 <chr [1]>    ranger_1          2002
#> 4 <chr [1]>    miata_1           1990
#> 5 <chr [1]>    miata_2           1991

^{Created on 2025-01-30 with reprex v2.1.1}

Here's what model__r looks like:

[StevenMMortimer]

Got it. Maybe the gap in documentation is letting people know that if you want to set the value of a lookup field using an external id, then it needs to provided as a column of named lists (length 1) and you must use the REST API?

Separate question: Did you ever try setting the lookup field using Salesforce Ids of the records instead of using the external ids? Asking because, I hope/assume that works. I understand that using the external ids might be easier if you have the data on hand, but you could always derive the Salesforce Ids from the external ids. I believe most people update lookup fields by providing a column of Salesforce Ids.

[mccarthy-m-g]

Maybe the gap in documentation is letting people know that if you want to set the value of a lookup field using an external id, then it needs to provided as a column of named lists (length 1) and you must use the REST API?

Yeah, something like that. I don't know if Salesforce actually document this anywhere either, it seemed like hidden knowledge (although if they do maybe linking to that would be helpful).

Separate question: Did you ever try setting the lookup field using Salesforce Ids of the records instead of using the external ids? Asking because, I hope/assume that works.

Yes, we tried that and it does work!