Upsert & Parent Lookup via Name Field (idLookup property)

For last few years of working with the Salesforce platform, I had following understanding of the upsert
statement:
- it is an operation that will either create a new record, or update it already exists;
- it can use other referencing field than standard ID. Using it requires naming that field in a statement, and the field must be an External ID.
Recently I was doing some data importing with Apex scripts, and accidentally came on the topic of idLookup
attribute, which makes the second statement not entirely true.
What exactly is idLookup
?
It is a field’s property, and it’s referenced in some documentation pages. Let’s look at the most descriptive one that I found, on API Field Properties:
idLookup
Can be used to specify a record in an upsert call. The Id field of each object has this property and some Name fields. There are exceptions, so check for the property in any object you wish to upsert.
Word "some" is interesting here – it is not a precise quantifier by any means, so what exactly is going on?
I tested it by checking Standard Objects and creating multiple Custom Object. At the end, it appears that not all of Standard Objects have that property on their Name field, but all of created Custom Objects do. Also, the following is true – all External ID fields are idLookup
, but not all idLookups
are External ID.
Venn diagram of idLookup and External ID
Another interesting place is SOAP documentation for upsert:
Upsert call is available for objects if the object has an external ID field or a field with the
idLookup
field property.
On custom objects, this call uses an indexed custom field called an external ID to determine whether to create a record or update an existing record. On standard objects, this call can use the name of any field with the idLookup instead of the external ID.
So, in the first part it is saying that idLookup
allows upserts, then in the second part it states that Custom Objects shall use External ID, while Standard Object can use fields with idLookup
. It is, again, slightly confusing and contradicting.
So, what all of this means, how this field’s property can be checked, and what it allows to do? Let’s test it out.
Salesforce Documentation uses both "property" and "attribute" words when mentioning
idLookup
. Therefore, these two will be using interchangeably in the whole post.
Apex
You can check whether the field has idLookup
property, using isIdLookup()
method on DescribeFieldResult
instance. It can be obtained from some Schema
methods, sObjectType
tokens, and other ways.
Contact.Email.getDescribe().isIdLookup(); // using sObjectType token Schema.getGlobalDescribe() .get('Contact').getDescribe().fields.getMap() .get('Email').getDescribe().isIdLookup(); // checking dynamically
REST
It can also be checked using REST API on standard endpoints.
GET /services/data/v62.0/sobjects/{{ObjectApiName}}/describe
Response:
{ ... "fields": [ { "externalId": false, "idLookup": true, "label": "Parent Name", "length": 80, "name": "Name", }, ... ], ... }
Referencing other records using field with idLookup
attribute
How it can be used? Let’s see if the usage of External ID works for the idLookup
field of Name, which is available on every Custom Object by default.
Custom Objects configuration
I’ve created two Custom Objects, Parent__c
and Child__c
. Neither of them have an External ID fields defined.
On Parent record, I defined an additional Text field, Description
.
On Child record, I defined a Lookup field to a Parent__c
object.
As mentioned before, Name
field on both of them has idLookup
attribute by default.
Then, I’ve created a single Parent record with Name 'Parent Test 01'
.
Apex
Upsert statement on Name field
Salesforce documentation on Upserting Records, mentions idLookup
attribute
The code below is run from Anonymous console.
Let’s try to use upsert
with a Name that does not exists yet.
upsert new Parent__c ( Name ='Parent Test 02', // Not existing yet Description__c = 'Inserted with Upsert on Name' ) Name;
The record was inserted successfully.
Now, use the same Name
, but change the Description__c
, which should result on an update.
upsert new Parent__c ( Name ='Parent Test 02', // Already existing Description__c = 'Now, updated with Upsert on Name' ) Name;
The record was updated successfully, the Description has changed.
Insert Child linked to Parent
Salesforce documentation on Relating Records by Using an External ID
The code below is run from Anonymous console.
Let’s try to insert Child record referencing Parent, without querying the Parent’s record ID, but using their Name.
insert new Child__c( Name = 'Child 01', Parent__r = new Parent__c(Name ='Parent Test 01') );
The record was inserted successfully, and the Parent was referenced properly.
Let’s now try with a non-existing Parent name:
insert new Child__c( Name = 'Child 02', Parent__r = new Parent__c(Name ='Parent Non exiting') );
And the error was received:
Error on line 1, column 1: System.DmlException: Insert failed. First exception on row 0; first error: INVALID_FIELD, Foreign key external ID: Parent Non exiting not found for field Name in entity Parent__c: []
As we can see, the error message is the same as upon calling Upsert, when no such records were found.
Similar to Upsert, when I create another Parent record with the same Name, 'Parent Test 01'
, I get the Exception related to duplicates:
Error on line 1, column 1: System.DmlException: Insert failed. First exception on row 0; first error: DUPLICATE_EXTERNAL_ID, Parent Name: more than one record found for external id field: [a09Qy000008zG01IAE, a09Qy000008zFK5IAM]: [Name]
Since Name
can’t be configured as Unique on Custom Objects, using it as an external reference can easily cause DUPLICATE_EXTERNAL_ID errors in upserts or child record inserts if more than one record exists with the same Name
. More about it later.
Creating Parent and Child Records in a Single Statement
Salesforce documentation on Creating Parent and Child Records in a Single Statement Using Foreign Keys
The code below is run from Anonymous console.
Let’s now try to insert Child and Parents records at the same time, referencing the relation by the Parent’s Name field. The code below also uses only one DML statement.
Parent__c parentInstance = new Parent__c(); parentInstance.Name = 'Parent Test 03'; parentInstance.Description__c = 'Inserted with Child in single statement.'; Parent__c parentReference = new Parent__c(Name = parentInstance.Name); Child__c childInstance = new Child__c(); childInstance.Parent__r = parentReference; childInstance.Name = 'Child 02'; insert new List<SObject>{parentInstance, childInstance}; // upsert statement also works Assert.areEqual(1, Limits.getDmlStatements());
Both records were inserted successfully and linked, using one DML statement limit.
REST
You can use fields with idLookup
with REST API calls on standard endpoints, with GET and PATCH records. Like previous, let’s use Name
field.
PATCH – works like Upsert statement
Upserting record works on PATCH request.
PATCH /services/data/v62.0/sobjects/Parent__c/Name/ParentTest04API
Empty Body:
``` #### GET - obtaining the records [Getting](https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/resources_sobject_upsert_get.htm) record works with GET, as usual. `GET /services/data/v62.0/sobjects/Parent__c/Name/ParentTest04API` <sub>Response:</sub> ```json { "attributes": { "type": "Parent__c", "url": "/services/data/v62.0/sobjects/Parent__c/a09Qy000008zLxNIAU" }, "Id": "a09Qy000008zLxNIAU", "Name": "ParentTest04API", "Description__c": "", // rest of system fields... }
POST – link Child to Parent bwith nested object
Linking works on JSON object, allowing to reference parent on `idLookup` field:
`POST /services/data/v62.0/sobjects/Child__c`
<sub>Body:</sub>
{ "Name": "Child 04 API", "Parent__r": { "Name": "ParentTest03" } }
Child record is successfully created and related to a Parent.
POST – insert both Parent and Child with Composite request
A Composite request can be used to create both records.
`POST /services/data/v62.0/composite/`
<sub>Body:</sub>
{ "allOrNone": true, "compositeRequest": [ { "method": "PATCH", "url": "/services/data/v62.0/sobjects/Parent__c/Name/ParentTest05API", "referenceId": "NewParent", "body": { "Description__c": "Created in Composite request with Child" } }, { "method": "POST", "url": "/services/data/v62.0/sobjects/Child__c", "referenceId": "newChild", "body": { "Name": "Child 03 API", "Parent__r": { "Name": "ParentTest05API" } } } ] }
Both records were inserted successfully and linked, using one Request.
</br>
LWC
Sadly, the `lightning/uiRecordApi` does not allow to reference records using External IDs, as they strictly need Salesforce ID. Related Stack Exchange post – How to Create Record from LWC using External Id in lookup fields. The examples from Apex's section at the top work fine in the imported referenced methods.
Data Importing with Salesforce Inspector Reloaded
Using the mighty Salesforce Inspector Reloaded, we can specify the Name field as well, for both upserts, and for referencing the Parent from child's level. In the latter, mapping requires following syntax: `Parentr:Parentc:Name`
Example CSV for Child insert.
"_","Name","_Parent__r","Parent__r.Name" "[Child__c]","Child 13","", "[Child__c]","Child 12","[Parent__c]","Parent Test 03" "[Child__c]","Child 11","[Parent__c]","Parent Test 01"
Note that `"[Parent__c]"` value in lines – the column itself should be ignored with _underscore, but is important to be there, as it allows to correctly import the parentless records. Without it, you might get error mentioning missing cells:
"_","Name","Parent__r.Name" "[Child__c]","Child 13","",
`Error: row 2 has 3 cells, expected 1`
</br>
Other fields configurations
Polymorphic Fields
The tricks above does not work for Polymorphic Fields, like Task's `What` or `Who`. The following code results in an exception:
insert new Task( Subject = 'Task with Parent using Name', What = new Parent__c(Name ='Parent Test 01') );
Exception message: `Compile failure on line 4, column 11: Field is not writeable: Task.What`
It was also confirmed on StackExchange question.
Unique
Making a field Unique in the Setup makes that field indexed, however it does not add `idLookup` property, and that field cannot be used in the scenarios presented above. Trying them results in the following errors:
-
upserting on Unique field:
`Compile failure on line 2, column 9: Invalid field for upsert, must be an External Id custom or standard indexed field: Unique__c` -
referencing Parent with Unique field:
`Error on line 1, column 1: System.DmlException: Insert failed. First exception on row 0; first error: INVALID_FIELD, Field name provided, Uniquec is not an External ID or indexed field for Parentc: []`
Possible issue with duplicate values on Name
Sadly, standard Name field cannot be configured an Unique, which means the above features are prone to duplicates, resulting in the following exception:
`System.DmlException: Insert failed. DUPLICATE_EXTERNAL_ID, Parent Name: more than one record found for external id field: [recordId_1, recordId_2]: [Name]`
Workarounds:
1. Validation Rule with VLOOKUP
Replace `ObjectNamec` with object's API name, like `Contact` or `Parentc`, and `Name` with the field's API name.
AND( Name = VLOOKUP($ObjectType.ObjectName__c.Fields.Name, $ObjectType.ObjectName__c.Fields.Name, Name), OR(ISNEW(), ISCHANGED(Name)) )
Source: Sara Monksfield's at Salesforce Ideas.
You might also find previous version, most likely less performant, without use of `ISNEW()` and `ISCHANGED()` at Marty Y. Chang's blogpost.
2. Duplicate Rules
Duplicate Rules with Matching Rules can be used to prevent duplicates. Only five Duplicates per object are allowed.
3. Using Flow with Get Record
In Record Trigger Flow, two things should be implemented:
- Check database with Flow's Get Record block, checking `WHERE Name = Record.Name`
- For Bulk operations, check for duplicates in the whole collection that is being insert.
- Create a Text Collection variable,
- Loop over inserted Records, adding the Names into new Text Collection
- Before adding entry to the Text Collection, add Condition block with a check again it, using CONTAINS operator
4. Trigger with SOQL
With Flow or Trigger, two things should be implemented:
- Check database with Trigger's SQL, with `WHERE Name = {record from Trigger.new}.Name`
- For Bulk operations, check for duplicates in the whole `Trigger.new`
- Create a `Set namesSet`,
- Loop over the `Trigger.new`, adding the Names into the Set,
- Before adding entry to the Set, add if-statement with `namesSet.contains(record.Name)` method
Examples of use
Creating Users in Tests without querying Profile and Role
Both `Profile` and `UserRole` have `idLookuip` on their Name fields, so following snippet works, saving some SOQL statements:
User user = new User( LastName = 'Some Last Name', Alias = 'Some Alias', Profile = new Profile(Name = 'Standard User'), UserRole = new UserRole(Name = 'Manager'), Username = 'SomeUsername@someemail.com', Email = 'SomeEmail@someemail.com', TimeZoneSidKey = 'America/Los_Angeles', LocaleSidKey = 'en_US', EmailEncodingKey = 'UTF-8', LanguageLocaleKey = 'en_US' ); insert user;
Thanks [Piotr Gajek](https://www.linkedin.com/posts/piotr-gajek_have-you-heard-about-salesforce-idlookup-activity-7333041367901798400-3cdQ/ "Piotr Gajek") for the idea!
Additional out-of-topic insight from the [Scott Covert](https://www.linkedin.com/feed/update/urn:li:ugcPost:7333041366907752448?commentUrn=urn%3Ali%3Acomment%3A%28ugcPost%3A7333041366907752448%2C7333096361753526273%29&dashCommentUrn=urn%3Ali%3Afsd_comment%3A%287333096361753526273%2Curn%3Ali%3AugcPost%3A7333041366907752448%29 "Scott Covert"): you can reference that User object in `System.runAs(user)` without actually inserting the record, and it will work.
Referencing RecordType in REST without Id
Name field on `RecordType` object is `idLookup` as well, which can be handy in API integrations. No more needs to store RecordTypeId in external system, or adding a custom field `RecordTypeName__c` with trigger to fill the Id during the processing:
`POST /services/data/v62.0/sobjects/Contact`
<sub>Body:</sub>
{ "FirstName": "Eren", "LastName": "Jaeger", "RecordType": { "Name" : "Paradis Eldian" } }
Standard Objects with `idLookup` fields
A table of some of Standard Objects with their fields with `idLookup` property.
Standard Object | Fields with idLookup, acting like External ID |
---|---|
Account | – |
Address | Name (AUTO-NUMBER) |
Asset | Name (STRING), |
Campaign | Name (STRING) |
CampaignMember | – |
Case | CaseNumber (AUTO-NUMBER) |
Contact | Email (EMAIL) |
Contract | ContractNumber (AUTO-NUMBER) |
Incident | IncidentNumber (AUTO-NUMBER) |
Individual | – |
Invoice | DocumentNumber (AUTO-NUMBER) |
Lead | Email (EMAIL) |
Opportunity | Name (STRING) |
OpportunityLineItem | – |
Order | OrderReferenceNumber (STRING), <br>OrderNumber (AUTO-NUMBER) |
PricebookEntry | – |
Product2 | Name (STRING) |
Quote | Name (STRING) |
QuoteLineItem | LineNumber (AUTO-NUMBER) |
Task | – |
User | Username (STRING), <br>Email (EMAIL), <br>EmployeeNumber (STRING), <br>FederationIdentifier (STRING) |
WorkOrder | WorkOrderNumber (AUTO-NUMBER) |
WorkOrderLineItem | LineItemNumber (AUTO-NUMBER) |
<sup>Remember that Auto-Number fields are read-only.</sup>
Code
To generate list of field, use the following code:
List sobjectNames = new List{ 'Account', 'Address', 'Asset', 'Asset', 'Campaign', 'CampaignMember', 'Case', 'Contact', 'Contract', 'Incident', 'Individual', 'Invoice', 'Lead', 'Opportunity', 'OpportunityLineItem', 'Order', 'PricebookEntry', 'Product2', 'Quote', 'QuoteLineItem', 'Task', 'User', 'WorkOrder', 'WorkOrderLineItem' }; Map<String, List> idLookups = new Map<String, List>(); for (DescribeSObjectResult sObjectResult : Schema.describeSObjects(sobjectNames)) { List idLookupsInSobject = new List(); idLookups.put(sObjectResult.getName(), idLookupsInSobject); for (SObjectField sObjectField : sObjectResult.fields.getMap().values()) { DescribeFieldResult describeFieldResult = sObjectField.getDescribe(); if (describeFieldResult.isIdLookup()) { String fieldType = describeFieldResult.getType().toString(); if (fieldType.equals('ID')) { // All ID has idLookup property continue; } if (describeFieldResult.isAutoNumber()) { fieldType = 'AUTO-NUMBER'; } idLookupsInSobject.add(describeFieldResult.getName() + ' (' + fieldType + ')'); } } } System.debug(JSON.serializePretty(idLookups));
Summary
So, what we have learned:
- The
idLookup
property is what really allows for upsert operations – not everyidLookup
is External IDs. - You can check which fields have
idLookup
via ApexDescribeFieldResult.isIdLookup()
or the REST API. - The standard Name field on Custom Objects always has
idLookup=true
, allowing it to act as an identifier for upsert. - Apex code to use Name fields for referencing parent record, in many separated individual transactions and in single statements.
- Composite REST request using the PATCH method for Upsert, referencing
idLookup
in place of External ID. - This feature can be practically useful in record creation via Apex script or CSV, integrations, and simplifying upsert logic.
References
- https://developer.salesforce.com/docs/atlas.en-us.object_reference.meta/object_reference/access_for_fields.htm#:~:text=18.0%20and%20later.-,idLookup,-Can%20be%20used
- https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_calls_upsert.htm#:~:text=field%20with%20the-,idLookup,-instead%20of%20the
- https://developer.salesforce.com/docs/atlas.en-us.254.0.object_reference.meta/object_reference/access_for_fields.htm#access_lookup
- https://developer.salesforce.com/docs/atlas.en-us.apexref.meta/apexref/apex_methods_system_fields_describe.htm#apex_Schema_DescribeFieldResult_isIdLookup
- https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_dml_examples_upsert.htm
- https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_dml_nested_object.htm
- https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_dml_foreign_keys.htm
- https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/resources_sobject_upsert_patch.htm
- https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/resources_sobject_upsert_get.htm
- https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/dome_composite_junction_object.htm
- https://developer.salesforce.com/docs/platform/lwc/guide/reference-lightning-ui-api-record.html
- https://chromewebstore.google.com/detail/salesforce-inspector-relo/hpijlohoihegkfehhibggnkbjhoemldh
- https://salesforce.stackexchange.com/a/244712/113954
- https://ideas.salesforce.com/s/feed/0D58W00009el1vLSAQ
- https://frombelvideres4thfloor.blogspot.com/2010/12/enforcing-record-name-uniqueness-in.html
- https://help.salesforce.com/s/articleView?id=sales.duplicate_rules_map_of_reference.htm&type=5