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.

Configuration of Parent__c object

On Child record, I defined a Lookup field to a Parent__c object.

Configuration of Child__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.

Parent__c after inserting with Upsert

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.

Parent__c after updating with Upsert

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.

Child__c after inserting with Upsert, referencing Parent's Name

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.

Child__c and new Parent after inserting together with single Insert/Upsert.

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 record works with GET, as usual.

GET /services/data/v62.0/sobjects/Parent__c/Name/ParentTest04API
_Response:

{
    "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
_Body:

{
    "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/
_Body:

{
    "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.

Child__c and new Parent after inserting together with single Composite Request

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: Parent__r:Parent__c: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

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, Unique__c is not an External ID or indexed field for Parent__c: []

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 ObjectName__c with object’s API name, like Contact or Parent__c, 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 for the idea!
Additional out-of-topic insight from the 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
_Body:

{
   "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), OrderNumber (AUTO-NUMBER)
PricebookEntry	–
Product2	Name (STRING)
Quote	Name (STRING)
QuoteLineItem	LineNumber (AUTO-NUMBER)
Task	–
User	Username (STRING), Email (EMAIL), EmployeeNumber (STRING), FederationIdentifier (STRING)
WorkOrder	WorkOrderNumber (AUTO-NUMBER)
WorkOrderLineItem	LineItemNumber (AUTO-NUMBER)

^{Remember that Auto-Number fields are read-only.}

Code

To generate list of field, use the following code:

List<String> sobjectNames = new List<String>{
    '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<String>> idLookups = new Map<String, List<String>>();

for (DescribeSObjectResult sObjectResult : Schema.describeSObjects(sobjectNames)) {
    List<String> idLookupsInSobject = new List<String>();
    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 every idLookup is External IDs.
You can check which fields have idLookup via Apex DescribeFieldResult.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.