Monday, March 23, 2015

Filled Under: , , ,

Getting Dependent Picklist Values from your Apex Code


The requirement is to get all the State values based on the Country values from the standard Country - State picklist values.
In several Salesforce.com implementations, the standard State and Country Picklists are enabled and setup as per the org requirement.
In many cases, we may need to query all the States active for a specific country.
For example, 
1. In a custom page we want to show users valid States to choose from based on the Country chosen by the user.
2. We might have an external UI application using the Force.com platform and we need to expose State values based on Country values.
To do so, I need to fetch dependent picklist values based on the controlling field value.
For example, for country United States the values returned should be "Alabama", "Alaska", "Arizona",......etc.
Salesforce does not allow in your apex code to directly fetch such values.
However, in the API documentation (Salesforce documentation), the PicklistEntry object is shown to have a field as below:       
Name   Type   Description
validFor   byte[]  A set of bits where each bit indicates a controlling  value for which this PicklistEntry is valid.
So there we have the information of the dependency, which value in a dependent picklist set depends on which controlling value. 
Now to use this information, we have two hurdles to overcome:
First problem, as the description of validFor says, the value returned is a set of bits, which we need to decode to get the original field value.
Second problem, we need to use SOAP API from an external application to get the values based on the controlling value.

Now we solve.
For the first problem, Salesforce document gives a Java method, showing how to decode the bits and get the validFor value. We need to find a way to get the values in the validFor in a human readable format, namely, the string value.
We create an Apex method to find the values from the byte array we get.
Now, my requirement is to get the dependent values based on the controling value in my apex class and expose the same values to an external service to consume, without any additional logic on the external application.
The external application will just use a simple service query and get all the results, like in a simple REST query to get the value of a text field.
Once we think about it, the Salesforce documentation exposes the validFor field in the API calls. Hence, if we serialize this PicklistEntry object value to a JSON structure, the validFor value will get transfered, and if we now deserialize the same JSON into a wrapper class we can get the validFor value. Then, all we need to do is to decode this set of bits, and get the validFor value.
What we do first, is create an Apex class to decode the bit set for validFor following the Java example in the documentation.

public class BitSet{
    public Map<String,Integer> alphaNumCharCodes {get;set;}
    public Map<String, Integer> base64CharCodes {get;set;}
    
    public BitSet(){
        LoadCharCodes();
    }
    
    //Method loads the character codes for all letters
    private void LoadCharCodes(){
        alphaNumCharCodes = new Map<String,Integer>{
            'A'=>65,'B'=>66,'C'=>67,'D'=>68,'E'=>69,'F'=>70,'G'=>71,'H'=>72,'I'=>73,'J'=>74,
            'K'=>75,'L'=>76,'M'=>77,'N'=>78,'O'=>79,'P'=>80,'Q'=>81,'R'=>82,'S'=>83,'T'=>84,
            'U'=>85,'V'=> 86,'W'=>87,'X'=>88,'Y'=>89,'Z'=>90    
        };
        base64CharCodes = new Map<String, Integer>();
        //all lower cases
        Set<String> pUpperCase = alphaNumCharCodes.keySet();
        for(String pKey : pUpperCase){
            //the difference between upper case and lower case is 32
            alphaNumCharCodes.put(pKey.toLowerCase(),alphaNumCharCodes.get(pKey)+32);
            //Base 64 alpha starts from 0 (The ascii charcodes started from 65)
            base64CharCodes.put(pKey,alphaNumCharCodes.get(pKey) - 65);
            base64CharCodes.put(pKey.toLowerCase(),alphaNumCharCodes.get(pKey) - (65) + 26);
        }
        //numerics
        for (Integer i=0; i<=9; i++){
            alphaNumCharCodes.put(string.valueOf(i),i+48);
            //base 64 numeric starts from 52
            base64CharCodes.put(string.valueOf(i), i + 52);
        }
    }
        
      public List<Integer> testBits(String pValidFor,List<Integer> nList){
            List<Integer> results = new List<Integer>();
            List<Integer> pBytes = new List<Integer>();
            Integer bytesBeingUsed = (pValidFor.length() * 6)/8;
            Integer pFullValue = 0;
            if (bytesBeingUsed <= 1)
                return results;
            for(Integer i=0;i<pValidFor.length();i++){
                pBytes.Add((base64CharCodes.get((pValidFor.Substring(i, i+1)))));
            }   
            for (Integer i = 0; i < pBytes.size(); i++)
            {
                Integer pShiftAmount = (pBytes.size()-(i+1))*6;//used to shift by a factor 6 bits to get the value
                pFullValue = pFullValue + (pBytes[i] << (pShiftAmount));
            }
            
            Integer bit;
            Integer targetOctet;
            Integer shiftBits;
            Integer tBitVal;
            Integer n;
            Integer nListSize = nList.size();
            for(Integer i=0; i<nListSize; i++){
                n = nList[i];
                bit = 7 - (Math.mod(n,8)); 
                targetOctet = (bytesBeingUsed - 1) - (n >> bytesBeingUsed); 
                shiftBits = (targetOctet * 8) + bit;
                tBitVal = ((Integer)(2 << (shiftBits-1)) & pFullValue) >> shiftBits;
                if (tBitVal==1)
                    results.add(n);
            }
            return results;
        }
}

Next, important step, is to define a wrapper class which we will use to deserialize the JSON format and get the validFor value in a field of the class.


public class PicklistEntryWrapper{
    
    public PicklistEntryWrapper(){            
    }
    public String active {get;set;}
    public String defaultValue {get;set;}
    public String label {get;set;}
    public String value {get;set;}
    public String validFor {get;set;}
}

Next, we fetch all the controlling and dependent values for specific picklist fields in an object.
Then we search through each dependent value and see if it is valid for a specific controlling field.
We define a method to do so. To get any dependent picklist values we pass the method, the object api name, the controlling field api name and the dependent api field name.
We get back the list of dependent values, as a comma separated text, for each controlling field value.
For each controlling field we check the dependent values and check if the dependent is valid for the controlling field. We insert the controlling field into a Map<String,List<String>> as the key and the list of dependent values in a list as the value.


public with sharing class PicklistFieldController {    
    
    public Map<String,List<String>> getDependentOptionsImpl(String objName, String contrfieldName, String depfieldName){
        
        String objectName = objName.toLowerCase();
        String controllingField = contrfieldName.toLowerCase();
        String dependentField = depfieldName.toLowerCase();        
        
        Map<String,List<String>> objResults = new Map<String,List<String>>();
        //get the string to sobject global map
        Map<String,Schema.SObjectType> objGlobalMap = Schema.getGlobalDescribe();
        if (!Schema.getGlobalDescribe().containsKey(objectName)){
            System.debug('OBJNAME NOT FOUND --.> ' + objectName);
            return null;
        }
        
        Schema.SObjectType objType = Schema.getGlobalDescribe().get(objectName);
        if (objType==null){
            return objResults;
        }
        Bitset bitSetObj = new Bitset();
        Map<String, Schema.SObjectField> objFieldMap = objType.getDescribe().fields.getMap();
        //Check if picklist values exist
        if (!objFieldMap.containsKey(controllingField) || !objFieldMap.containsKey(dependentField)){
            System.debug('FIELD NOT FOUND --.> ' + controllingField + ' OR ' + dependentField);
            return objResults;     
        }
        List<Schema.PicklistEntry> contrEntries = objFieldMap.get(controllingField).getDescribe().getPicklistValues();
        List<Schema.PicklistEntry> depEntries = objFieldMap.get(dependentField).getDescribe().getPicklistValues();
        objFieldMap = null;
        List<Integer> controllingIndexes = new List<Integer>();
        for(Integer contrIndex=0; contrIndex<contrEntries.size(); contrIndex++){            
            Schema.PicklistEntry ctrlentry = contrEntries[contrIndex];
            String label = ctrlentry.getLabel();
            objResults.put(label,new List<String>());
            controllingIndexes.add(contrIndex);
        }
        List<Schema.PicklistEntry> objEntries = new List<Schema.PicklistEntry>();
        List<PicklistEntryWrapper> objJsonEntries = new List<PicklistEntryWrapper>();
        for(Integer dependentIndex=0; dependentIndex<depEntries.size(); dependentIndex++){            
               Schema.PicklistEntry depentry = depEntries[dependentIndex];
               objEntries.add(depentry);
        } 
        objJsonEntries = (List<PicklistEntryWrapper>)JSON.deserialize(JSON.serialize(objEntries), List<PicklistEntryWrapper>.class);
        List<Integer> indexes;
        for (PicklistEntryWrapper objJson : objJsonEntries){
            if (objJson.validFor==null || objJson.validFor==''){
                continue;
            }
            indexes = bitSetObj.testBits(objJson.validFor,controllingIndexes);
            for (Integer idx : indexes){                
                String contrLabel = contrEntries[idx].getLabel();
                objResults.get(contrLabel).add(objJson.label);
            }
        }
        objEntries = null;
        objJsonEntries = null;
        return objResults;
    }

}

Now, to get the standard Country - State picklist values, we need to make use of two fields in the Account object.
a) ShippingCountryCode, b) ShippingStateCode

Once we use these two fields as the controlling field and the dependent field, we get all the Country names and the dependent State names respectively.

We write a simple Test class and to get the dependent values and print them.


@isTest
public class PicklistFieldControllerTest {    
    static testMethod void getDependentOptionsImplTest(){
        PicklistFieldController controller = new PicklistFieldController();
        Map<String,List<String>> valueMap = controller.getDependentOptionsImpl('Account','ShippingCountryCode','ShippingStateCode');
        for(String contr : valueMap.keySet()){
            System.debug('CONTROLLING FIELD : ' + contr);
            System.debug('DEPENDENT VALUES ...  : ' + valueMap.get(contr));
        }
    }

}

I have only two Countries enabled 1. India, 2. Unites States
So what I get running the test class :

CONTROLLING FIELD : United States
DEPENDENT VALUES ...  : Armed Forces Americas, Armed Forces Europe

CONTROLLING FIELD : India
DEPENDENT VALUES ...  : Andaman and Nicobar Islands, Andhra Pradesh, Arunachal Pradesh, Assam, Bihar, Chandigarh, Chhattisgarh, Dadra and Nagar Haveli, Daman and Diu, Delhi, ...

So we are done. Now, access dependent values based on controlling value for a dependent picklist field, right from your apex code.

16 comments:

  1. Great work!
    I have the same requirement and you just gave me the answer I was looking for.
    But I think it might be a little bug in the BitSet implementation. When I run the test in the "Execute Anonymous" Debug Console, the states are not been shown correctly to their countries.
    Have you had this problem?

    ReplyDelete
  2. Hi,

    I am trying to implement this and ran into NULL exception in testBits method.

    Line: pFullValue = pFullValue + (pBytes[i] << (pShiftAmount));


    I did further debugging and found below:

    My picklists are Product and Version (Product is controlling field for Version). I have Version value as 10.2.2 and converted to PicklistWrapper using below statement:
    objJsonEntries = (List)JSON.deserialize(JSON.serialize(objEntries), List.class);

    After converting:

    PicklistEntryWrapper:[active=true, defaultValue=false, label=10.2.2, validFor=hKv4H8C/8iEQAsh8, value=10.2.2]

    The "validFor" has "/" and the BitSet class is returning "null" for "/" at pBytes[i] in my previous comment.

    Line: pFullValue = pFullValue + (pBytes[i] << (pShiftAmount));

    Debug log for
    14:32:45.051 (2051450466)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: h *** base 64 code: 33
    14:32:45.051 (2051580717)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: K *** base 64 code: 10
    14:32:45.051 (2051705340)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: v *** base 64 code: 47
    14:32:45.051 (2051829637)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: 4 *** base 64 code: 56
    14:32:45.051 (2051952643)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: H *** base 64 code: 7
    14:32:45.052 (2052075412)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: 8 *** base 64 code: 60
    14:32:45.052 (2052198921)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: C *** base 64 code: 2
    14:32:45.052 (2052320420)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: / *** base 64 code: null
    14:32:45.052 (2052442088)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: 8 *** base 64 code: 60
    14:32:45.052 (2052567420)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: i *** base 64 code: 34
    14:32:45.052 (2052692673)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: E *** base 64 code: 4
    14:32:45.052 (2052816002)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: Q *** base 64 code: 16
    14:32:45.052 (2052939342)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: A *** base 64 code: 0
    14:32:45.053 (2053061557)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: s *** base 64 code: 44
    14:32:45.053 (2053185562)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: h *** base 64 code: 33
    14:32:45.053 (2053308526)|USER_DEBUG|[44]|DEBUG|********** pValidFor char: 8 *** base 64 code: 60


    Anyone has any idea why this null exception? I tried to skip if the value is "null", then I got wrong mappings for depedent picklist.


    Any help much appreciated.

    Thanks in advance.
    Hari

    ReplyDelete
  3. Hi,
    great work on this brain teaser. Thanks for that.
    It worked great for me for fields that did not have too many values, or did not have dependent values associated with many controlling values.
    However, when any combination of the above conditions occurred, it gave me null pointer exceptions and values matched to incorrect controlling values. What I did notice was, incorrectly matched indices fit the formula i + 8*n, where if index 6 matched, then any indices found at 14, 22, 30, 38 and so on, were matched as well although they shouldn't be.
    I'd love to dive into this but I get lost in the binary conversions and shifts left and right. So I can't figure out how to prevent this "harmonic resonance" of sorts.
    Could you come back with at least an explanation on why it is like that?

    Thanks in advance

    -ralf

    ReplyDelete
  4. Its working but not perfectly for some of the countries i am not getting values,ex : Germany

    ReplyDelete
  5. Hi all,

    I have tha same problem as @Ralf Desulme. I investigated a bit and

    1) I fixed the null by adding this to the end of LoadCharCodes
    ...
    AlphaNumCharCodes.put('+', 43);
    AlphaNumCharCodes.put('/', 47);
    Base64CharCodes.put('+', 62);
    Base64CharCodes.put('/', 63);

    2) I changed the shiftBits calculation in testBit so that it would work in every situation:
    shiftBits = bytesBeingUsed * 8 - n - 1;

    3) I have a last problem which points down to the JSON Serializer. It seems that sometimes, the JSON Serializer serilizes the validFor with prepending zeros, but sometimes, it does not. Since the position of the bit is read from left to right for dependant field, this causes a major problem since the start is not always at the same place.

    For example, I have 29 controlling fields:
    When dependant field is active on controlling field #26, I get
    valueFor=AAAAIAAAA by JSON
    which really means:
    0000 0000 0000 0000 0000 0000 0010 0000 0000 0000 0000 0000

    however, if active is enabled on controlling field #25, I get
    valueFor=QAAA by JSON
    which really means:
    0100 0000 0000 0000 0000 0000


    Obviously, since the first 3 bytes are not present, I am not able to calculate correctly the position of the bit I need to check.

    Anyone knows why the JSON Serializer does this?

    Thx

    ReplyDelete
    Replies
    1. Hello Hugo,

      I would like to know how you did for point 2.
      I do not understand what you changed in the code. Can you provide us with the updated code?

      Thank you.

      James

      Delete
  6. Heyy i am in thus situtation that i have 3 picklist picklist 1 picklist 2 and picklist 3 . Picklist 1 is controlling list for picklist 2 and picklist 2 is controlling field for picklist 3. Now i am able to fetch the picklist 2 dependent values based on picklist 1 controlling value. But problem is that on the basis of picklist 2 selectrd value i have to fetch picklist 3 depent values. The same function who return me picklist 2 depent value not returning picklist 3 dependent value for conttolling value from picklist 2

    ReplyDelete
  7. Heyy i am in thus situtation that i have 3 picklist picklist 1 picklist 2 and picklist 3 . Picklist 1 is controlling list for picklist 2 and picklist 2 is controlling field for picklist 3. Now i am able to fetch the picklist 2 dependent values based on picklist 1 controlling value. But problem is that on the basis of picklist 2 selectrd value i have to fetch picklist 3 depent values. The same function who return me picklist 2 depent value not returning picklist 3 dependent value for conttolling value from picklist 2. Help me i am stuck in this situation. I am using same above code for dependentency upto 2 1 level showing me correct dependent values but at third level i am not getting any result. Suppose i select fruit dependent value is green colr fruit and yellow color fruit i choose yellow option should show me banana mango like that

    ReplyDelete
  8. Heyy i am in thus situtation that i have 3 picklist picklist 1 picklist 2 and picklist 3 . Picklist 1 is controlling list for picklist 2 and picklist 2 is controlling field for picklist 3. Now i am able to fetch the picklist 2 dependent values based on picklist 1 controlling value. But problem is that on the basis of picklist 2 selectrd value i have to fetch picklist 3 depent values. The same function who return me picklist 2 depent value not returning picklist 3 dependent value for conttolling value from picklist 2. Help me i am stuck in this situation. I am using same above code for dependentency upto 2 1 level showing me correct dependent values but at third level i am not getting any result. Suppose i select fruit dependent value is green colr fruit and yellow color fruit i choose yellow option should show me banana mango like that

    ReplyDelete
  9. Heyy i am in thus situtation that i have 3 picklist picklist 1 picklist 2 and picklist 3 . Picklist 1 is controlling list for picklist 2 and picklist 2 is controlling field for picklist 3. Now i am able to fetch the picklist 2 dependent values based on picklist 1 controlling value. But problem is that on the basis of picklist 2 selectrd value i have to fetch picklist 3 depent values. The same function who return me picklist 2 depent value not returning picklist 3 dependent value for conttolling value from picklist 2

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. I have two picklists one of country and one of state. Country has around 150 value in it and states has around 300 values in it. I am getting the same states values in multiple countries. For example i am getting "Andaman and Nicobar Islands, Andhra Pradesh" state repeated in different countries.

    ReplyDelete
  12. Thank you for the post. This worked perfectly!

    ReplyDelete
  13. Thanks for sharing a good information about salesforce . .......This article is very useful to learners. Sales force online training

    ReplyDelete
  14. I have read your blog its very attractive and impressive. I like your blog. salesforce Online course

    ReplyDelete
  15. Grateful to you, for sharing those superb expressive confirmations. I'll try to do around a spurring power in reacting; there's a striking course of action that you've crushed in articulating the important goals, as you charmingly put it. Keep Sharing......Tableau online training in India......Tableau online training in USA

    ReplyDelete