Filtered lookup - Child Drop-down not filtering

Jul 11, 2014 at 2:07 PM
Edited Jul 11, 2014 at 2:08 PM
Hi All,

I have an assignment to add a Parent/Child related filtered list look-up to a document library in SP 2010.

I came across SPServices while trying to work on this assignment. This post has gotten me the closest to completing the task: https://spservices.codeplex.com/discussions/451461

My set up is similar to the one in the discussion. I have a Parent list which holds the primary key for all the related list/document library called DCAgreements. In this list is a renamed title column labeled "Number" which is the primary key. On the Child list (Funding Records): I have the Title Column, and a Look-up column to the DCAgreement List that points to the "Number" column.

In the Document Library, I have two look up Fields:

"Parent" which points to the DCAgreement List Column: "Number"
"Funding" which points to the Funding Records List Column "Title"

Here is the script I have:
<script language="javascript" type="text/javascript" src="http://ai-seb03dev-001:5000/Shared%20Documents/jquery-1.11.1.js"></script>
<script language="javascript" type="text/javascript" src="http://ai-seb03dev-001:5000/Shared%20Documents/jquery-migrate-1.2.js"></script>
<script language="javascript" type="text/javascript" src="http://ai-seb03dev-001:5000/Shared%20Documents/jquery.SPServices-0.7.2.min.js"></script>
<script language="javascript" type="text/javascript" src="http://ai-seb03dev-001:5000/Shared%20Documents/spjs-utility.js"></script>
<script language="javascript" type="text/javascript">
$(document).ready(function() {

$().SPServices.SPComplexToSimpleDropdown({
    columnName: "Parent",
    completefunc: null,
    debug: true
});

var field = $("nobr:contains('Parent')").closest("tr");
var itemParent = $(field).find("select");
$(itemParent).change(function(){ChangeOptions(itemParent)});

function ChangeOptions(keyParent) {
    var parentName =$(keyParent).val();
    var myQuery = "<Eq><FieldRef Name='Parent' /><Value type='Choice'>"+parentName+"</Value></Eq>";
    //alert(parentName);
    $().SPServices.SPFilterDropdown ({
        relationshipWebURL: "http://ai-seb03dev-001:5000/Lists",
        relationshipList: "{16FD0D02-238A-4C9B-B17D-7E17762B955D}",
        relationshipListColumn: "AgreementParent",
        relationshipListSortAscending: true,
        relationshipListSortColumn: "Title",
        columnName: "Funding",
        listName: $().SPServices.SPListNameFromUrl(),
        promptText: "",
        noneText: "(Choose a Child Record if applicable:)",
        CAMLQuery: myQuery,
        completefunc: null,
        debug: true
    });
}

});


</script>
I place this script in a CEWP and the first step works. I choose a Parent item and I see the noneText display meaning the onChange is firing, but the child drop down is not filled with options.

But in debug mode in Chrome, I see the error: 500 (Internal Server Error) and the script it references is the jquery-1.11.1.js

I am new to jQuery/Ajax/SPServices. Any help with this is truly appreciated.

Thanks,
James (JP)
Coordinator
Jul 11, 2014 at 2:11 PM
James:

If the script you show above is all you are doing, then you don't need the jquery-migrate and spjs-utility references. Those have nothing to do with SPServices.

I'd also suggest that you update to SPServices 2014.01, as the version you are using is quite old.

Shouldn't you be using SPCascadeDropdown for this rather than the functions you have?

Once you'd done that, let me know if you're still having problems.

M.
Jul 11, 2014 at 2:30 PM
Also, check your "relationshipWebUrl" as it will likely need tweaked whether you stick with your current method or change to the preferred "SPCascadeDropdown" method. The relationshipWebUrl should be the target site root without the "/Lists" extension....unless "Lists" is actually the name of the site that contains the relationship list.

Geoff
Jul 11, 2014 at 3:54 PM
Edited Jul 11, 2014 at 3:55 PM
Thanks sympmarc and Geoff for the quick responses.

After testing both options, I now get the Can not find relationshipListParentColumn: AgreementParent or relationshipListChildColumn: Title

message.

I only get this message when the relationshipWebUrl: is set to "http://ai-seb03dev-001:5000" The child column is the default SP list "Title" column unchanged. Here is the new code:
<script language="javascript" type="text/javascript" src="http://ai-seb03dev-001:5000/Shared%20Documents/jquery-1.11.1.js"></script>
<script language="javascript" type="text/javascript" src="http://ai-seb03dev-001:5000/Shared%20Documents/jquery.SPServices-2014.01.min.js"></script>
<script language="javascript" type="text/javascript">
$(document).ready(function() {

$().SPServices.SPComplexToSimpleDropdown({
    columnName: "Parent",
    completefunc: null,
    debug: true
});

var field = $("nobr:contains('Parent')").closest("tr");
var itemParent = $(field).find("select");

$(itemParent).change(function(){ChangeOptions(itemParent)});

function ChangeOptions(keyParent) {
    var parentName =$(keyParent).val();
    var myQuery = "<Eq><FieldRef Name='Parent' /><Value type='Choice'>"+parentName+"</Value></Eq>";
    //alert(parentName);
    $().SPServices.SPCascadeDropdowns({
    relationshipWebURL: "http://ai-seb03dev-001:5000",
    relationshipList: "FundingRecordsLive",
    relationshipListParentColumn: "AgreementParent",
    relationshipListChildColumn: "Title",
    relationshipListSortColumn: "Title",
    parentColumn: "Parent",
    childColumn: "Funding",
    CAMLQuery: myQuery,
    //CAMLQueryOptions: "",
    listName: $().SPServices.SPListNameFromUrl(),
    //simpleChild: false,
    //selectSingleOption: false,
    //matchOnId: false,
    //completefunc: null,
    debug: true
});
}
});
I will dive deeper to figure out why the columns are not being found.
Jul 11, 2014 at 7:50 PM
Hello again.

After looking into the column problem: I am still unable to figure out why it is unable to find the columns on the relationship List.

This is the column name from the browser:

http://ai-seb03dev-001:5000/Lists/FundingRecordsLive/AllItems.aspx?View={234302ee-d124-4670-b55a-e34667f25a44}&SortField=AgreementParent&SortDir=Asc

I changed the relationshipWebURL: from "http://ai-seb03dev-001:5000" to "/" but I still get the same issue of not being able to read the columns. I also tested this with an alert asking if the column is on the page. It fails to find the column.

Also previously using the SPFilterDropdown version of the code, the onChange was firing. It is not firing with the SPCascadeDropdown version of the code.

Any suggestions as to why the FilterDropdown can find the columns on the relationshipList but not the CascadeDropdown code?

Thanks,

James (JP)
Aug 7, 2014 at 12:44 PM
Edited Aug 7, 2014 at 12:45 PM
I was able to get the Filtered drop down to work for my Parent/Child List with Document Library related Web Part. Here is the code I am using:
<script language="javascript" type="text/javascript" src="http://ai-seb03dev-001:5000/Shared%20Documents/jquery-1.11.1.min.js"></script>
<script language="javascript" type="text/javascript" src="http://ai-seb03dev-001:5000/Shared%20Documents/jquery.SPServices-2014.01.min.js"></script>
<script language="javascript" type="text/javascript">
$(document).ready(function() {
    var ID = getCookie("ID");
    //alert(ID);
    if(ID != undefined)
    {
        SetLookup("Parent", ID );
    }

    function getCookie(name) {
        var cookieArray = document.cookie.split(";");
        for (index in cookieArray)
        {
            var keyValuePair = cookieArray[index].split("=");
            var key = keyValuePair[0];
            key = key.replace(/^\s+|\s+$/g, "");
            if (key == name)
            {
                var value = keyValuePair[1];
                return value;
            }
        }
    }
    
    // Sets the value of a lookup field whether it is an input or select field
    function SetLookup( fieldTitle, lookupVal)
    {
        // Set default value for lookups with less than 20 items
        if ( $("select[title='" +fieldTitle+ "']").html() != null)
        {
            var element = $("select[title='" + fieldTitle +"']");
            $(element).val(lookupVal);
            // disable field to stop users from changing it if added from Parent list
            $(element).find('option:not(:selected)').attr('disabled',true);
        }
        else
        {
            var element = $("select[title='" + fieldTitle +"']");
            $(element).val(lookupVal);
            // enable field if adding a document manually
            $(element).find('option:not(:selected)').attr('disabled',false);
        }
        //else
        //{
        //  choices = $("input[title='" +fieldTitle +"']").attr("choices");
        //  hiddenInput = $("input[title='" +fieldTitle +"']").attr("optHid");
        //  $("input[id='" +hiddenInput +"']").attr("value",lookupVal)
        //  
        //  choiceArray = choices.split("|");
        //  for (index = 1; index < choiceArray.length; index = index + 2)
        //  {
        //      if (choiceArray[index] == lookupVal) {
        //          var element = $("input[title+'" +fieldTitle +"']");
        //          $(element).val(choiceArray[index - 1]);
                    //disable field to stop pesky users from changing it
        //          $(element).attr('readonly',true);
                    //hide the button that is used for the dropdown list
        //          $(element).next('img').hide();
        //      }
        //  }
        //}
    }
    
    // Handle the filtering of the Parent Item and Related Child Items
    $().SPServices.SPCascadeDropdowns({
        relationshipList: "FundingRecordsLive",
        relationshipListParentColumn: "AgreementParent",
        relationshipListChildColumn: "Title",
        parentColumn: "Parent",
        childColumn: "Funding",
        debug: true
    });
});
</script>
Marked as answer by JayPru12 on 8/7/2014 at 6:59 AM