Editing Items that have been added using SPCascadeDropdowns

Dec 2, 2009 at 2:49 PM

Hi Marc,

This is a fantastic library of features you have published here.  After evaluating a few commercial tools that claimed to "Cascade Lookups" your solution is the only one I've found yet that actually allows cascading of SharePoint columns of type "Lookup Column". 

You should also be proud of your documentation.  It exemplifies the level of detail that fellow developers expect but never receive.  Thank you, thank you!

Here's my scenario:  I have several large lists that are now cascading correctly when adding a new item.  I followed your advice and created a CustomNewForm.aspx.  I'd like to cascade/filter the same lookup columns when editing (and create a CustomEditForm.aspx), but any child column in a cascading relationship is blanked out initially when editing.  This makes sense when adding a new item, but I'd expect the value to not be initialized (forcing the user to choose it again) when editing.  Is it possible to prevent this with your current release?

If I use the standard EditForm.aspx and change any other column, the parent/child columns appear to remain in tact and as expected even though there are multiple child options with the same text value (with a different ID behind the covers).  Will this always the case, or should I try to enforce a cascade/filter like in the CustomNewForm.aspx? 

I hope that made sense.

Thanks again,

david

Coordinator
Dec 2, 2009 at 3:11 PM

David:

Thanks for the kind words.  My goal is to have this library actually work for people.  Otherwise, what's the point?!?!?

On your question about the EditForm, the currently selected value ought to be shown with the current code.  If you're not seeing that, then either I have a bug or you may have an error in how you are calling the SPCascadeDropdowns function.  Are you using exactly the same call in your EditForm as in your NewForm?

M.

Dec 2, 2009 at 4:30 PM

Hi Marc,

 Thanks for the quick reply!

 My example is not working because the relationshipListChildColumn is another column of type "Lookup", and is showing values as "(ID);#(Title)".  I'm still able to cascade and save related data correctly, values are just not matched correctly when editing.  I tested your code in another list where the relationshipListChildColumn is of type “

 Specifics of my example:

Grandparent Lists: FeatureGroups & Categories

Parent List: CategoryFeatureGroups – contains lookup columns to FeatureGroups & Categories – is not using Title column because Title should really be the Feature Group’s Title (hence the problem)

Child List: CategoryFeatures, with lookup to CategoryFeatureGroups & Categories

 Within CategoryFeatures I’m trying to cascade the CategoryFeatureGroup values based on the Category selected.

 If I was populating the Title column in CategoryFeatureGroups, I wouldn’t have the problem.  I want to avoid this, since it’s really the same value as the FeatureGroup.  Maybe I can accomplish this with a Calculated Column.

 Long story short: your code works when the Parent List has populated the Title column.

 Thanks again,

david

Coordinator
Dec 2, 2009 at 5:02 PM
Edited Dec 2, 2009 at 8:18 PM
What version of the library are you using? I have a fix in v0.4.6 which ought to take care of the ID;#value thing.
M.
Dec 2, 2009 at 5:22 PM

Marc, you're clearly 8 steps ahead of me!

I was using v0.4.5.  I've downloaded v0.4.6 and, as promised, the ID;#value thing is fixed.

I still need to populate the Title of the CategoryFeatureGroups in order for the CategoryFeatureGroup dropdown to be populated when editing a CategoryFeature.

Can you think of a clever way to create a calculated column that would simply pull the value (from the ID;#value) out of a Lookup Column?  If I could do that, then maybe I wouldn't have to duplicate the FeatureGroup column value into the Title column.

Thanks,

david

Coordinator
Dec 2, 2009 at 8:32 PM

Well, half the issue down, anyway.

I'm wondering if your architecture is right with the other lists.  Could you give some data examples? If there's any issue with confidentiality, you can send to me with the link under my sympmarc name.

M.

Dec 3, 2009 at 10:25 AM

Hi Marc,

 

I have 3 master lists:

1)      Categories – Categories of Appliances

ID              Title

1                                Ovens

2                                Dishwashers

 

2)      FeatureGroups – Groupings of Appliance Features

ID              Title

1                                Dimensions

2                                Colour Options

 

3)      Features – Appliance Features

ID              Title

1                                Height in tall unit (cm)

2                                Height in base unit (cm)

3                                Width (cm)

4                                Depth (cm)

 

I’m then defining all of the FeatureGroups and Features specific to each Category in these 2 lists:

4)      CategoryFeatureGroups – Has Category column which is a lookup back to Categories…and a FeatureGroup column which is a lookup back to FeatureGroups(I don’t see the need to populate Title, since I know the parent FeatureGroup)

ID              Category         FeatureGroup

1                                Ovens              Dimensions

2                                Dishwashers    Dimensions

 

5)      CategoryFeatures – Has Category column which is a lookup back to Categories…and a Feature column which is a lookup back to Features…and a CategoryFeatureGroup column which is a lookup back to CategoryFeatureGroups.  This is where I’d like the cascading dropdown to only show the CategoryFeatureGroups for the Category selected

ID              Category         CategoryFeatureGroup           Feature

1                                Ovens              Dimensions                             Width(cm)

2                                Ovens              Dimensions                             Depth(cm)

 

I hope this helps.

I can provide screenshots if they would be more helpful.

 

Thanks for looking at this,

david

Coordinator
Dec 3, 2009 at 5:20 PM

David:

Sorry for the delay.  I see what you're trying to do, and you'll have a problem using SPCascadeDropdowns with things as above.  The issue is going to be that in the CategoryFeatureGroups and CategoryFeatures lists, the IDs of the items don't correspond to the IDs for either the Category, the FeatureGroup, or the Feature.  What you're doing is good relational structure, but it's not the way I've built SPCascadeDropdowns, at least so far.

Does that make sense? I had one other person who wanted to do something similar, and I think she reorganized her data to make it work.

M.

Coordinator
Dec 5, 2009 at 5:00 PM

David:

I had a thought on your issue here this morning and I think I have a potential feature addition to allow it.  I've set up an Issue to track this:
http://spservices.codeplex.com/WorkItem/View.aspx?WorkItemId=5010

M.

 

Coordinator
Dec 6, 2009 at 6:08 PM

David:

If you're still working on this, give v0.4.7ALPHA4 a look: http://spservices.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=36650.  I've added some code into that version that ought to let you do what you wanted here.

M.

Dec 7, 2009 at 10:06 AM

Hi Marc,

Cheers for your impressive customer service!

I followed your link and downloaded v0.4.7ALPHA5 (not ALPHA4). When editing Category Features, cascaded Category Feature Group values appear correct in the CustomNewForm and CustomEditForm, but I'm afraid they are saving as a different value...and one that doesn't exist in the cascaded list!

Any ideas?

I have a workaround working with v0.4.6 where I just need to ensure that the TITLE of the Category Feature Group list is populated with the FeatureGroup value.

Thanks again,

david

 

Coordinator
Dec 7, 2009 at 12:53 PM
Edited Dec 7, 2009 at 1:21 PM

Well that certainly doesn't seem useful, does it?  Argh.

Any chance you could save your site as a template with the content and send it to me?  If not, perhaps we can debug through this together.

M.

Dec 7, 2009 at 3:01 PM
Edited Dec 7, 2009 at 3:02 PM

Hello

David, could you post your call of "SPCascadeDropdowns" function?
I think it should be called on the CategoryFeatures list like this:

$().SPServices.SPCascadeDropdowns({
relationshipWebURL: "Your web (site)", //web (site) where is relationshipList relationshipList: "CategoryFeatureGroups" , //display name list
relationshipListParentColumn: "Category",
relationshipListChildColumn: "FeatureGroup",
parentColumn: "Category",
childColumn: "CategoryFeatureGroup", //display name
});

If you make a change in CategoryFeatureGroups and break the relation between Category and FeatureGroup, then when you edit a CategoryFeatures element containing that relation CategoryFeatureGroups will be empty.

It can also break when you have two lookups to the same list (Ex CategoryFeatureGroups and FeatureCategory both relating to Category) that show different fields from the third list.

I hope I helped you.

J.

Dec 7, 2009 at 3:33 PM

Hi Marc & J,

I didn't make a template of the site I'm working on because there is a serious amount of extra data on it than I care to share.

I've created a test site with the lists in question and created a template of that instead.  I was hoping to attach a .zip file to this thread, but it looks like I must make it available another way.  http://87.127.227.133/mieleB2BDev/codeplex/TemplateForMarc.zip  Please let me know once you've downloaded this so that I can remove it.

This template includes my versions of CustomNewForm & CustomEditForm, where my calls look like this:

$().SPServices.SPCascadeDropdowns({
            relationshipList: "Category Feature Groups",
            relationshipListParentColumn: "ProductRange", // internal name of Category
            relationshipListChildColumn: "FeatureGroup",
            parentColumn: "Category",
            childColumn: "Category Feature Group"
        });

Thanks,
david
Coordinator
Dec 7, 2009 at 6:02 PM

David:

I've got the template; thanks.  Is this from WSS or MOSS?  I'm going to take a look and will get back to you asap...

M.

Coordinator
Dec 8, 2009 at 3:43 AM

David:

I'll admit that I'm having a little trouble following things here.  The Lookup columns don't come across intact in the template, unfortunately, and I've tried to recreate the columns, but even if I have them right, I don't have the data.

Let's take a step back: You say that "the cascaded Category Feature Group values appear correct in the CustomNewForm and CustomEditForm, but I'm afraid they are saving as a different value...and one that doesn't exist in the cascaded list!" Can you discern any pattern in this?  Do the IDs of the value you set have any connection to the values that are saved?

M.

Dec 8, 2009 at 7:18 AM

Hello

David I downloaded the template, thanks.

J.

Coordinator
Dec 8, 2009 at 12:42 PM

David:

I notice that you have columns in your list for IDs: ProductFeatureGroupID, ProductFeatureID, etc.  How are you intending these to be used? The SPCascadeDropdowns function works with the list item IDs, so I'm wondering if there's something about these *other* IDs that you're wanting to happen that might be causing the issue.

M.

Dec 8, 2009 at 1:39 PM

Hi Marc,

The other IDs you're asking about are no longer being used.  I loaded the SP Lists from a SQL DB, and I used those ID columns initially to create the Lookup relationships with a utility I wrote in C#.net.

Let me try to save my site as a template and then remove anything superfluous.

Thanks,

david

Coordinator
Dec 8, 2009 at 1:42 PM

David:

I think I'm good with the template that you've already given me. What I'll try to do is strip things down as much as possible to see what's going on.  In the meantime, if you could do a little sleuthing about the relationship between the values you are choosing and the values that are being stored, that would be helpful.

M.

Dec 8, 2009 at 3:10 PM

Hi Marc,

I think I see what's going on.

Within Category Features, I'm cascading Category Feature Group based on the Category selected.

The Category Feature Groups list is simply the Feature Groups stored at a Category level.

Example: Adding a Category Feature for Category = "Ovens"

The Category Feature Groups are:

  1. Cleaning & Care (ID 320)
  2. Designs (ID 261)
  3. Dimensions (ID 319)
  4. Features (ID 260)
  5. Programmes (ID 264)
  6. Specifications (ID 266)

When being saved, they are not being saved as their Category Feature Group value, but as their corresponding Feature Group ID value, which gets translated into a different Category Feature Group value than above.

  1. ID 17
  2. ID 29
  3. ID 30
  4. ID 47
  5. ID 86
  6. ID 320

 I hope this makes sense.

Maybe I need to change the values I'm passing in my call to SPCascadeDropdowns?

Thanks,

david

 

Coordinator
Dec 8, 2009 at 7:44 PM

Argh. I just typed up a big long response and my Internet connection flipped out for a second so I lost it.

While I understand what you are describing, it doesn't seem to mesh with how my code is set up.  Since I'm not sure what's going on on your end, I though that I would outline one of my test scenarios to see if you might spot a difference.

I have a list called Sales Opportunities with columns for State and City.  I've created secondary list to drive the SPCascadeDropdowns called StateCity, which has a columns:

  • State (lookup to the Title in the States list) and
  • City (lookup to the Title in the Cities list)

Here's the data:

ID Title State ID in States City ID in Cities
1 (no title) Massachusetts 1 Brookline 398
2 (no title) Massachusetts 1 Newton 558
3 (no title) Massachusetts 1 Billerica 383
4 (no title) Massachusetts 1 West Boylston 672
5 (no title) Rhode Island 2 Covington 705
6 (no title) Rhode Island 2 Providence 706
7 (no title) Rhode Island 2 Warwick 663

Then my jQuery looks like this:

$().SPServices.SPCascadeDropdowns({
    relationshipList: "StateCity",
    relationshipListParentColumn: "State",
    relationshipListChildColumn: "City",
    parentColumn: "State",
    childColumn: "City",
    debug: true
   });

When I change the State on my Sales Opportunities form, the City filters appropriately, and the values for State and City are saved correctly.  In other words, if I choose Massachusetts and West Boylston, the IDs which are saved are 1 and 672.

I've done the same thing with Region/State and that works fine as well.

M.

Dec 9, 2009 at 10:12 AM

Hi Marc,

Thanks for that.  I have now solved the problem!

The issue was that I needed to add a Lookup column to Feature Group to my Category Features list .  I was trying to force a Feature Group value into my Category Feature Group lookup column.  This gives me exactly what I need.  So, Category Features no longer needs a Category Feature Group lookup column...and the values for the new Feature Group lookup column are controlled with an SPCascadeDropdowns call to the Category Feature Groups list, which controls which Feature Groups are available for the given Category.

Thanks again for all the time you've spent on this for me!

Cheers,

david

Coordinator
Dec 9, 2009 at 2:50 PM

Great that you got it sorted out! Do you feel that the way the function is working is right for your situation or are there other enhancments that might make it better?

M.

Dec 11, 2009 at 9:29 AM

Hi Marc,

I definitely think the way the function is working is correct for my situation.

Thanks again so much for the quick responses and for providing a quick update for my situation.

All the best,

david

Coordinator
Dec 11, 2009 at 10:30 PM

You bet.  Let me know if there's anything else I can help with.

M.