SharePoint Cascading Dropdown with CAML query that filters one of the choices

I have made a cascading dropdown form using http://spservices.codeplex.com but want to add a filter to the third cascading dropdown. So SPServices have a function called CAMLQuery where I can add this filter but I want the filter to be specified with the value from the top dropdown on the form.

One example that is given on the page is:

            CAMLQuery: "<Eq><FieldRef Name='Status'/><Value Type='Text'>Active</Value></Eq>"

I want this Status column to get its value from the choice selected on of the fields on the form. Is this possible and how would I write that?

Edit: I am trying to make an asset list with cascading choices to find the right asset after picking unit and department. Code example after trying the below answer and not getting anything coming up in the "Asset" chooser. There are two lists, one containing Units and Departments and one containing the assets with a Department and Unit key to filter with. If I replace the "dynamic" queries with a static one such as:

CAMLQuery: "<Eq><FieldRef Name='Unit'/><Value Type='Text'>Unit 1</Value></Eq>"

the assets appear filtered for that unit. Current code:

<script language="javascript" type="text/javascript" src="../../Hidden/jquery-1.5.2.min.js"></script>
<script language="javascript" type="text/javascript" src="../../Hidden/jquery.SPServices-0.6.0.min.js"></script>
<script language="javascript" type="text/javascript">
var sCamlQuery = "";

$(document).ready(function() {
    $().SPServices.SPCascadeDropdowns({
        relationshipList: "Departments",
        relationshipListParentColumn: "Unit",
        relationshipListChildColumn: "Title",
        parentColumn: "Unit",
        childColumn: "Department",
        completefunc: function() {
        sCamlQuery = "<Eq><FieldRef Name='Unit'/><Value Type='Text'>" + $("select[title='Unit']").attr("spcascadedropdown_selected_") + "</Value></Eq>";
        }
    });
        $().SPServices.SPCascadeDropdowns({
        relationshipList: "Asset Database",
        relationshipListParentColumn: "Department",
        relationshipListChildColumn: "Title",
        parentColumn: "Department",
        childColumn: "Asset",
        CAMLQuery: sCamlQuery
    });

});

The HTML:

<tr>
<td width="190px" valign="top" class="ms-formlabel">
    <H3 class="ms-standardheader"><nobr>Unit</nobr></H3>
</td>
<td width="400px" valign="top" class="ms-formbody">
    <span dir="none">
        <select name="ctl00$PlaceHolderMain$g_c482307d_1a31_4129_8a0a_376d70562746$ff3_1$ctl00$Lookup" id="ctl00_PlaceHolderMain_g_c482307d_1a31_4129_8a0a_376d70562746_ff3_1_ctl00_Lookup" title="Unit">
            <option selected="selected" value="0">(None)</option>
            <option value="1">Unit 1</option>
            <option value="2">Unit 2</option>
        </select>
    </span>¨
</td>
</tr>

<tr>
<td width="190px" valign="top" class="ms-formlabel">
    <H3 class="ms-standardheader"><nobr>Department</nobr></H3>
</td>
<td width="400px" valign="top" class="ms-formbody">
    <span dir="none">
        <select name="ctl00$PlaceHolderMain$g_c482307d_1a31_4129_8a0a_376d70562746$ff4_1$ctl00$Lookup" id="ctl00_PlaceHolderMain_g_c482307d_1a31_4129_8a0a_376d70562746_ff4_1_ctl00_Lookup" title="Department">
            <option selected="selected" value="0">(None)</option>
            <option value="5">Administration</option>
            <option value="4">IT</option>
            <option value="1">Kitchen</option>
            <option value="2">Production</option>
            <option value="7">Production</option>
            <option value="6">Sales</option>
            <option value="3">Warehouse</option>
        </select>
    </span>
</td>
</tr>

<tr>    
<td width="190px" valign="top" class="ms-formlabel">
    <H3 class="ms-standardheader"><nobr>Asset</nobr></H3>
</td>
<td width="400px" valign="top" class="ms-formbody">
    <span dir="none">
        <select name="ctl00$PlaceHolderMain$g_c482307d_1a31_4129_8a0a_376d70562746$ff5_1$ctl00$Lookup" id="ctl00_PlaceHolderMain_g_c482307d_1a31_4129_8a0a_376d70562746_ff5_1_ctl00_Lookup" title="Asset">
            <option selected="selected" value="0">(None)</option>
            <option value="3">Computer</option>
            <option value="2">Copy machine</option>
            <option value="1">Dishwasher</option>
            <option value="5">Dishwasher</option>
            &开发者_StackOverflow中文版lt;option value="4">Oven</option>
        </select>
    </span>
</td>
</tr>

Verifying function:

<script language="javascript" type="text/javascript" src="../../Hidden/jquery-1.5.2.min.js"></script>
<script language="javascript" type="text/javascript" src="../../Hidden/jquery.SPServices-0.6.0.min.js"></script>
<script language="javascript" type="text/javascript">
var sCamlQuery = "";

$(document).ready(function() {

    $().SPServices.SPCascadeDropdowns({
        relationshipList: "Departments",
        relationshipListParentColumn: "Unit",
        relationshipListChildColumn: "Title",
        parentColumn: "Unit",
        childColumn: "Department",
        completefunc: function() {
        sCamlQuery = window.alert("completefunc");//"<Eq><FieldRef Name='Unit'/><Value Type='Text'>"+ $("select[title='Unit'] option:selected").text() +"</Value></Eq>";
                                }
    });

        $().SPServices.SPCascadeDropdowns({
        relationshipList: "Asset Database",
        relationshipListParentColumn: "Department",
        relationshipListChildColumn: "Title",
        parentColumn: "Department",
        childColumn: "Asset",
        CAMLQuery: sCamlQuery
    });

});
</script>

Departments list:

Title Unit

IT Unit1

Sales Unit1

IT Unit2

Sales Unit2

Asset Database:

Title Department *Unit*

Asset1 IT Unit1

Asset2 IT Unit2

David

Looking at the documentation for SPCascadeDropdowns, you have the ability to specify a callback function that executes whenever the parent column changes. This seems like exactly the hook you need to wire up what you're asking for.

You posted no code samples, so I'll just have to make something up. Their demo page uses Region, State and City, so let's play with that.

If I wanted to add a filter to "City" based on the selected value of "Region", I might use this:

var sCamlQuery = "";

// Cascade definition for State dropdown
$().SPServices.SPCascadeDropdowns({
    relationshipList: "States",
    relationshipListParentColumn: "Region",
    relationshipListChildColumn: "Title",
    relationshipListSortColumn: "Title",
    parentColumn: "Region",
    childColumn: "State" 
    completefunc: function() {
        sCamlQuery = "<Eq><FieldRef Name='Status'/><Value Type='Text'>" + $("select[title='Region'] option:selected").text() + "</Value></Eq>";
    })
});

// Cascade definition for City dropdown
$().SPServices.SPCascadeDropdowns({
    relationshipList: "Cities",
    relationshipListParentColumn: "State",
    relationshipListChildColumn: "Title",
    parentColumn: "State",
    childColumn: "City",
    CAMLQuery: sCamlQuery
});

That's a pretty naive jQuery selector to find the SELECT element (looking at the TITLE attribute), but you should get the idea. The anonymous function defined for completefunc should run whenever the parent column ("Region") is changed, and updates the value of the global var sCamlQuery. When the "City" cascade runs, it will use the generated CAML query.

Edit: Updated example to better match the OP scenario. When the top-most cascade runs ("Region"), it will trigger the completefunc anonymous method in the "State" cascade. This sets the value of the sCamlQuery variable, and will be used when the "City" cascade runs.

Edit #2: Updated sample code to use a more general-purpose jQuery selector; it will retrieve the text value of the selected OPTION. If you want the OPTION value instead, you can simplify it to be: $("select[title='Region']").val()

David:

You really should have asked this on the SPServices site in the Discussions to start with!

What you are showing in your code above isn't necessary. If you make the two calls to SPCascadeDropdowns in the order you show, then you don't need

CAMLQuery: "<Eq><FieldRef Name='Unit'/><Value Type='Text'>Unit 1</Value></Eq>" 

That's how the cascading works. The first call will limit the Departments to those that are in the selected Unit on selection of Unit. The second call will limit the Assets to those that are in the selected Department on selection of Department.

If you aren't getting the results you expect, then your relationshipLists may not have the values in them that you need.

M.

Edit based on additional info about the list contents:

It's definitely a data problem. You are combining relationships in the Department list and the Assets list. You should have these lists:

• Departments -- Each Department in the list exactly once.
• Units -- Each Unit in the list exactly once, with a column as a lookup to Title in Department indicating that Unit's Department.
• Assets -- Each Asset in the list exactly once, with a column as a lookup to Title in Units indicating that Asset's Unit.

This makes your lists truly relational and then the calls to SPCascadeDropdowns will do what you want, just as in the example in the docs for Country/Region/State/City.