Retrieving Dependent Picklist Information

Example
Results

Retrieving Dependent Picklist Information

Dependent picklists in Salesforce are picklist fields whose available values are controlled by another field (referred to as the controlling field). This page shows how to retrieve metadata that defines these relationships, including which picklist values are valid for each controlling field value.

Example

To retrieve dependent picklist information for an object (in this case, the Lead object):

Create an empty Input table, as shown below:

CREATE TABLE MD_LeadPicklists ([Name] [nvarchar](255) NULL,
[Member] nvarchar(255) NULL,
[MetadataXML] xml NULL,
[CreatedByWildcard] bit NULL,
[CreatedByList] bit NULL,
[Error] nvarchar(255) NULL,
[ID] nchar(18) NULL
)

Populate the Input table by inserting a single row into the table that uses a value of CustomObject for the Name column and a value of Lead for the Member column.
```
INSERT INTO MD_LeadPicklists (Name, Member) VALUES('CustomObject', 'Lead')
```
Execute the SF_Metadata stored procedure to retrieve the information.
```
EXEC SF_Metadata 'Retrieve', 'Salesforce', 'MD_LeadPicklists'
```

Execute the following query against the table to generate the results:

-- A query to select dependent picklists
;WITH XMLNAMESPACES(DEFAULT 'http://soap.sforce.com/2006/04/metadata') SELECT Member
,fn.c.value('(../../../fullName)[1]','nvarchar(50)') as FieldName
,fn.c.value('(../../controllingField)[1]','nvarchar(50)') as ControllingFieldName
,fn.c.value('(../valueName)[1]','nvarchar(50)') as PicklistValue ,
fn.c.value('(.)[1]','nvarchar(50)') as ControllingPicklistValue
FROM MD_LeadPicklists
cross apply metadataxml.nodes
('/CustomObject/fields/valueSet/valueSettings/controllingFieldValue') as fn(c)

Results

These steps output a table similar to the following:

Member  FieldName           ControllingFieldName  PicklistValue  ControllingPicklistValue
-----   ---------           --------------------  -------------  ------------------------

Lead   ProductInterest__c   Industry              GC1000 series  Agriculture
Lead   ProductInterest__c   Industry              GC1000 series  Apparel   
Lead   ProductInterest__c   Industry              GC1000 series  Banking
Lead   ProductInterest__c   Industry              GC1000 series  Biotechnology
Lead   ProductInterest__c   Industry              GC1000 series  Construction
Lead   ProductInterest__c   Industry              GC1000 series  Education
Lead   ProductInterest__c   Industry              GC5000 series  Biotechnology
Lead   ProductInterest__c   Industry              GC5000 series  Chemicals
Lead   ProductInterest__c   Industry              GC5000 series  Construction
Lead   ProductInterest__c   Industry              GC5000 series  Electronics