Search code examples
salesforcesoql

Salesforce object describe has big data, how to get limited data like picklist values from salesforce object describe

I am looking for way to get fields and picklists for a salesforce object. I can do it with a REAT API call using /describe after the object name. But sometimes the returned JSON data is really big with 95% extra data I don't want, with repetitive pattern strings.

This would be too inefficient to pull all that data, which could actually be as large as 2.8Mb, just to get small info I require.

How can I query query filter this data to get more specific results? Or is there a better way to get picklists for a field, or any other sub data from that big json at /describe?

Here is what I am using currently

https://[myinstance].salesforce.com/services/data/v51.0/sobjects/Casedata/describe

Solution

  • You can query FieldDefinition table in Tooling API, for example

    /services/data/v52.0/tooling/query?q=SELECT+Metadata+FROM+FieldDefinition+WHERE+EntityDefinitionId+=+'Account'+AND+QualifiedApiName+=+'Status__c'

    (...)
"valueSet" : {
        "controllingField" : null,
        "restricted" : true,
        "valueSetDefinition" : {
          "sorted" : false,
          "value" : [ {
            "color" : null,
            "default" : false,
            "description" : null,
            "isActive" : null,
            "label" : "Prospect",
            "urls" : null,
            "valueName" : "Prospect"
          }, {
            "color" : null,
            "default" : false,
            "description" : null,
            "isActive" : null,
            "label" : "Live",
            "urls" : null,
            "valueName" : "Live"
          }, {
            "color" : null,
            "default" : false,
            "description" : null,
            "isActive" : null,
            "label" : "Cancelled",
            "urls" : null,
            "valueName" : "Cancelled"
          }
(...)

    The picklist values will be in the Metadata field but to query it you need to ensure only 1 row is returned. So if you need 3 picklists - that's 3 API calls...

    It'll return the "master" picklist, not filtered by record type.

    There's also interesting table called PicklistValueInfo. It's not described too well, it's a related list to EntityParticle. You can query to get multiple picklist values in 1 go

    SELECT DurableId,EntityParticleId,IsActive,Label,Value 
FROM PicklistValueInfo
WHERE EntityParticle.EntityDefinition.DeveloperName = 'Account' AND 
(DurableId LIKE 'Account.Industry%' OR DurableId LIKE 'Account.Type%')
ORDER BY DurableId

    enter image description here

    Or use it related list style (which might be closer to results of describe call?)

    SELECT DataType, FieldDefinition.QualifiedApiName,
    (SELECT Value, Label FROM PicklistValues)
FROM EntityParticle 
WHERE EntityDefinition.QualifiedApiName ='Account'
    AND QualifiedApiName IN ('Industry', 'Type', 'Status__c')

    If you use record types - UI API David linked to is easiest.

    https://developer.salesforce.com/docs/atlas.en-us.uiapi.meta/uiapi/ui_api_resources_picklist_values_collection.htm

    You can grab them all

    /services/data/v52.0/ui-api/object-info/Account/picklist-values/012...

    enter image description here

    Or build links like shown on the screenshot to get data for single field.