Cascading (dropdown) selections not always (!!!) working

[mai-kel]

Hi there,

I have a problem with one of my Nintex forms.

I have three categories, which depend on each other:

Kategorie 1. Product category

Kategorie 2. Product

Kategorie 3. Product details

All of this information is written into one big table:

So if you choose "Service" as a Kategorie 1 you can choose "Kommunikation" or "Umzug" as Kategorie 2. This is working:

Selecting "Kommunikation" leads to options "Benutzer" and "Postfach" for Kategorie 3. Everything fine up to now. This is also working for different Kategories.

BUT for Kategorie 1 "Vorhaben" this is not working, at least when it comes to Kategorie 3.

So, first step selecting "Vorhaben" leads to correct selections within Kategorie 2:

There is only "Neue Software" to select for Kategorie 2 if you choose "Vorhaben" for Kategorie 1 (see table above). So far so good.

But the problem I have is with Kategorie 3 here. You should be able to select "Softwareentwicklung" and "Softwareauswahlverfahren" (also see table on top). But unfortunately I am not able to select anything:

As I already mentioned in most cases everything is working fine, but in this special case not. I do not know what could be the reason for that.

Here are the two config settings of the two lookup fields:

Kategorie 2

Kategorie 3:

Maybe you have an idea?! I am clueless. :catsad:

Cheers

mai-kel

[SimonMuntz · Accepted solution]

Hi,
The key to cascading lookups is the setup of the lists.
Having one list is not how it’s done.
Please see the three tier lookup tutorial here:
https://community.nintex.com/t5/Community-Blogs/Three-Tier-Cascading-Drop-Downs-Do-Go-Chasing-Waterf...

[mai-kel]

Hi Simon,

Thanks for your fast response. I know the article and that was also my first approach. But customer was not happy with three different lists, so we put everything into one list. And as I mentioned, in 99% everything is working fine. So maybe there is also a way with only one list?!

Cheers
mai-kel

[SimonMuntz]

I always use multiple lists when doing cascading drop downs as this is the correct way to do it.
If this could be done with just one list which would be easier and more efficient you would find a tutorial on this site on how to do it but you will not.
What your customer wants and what is technically possible are two different things in this instance.

[kunalpatel]

@mai-kel .....Could you please try below approach?

1. In your list create a calculated column "calcKategorie1Kategorie2"...formula will be [Kategorie1]&"_"&[Kategorie2]
2. On the form add a calculated control (hide it always using 1<2 as condition)
3. Now you have 2 lookup controls on the form for Kategorie1 and Kategorie2, using parse lookup function in the calculated control, construct the same string as we have in Step # 1
4. Use Step 1 calculated column and Step 3 calculated control to filter Kategorie3
5. If you want to add 1 more level of cascading then you add one more columns and controls and so on

Let us know if this works for you

[mai-kel]

Hi @kunalpatel,

first of all thanks for your response! I would like to test your approach, but I am not sure if I understand you correctly.

So just for the record, this is the situation I have currently:

I have one "big" list where I can choose between different categories "cat1", "cat2" & "cat3" (via lookup). Beside that I have one "supporting" list containing all categories and their dependencies.

The "big" list contains three lookup columns "cat1", "cat2" and "cat3".
The "supporting" list has three single line of text columns "cat1", "cat2" & "cat3".

The form is on top of the "big" list.

So I am not able to create a formula [Kategorie1]&"_"&[Kategorie2] because these columns are lookup columns and therefor not usable within calculated columns. Or did I misunderstand something?

[kunalpatel]

@mai-kel ....You don't need multiple list with this approach, that's the plus point of this. Sorry I missed few points before. Follow below detailed steps for this approach:

• Create a list (One List Only) using custom list template
• Create some SLT columns now e.g. Cat1, Cat2, Cat3, Cat4. These columns will be our lookup column on the form
• Create 2 choice columns (choices will be No, Yes, keep default No) and name it "UniqueCat1" & "UniqueCat2"
• Find all unique values in Cat1 column and set UniqueCat1 as "Yes" & rest as "No"
• Find all unique values in Cat2 for each unique values of Cat1 (I know it's little confusing... lol) and set UniqueCat2 as "Yes" & rest as "No"
• Create 2 Views now:
  • UniqueCat1View - Keep only Cat1 & UniqueCat1 columns, filter it UniqueCat1 = Yes, sort by Cat1 in ascending order
  • UniqueCat2View - Keep Cat1, Cat2 & UniqueCat2 columns, filter it UniqueCat1 = Yes, sort by Cat1 & then Cat 2 in ascending order
• Now create 2 calculated list columns:
  • lstcol_calc_Cat1_Cat2:- Use formula =[Cat1]&"_"&[Cat2]
  • lstcol_calc_Cat1_Cat2_Cat3:- Use formula =[Cat1]&"_"&[Cat2]&"_"&[Cat3]
• Now on the form add lookup control for Cat1 and Name it as "ctrl_lkup_cat1". Add others lookup controls and name them with same naming convention as for Cat1.
• Now add 2 calculated controls for Cat3 & Cat4 filters (I usually put them in panel and hide the panel using 1<2 rule), functions will be (and make sure they DO recalculate formula in all 3 modes):
  • parseLookup(ctrl_lkup_cat1)+"_"+parseLookup(ctrl_lkup_cat2)
  • parseLookup(ctrl_lkup_cat1)+"_"+parseLookup(ctrl_lkup_cat2)+"_"+parseLookup(ctrl_lkup_Cat3)
• For Cat1:
  • Select "One List Only" as Source List
  • Source View - UniqueCat1View
  • List Column Name Cat1
• For Cat2:
  • Select "One List Only" as Source List
  • Source View - UniqueCat2View
  • List Column Name Cat2
  • Filter it:
    • By control's value
    • Where field Cat1
    • Filtered by control - ctrl_lkup_cat1
• For Cat3:
  • Select "One List Only" as Source List
  • Source View: All Items (from now onwards we will use this view for all other cascades)
  • List Column Name: Cat3
  • Filter it:
    • By control's value
    • Where field: lstcol_calc_Cat1_Cat2
    • Filtered by control: ctrl_calc_Cat1_Cat2
• For Cat4:
  • Select "One List Only" as Source List
  • Source View: All Items
  • List Column Name: Cat4
  • Filter it:
    • By control's value
    • Where field: lstcol_calc_Cat1_Cat2_Cat3
    • Filtered by control: ctrl_calc_Cat1_Cat2_Cat3
• Now if you have more columns then all you need is:
  • Calculated column in a list
  • Calculated control on the form
  • Follow the same steps we did for Cat3 & Cat4

Let me know if you have any questions

[kunalpatel]

@ArturoHerbe13 ....I'm little confused here....could you please elaborate more?

[JakeThomas1]

ArturoHerbe13here:

hmmm, could you please explain your point of view? Just a little bit confused