Cascading Drop Down Using One Reference List

  • 17 November 2016
  • 18 replies
  • 18 views
kelliganp
Userlevel 4
Badge +10

Hi Folks,

I some content here that demonstrates how to create cascading drop down boxes using a different reference list for each control.

https://community.nintex.com/docs/DOC-1201

https://community.nintex.com/community/training/blog/2016/03/17/forms-film-festival-ep-1-cascading-lookups

I want to know if it is possible to do the same thing with one reference list. All of my lookup data is ion one list called "Issue Item catalog". The first drop down will allow a person to select an item name, this then will offer one or more "National Stock Numbers" (NSNs) in the next control.

I have been tinkering around with it based on the videos linked above but have not been able to make it work. if I cannot do it, please let me know and I will split my catalog in two. Splitting the catalog will just make more work for the person tasked with managing it.

Thanks and Regards,

Patrick Kelligan

18 replies

E
Userlevel 5
Badge +14

technically, it's possible to define lookup field that back reference the same list.

however, in order you could build cascading hierarchy (master/detail relationship), you have to ensure that every value being looked up gets unique key/ID. ie. every item name and every NSN number must get unique ID. that means they can appear just once in the list.

considering that it means it will just work for very specific case when NSN groups are disjunct across items, aka. every single NSN can be assigned to at most one item.

if that's your case, then you should be able to manage it with one list.

but I personally wouldn't do that.

see my testcease

list definition

parent field references its own list

list data

level1 dropdown

note you by default will see all the items in the list, so you will have to define a view that filters just for items. but as I have noticed from release notes lookups with views are quite buggy.

level2 dropdown

kelliganp
Userlevel 4
Badge +10

Hi Marian,

Thanks for your response. Let me give you a better rundown of what I am trying to do.

I have a catalog of items in a list. These items are supplies that are in stock on site and can be issued upon request as opposed to a supply request which must be ordered.

An item has a name/nomenclature like "Fire Extinguisher". A fire extinguisher may have several NSNs depending on the details of the description.

In a separate list, I created a form to request stock items. First a user will select an item like a fire extinguisher, then the will select an NSN with a lookup populated by the first selection. It is possible that we could have multiple items with the same NSN because large items or items with high volume of stock may be located in multiple areas (the catalog has a location and inventory count included with it), however this should be pretty rare. If duplicate NSNs cannot be done in my situation, I can eliminate the multiple NSNs and consolidate those stock lines for the sake of making this work.

Is this scenario in line with your example above?

Regards,

Patrick Kelligan

B
Badge +11

I would assume you can compare this to multiple cities falling under the same state. Is that correct?

E
Userlevel 5
Badge +14

if you can clean it and make sets of NSNs unique across items, and especially ensure that you can follow this rule for future,  it should work.

but as I've said, I wouldn't go that way at all!.

it's not good design. you implement considerable design restriction, and I would bet soon or later you will hit that restriction.

what about eg. case  when you introduce new type of fire extinguisher and (some) NSNs will need to be shared for both old and new type?

it will break your application....

kelliganp
Userlevel 4
Badge +10

Correct. Thanks!

B
Badge +11

I would have a State list and I would have a city list with a city and a state field. Then the main list would have a State field and a City field. Both would point back to the associated lists. The City would be filtered based on the state.

kelliganp
Userlevel 4
Badge +10

I cleaned it up but still cannot get it to work.

An NSN is very unique. I do not see NSNs being shared. A new item will be issued a new National Stock Number. It is sort of like a "social security number" (SSN) for products. That is why I really want to keep them in a single list/table.

Here is my configuration for the first lookup (Item Name):

195242_pastedImage_1.png

Here is the second lookup (Item Identifier/NSN):

195243_pastedImage_2.png

Thanks!

E
Userlevel 5
Badge +14

if you followed my example you should be able to make it working.

I would need to see your setup and know exact error/problem you experience.

kelliganp
Userlevel 4
Badge +10

I edited my previous post with configuration before I saw that you responded.

kelliganp
Userlevel 4
Badge +10

Hi ‌,

I agree that this would work and be pretty straight forward but with 50 states, a second list would be easy to manage. With many hundreds of stock items that change frequently, my customer wants to manage only one list/catalog.

kelliganp
Userlevel 4
Badge +10

Hi Marian,

I have duplicated your set up in the manner that I will need to use it. See below and tell me if you can see where I went wrong.

List configuration...

195244_pastedImage_1.png

List Layout/Look with data...

195248_pastedImage_2.png

Request Form...

195249_pastedImage_3.png

Parent Lookup configuration...

195250_pastedImage_4.png

Child Lookup configuration (with filter settings)...

195251_pastedImage_5.png

Preview with  Parent open...

195252_pastedImage_6.png

Preview with child open after parent value chosen...

195253_pastedImage_7.png

Not sure why mine is not working here. Any ideas??

Thanks and Regards,

Patrick Kelligan

E
Userlevel 5
Badge +14

check carefully what are sources of level1 and level2 dropdowns.

you have swapped them.

kelliganp
Userlevel 4
Badge +10

Hi Marian,

Ok... I see why my set up was not working like yours. I put some more menaingful data in the reference list to demonstrate the difference in what your scenario did and what I want my scenario to do.

Here is your list and its configuration with different data...

195967_pastedImage_1.png

In your list, you are self reference the same column. My populated list will look more like this...

195977_pastedImage_2.png

On the form, the first dropdown should show:

Pens; Pencil; Paper; Chair; Desk;

If I select Chair, the second dropdown should show only 2 values as follows:

PN456; PN457;

This represents a situation where the customer narrows down his selection first, by choosing a generic term of Chair, and then gets specific with the Part Number of, say, "PN457".

My issue, is that unless I put Nomenclature in one list and Part Number in a separate list, I cannot get the cascading drop downs to work.

Let me know if this is still unclear.

Thanks!!

E
Userlevel 5
Badge +14

it's absolutely clear to me what you want to achieve, and I provided you with explanation how does cascading works and what are prerequisites to make it working within one list.

I'm sorry, but the way you have it set up or you want to have it set up is unfortunately not going to work!

as I've already explained in my very first post, in order you could filter second dropdown by chair you have to have:

- the chair assigned unique ID - hence those rows with no parent defined in my testcase

- all the part numbers assigned to chair have to reference the same parent (nomenclature), the one with that unique ID, otherwise filtering can not work.

let's see what happens if I reproduce your list setup.

I have two items with nomenclature == chair. if I select them in lookup they both evaluate to different values.

note value of lookup control/field is always 'ID;#display text', despite it shows just text in its option  list.

in order filtering to work you have to filter second lookup/dropdown by a field that have the same value and in the same form.

that in turn means that the field has to be of lookup type (to ensure format) and it has to have exactly the value of (eg.) 21;#chair

value of 22;#chair is considered to be another nomenclature and their assigned part numbers would be filtered out.

what you would need is filtering just by lookup text.

so either to get just the text from lookup control or to be able to define a filter that would pick just text out of lookup value.

that would have been possible if nintex had supported custom configuration what should be lookup value for the former case or to support runtime functions and named controls references in filter definition for the later.

but none of that is unfortunately the case.

to rephrase you, Let me know if this is still unclear happy.png

kelliganp
Userlevel 4
Badge +10

I did not mean in any way to insult or irritate you. By your last response, it seems that I may have. My apologies for that. I will go ahead and give you the "Correct Answer" on this as I am still pretty new to Nintex Forms and you probably know what you are talking about better than I. I will find a way to make this work.

Regards,

Patrick

E
Userlevel 5
Badge +14

no, no, I do not feel aggrieved at all!
I haven't wanted to be unpleasant anyhow. I'm sorry if that sounded so.

good luck with your investigation.

kelliganp
Userlevel 4
Badge +10

I am very glad to hear that. I will post what I end up with. Thanks for all of your many helpful posts!

Best Regards,

Patrick

E
Userlevel 5
Badge +14

you're welcome happy.png

looking forward seeing some new approach

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings