SPServices.SPCascadeDropdown/SPFilterDropdown - Lookup multi value

[sgovaerts]

Hello,

I was wondering if I'm missing something or if it isn't possible but I'm having a problem where I want to filter based on a multi value lookup. An example below.

So I've got a list Brands with just some titles like BrandA, BrandB, BrandC.
Then I've got a list with Machines that has a lookup to brands and 1 machine can support multiple brands.
Then I've got a 3rd list: Offers which points to a machine and a brand, but I want the Offers list to filter the Machines based on the supported brands. That way if MachineA only supports BrandA, I want to see only BrandA, if MachineC supports BrandA and BrandB then I want to choose BrandA or BrandB or BrandA and BrandB. But right now I can't filter multiple values, it would only show BrandA in the dropdown. Am I doing this wrong or is it not supported to filter based on a multi value lookup?

So it would like look:

Brands

BrandA
BrandB
BrandC

Machines - Brands (Lookup to Brands)

MachineA | BrandA
MachineB | BrandB
MachineC | BrandA,BrandB

Offers - Machine - Brands (Machine lookup to machines, Brands lookup to Brands)

Offer1 | MachineC | BrandA,BranddB

---

jQuery/SPServices:

$().SPServices.SPCascadeDropdowns({
relationshipList: "Machines",
relationshipListParentColumn: "Title",
relationshipListChildColumn: "Brands",
parentColumn: "Machine",
childColumn: "Brands",
debug: true
});

$().SPServices.SPFilterDropdown({
relationshipList: "Machines",
relationshipListColumn: "Brands",
columnName: "Brands",
CAMLQuery: "" + machineField.GetValue() + "",
debug: true
});

Many thanks & thank you for such an awesome addition which saves me many headaches!

[sympmarc]

I don't think this will work with your data structure, and I'm not sure I understand well enough to say it will work at all. Each "cascade" is independent of the others,, but as you change a value, the change cascades down the chain.

The CAMLQuery can't be dynamic; everything is set up when you make the call and doesn't change.

It's possible you could clone the function(s) and adapt to get what you want to happen. If you do and feel that you have a generally useful enhancement, please post back!

M.

[sgovaerts]

Ok, I'm on to something. It returns all the items correctly but you only grab the first item.
So if a Lookup contains multiple items ==>

Working on the spfilterdropdown code

// Add an option for each item
$(xData.responseXML).SPFilterNode("z:row").each(function () {

                var thisOption = {};

                // If relationshipListColumn is a Lookup column, then the ID should be for the Lookup value,
                // else the ID of the relationshipList item
                var thisValue = $(this).attr("ows_" + opt.relationshipListColumn);
                console.log("typeof: " + typeof thisValue);
				console.log("spdelim: " + thisValue.indexOf(spDelim));
                if (typeof thisValue !== "undefined" && thisValue.indexOf(spDelim) > 0) {
                	console.log("test split");
                	console.log(new SplitIndex(thisValue));
                    thisOption = new SplitIndex(thisValue);
                } else {
                    thisOption.id = $(this).attr("ows_ID");
                    thisOption.value = thisValue;
                }

So you call SplitIndex but only grab the first result!
function SplitIndex(s) {
var spl = s.split(spDelim);
console.log(spl);
this.id = spl[0];
this.value = spl[1];
}

As you can see my lookup returns multiple result but you only add the first result.
I'm logging to the console and working on it but I think it should be a small change to check if splitindex returns an array with more than 2 elements ==> For each 2 elements add an option instead of only the first 2 (id & value

[sgovaerts]

Ok, got it working. Might be messy but it should make it clear :)
So I've copied SPFilterDropdown ==> SPFilterDropdown2 and created a new SplitIndex2 function.

You can view the code here.
http://pastebin.com/3F5dECrX

So what I ended up doing was instead of checking the return value like this thisValue.indexOf(spDelim) > 0
I changed it to (thisValue.match(/;#/g) || []).length > 1), this way I can see If I get more than 1 result back. Then it's just retrieving the values & instead of creating 1 option, I'm creating an option for every returned value.

So if I split on "#;" and get 4 values back I know there's 2 separate values (0 = ID, 1 = Value, 2 = 2nd ID, 3 = 2nd value

[sgovaerts]

Hmm was looking at the SPCascadeDropdown & it calls cascadeDropdown(parentSelect).
Then it checks like this:

var allParentSelections = parentSelectSelected.join(spDelim);
if (parentSelect.Obj.data("SPCascadeDropdown_Selected_" + childColumnStatic) === allParentSelections) {
return;
}
But making the same kind of changes to cascadeDropdown(parentSelect) I get it working on the first call but once I change my selection it doesn't seem to get triggered? When I call this function again after a field change the calls stop because the selection "didn't change", but it did change and only added the first option? If I comment out the return it works fine?

Any chance you could explain the behaviour above?

[francoise61]

I was able to solve this by making a couple of changes to the function cascadeDropdown (the internal one called by SPCascadeDropdown). Would be nice if this were part of the standard SPCascadeDropdown because if the child field has multiple choices selected in the relationship list, all of them should be available in the cascaded dropdown.

Here are the changes I made in the function code for $(xData.responseXML).SPFilterNode("z:row").each(function() {...

Immediately after var thisValue = $(this).attr("ows_" + opt.relationshipListChildColumn); add these lines:

// ****** This block of code added to support a multi-select in the child column of the relationship list

                    var allValues = thisValue;

                    var allValuesAr = allValues.split(spDelim);

                    for (i = 0; i < allValuesAr.length; i += 2) {

                        if (allValuesAr.length > 1) thisValue = allValuesAr[i] + spDelim + allValuesAr[i + 1];

// ****** end added block of code

And immediately before the ending });, add the closing brace for the new for loop:

// ****** This block of code added to support a multi-select in the child column of the relationship list

                    } // end of for loop over allValuesAr

// ****** end added block of code

[4thelove]

I second francois61 update for inclusion into the standard, this resolved the issue for me as well (mostly)

I need it to work with matchOnId: true and right now it does not. Any chance you could update your changes to help with that Francois?

Nevermind!, I just needed to set the parent column to ID instead of Title

[francoise61]

Glad you were able to resolve it without my help, because I am retired now, and no longer have access to a SharePoint site. :-)