SPCascadeDropdowns Item Limit?

Coordinator
Jan 17, 2010 at 7:25 PM
Edited Jan 17, 2010 at 7:25 PM

Is there a limit on the number of items that can be in the list? I was using this list to do a lookup on a company and then the employees associated with that company. When I have a small number of companies it works fine but when I go over about 100 the duplicates do not get removed from the list. Below is the code I have added.

$(document).ready(function() {
// Make sure each Company name only appears once in dropdown list
var uniqueNames = {};
$("select[title='Company'] > option").each(function () {
if(uniqueNames[this.text]) {
$(this).remove();
} else {
uniqueNames[this.text] = this.value;
}
});
// restrict the Contact Name List to selection in Company dropdown
// From http://spservices.codeplex.com/wikipage?title=%24%28%29.SPServices.SPCascadeDropdowns&referringTitle=Documentation
$().SPServices.SPCascadeDropdowns({
relationshipList: "Contacts",
relationshipListParentColumn: "Company",
relationshipListChildColumn: "Title",
parentColumn: "Company",
childColumn: "Contact Name",
debug: true
});

Coordinator
Jan 17, 2010 at 7:33 PM

SPCascadeDropdowns is going to grab the full set of values from the Contacts list every time.  If you want to remove duplicates on each change, then I think you are going to need to use the completefunc on the SPCascadeDropdowns function.  Check the documentation on this and post back if you have more questions.

M.

Feb 16, 2010 at 9:04 PM

I think I followed the instructions prety well but when I put the code in a NewForm.aspx I got

Only Content controls are allowed directly in a content page that contains Content controls. 

Can I not put this in an existing list?

 

 

Coordinator
Feb 17, 2010 at 2:50 AM

You certainly can use this in an existing list.  I Binged that error, and I think it's something else you've done while editing the page.  Here's one post:

http://social.msdn.microsoft.com/forums/en-US/sharepointdevelopment/thread/de64e551-1441-4e1d-9956-277b352cd9b1

M.

Feb 17, 2010 at 9:38 PM

Marc,

I am as we say sometimes LCD (lowest common .....) and I finally got the cascade function to work. Is there any way you can post a step by step guide to using the complete function to elminate duplicates. I am using a simlar setup to your examples where "State" is the Parent column and of course I have duplicates. Any help is greatly appreciated.

Coordinator
Feb 18, 2010 at 12:18 AM

Scott,

Marc is on vacation, but I'll see if I can help you with this. If you define a function for compeltefunc, it fires automatically anytime the SPCascadeDropdowns function finishes processing. You can either reference a function that you define seperately, or you can build the function right inside the SPCascadeDropdowns call, though I tend to call a seperate function as it's easier for me to follow my code that way (so that's how this example works).

I tested this on a stand-alone select element, but not as part of SPCascadeDropdowns, but something along this line 'should' help you, obviously substituting "mylist" for your select element's id value. Obviously this could be "smartened" up a bit to use the displayname of the field as opposed to the ugly list ID string, but I didn't take it that far.

function removeDuplicates() {
  var a = new Array(); 
  $("#mylist").children("option").each(function(x){ 
    test = false; 
    b = a[x] = $(this).val(); 
    for (i=0;i<a.length-1;i++){ 
      if (b ==a[i]) test =true; 
    } 
    if (test) $(this).remove(); 
  });
}
You would then add this to your SPCascadeDropdowns call:
completefunc: removeDuplicates
Feb 18, 2010 at 4:18 PM

Thank you for the help. But I am really lost on this. I have researched more on the web to try and understand what each line is doing. Below is the first script that I have that is functioning to achieve the dropdowns.

 

<script src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery-1[1].3.2.min.js"></script>

<script src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery[1].SPServices-0.4.8ALPHA5.js"></script>

<script>

                $(document).ready(function() {

                                $().SPServices.SPCascadeDropdowns({

                                                relationshipList: "SiteInfo",

                                                relationshipListParentColumn: "State",

                                                relationshipListChildColumn: "SiteCity",

                                                parentColumn: "State",

                                                childColumn: "City",

                                                debug: true

                                });

$().SPServices.SPCascadeDropdowns({

                                                relationshipList: "SiteInfo",

                                                relationshipListParentColumn: "SiteCity",

                                                relationshipListChildColumn: "SiteName",

                                                parentColumn: "City",

                                                childColumn: "Site",

                                                debug: true

                                });

 

                });

</script>

 

I added the below before the </script> tag and did not get what I was looking for.

function removeDuplicates() {

  var a = new Array();

  $("City").children("option").each(function(x){

    test = false;

    b = a[x] = $(this).val();

    for (i=0;i<a.length-1;i++){

      if (b ==a[i]) test =true;

    }

    if (test) $(this).remove();

  });

 

I am trying to eliminate the duplicate values in the City column.

Coordinator
Feb 18, 2010 at 5:00 PM

Scott,

You're part way there. The removeDuplicates function should be be before the </script> tag, so you've got that right. There is two things you've missed:

First, you need to add completefunc in the SPCascadeDropdowns call that will initiate the filter to remove the duplicates. This way, every time the city field is rebuilt by SPCascadeDropdowns the removeDuplicates function will fire to remove the duplicates. Do this by adding the following above the debug: true line of the first SPCascadeDropdowns call:

completefunc: removeDuplicates,

Next, we need to update the removeDuplicates function a tad. The function I gave you won't just look for the City field, you'd have to get the field ID for the field from the form itself (I didn't go the whole way for you). Replace the $("City") part of the third line of my function with the following:

$("select[title='City']")
Mike

 

 

Feb 19, 2010 at 5:20 PM

I am still not getting anywhere with this. Of course there is no one in my Dev Group that has any clue what I am doing to help. So if you still have time to hold my hand through this I would appreciate it.

 

<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery-1[1].3.2.min.js"></script>
<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery[1].SPServices-0.4.8ALPHA5.js"></script>
<script language="javascript" type="text/javascript">
 $(document).ready(function() {
  $().SPServices.SPCascadeDropdowns({
   relationshipList: "SiteInfo",
   relationshipListParentColumn: "State",
   relationshipListChildColumn: "SiteCity",
   parentColumn: "State",
   childColumn: "City",
completefunc: removeDuplicates,
   debug: true
  });
$().SPServices.SPCascadeDropdowns({
   relationshipList: "SiteInfo",
   relationshipListParentColumn: "SiteCity",
   relationshipListChildColumn: "SiteName",
   parentColumn: "City",
   childColumn: "Site",
   debug: true
  });
function removeDuplicates() {
  var a = new Array();
  $("select[title='City']") children("option").each(function(x){
    test = false;
    b = a[x] = $(this).val();
    for (i=0;i<a.length-1;i++){
      if (b ==a[i]) test =true;
    }
    if (test) $(this).remove();
  });

</script>

Coordinator
Feb 20, 2010 at 4:03 AM

Scott,

You've just got some issues with the way your functions are structured... You've got the removeDuplicates function inside the document.ready call... just have to close the document.ready call and close the removeDuplicates function. Try the version of your code below:

<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery-1[1].3.2.min.js"></script>
<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery[1].SPServices-0.4.8ALPHA5.js"></script>
<script language="javascript" type="text/javascript">
 $(document).ready(function() {
  $().SPServices.SPCascadeDropdowns({
   relationshipList: "SiteInfo",
   relationshipListParentColumn: "State",
   relationshipListChildColumn: "SiteCity",
   parentColumn: "State",
   childColumn: "City",
   completefunc: removeDuplicates,
   debug: true
  });
$().SPServices.SPCascadeDropdowns({
   relationshipList: "SiteInfo",
   relationshipListParentColumn: "SiteCity",
   relationshipListChildColumn: "SiteName",
   parentColumn: "City",
   childColumn: "Site",
   debug: true
  });
});

function removeDuplicates() {
  var a = new Array();
  $("select[title='City']") children("option").each(function(x){
    test = false;
    b = a[x] = $(this).val();
    for (i=0;i<a.length-1;i++){
      if (b ==a[i]) test =true;
    }
    if (test) $(this).remove();
  });
}
</script>

Coordinator
Feb 22, 2010 at 4:20 PM

Scott,

I got an email from Marc about this also. He's away on vacation but he wanted me to point out that you're using an old alpha version of SPServices, which is really not advised in a production environment. You should grab the current version off the downloads page.

Mike

Feb 24, 2010 at 2:19 PM

Ok, I have updated all the software. Verified the names and the lists that I am using. I still cannot get the remove duplicate function to work. Do you know anywhere that I can look at that I might be able to troubleshoot this further. I hate to keep asking for assistance on this since you probably get paid quite a bit for consulting work.

Thank you.

Coordinator
Feb 24, 2010 at 3:50 PM

Replace the above removeDuplicates function with this. I've added an alert to it so we can see if it's getting called or not. See if you see the alert. If so, it's a problem within the removeDuplicates function, if not it's a problem within your SPServices call...

function removeDuplicates() {
  var a = new Array();
  alert('calling removeDuplicates()');
  $("select[title='City']") children("option").each(function(x){
    test = false;
    b = a[x] = $(this).val();
    for (i=0;i<a.length-1;i++){
      if (b ==a[i]) test =true;
    }
    if (test) $(this).remove();
  });
}

Feb 24, 2010 at 5:20 PM

This is what I have now:

<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery-1[1].3.2.min.js"></script>
<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery[1].SPServices-0.4.8ALPHA5.js"></script>
<script language="javascript" type="text/javascript">
 $(document).ready(function() {
  $().SPServices.SPCascadeDropdowns({
   relationshipList: "SiteInfo",
   relationshipListParentColumn: "State",
   relationshipListChildColumn: "SiteCity",
   parentColumn: "State",
   childColumn: "City",
   completefunc: removeDuplicates,
   debug: true
  });
$().SPServices.SPCascadeDropdowns({
   relationshipList: "SiteInfo",
   relationshipListParentColumn: "SiteCity",
   relationshipListChildColumn: "SiteName",
   parentColumn: "City",
   childColumn: "Site",
   debug: true
  });
});

function removeDuplicates() {
  var a = new Array();
  alert('calling removeDuplicates()');
  $("select[title='City']") children("option").each(function(x){
    test = false;
    b = a[x] = $(this).val();
    for (i=0;i<a.length-1;i++){
      if (b ==a[i]) test =true;
    }
    if (test) $(this).remove();
  });
}
</script>

 It did not throw any alerts and none of the functions worked. It acts as is there are no JQuery's on the page.

Feb 24, 2010 at 5:34 PM

Can I recommend that you upgrade to SPServices 0.5.1 and JQuery 1.4, then try again?

http://spservices.codeplex.com/releases/view/40011

http://jquery.com/

Feb 24, 2010 at 5:34 PM

I just corrected the links in the top of the above reply. They now match the files in my library. No more beta or old versions.

 

 

Feb 24, 2010 at 5:36 PM

Here is the new header.

<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery-1.4.2.min.js"></script>
<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery.SPServices-0.5.1.js"></script>
<script language="javascript" type="text/javascript">

Still no go.

Coordinator
Feb 24, 2010 at 5:41 PM

That was going to be my next suggestion...

There's nothing that looks obviously wrong, let's have you change the removeDuplicates function to this:

function removeDuplicates() {
  alert('calling removeDuplicates()');
}

See if you see the alert now, and if your cascade dropdowns work.

Feb 24, 2010 at 5:46 PM

No alerts and no cascade dropdowns.

<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery-1.4.2.min.js"></script>
<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery.SPServices-0.5.1.min.js"></script>
<script language="javascript" type="text/javascript">
 $(document).ready(function() {
  $().SPServices.SPCascadeDropdowns({
   relationshipList: "SiteInfo",
   relationshipListParentColumn: "State",
   relationshipListChildColumn: "SiteCity",
   parentColumn: "State",
   childColumn: "City",
   completefunc: removeDuplicates,
   debug: true
  });
$().SPServices.SPCascadeDropdowns({
   relationshipList: "SiteInfo",
   relationshipListParentColumn: "SiteCity",
   relationshipListChildColumn: "SiteName",
   parentColumn: "City",
   childColumn: "Site",
   debug: true
  });
});

function removeDuplicates() {
  alert('calling removeDuplicates()');
}

  $("select[title='City']") children("option").each(function(x){
    test = false;
    b = a[x] = $(this).val();
    for (i=0;i<a.length-1;i++){
      if (b ==a[i]) test =true;
    }
    if (test) $(this).remove();
  });
}
</script>

Coordinator
Feb 24, 2010 at 6:02 PM

Well now you should see a script error because you've got JavaScript that's just hanging out at the bottom. You need to remove the rest of the function too... the removeDuplicates function went all the way to the last }... should look like this:

<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery-1.4.2.min.js"></script>
<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery.SPServices-0.5.1.min.js"></script>
<script language="javascript" type="text/javascript">
 $(document).ready(function() {
  $().SPServices.SPCascadeDropdowns({
   relationshipList: "SiteInfo",
   relationshipListParentColumn: "State",
   relationshipListChildColumn: "SiteCity",
   parentColumn: "State",
   childColumn: "City",
   completefunc: removeDuplicates,
   debug: true
  });
$().SPServices.SPCascadeDropdowns({
   relationshipList: "SiteInfo",
   relationshipListParentColumn: "SiteCity",
   relationshipListChildColumn: "SiteName",
   parentColumn: "City",
   childColumn: "Site",
   debug: true
  });
});

function removeDuplicates() {
  alert('calling removeDuplicates()');
}
</script>

 

Coordinator
Feb 24, 2010 at 6:07 PM

And I guess the other thing to confirm... you did have SPCascadeDropdowns working before we tried to add the removeDuplicates piece, correct?

Feb 24, 2010 at 6:40 PM

Yes, the cascade works fine without the removeDuplicates function.

When I loaded the code from your last suggestion I the the following error:

calling removeDuplicates()

Coordinator
Feb 24, 2010 at 6:43 PM

Excellent, that's not an error, that's what we wanted to see. So your cascades work correctly and you see that prompt when you change the parent select box. That means that it is calling our removeDuplicates function, meaning that our error was somewhere in that function that you removed as part of the last test. Let me look at that again...

 

Coordinator
Feb 24, 2010 at 6:44 PM

Is the City field your regular dropdown select box, or have you enabled multiple selections on that?

 

Feb 24, 2010 at 6:57 PM

The city field on the child site is called SiteCity, and it is a text field. On the parent site is called City and is a lookup column. I hope that is what you are asking. I am getting excited that you may have solved the great mystery here.

 

<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery-1.4.2.min.js"></script>
<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery.SPServices-0.5.1.js"></script>
<script language="javascript" type="text/javascript">
 $(document).ready(function() {
  $().SPServices.SPCascadeDropdowns({
   relationshipList: "SiteInfo",
   relationshipListParentColumn: "State",
   relationshipListChildColumn: "SiteCity",
   parentColumn: "State",
   childColumn: "City",
   completefunc: removeDuplicates,
   debug: true
  });
$().SPServices.SPCascadeDropdowns({
   relationshipList: "SiteInfo",
   relationshipListParentColumn: "SiteCity",
   relationshipListChildColumn: "SiteName",
   parentColumn: "City",
   childColumn: "Site",
   debug: true
  });
});

function removeDuplicates() {
  var a = new Array();
  alert('calling removeDuplicates()');
  $("select[title='City']") children("option").each(function(x){
    test = false;
    b = a[x] = $(this).val();
    for (i=0;i<a.length-1;i++){
      if (b ==a[i]) test =true;
    }
    if (test) $(this).remove();
  });
}
</script>

Coordinator
Feb 24, 2010 at 8:36 PM

Alright, sorry for the holdup (had to run to a meeting), I think the holdup is in the selector for the city field (the field we're actually trying to remove the duplicates from.

I tested that removeDuplicates function and it worked, so the only variance is the actual name of the field. Can you confirm that "City" is the full displayName of the dropdown we're removing the duplicates from?

Here's a quick way to confirm it:

  • Right click on the page and view the page source.
  • Do a find on "City" and you should get to something that looks similar to the following:

<nobr>City<span class="ms-formvalidation"> *</span></nobr>
</H3></TD>
	<TD valign="top" class="ms-formbody" width="400px">
	<!-- FieldName="City"
		 FieldInternalName="City"
		 FieldType="SPFieldLookup"
	  -->
		<span dir="none"><select name="ctl00$m$g_e69a5a02_f587_4cc8_a85c_d25a520448af$ctl00$ctl04$ctl04$ctl00$ctl00$ctl04$ctl00$Lookup" id="ctl00_m_g_e69a5a02_f587_4cc8_a85c_d25a520448af_ctl00_ctl04_ctl04_ctl00_ctl00_ctl04_ctl00_Lookup" title="City">
				<option value="1">Option 1</option>
				<option value="2">Option 2</option>
				<option value="3">Option 3</option>
				<option value="4">Option 4</option>
				<option value="5">Option 5</option>
			</select><br/></span>

What we care about is the "title" attribute on the select element, in this case title="City". This is the attribute we're using to tell jQuery to access the field by doing $("select[title='City']").

Can you run through this and confirm the value of the title attribute.

<nobr>Line of Business<span class="ms-formvalidation"> *</span></nobr>
    </H3></TD>
        <TD valign="top" class="ms-formbody" width="400px">
        <!-- FieldName="Line of Business"
             FieldInternalName="Line_x0020_of_x0020_Business"
             FieldType="SPFieldLookup"
          -->
            <span dir="none"><select name="ctl00$m$g_e69a5a02_f587_4cc8_a85c_d25a520448af$ctl00$ctl04$ctl04$ctl00$ctl00$ctl04$ctl00$Lookup" id="ctl00_m_g_e69a5a02_f587_4cc8_a85c_d25a520448af_ctl00_ctl04_ctl04_ctl00_ctl00_ctl04_ctl00_Lookup" title="Line of Business">
                    <option value="5">New Ventures (NV)</option>
                    <option value="2">Radar Systems (RS)</option>
                    <option value="1">Ship and Aviation Systems (S&amp;AS)</option>
                    <option value="3">Surface-SBMD Systems (SSBMD)</option>
                    <option value="4">Undersea Systems (USS)</option>

                </select><br/></span>
Feb 24, 2010 at 9:23 PM

Its called "SiteCity" on the source or child list.

Coordinator
Feb 25, 2010 at 1:23 AM

Scott:

I think that the issue with the duplicates is that you're trying to manage all of the relationships in the same list. If you look at the documentation page for $().SPServices.SPCascadeDropdowns, you'll see that I recommend using one list to manage each relationship. This is good relational table design and will eliminate the duplicate issue altogether.

So, for what you are trying to accomplish, you'de have three lists: States, Cities, and Sites. States would just have the Title column which contains te names of the States. Cities would have the City names in the Title column, and a lookup column called State which is a lookup to States:Title. Finally, Sites would have the Site names in the Title column and a lookup column called City which is a lookup to Cities:Title.

M.

Feb 25, 2010 at 3:31 PM

I gave in and went with seperate lists. I was trying to keep it as small as possible, but this works. I really appreciate your time and help on this, this will be a big hit with the business units I support.

On another subjuct, is there a way to update a comment box with a workflow or other means and not write over the previous comments? Kind of like the the versioning you get on a comment box but I would need to export all the data not just the last entry.

 

Coordinator
Mar 1, 2010 at 9:29 PM

Scott:

Did you figure out your second question? Still need help?

M.

Mar 1, 2010 at 9:34 PM

Not really, I have been struggling with this for a while. I love the way that SharePoint time stamps who and when a comment was made with versioning. The problem I have is due to Banking regulations I need to be able to export the entire history of the comments, not just the last one.

Coordinator
Mar 2, 2010 at 3:49 AM

Scott:

It probably depends on all of your requirements together.  There are probably quite a few options.

When you say that you need to exports the comment history, what are your actual requirements there?  Where does the item content need to end up?

M.

Mar 2, 2010 at 1:54 PM

Worst case is that it will be exported by the user to excel from the actions button. Best case it will be exported using SSIS and displayed on a website using SSRS.

Coordinator
Mar 2, 2010 at 3:59 PM

One thought would be to capture the user comments on one column and then have a workflow append those comments with a timestamp to another column which is inaccessible on the forms, but visible in the views.  If you have versioning on, that will of course create a new version which may not be what you want.

You could also shove the whole document with it's history into the Records Center for storage ad infinitem.

Again, it all depends on what the actual record-keeping requirement is. I doubt that downloading to Excel covers the regulatory requirements.

M.