Interested in adding dynamic filters to cascading dropdowns?

[JohnMark8080]

Not sure that this of general interest but I added the ability to pass a function in addition to the CAMLQuery variable in SPCascadeDropdowns. For my case, I had a static filter (Status = Open) and I had two form fields which changed the CAML query filtering (one was that the Date field is within Start and End for the item in the cascade list). I accomplished this by adding another argument to the SPCascadeDropdowns JSON input that was called CAMLQueryFunc. Then I added a few lines to your library to add a dynamically evaluated string generated by the passed function to the CAML query string.

I did this because I couldn't figure our a way to combine SPFilterDropdown with SPCascadeDropdowns to react to changes to other fields on the form that were not look-ups. For me, making a dynamic CAML query worked. I will share my code if you would be interested in adding this functionality.

[sympmarc]

@JohnMark8080 Sorry for the delayed reply. Yes, please do share your code!

[JohnMark8080]

To make the cascade function more dynamic I added the ability to pass a function. I did this because when I tried to modify the query string being passed in the JSON the changes were not dynamic. Changes after the initial call were not used...maybe this was an error on my part, but this is my solution.

Prior to calling the modified cascade JSON, create the function which will return different values based on the option passed:

• (option = 1): the CAML query string dynamically evaluated
• (option = 2): a switch to indicate if the cascade function call should be fired (0 = no, !0 = yes)

In my case this function looked like this:

function evalDynamicCamq(option){
    switch (option) {
        case 1:
            return workItemDynamicCamq;
            break;
        case 2:
            return makeItDynamic;
            break;
        default:
    }
    return;
}

And the SharePoint form JavaScript included a call to specifically trigger the event:

    var columnSelect = $().SPServices.SPDropdownCtl({
           displayName: "Project"
        });
    makeItDynamic = 1;
    $(columnSelect.Obj.selector).trigger("change");
    makeItDynamic = 0;

The corresponding call to a modified version of SPServices:

    $().SPServices.SPCascadeDropdowns({
      relationshipList: "Work Items",
      relationshipListParentColumn: "Project",
      relationshipListChildColumn: "Title",
      parentColumn: "Project",
      childColumn: "Work Item",
      CAMLQuery: workItemStaticCamq,
      CAMLQueryFunc: evalDynamicCamq,
      CAMLQueryOptions: "<QueryOptions><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns></QueryOptions>", // Added in 2013.01
      simpleChild: false,
      selectSingleOption: false,
      matchOnId: true,
      debug: true
     });

Code from jquery.SPServices-2014.02 with modifications highlighted

function cascadeDropdown(parentSelect) {
       var choices = "";
       var parentSelectSelected;
       var childSelectSelected = null;
       var newMultiLookupPickerdata;
       var numChildOptions;
       var firstChildOptionId;
       var firstChildOptionValue;

       // Filter each child column
       var childColumns = parentSelect.Obj.data("SPCascadeDropdownsChildColumns");
       $(childColumns).each(function () {

            // Break out the data objects for this child column
           var opt = this.opt;
           var childSelect = this.childSelect;
            var childColumnStatic = this.childColumnStatic;
            var childColumnRequired = this.childColumnRequired;

            // Get the parent column selection(s)
            parentSelectSelected = getDropdownSelected(parentSelect, opt.matchOnId);

            // If the selection hasn't changed, then there's nothing to do right now.  This is useful to reduce
            // the number of Web Service calls when the parentSelect.Type = dropdownType.complex or dropdownType.multiSelect, as there are multiple propertychanges
            // which don't require any action.  The attribute will be unique per child column in case there are
            // multiple children for a given parent.
            var allParentSelections = parentSelectSelected.join(spDelim);

* Modification 1: check to see if the function based query should be added and this can change the cascade dropdown behavior*

         var camlQueryForce = 0;
         if (opt.CAMLQueryFunc !== null) {
                camlQueryForce = opt.CAMLQueryFunc(2);
            }
            if (camlQueryForce == 0 && parentSelect.Obj.data("SPCascadeDropdown_Selected_" + childColumnStatic) === allParentSelections) {

* End of Modification 1*

                return;
            }
            parentSelect.Obj.data("SPCascadeDropdown_Selected_" + childColumnStatic, allParentSelections);

            // Get the current child column selection(s)
            childSelectSelected = getDropdownSelected(childSelect, true);

            // When the parent column's selected option changes, get the matching items from the relationship list
            // Get the list items which match the current selection
            var sortColumn = (opt.relationshipListSortColumn.length > 0) ? opt.relationshipListSortColumn : opt.relationshipListChildColumn;
            var camlQuery = "<Query><OrderBy><FieldRef Name='" + sortColumn + "'/></OrderBy><Where><And>";

            var camlQueryFOUT = "";

* Modification 2: if needed, add the AND to the CAML query*

            var camlQueryFOUT = "";
            if (opt.CAMLQueryFunc !== null) {
                camlQueryFOUT = opt.CAMLQueryFunc(1);
            }

            if (camlQueryFOUT.length > 0) {
                camlQuery += "<And>";
            }

* End of modification 2*

            if (opt.CAMLQuery.length > 0) {
                camlQuery += "<And>";
            }

            // Build up the criteria for inclusion
            if (parentSelectSelected.length === 0) {
                // Handle the case where no values are selected in multi-selects
                camlQuery += "<Eq><FieldRef Name='" + opt.relationshipListParentColumn + "'/><Value Type='Text'></Value></Eq>";
            } else if (parentSelectSelected.length === 1) {
                // Only one value is selected
                camlQuery += "<Eq><FieldRef Name='" + opt.relationshipListParentColumn +
                (opt.matchOnId ? "' LookupId='True'/><Value Type='Integer'>" : "'/><Value Type='Text'>") +
                escapeColumnValue(parentSelectSelected[0]) + "</Value></Eq>";
            } else {
                var compound = (parentSelectSelected.length > 2);
                for (i = 0; i < (parentSelectSelected.length - 1); i++) {
                    camlQuery += "<Or>";
                }
                for (i = 0; i < parentSelectSelected.length; i++) {
                    camlQuery += "<Eq><FieldRef Name='" + opt.relationshipListParentColumn +
                    (opt.matchOnId ? "' LookupId='True'/><Value Type='Integer'>" : "'/><Value Type='Text'>") +
                    escapeColumnValue(parentSelectSelected[i]) + "</Value></Eq>";
                    if (i > 0 && (i < (parentSelectSelected.length - 1)) && compound) {
                        camlQuery += "</Or>";
                    }
                }
                camlQuery += "</Or>";
            }

            if (opt.CAMLQuery.length > 0) {
                camlQuery += opt.CAMLQuery + "</And>";
            }

* Modification 3: add the dynamic string to the CAML query*

            if (camlQueryFOUT.length > 0) {
                camlQuery += camlQueryFOUT +"</And>";
            }

** End of modification 3 **

            // Make sure we don't get any items which don't have the child value
            camlQuery += "<IsNotNull><FieldRef Name='" + opt.relationshipListChildColumn + "' /></IsNotNull>";

            camlQuery += "</And></Where></Query>";

I have attached a zipped file of my version of jquery.SPServices-2014.02
jquery.SPServices-2014.02.jmj.zip

[sympmarc]

Thanks, @JohnMark8080. I'm going to keep this open to remind me to come back to it. Thanks for taking the time to write this up!

M.