return only values of picklistEntries where controllingField value is something particular

  • 1
  • Question
  • Updated 3 years ago
  • (Edited)
Is there some way in the skuid Field API to see only certain picklistEntries from a field based on a given value from the controllingField

I want to create a number of rows based on the number of picklistEntries in a given picklist field. I've got it working, but my picklist has controlling field, and I only want to return picklistEntries for a certain given value of my controlling field. Is this possible somehow?

Here's my snippet which creates rows for every picklist entry:
var params = arguments[0],	
$ = skuid.$,
expenses = skuid.model.getModel('Expenses'),
description = expenses.getField('Description__c'),
entries = description.picklistEntries;

$.each(entries, function(){

var entryValue = this;

var newExpense = expenses.createRow({
additionalConditions: [
{field: 'Description__c', value: entryValue, operator: '='}
]});
});
This works great, but the Description field is a dependent picklist. The controlling field has two values, Income and Expense. I only want the entries where the controlling value is Expense.

I've tried doing something like:
var type = description.controllingField;
$.each(entries, function(){
if (type.value === 'Expense'){
var entryValue = this;
var newExpense = expenses.createRow({
additionalConditions: [
{field: 'Description__c', value: entryValue, operator: '='}
]});
}
});

But that's not working. It's not really able to look up from a single picklistEntry to what its controlling field value is (since there could be more than one). Should probably use CONTAINS there. Or, could I structure it so i have nested each functions, and be able to look down at all the picklist entries from a given controlling field value?

I also tried this and it didn't work, says can't find the value of undefined:
$.each(entries, function(){
if (this.controllingField.value === 'Expense'){
var newExpense = expenses.createRow({
additionalConditions: [
{field: 'Description__c', value: entryValue, operator: '='}
]});
}
});
I also tried using an aggregate model for my IncomeExpense object, with a condition where Type = Expense, and grouping by Description__c, but I kept getting errors that it couldn't count the length of the model when using rows = aggmodel.rows then each on rows. 
Photo of Jack Sanford

Jack Sanford, Champion

  • 8,322 Points 5k badge 2x thumb

Posted 3 years ago

  • 1
Photo of Jack Sanford

Jack Sanford, Champion

  • 8,322 Points 5k badge 2x thumb
Some interesting stuff. Each picklistEntries object has a variable called validFor, and this corresponds to the field dependency. In the screenshot below, object 5 description value is only valid for type value of Income, object 7 is only valid for type of Expense, and object 6 is valid for both.

Problem is, the value of validFor is some unique ID, I guess we found Salesforce's ID for a field dependency junction object, in this case gAAA, wAAA, and QAAA



So the following snippet works perfect:

var params = arguments[0],    $ = skuid.$,
    models = skuid.model.map(),
    expenses = skuid.model.getModel('Expenses'),
    description = expenses.getField('Description__c'),
    entries = description.picklistEntries;
$.each(entries, function(){
var isExpense = this.validFor !== 'gAAA' ? true : false;
if (isExpense){
    var entryValue = this.value;
var newExpense = expenses.createRow({
additionalConditions: [
//
{field: 'Description__c', value: entryValue, operator: '='}
]
});
}
// var conditionId = conditions.getFieldValue(conditionRow, 'Id');
});
Next question is, how do I query for the value of validFor without using console??
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Hi Jack,

We don't have any supported, documented APIs for figuring out picklist dependencies. However, there is a blog post that goes through some of this stuff and explains what the cryptic value of "validFor" means. Here it is...

https://iwritecrappycode.wordpress.com/2012/02/23/dependent-picklists-in-salesforce-without-metadata...
Photo of Jack Sanford

Jack Sanford, Champion

  • 8,322 Points 5k badge 2x thumb
Thanks Ben. I think the answer might be somewhere in that post.

I was hoping that maybe gAAA decoded from base64 would equal 'Expense' but no dice. Or maybe there would be a matching validFor on the controlling field picklist entry, but no dice. 

Works pretty well right now, and just a matter of doing a console log of the field or of the picklist entries to get the validFor value if I wanted to repeat this somewhere else.
console.log (description);
console.log (entries);
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Your solution is a bit brittle, if you make any changes to your dependencies grid, that value for valid for will change. Since a particular picklist value can be valid for multiple controlling values, "gAAA" does not map directly to "Expense".

The part of the blog post that is most relevant is this function.

// sub function to do the validFor test
function isDependentValue(index, validFor)
{
     var base64 = new sforce.Base64Binary("");
     var decoded = base64.decode(validFor);
     var bits = decoded.charCodeAt(index>>3);
            
     return ((bits & (0x80 >> (index%8))) != 0);
}

It tells you if a picklist value is valid for a particular index of your controlling field. You can try it on a few values.

isDependentValue(0,'gAAA'); // results in true
isDependentValue(1,'gAAA'); // results in false
isDependentValue(2,'gAAA'); // results in false
isDependentValue(3,'gAAA'); // results in false

isDependentValue(0,'QAAA'); // results in false
isDependentValue(1,'QAAA'); // results in true
isDependentValue(2,'QAAA'); // results in false
isDependentValue(3,'QAAA'); // results in false