Cascading lookup - child drop-down not filtering

Jul 26, 2013 at 4:02 AM
Edited Jul 26, 2013 at 4:19 AM
Hi,

I am pretty new to jQuery and am trying to get my head around the Cascading Lookup function.

I have this scenario in SharePoint 2010:
Single Site Collection. At TOP LEVEL there is a list of PCYC Clubs which feeds a Site Column that I use a lot (as the Site Collection is the PCYC's Intranet) PCYC = Police Citizens Youth Clubs in NSW, Australia but that's not relevant.

Further down the Site Collection I have a Site with a couple of lists. The site is to manage attendance and participation in a new State Govt. Learner Driver program.

Within that site I have a List called "Courses" and another called "Participants", and I am trying to implement the cascading lookup on the NewForm page of the Participants List.

The Courses List has a Title column (re-named Course ID) which has a unique value for each course (e.g. 2013 KTA SDC01) and a Column called "PCYC Club" which is the Site Column I mentioned before (which has a Static Name PCYC%5Fx0020%5FClub and I replaced %5F with underscores).

The Participants List also has the PCYC Club lookup using the same Site Column to get the list of clubs, and a lookup to select the Course (2013 XXX SDC XX).

What I am trying to do on the Add Item form when we add a new participant is to have the user select the PCYC Club in the first drop-down and have any Courses that are already created for that club populate the next drop-down which is a lookup to Course ID (Title) from the Courses list.

I know the calls to the jQuery library and the SPServices are working because I got error pop ups at one point when I was playing around.

Here is my script:
<script language="javascript" src="/activities/pcycbrandedprograms/safedrive/jQuery/jquery-1.10.2.min.js" type="text/javascript"></script>
<script language="javascript" src="/activities/pcycbrandedprograms/safedrive/jQuery/jquery.SPServices-2013.01.min.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">

$(document).ready(function() {
  $().SPServices.SPCascadeDropdowns({
    relationshipList: "{1252A398-76C4-46F3-BBE8-BFD71BDCE83D}",
    relationshipListParentColumn: "PCYC_x0020_Club",
    relationshipListChildColumn: "Title",
    parentColumn: "PCYC Club",
    childColumn: "Course",
    debug: true
  });
});</script>
The list GUID above is for the Courses list, which is where I assume I need the relationship?

What happens when I place this in a CEWP at the BOTTOM of the NewForm.aspx page is that the Club lookup works as expected (ie shows all clubs from the Site Column) but the next lookup for Course is NOT FILTERED.
If I choose a Club that I KNOW has some Courses it is still not filtered. In other words it is the same as not having the script.

It has to be something I am doing wrong but I have managed to confuse myself to a headache so am hoping you can set me straight.

Cheers,
Mark
Jul 27, 2013 at 5:37 AM
Hi Mark,
Unfortunately, the way your lists are structured, especially with using a site column, is not compatible with the cascading dropdown function. However, I drafted and tested the code below, which uses the SPFilterDropDown function instead, and is triggered whenever the user changes the "PCYC Club" selection in the "Participants" list. Please see the annotations below the code for guidance and an explanation on how to apply it to your situation.

1 <script src="/javascript/jquery-1.8.3.js" type="text/javascript"></script>
2 <script src="/javascript/jquery.SPServices-0.7.2.js" type="text/javascript"></script>
3 <script src="/javascript/spjs-utility.js" type="text/javascript"></script>
4 <script language="javascript" type="text/javascript">
5
6$(document).ready(function() {
7
8 $().SPServices.SPComplexToSimpleDropdown({
9 columnName: "Center",
10 completefunc: null,
11 debug: true
12 });
13
14 var field = $("nobr:contains('PCYC Club')").closest("tr");
15 var itemClub = $(field).find("select");
16 $(itemClub).change(function(){ChangeOptions()});
17
18 });
19
20 function ChangeOptions(){
21 fields = init_fields_v2();
22 var clubName = getFieldValue("Centers");
23 var myQuery = "<Eq><FieldRef Name='Centers' /><Value Type='Choice'>"+clubName+"</Value></Eq>";
24 $().SPServices.SPFilterDropdown({
25 relationshipWebURL: "",
26 relationshipList: "Courses",
27 relationshipListColumn: "Title",
28 relationshipListSortAscending: true, // Added in 2013.01
29 relationshipListSortColumn: "Title",
30 columnName: "Course",
31 listName: $().SPServices.SPListNameFromUrl(),
32 promptText: "",
33 noneText: "(None)",
34 CAMLQuery: myQuery,
35 CAMLQueryOptions: "<QueryOptions></QueryOptions>",
36 completefunc: null,
37 debug: false
38 });
39 }
40
41 </script>

Line 3 - Download spjs-utility.js from spjs.com. Among many other capabilities, the library allows you to read column values in a form, which is used on line 22
Lines 8 to 12 - Use this function to ensure that the "PCYC Club" selection column is presented as a simple dropdown. If there are more than 19 choices in that site column, then it would be presented as a "complex" dropdown, and would cause issues with line 15, which assumes that the column is rendered as a "Select" type of column in the HTML code.
Lines 14 to 16 - jquery locates the "PCYC Club" column, and attaches a function that will execute if the user changes the "PCYC Club" dropdown selection.
Line 14 - Change "PCYC Club" to the "display" name for that column in the "Participants" list.
Line 21 - Initialization code for reading column values in a form.
Line 22 - Change "Centers" to the "static" name, aka "Field Internal Name", for the "PCYC Club" column in the "Participants" list.
Line 23 - Creates a CAML query fragment that will filter the "Courses" list based on the selection in the "PCYC Club" column in the "Participants" list.
Line 23 - Change "Centers" to the "static" name for the "PCYC Club" column in the "Courses" list.
Line 26 - Change "Courses" to the name of the "Courses" list, or use its GUID, like you did in your example.
Line 27 - Change "Title" to the "static" name of the "Course" column in the "Courses" list.
Line 29 - Change "Title" to the "static" name of whichever column that you want to sort on in the "Courses" list, which will in turn sort the dropdown options in the "Course" column in the "Participants" list.
Line 30 - Change "Course" to the "display" name for that column in the "Participants" list.

This solution assumes that the "Course" column in the "Participants" list was created as a Lookup column to the "Course" column in the "Courses" list, and that you used the "PCYC Club" site column in both the lists.

Regards,

Charles
Jul 27, 2013 at 7:53 AM
Edited Jul 27, 2013 at 7:56 AM
Hi Charles,

thank you very much for your time with this. I have tried to use your suggested approach BUT the Course drop down still doesn't filter. I tried in Chrome as well as IE10 just to make sure it wasn't a browser issue.

My current code is below, with additional annotations for clarification (mine, not yours :).

I assumed I should also change line 9 to the display name of the PCYC Club column on the add Participant form.

Your assumptions about the columns are correct: the Course column in the Participants list is a Lookup to the "Course ID" column in the Courses list, and the PCYC Club column in both lists use the same Site Column to get the list of clubs.

I know the calls to the libraries are correct because the PCYC Club drop down does indeed change when I have the script either in a CEWP on the page, or a CEWP linked to the script in the library (I put it as a text file in the same library as the jquery etc. just for testing).

I am not averse to using SharePoint Designer to put the code on the page with a little guidance, but I figured a CEWP should work just as well as long as it is at the bottom of the page?
<script src="http://intranet.pcycnsw.org.au/activities/pcycbrandedprograms/safedrive/jQuery/jquery-1.10.2.min.js" type="text/javascript"></script>
<script src="http://intranet.pcycnsw.org.au/activities/pcycbrandedprograms/safedrive/jQuery/jquery.SPServices-2013.01.min.js" type="text/javascript"></script>
<script src="http://intranet.pcycnsw.org.au/activities/pcycbrandedprograms/safedrive/jQuery/spjs-utility.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">
$(document).ready(function() { 

$().SPServices.SPComplexToSimpleDropdown({  
columnName: "PCYC Club", //this is the DISPLAY name of the club lookup column in the form.
completefunc: null, 
debug: true 
}); 
 
var field = $("nobr:contains('PCYC Club')").closest("tr"); 
$(itemClub).change(function(){ChangeOptions()}); 

}); 

function ChangeOptions(){ 
fields = init_fields_v2(); 
var clubName = getFieldValue("PCYC_x0020_Club"); 
var myQuery = "<Eq><FieldRef Name='PCYC_x0020_Club' /><Value Type='Choice'>"+clubName+"</Value></Eq>"; 
$().SPServices.SPFilterDropdown({ 
relationshipWebURL: "", 
relationshipList: "{1252A398-76C4-46F3-BBE8-BFD71BDCE83D}", // this is the Courses list GUID
relationshipListColumn: "Title", // the STATIC name of the Course ID column is Title
relationshipListSortAscending: true, 
relationshipListSortColumn: "Title",  // sorting on the Course Id column is fine
columnName: "Course", // DISPLAY name of the Course lookup column in the Participant list
listName: $().SPServices.SPListNameFromUrl(), 
promptText: "", 
noneText: "(None)", 
CAMLQuery: myQuery, 
CAMLQueryOptions: "<QueryOptions></QueryOptions>", 
completefunc: null, 
debug: false 
}); 
} 
 
</script> 
I am sure I am just missing something but I cannot see what.

Thanks again

Mark
Jul 27, 2013 at 1:51 PM
Edited Jul 27, 2013 at 5:31 PM
Hi Mark,

You're right about line 9. I missed that one in my explanation. Also, the correct web address for the spjs utility library is spjsblog.com.

Regarding your updated code, it appears that line 15 is missing. This would prevent the "change" function from executing when the user first selects or changes the PCYC Club in the "Participants" list. Try that and see if it gets things going.

Also, as a technique to avoid the representation of the space character in "static" column names, such as in "PCYC_x0020_Club", I first create columns using no spaces in the column name, and then I go back and insert the spaces afterwards. Thus, the "static" name is less wieldy to code against, and yet the "display" name is in a "human-readable" form.

Regards,

Charles
Jul 28, 2013 at 1:43 AM
I cannot thank you enough Charles!

I found the right place to get the spjs library, but I would never in a million years have realised I'd deleted a line form your code. I must have deleted that line when I was stripping the line numbers out in Notepad in my excitement to try it. More haste, less speed next time methinks. I have put it back in its place and bingo! the drop down filters beautifully.

Re the naming of columns - yes I normally do that when I think I will need to use the column name in a parameter filter or something, and this lesson has taught me to just do that for every column name regardless because, as with this requirement, someone always wants something you hadn't considered... Not being an actual programmer these are probably coding 101 rookie mistakes, but I'm learning valuable lessons as I progress.

Once again thank you!

Best wishes

Mark
Jul 28, 2013 at 2:44 AM
You're welcome Mark. I'm glad I was able to help. :)

Charles
Aug 15, 2013 at 4:57 AM
Hi Charles,

should this work on a list form which I have customised in SPD2010? I have created a new form to add records so that I could delete a bunch of fields that aren't really relevant at the time a participant is created. I put a hidden CEWP at the bottom of the page (exactly same way as I did it in the default form) with the code which works great on the default NewForm page, but doesn't filter the Courses - in fact the Courses drop-down is empty - on the customised form.

Would this have something to do with the custom form using a DFWP whereas the standard form uses an XSL List View?

The static and display names are the same, the relationship list GUID is the same. I shouldn't need to change anything in the working script when I put it on the customised page should I?

The PCYC Club drop down is rendered as a simple drop down so my src= references are OK.

I can't think of a reason why using the same code which works perfectly on the standard OOB NewForm should fail to filter the Course drop down on a custom form.

Any ideas?
Aug 15, 2013 at 1:27 PM
Hi Mark,

A quick question to better help me understand the context. Your post states that you created a customized form for adding new participants, since some of the fields aren't relevant when adding a new participant. Rather than creating a customized NewForm page, you could simply hide those fields via jQuery. If you think that is a viable approach, I could walk you through the code for hiding form fields, which of course can be made to work on EditForm and DisplayForm as well. At a higher level, this technique is extremely useful for dynamically displaying and hiding form fields based on user choices in other form fields, or perhaps based on the ID of the user who is adding/editing data.

Charles
Aug 16, 2013 at 1:27 AM
Hi Charles,

ooh yes please! I would love to get my head around jQuery and I intend to - any books/tutorials for newbs with very little coding knowledge you would recommend?

One of the things I am trying to do with this site is to avoid using InfoPath forms if possible. Not because I think InfoPath is bad; I have used InfoPath forms on another site with similar purpose but found that the more I added rules and views and validations etc. the longer they take to render. So I thought I'd use SPD and create a new form for adding records because I could hide fields not needed at the initial data entry point. However, a couple of the fields are YES/NO and that is why I am customising - because SharePoint 2010 won't enable the Required/Optional/Hidden settings for the YES/NO fields when I enable management of content types. There's always a gotcha!

So, to your question - yes please. I would love to improve my skill set to use jQuery to manipulate forms as you have suggested and would appreciate the walk through you have offered. I would still like to know if the code should have worked on a customised form or was the XLV / DFWP difference my problem?

Here are the columns in the Participants list:

Address Single line of text
Booking Method Choice
ClubBilled Yes/No I want to hide this one
Coach Notes Multiple lines of text I want to hide this one
Conditions Choice
Contact E-mail Single line of text
Contact Name Single line of text
Contact Phone Single line of text
Coordinator Notes Multiple lines of text
Course Lookup
Course:Course Date Lookup
DOB Date and Time
Facilitator Notes Multiple lines of text I want to hide this one
First Name Single line of text
Gender Choice
In Vehicle Coach Lookup
In Vehicle Coach:IL Expiry Lookup
In Vehicle Coach:Instructors License Lookup
In Vehicle Completed Yes/No I want to hide this one
In Vehicle Date Time Date and Time
Last Name Single line of text
Learner License No Single line of text
Log Book Hours Group Number
Log Book In Vehicle Number I want to hide this one
Medical Conditions Single line of text
Other Names Single line of text
Payment Method Choice
PCYC Club Lookup
PCYC Club:Suburb or Town Lookup
Postcode Single line of text
RMSBilled Yes/No I want to hide this one
RMSInvoiceNo Single line of text I want to hide this one
State Choice
Suburb / Town Single line of text
Transmission Choice
Created By Person or Group
Modified By Person or Group

Sorry- could not figure out how to put these in a table format (looked up Daring Fireball but using standard html tags didn't work)

Thanks doesn't seem adequate, but thanks anyway!

Mark
Aug 16, 2013 at 3:10 AM
Edited Aug 16, 2013 at 3:59 AM
Hi Mark,

The sample code below will get you going for hiding each of the desired fields when the form is opened. The implementation is actually very simple. A full explanation of how it works is below the code.
<script type="text/javascript" src="javascript/jquery-1.8.3.js"></script>
<script type="text/javascript" src="javascript/jquery.SPServices-0.7.2.js"></script>
$(document).ready(function() {

    $("nobr:contains('Club Billed')").closest("tr").hide();
    $("nobr:contains('Coaches Notes')").closest("tr").hide();
    //repeat for other fields
    
}); 
Below is a snippet of code from the "NewForm.aspx" file of an abbreviated "Participants" list I created. Notice that the "Club Billed" text, which is the display name of that list field, is within the HTML "<nobr>" (i.e. "No Break") tag. We can use jQuery to traverse the web page's DOM (i.e. Document Object Model) to find the "<nobr>" tag that contains a specified text string. Using the "chaining" feature of jQuery, within the same line of code, we can also tell jQuery to find the closest "<tr>" (i.e "Table Row") HTML tag, and then to hide that table row. And since that table row contains the list field, we end up hiding that list field.

Image

w3schools has an excellent qQuery tutorial for newbies. Also, I found the jQuery book below to be very helpful. And you probably know this already, but the jQuery website (jQuery.com), contains full documentation of the API. But it can be difficult to decipher, as it seems to assume a certain amount of baseline knowledge. However, the site does link to some excellent free jQuery tutorials produced on codeschool.com.

Image

I'm not sure about why the code didn't work with your customized form. I'll do a little bit of research, and follow up with you.

Charles
Aug 16, 2013 at 10:50 AM
Hi Charles,

I am in your debt! I modified your snippet to match my jQuery and SPServices locations, and fixed up my field names. Put that in another CEWP and it works like a charm. So now I have filtered lookup and hidden non-essential fields.

Thank you for the help again; and thanks too for the book and other resource recommendations. The book is now on my Kindle and I look forward to learning some handy tricks.

If you figure out why the customized (see - I used a z there) form doesn't want to play ball I'd like to know.

Cheers,

Mark
Aug 17, 2013 at 2:51 AM
Hi Mark,

At first I thought there might be a compatibility issue between the DFWP and the SPFilterDropDown function. But I discovered that it is actually an issue with the init_fields_v2() function of spjs-utility.js, as discussed by the author on one of his blog posts. So, I removed the dependency on init_fields_v2() by refactoring the code to use the necessary jQuery functionality myself. Below is the modified code, which I tested on both the standard NewForm.aspx, and a customised ;) form.

Cheers,

Charles
<script src="/javascript/jquery-1.8.3.js" type="text/javascript"></script>
<script src="/javascript/jquery.SPServices-0.7.2.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">

$(document).ready(function() {

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

    var field = $("nobr:contains('PCYC Club')").closest("tr");
    var itemClub = $(field).find("select");
    $(itemClub).change(function(){ChangeOptions(itemClub)}); //pass the object associated with the PCYC club to the ChangeOptions function

});

function ChangeOptions(clubChapter){  //added the parameter clubChapter to receive the value passed to it when the selection for the PCYC club is changed
    
    var clubName = $(clubChapter).val(); // get the name of the PCYC club that was selected
    var myQuery = "<Eq><FieldRef Name='PCYCClubs' /><Value Type='Choice'>"+clubName+"</Value></Eq>";
    
    $().SPServices.SPFilterDropdown({
        relationshipWebURL: "",
        relationshipList: "Courses",
        relationshipListColumn: "Title",
        relationshipListSortAscending: true, // Added in 2013.01
        relationshipListSortColumn: "Title",
        columnName: "Course",
        listName: $().SPServices.SPListNameFromUrl(),
        promptText: "",
        noneText: "(None)",
        CAMLQuery: myQuery,
        CAMLQueryOptions: "<QueryOptions></QueryOptions>",
        completefunc: null,
        debug: false
    });
}

</script> 
Aug 18, 2013 at 8:01 AM
Hi again Charles,

getting a SharePoint error:
Error
Failed to get value of the "Course" column from the "Lookup" field type control. See details in log. Exception message: Input string was not in a correct format..

I tried a few things: used GUID for relationshipList, tried changing the FieldRef Name in the myQuery line to 'PCYC Club' as well as 'PCYC_x0020_Club' but the Course drop down remains empty. Unlike Ned Flanders' parents I did try something but I'm all out of ideas...

I note that we're not using the static name (PCYC_x0020_Club) in the new script? Stab in the dark I will admit.

Thanks

Mark
Aug 20, 2013 at 8:47 AM
Edited Aug 20, 2013 at 8:48 AM
G'day Charles,

The script to hide fields works great on NewForm and EditForm, but Dispform for some reason known best to Microsoft doesn't use the NOBR tags, so I tried using the h3 tags instead and that works. Thanks for the pointer.
Aug 21, 2013 at 2:22 AM
Hmm, that's interesting that Dispform.aspx doesn't use the <nobr> tag. Seems like you're getting more comfortable with jQuery, though, since you figured that the <h3> tag might work :)
Jul 11, 2014 at 2:34 PM
Has anyone figured out what is stopping the associated fields from rendering in the second drop down? I too am getting stopped at this point following the same code. Using the chrome debugger, I am seeing there is a problem with the ajax call to return the associated columns, but I am not an ajax/javascript/jQuery experienced developer.

Thanks,

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

This is a really old thread. Why don't you start a new one and post the code that's causing you problems?

M.