SPFilterDropdown by Parent List via Multiple Parameters

Feb 20, 2015 at 6:12 PM
Edited Feb 20, 2015 at 6:19 PM
jQuery 1.6.4, SPServices 0.6.2

I have a drop-down lookup column successfully filtering via a Parent List Item based on the current user, but I also want to filter it based on a Status column. In this example, the Parent List is a list of Projects, and the Child List is a list of Project Updates. The Drop Down in the Child List shows only MY projects based on a specified Project Manager (people picker field) in the parent list.

I also want it to filter the drop down by the status of the project (either Active or Archived). I have fooled around with the CAML query but can't seem to get it to work right. Here's my code:
<script language="javascript" type="text/javascript">
    _spBodyOnLoadFunctionNames.push("onPageLoad");

    function onPageLoad() {

        var userDisplayName = $().SPServices.SPGetCurrentUser({
            fieldName: "Title"
        });

        var query = '<Query>' +
            '<Where>' +
            '<Eq><FieldRef Name="PM" /><Value Type="Text">' + userDisplayName + '</Value></Eq>' +
            '</Where>' +
            '</Query>';

        $().SPServices.SPFilterDropdown({
            relationshipList: "{##################}",
            relationshipListColumn: "Project_x0020_Name",
            columnName: "Project Name",
            CAMLQuery: "<Eq><FieldRef Name='PM' /><Value Type='Text'>" + userDisplayName + "</Value></Eq>",
            completefunc: null,
            debug: false
        });
    }
</script>
What does not work is:
CAMLQuery: "<Where><And><Eq><FieldRef Name='PM' /><Value Type='Text'>" + userDisplayName + "</Value></Eq><Contains><FieldRef Name='Status' /><Value Type='Choice'>" + 'Active' + "</Value></Contains></And></Where>",
Any assistance would be GREATLY appreciated!
Coordinator
Feb 20, 2015 at 6:45 PM
A few things...

First, upgrade your versions of the two libraries. You're using a very old version of each. I'd recommend jQuery 1.11.2 and SPServices 2014.02 (both the latest).

Second, you can't do what you're aiming for with SPCascadeDropdowns, if I'm understanding you correctly. The CAMLQuery is evaluated once when the function is initially called, not on every change of the parent.

M.
Feb 20, 2015 at 7:11 PM
sympmarc wrote:
A few things...

First, upgrade your versions of the two libraries. You're using a very old version of each. I'd recommend jQuery 1.11.2 and SPServices 2014.02 (both the latest).

Second, you can't do what you're aiming for with SPCascadeDropdowns, if I'm understanding you correctly. The CAMLQuery is evaluated once when the function is initially called, not on every change of the parent.

M.
I am not using SPCascadeDropdowns, but rather SPFilterDropdown. Will the same CAML evaluation principals apply to it as well?
Feb 20, 2015 at 7:16 PM
Actually, this worked!!
        $().SPServices.SPFilterDropdown({
            relationshipList: "{66618C33-DD80-49E0-8C45-33E790246E88}",
            relationshipListColumn: "Project_x0020_Name",
            columnName: "Project Name",
            CAMLQuery: "<And><Eq><FieldRef Name='PM' /><Value Type='Text'>" + userDisplayName + "</Value></Eq><Contains><FieldRef Name='Project_x0020_Name' /><Value Type='Text'>Active</Value></Contains></And>",
            completefunc: null,
            debug: true
        });