Update Salesforce picklist values and labels

Introduction

This document presents an alternative to using the Salesforce connector as a target for updating picklist values and labels in Salesforce. Salesforce classifies picklist labels and values as metadata, not as field data. Since the Salesforce connector is based on the Enterprise API, it cannot update metadata.

Salesforce provides two APIs for manipulating metadata:

The Tooling API is less cumbersome and is the core of the solution. There are two steps to using the Tooling API for this use case, described below.

Step 1: Retrieve picklist metadata and values

The first step is to retrieve the DurableId of the picklist field. For this use case, we use a Salesforce Query activity to query the EntityDefinition object using a subquery on Fields:

SELECT (SELECT Id, DeveloperName, DurableId, QualifiedApiName, Label, DataType FROM Fields WHERE DataType = 'Picklist'), DeveloperName, QualifiedApiName, NewUrl FROM EntityDefinition WHERE QualifiedApiName = '<picklist object name>'

Example response:

attachment

You then build a dictionary ($dict.picklist.field_id) with a key of the QualifiedApiName and value of the DurableId. This ID is in two parts, separated by a period (.). We are interested in the part following the period.

var = root$transaction.response$body$queryResponse$result$records.EntityDefinition$Fields.DurableId$;
AddToDict($dict.picklist.field_id,root$transaction.response$body$queryResponse$result$records.EntityDefinition$Fields.QualifiedApiName$,
Mid(var,Index(var,".") + 1,Length(var) - Index(var,".")));

This dictionary will be used to look up a field's ID based on its field name. Example: Account__c=>"00N8A00000JUypw".

Step 2: Build an HTTP patch request

The second step is to build the request. The schema required for the request is listed below. This API will overwrite the metadata definition and so the entire schema is sent, including the null fields and empty arrays. (A further refinement of this use case would be to use the first step to retrieve the existing metadata and update only the relevant fields. In that case, the object would be very basic and is not shown here.)

As a transformation will not generate an empty array, a script is used to replace the null value returned in the valueSettings field with an empty array:

$io = Replace($io,'"valueSettings": null','"valueSettings": []');

The HTTP connector is configured as follows:

Base URL: Enter the base URI, in the format of http://<domain>/services/data/<vXX.X>/tooling/ (see the Salesforce documentation Tooling API REST Resources).
HTTP Verb: Select Custom and enter PATCH.
Path: Enter /sobjects/CustomField/<field id>.
Request Headers: Specify a Name of Authorization Bearer and a Value of the session ID, which can be derived using the SalesforceLogin function.

The schema for the Tooling API:

{
    "FullName": "<objectname.fieldname>",
    "Metadata": {
        "businessOwnerGroup": null,
        "businessOwnerUser": null,
        "businessStatus": null,
        "caseSensitive": null,
        "complianceGroup": null,
        "customDataType": null,
        "defaultValue": null,
        "deleteConstraint": null,
        "deprecated": null,
        "description": null,
        "displayFormat": null,
        "displayLocationInDecimal": null,
        "encryptionScheme": null,
        "escapeMarkup": null,
        "externalDeveloperName": null,
        "externalId": false,
        "formula": null,
        "formulaTreatBlanksAs": null,
        "inlineHelpText": null,
        "isAIPredictionField": null,
        "isConvertLeadDisabled": null,
        "isFilteringDisabled": null,
        "isNameField": null,
        "isSortingDisabled": null,
        "label": "Test",
        "length": null,
        "lookupFilter": null,
        "maskChar": null,
        "maskType": null,
        "metadataRelationshipControllingField": null,
        "populateExistingRows": null,
        "precision": null,
        "readOnlyProxy": null,
        "referenceTargetField": null,
        "referenceTo": null,
        "relationshipLabel": null,
        "relationshipName": null,
        "relationshipOrder": null,
        "reparentableMasterDetail": null,
        "required": false,
        "restrictedAdminField": null,
        "scale": null,
        "securityClassification": null,
        "startingNumber": null,
        "stripMarkup": null,
        "summarizedField": null,
        "summaryFilterItems": null,
        "summaryForeignKey": null,
        "summaryOperation": null,
        "trackFeedHistory": false,
        "trackHistory": false,
        "trackTrending": false,
        "translateData": null,
        "type": "Picklist",
        "unique": null,
        "urls": null,
        "valueSet": {
            "controllingField": null,
            "restricted": false,
            "valueSetDefinition": {
                "sorted": false,
                "value": [
                    {
                        "color": null,
                        "default": false,
                        "description": null,
                        "isActive": null,
                        "label": "<labeldata>",
                        "urls": null,
                        "valueName": "<picklistvaluedata>"
                    },
                    {
                        "color": null,
                        "default": false,
                        "description": null,
                        "isActive": null,
                        "label": "<labeldata>",
                        "urls": null,
                        "valueName": "<picklistvaluedata>"
                    },
                    {
                        "color": null,
                        "default": false,
                        "description": null,
                        "isActive": null,
                        "label": "<labeldata>",
                        "urls": null,
                        "valueName": "<picklistvaluedata>"
                    }
                ]
            },
            "valueSetName": null,
            "valueSettings": []
        },
        "visibleLines": null,
        "writeRequiresMasterRead": null
    }
}