Operations on Upsert Mode
The "Upsert" mode of a sheet is a mode dedicated to importing external data on a sheet to update record data or insert new records. In upsert mode, when importing data from Excel or spreadsheets, it is easy to look up existing records and update them, or insert new records to avoid duplicates.
Overview of Upsert Mode
Identification Key
In upsert mode, some of columns in the sheet are marked as a key to identify the record (identification key). The identification key is responsible for linking the data to be imported to the record in Salesforce. Columns with the following fields can be used as identification keys.
Salesforce ID fields
Fields marked as unique in Salesforce
Auto-Number fields
Fields marked as "External ID" in Salesforce
Fields that are likely to be generally unique within the object (e.g. Account name, e-mail address, etc.)
Configuration
To enter the sheet into UPSERT mode, one or more columns must be set as a identification key. To set a column as a identification key, follow these steps:
Select "Setting" from the column header menu to display the column setting dialog
Check "Use as Key to Identify Records" [A] from the Editor Setting in the "Options" tab in the column settings dialog, and press save button
A key icon [B] appears next to the display label in the column header
Behavior
In upsert mode, no record information is retrieved immediately after the sheet is displayed or refreshed, and only blank rows are displayed on the sheet.
When a new row is added by pressing the "+" button [A] on an empty row in the sheet, an orange symbol [B] will appear at the head of that row just as it does in normal mode.
When you select a range of data to be imported from an Excel sheet or spreadsheet, copy it, and paste it against the cell in the new row created in the sheet, the system automatically looks for records matching the pasted values in the column specified as the identification key.
If an existing record is found that exactly matches the value of the key, the record's information is loaded and displayed in that row. A green mark [C] appears at the head of the row to indicate that the existing record is to be updated when saved. For data other than the identification key column, only cells with a difference in value from the existing record found will be marked for updating. [D]
If no record matching the key value is found, an orange marker will remain at the head of the row, indicating that a new record will be created when the row is saved. [E]
When the "Save" button on the sheet is pressed, corresponding records are updated for rows that match existing records (green mark at the head of the row), and new records are created for rows that do not have matching records for the key (orange mark at the head of the row).
Resolving Duplicated Records
If there are multiple records matching a key value, one of the multiple records is automatically selected as the record to be updated, and the cell for the identification key in that row displays a duplicate icon [F].
By double-clicking on the key cell and doing a lookup search, you can explicitly specify the record to be updated from among the duplicates.
Selecting Record to be Updated in Lookup Search
If no matching record is found or if there are duplicate records, it is possible to explicitly search and specify the record to be updated. By double-clicking on the cell of the key and performing a lookup search to find the target record, you can explicitly specify the record to be updated.
Lookup searches in identification key columns can be performed in the same way as in a normal lookup search of a reference field column, that is, by matching a keyword. You can also display Lookup Search Dialog to search for and specify records in a grid table format.
Specifying Compound Key
Multiple identification key columns can be specified in a sheet ("Compound Key"). If multiple columns with identification keys are set, the existing records will be searched for those matching the values (excluding blank values) of all the identification key columns.
Filter Behavior in Upsert Mode
In uspert mode, as in normal mode, filters can be set for columns in the sheet. However, the filter in upsert mode is used to narrow down the search for records matching the identification key in advance. Therefore, even if a record matching a key value exists in the database, if the record does not meet the filter criteria, it will not be resolved as a candidate record for update.
For example, if a sheet that imports lead information using an email field as a identification key has a filter that requires that the opt-out field for sending email be unchecked, a lead record that has opted out of sending email would not be eligible for updating even if the same email address is in the import data.
In addition, when child object records are displayed in a related sheet, the records are filtered by the ID of the parent record selected in the source sheet of the related sheet, so the search target is narrowed down to only those records belonging to the same parent record in upsert mode. This is also the case when a sheet is embedded in a record page with the Sheet component and filtered by the related record ID.
Last updated