Cascading Dropdowns- list directly from JavaScript

Jun 27, 2011 at 11:24 PM
Edited Jun 27, 2011 at 11:25 PM

Hi sir Marc (I have knighted you in my small, imaginary world with a SP kingdom),

I have a quick question for you sire,

I am trying to look for a cascading dropdown solution without using the lookup columns, we already have a generic dropdown column that we've been using with considerable amount of data already in place and my manager would like to refrain from having to add new columns- just afraid I guess...

I recall from one of your articles on your blog about you mentioning a possible way to read the lists directly from the Javascript... I'm a Javascript noob unfortunately....also searched the forum and the web but couldn't find much help....can you tell me how difficult this would be to implement / give me some guidance how to start / point me to the right direction?

Thank you so much for your time as always,

 

John

 


Coordinator
Jun 28, 2011 at 4:22 AM

John:

Thanks for the mega respect. I just watched Game of Thrones, and I'm not sure being a knight is all its cracked up to be.

How is your existing column populated? Is it a choice column?

However it's populated, it's certainly possible to build some script that addresses your particular needs. The "noob" moniker makes it seem like that might not be the right approach for you, though. Also, you have to maintain the relationship between column A and column B *somewhere*, which is why I took the approach I did.

M.

Jun 28, 2011 at 4:47 PM
Edited Jun 28, 2011 at 4:48 PM

Marc:

Np. At least I'm sure my imaginary world involves far more peace than George R. R. Martin's (author of that movie). lol

Anyhow, yes the existing columns are choice column- drop down menus, there's hundreds of data already populated and forgot to tell you we also have some custom workflows implemented on the form...

( not sure if that matters- but the tech lead is being very cautious and  he wants to keep the same column format as much as possible with minimum modification of the site definition files....)

I was planning to just add a CQWP (yes im aware you do not recommend it) with the Javascript and the relationships on there too, is this so very hard to do? Any resources you can point me to?

 

Gracias as always,

 

John

Coordinator
Jun 28, 2011 at 5:39 PM

SPCascadeDropdowns simply won't work with Choice columns, unfortunately. There's no foolproof way to ensure that the choices match whatever list you might store the relationships in, so I decided that was a non-starter right at the beginning.  The only way you could use SPCascadeDropdowns with your Choice columns is to clone the code and built your own function.

You're going to need to figure out where you want to go with this, I suppose...

M.

Jun 29, 2011 at 11:05 AM

It is possible to do cascading dropdowns with SPServices without using the SPCascadeDropdowns function. I have had to do this because we already had a huge list that was just not realistic to change. As Marc stated, you will need to decide what you want to do. In my situation I had 1 list that supported 3 dropdowns and it worked great. There are a lot of helpful folks here and I am certain we could engineer a solution that would work for you. Marc has done a great job but as he stated, choice columns are difficult. Are users allowed to "Fill In Choice" as well? Just need to be sure I understand what you are looking for.

Dan

Jun 29, 2011 at 2:14 PM
Edited Jun 29, 2011 at 2:18 PM

Hi Dan,

my situation is pretty simple, we just have two drop down menus (choices) named State/Country and PoP with more than 50 options each (unfortunately since we have so many options Fill-in Choice is not an option :( ),

The solution i'm looking for is when the user selects PoP, the State/Country is filled in automatically. The relationship is mostly 1 PoP to 1 State/Country, with an exception to maybe a couple 1 PoP to 4 State/Country relationships also.  I would like the first and only available State/Country to be filled in automatically if that's the case.

I've done this using marc's most recent version of SPServices and SPCascadeDropdowns function which also has the new

simpleChild
If set to true, the child dropdown will be converted to s "simple" dropdown if it is a "complex" dropdown on page load. See $().SPServices.SPComplexToSimpleDropdown for details on how this works.  The default value is false.

selectSingleOption
If set to true and there is only a single child option, select it.  The default value is false.

functions (kudos to marc) It really works well,

I was just looking for an option without having to create new lookup columns ( since close to your situation, we already have a huge list), maybe reading the list directly from the JS etc....)

I was about to just give up and let my supervisor know I couldn't find that option and maybe just create the lookup columns and run a workflow to transfer the column data somehow but if you can give some help / guidance as to how to do this it'll be most greatly appreciated :)

Thanks for your time Dan,

 

John 

Jun 29, 2011 at 2:21 PM
Edited Jun 29, 2011 at 2:23 PM

Dan or Marc,

Also, I was thinking about re-using

 

01	_spBodyOnLoadFunctionNames.push("SetUpCascading");
02	 
03	var Column1 = new Object();
04	var Column2 = new Object();
05	var savedColumn2Options = new Object();
06	 
07	function SetUpCascading() {
08	    // Find Column1 in the DOM (in this demo, the column is named "Region")
09	    Column1 = getTagFromIdentifierAndTitle("select","","Region");
10	    // Find Column2 in the DOM (in this demo, the column is named "State")
11	    Column2 = getTagFromIdentifierAndTitle("select","","State");
12	    // Attach the onchange event to Column1
13	    Column1.attachEvent('onchange', Column1Changed);
14	    // Call the onchange event to set the initial options for Column2
15	    Column1Changed();
16	}
17	 
18	function Column1Changed() {
19	    // Find the table with the Column1 / Column2 / ID information
20	    var Column1Table = document.getElementById("Column1Table");
21	    // Find all of the table rows
22	    var Column1TableRows = Column1Table.getElementsByTagName("TR");
23	    // See which Column2 options are allowed for the chosen Column1 option
24	    Column2Count = 0;
25	    // For each of the table rows...
26	    for (var i=0; i < Column1TableRows.length; i++) {
27	        // Get the table detail cells
28	        var Column1TableRowsDetails = Column1TableRows[i].getElementsByTagName("TD");
29	        // If the Region value in the table row matches the currently chosen Region
30	        if(Column1TableRowsDetails[1].innerHTML == Column1.options[Column1.selectedIndex].text) {
31	            // Add the option to the Column2 dropdown
32	            Column2.options[Column2Count] = new Option(Column1TableRowsDetails[0].innerHTML,
33	                Column1TableRowsDetails[2].innerHTML);
34	            // Increase the count of available options for Column2
35	            Column2Count++;
36	            // For this demo, set the background color of the Column2 cell to green
37	            Column1TableRowsDetails[0].style.backgroundColor = "green";
38	        // If the Region value in the table row doesn't match the currently chosen Region
39	        } else {
40	            // For this demo, set the background color of the Column2 cell to red
41	            Column1TableRowsDetails[0].style.backgroundColor = "red";
42	        }
43	    }
44	    // Set the length of the options array
45	    Column2.options.length = Column2Count;
46	    // If there aren't any available choices, then disable the Column2 dropdown
47	    if(Column2Count == 0) Column2.disabled = true;
48	     else Column2.disabled = false;
49	}

 

from one of Marc's posts (hopefully i'm allowed to repost this here? i apologize if i'm not, let me know and i'll delete right away). Is this designed only for lookup columns also? I read it over but couldn't differentiate (noob) and seems like it's reading from the generic DVWP xsl which doesn't really specify whether or not they are lookup columns...so i though this might work .... maybe not... :(

Best,

John

Jun 29, 2011 at 3:03 PM

I understand basically what Marc was doing with this code, but I am not certain how well it helps your situation. I will let MArc speak for his code on this one. If I were doing this in basically a one off solution, you could simply use the getlistitems webservice to populate the second dropdown from the change event of the first one. Where do these columns get their data from?

Jun 29, 2011 at 3:17 PM

Dan,

Right now they are not actually stored anywhere (like a list or anything) except just inside that initial "<label for="idChoices">Type each choice on a separate line</label>:" box when we first created that column.

I'm going to assume the data has to be inside a list for me to be able to use the getListItems webservice.

So one possible solution is creating lists, keeping the choice drop-down menus and using the getListItems to populate the second dropdown with Javascript...?

Thanks,

 

John 

Jun 29, 2011 at 3:26 PM

So both columns are choice columns? I just want to be sure? There are webservices that support this I just wanted to know what you were looking at. I believe the GetList webservice returns the list and column information which should also include all the choices in the choice column. I would have to review some code that I do not have access to from here, but I have done this before.

Coordinator
Jun 29, 2011 at 3:34 PM

John and Dan:

You can absolutely build something to meet your needs here. You'll need to store the relationships between the two choice columns in a list *somewhere* so that you can read from it to make decisions about what to show in your forms. Since you have 50+ options, you're dealing with what I call the "complex dropdown". By using the SPComplexToSimpleDropdown function, you can convert that to a simple dropdown and then do your work based on that (it'll be far easier). You'll bind to the change event for the simple dropdown, make your decisions, and populate the other dropdown. It sounds as though, it's not really a "cascade" per se, but more of an automagic population of the State/Country column.

I'm not sure how you could have use SPCascadeDropdowns here unless you're talking about using it in some other situation, as it won't work with Choice columns.

M.

Jun 29, 2011 at 4:21 PM
Edited Jun 29, 2011 at 4:22 PM

Marc and Dan:

Thank you both for your inputs, really appreciate it :)

Dan: Yes both of them are choice columns with the data stored inside their choice field values. It seems their is a way to obtain these values using GetList : http://digsharepoint.blogspot.com/2011/03/get-choice-field-values-using-getlist.html

Marc: Yes, it is more like an automagic population instead of filtering / cascading but i was not able to find a solution specifically for my situation so I used SPCascadeDropdowns and was planning to just migrate the old data

over to the new lookup columns ....But I think i got a better sense of the solution now, i'm going to look for something to play with and the first one I found is a different path using GetListItems- fetching data from a list and

attaching them to an onchange event of the choice drop down list  or something....

Thank you guys for your time and let me know if you guys have any other thoughts :)

John