This project has moved. For the latest updates, please go here.

SPCascadeDropdowns with 2 parents and one child

Jan 11 at 6:53 PM
Hi, I had a form where I wanted one of the dropdown fields to be populated based on two other fields (that are independent of each other). By pouring over the source code, I put together a solution that works in my case, so I thought I would share it. It could be improved to work in more cases, but I will leave that as an exercise for someone else (or me later, if I ever need it).

Let me know if I did anything incorrectly, though it's been working for me so far.

Many thanks for the great library!
Francoise

Problem statement:

Relationship list has 2 parents and 1 child. (call them P1, P2, and Ch)
  • Both parents are lookups or choice fields.
  • Each pair of parents can have multiple children associated with them.
Subject list includes drop-down menus for all three fields.
  • P2 is single-choice.
  • The parent fields are independent of each other (they can take on any possible value)
  • We want to use a cascade drop-down so that after P1 and P2 are selected, the Ch drop-down only includes choices related to both P1 and P2 selections.
My solution:
$(document).ready(function() {

   // Do the cascade from Parent 1 to the child
   $().SPServices.SPCascadeDropdowns({
       relationshipList: relationshipListName, 
       relationshipListParentColumn: "P1StaticName",
       relationshipListChildColumn: "ChStaticName",
       parentColumn: "P1",
       childColumn: "Ch"
   });

   // Add a CAMLquery to the above cascade to limit to P2 rows
   updateP1Query();

   // set updateP1query as "onchange" for P2
   $("[title='P2']").change(updateP1Query);  
});

function updateP1Query() {
   updateCAMLQuery("P1","P2","P2StaticName","Ch");
}

// updateCAMLQuery function
// Arguments:
//    p1Name is the display name for the cascading parent
//    p2Name is the display name for the other parent, which must 
//                   be single-choice
//    p2RelName is the static name in the Relationship List for the 
//                   "other" parent
//    chName is the display name for the child that is dependent on
//                  both parents
//    p1InvalidValue is optional and defaults to "X". 
//                 If "X" is a valid selection  value for P1, 
//                 pass in a different string -- it needs to be any   
//                 string that the current value of P1  can never 
//                 be (otherwise the cascade  will not happen 
//                 when that is the actual value of P1).

function updateCAMLQuery(p1Name,p2Name,p2RelName,
                        chName,p1InvalidValue) {
   // Default for p1InvalidValue
   if (p1InvalidValue === undefined) { p1InvalidValue="X"; }

   // Define a CAMLquery fragment for the current value of P2
   // Note: this is the reason P2 must be single-choice, but 
   // you could change the code to build a fragment containing 
   // a series of ORs to handle multiple-choice.
   var p2Val = $("[title='"+p2Name+"']").val();
   var p2Query="<Eq><FieldRef Name='"+p2RelName+"'/><Value Type='Text'>"+p2Val+"</Value></Eq>";

   // Find the cascade data for P1, and insert the query as a 
   // CAMLQuery for P1. Also pick up the child column's 
   // static name while we're at it.
   var p1Select= $().SPServices.SPDropdownCtl({displayName: p1Name});
   var chColumnStatic;  
   var cascadeChildren = p1Select.Obj.data("SPCascadeDropdownsChildColumns");

   $(cascadeChildren).each(function() {
       if (this.opt.childColumn == chName) {
           this.opt.CAMLQuery = p2Query;

           chColumnStatic = this.childColumnStatic;
           return false;  // stop looking
       }
   });

   // save the updated cascade data
   p1Select.Obj.data("SPCascadeDropdownsChildColumns", cascadeChildren);

   // force the cascade even though P1 didn't actually change 
   p1Select.Obj.data("SPCascadeDropdown_Selected_" + chColumnStatic, p1InvalidValue);

   // trigger a change event in the P1 selection to make the cascade 
   // update the child selections using the new CAMLquery
   $("[title='"+p1Name+"']").change();
}
Jan 11 at 8:05 PM
Edited Jan 11 at 8:05 PM
Another note: if P2 also needs a cascade defined for another field in the form, you should replace this code:
   // Add a CAMLquery to the above cascade to limit to P2 rows
   updateP1Query();

   // set updateP1query as "onchange" for P2
   $("[title='P2']").change(updateP1Query);  
with the call to SPCascadeDropdowns for P2 and put the call to updateP1Query() or the direct updateCAMLQuery call in the completefunc: parameter. E.g.:
   $().SPServices.SPCascadeDropdowns({
       relationshipList: otherRelationshipListName, 
       relationshipListParentColumn: "P2StaticName",
       relationshipListChildColumn: "OtherChStaticName",
       parentColumn: "P2",
       childColumn: "P2sOtherChild",
       completefunc: updateP1Query
   });
or
   $().SPServices.SPCascadeDropdowns({
       relationshipList: otherRelationshipListName, 
       relationshipListParentColumn: "P2StaticNameInOtherList",
       relationshipListChildColumn: "OtherChildStaticName",
       parentColumn: "P2",
       childColumn: "P2's Other Child",
       completefunc: function() {
              <other code>
              updateCAMLQuery("P1","P2","P2StaticName","Ch");
              <other code>
       }
   });