cancel
Showing results for 
Search instead for 
Did you mean: 

A 1-List Approach to Cascading Look-ups

Automation Master
Automation Master
6 13 3,472

Hi there, folks!

You may recall this blog post from a while back by Sean Fiene‌ -- it's a great write-up on how to approach cascading look-ups in Nintex Forms using lookup columns in SharePoint. A summary is: for each set of data you want to create - you need to make a new SharePoint list. It makes sense!

But, I, personally, don't like having so many lists. I'd rather just have one place for myself and my clients to update. I wanted to find a new way - and, I have. I'd like to share that with you today so you can poke holes in it, or try it for yourselves. 

Let's look at a 1-list set-up for 4 different cascaded sets of metadata.

AnimalVegetable

Tier 1Unique 1Tier 2Unique 2Tier 3Unique 3Tier 4
AnimalYMammalYCatYCalico
AnimalMammalCatManx
AnimalMammalDogYPug
AnimalMammalDogBeagle
AnimalAmphibianYTree Frog
AnimalAmbhibianSalamander
VegetableYLeafyYLight GreenYIceburg
VegetableLeafyLight GreenRomaine
VegetableLeafyDark GreenYKale
VegetableLeafyDark GreenSpinach
VegetableLeafyPurpleYCabbage
VegetableRootYBrownYPotato
VegetableRootOrangeYCarrot
VegetableMarrowYOrangeYPumpkin
VegetableMarrowYellowYSpaghetti Squash
VegetableMarrowYellowButternut Squash

Let me explain:

An issue we have right now is that if we simply use a List Lookup and pull back Tier 1, we will get hella duplication of Animal and Vegetable. Perhaps some day we will have a "remove duplicates" functionality, but right now - we don't.

This is where views save the day.

  1. For each unique value in Tier 1, I put a "Y" in my Unique 1 column. For consistency, I always tag the first unique item as unique. 
  2. Then, I create a view: Tier 1 - Unique. Filter when Unique 1 = Y. 
  3. Now, when I set-up my List Lookup control in my Nintex Form, I tell it to look at the list called AnimalVegetable and return column Tier 1 using view Tier 1 - Unique. Perhaps I've named that List Lookup something crazy like "Tier 1".
  4. This is repeated for each column, as you'll see above, in terms of setting up views. So I'll end up with a Tier 2 - Unique, and Tier 3 - Unique. And, I can have as many tiers as I like. 
  5. When I want to filter my Tier 2 values based on Tier 1, I set my Lookup List to look at list AnimalVegetable, column name Tier 2, using view Tier 2 - Unique, filtered by control Tier 1. 

For me, this works really well. How about you? What are your thoughts?

Cheers!

Rhia

13 Comments
Automation Master
Automation Master

Nice work! I really like this solution for the same reasons. Having one list vs multiple is more manageable.

eiben
Nintex Newbie

Awesome! I will take this into consideration for my next projects.

Automation Master
Automation Master

And, just so you know, ‌ mentioned on Twitter that they'd be taking the Remove Duplication functionality into consideration for Universal forms.

Automation Master
Automation Master

Where's mineral in your example?  Come on Rhia!  OK I'll do it for you:

Tier 1Unique 1Tier 2Unique 2Tier 3Unique 3Tier 4
MineralYRare ElementYRhia WieclawekYGold

Now your table is complete   Awesome work Rhia!

Automation Master
Automation Master

Awwwwwwwwwwww  Thanks Chris!
Shame we didn't get to see you at xchange, btw :<

Automation Master
Automation Master

Hehe.  Yep, I was pretty gutted to miss out.  Up for the next one though!

jpmhuls
Nintex Newbie

Excellen post Rhia‌,

You could even take it one step further, where you create calculated coluimns showing only the tier value where Unique is Yes (e.g. "Tier 1 LU" = If([Unique 1]="Y",[Tier 1], "")" etc.). And set the lookup dropdown to use "Tier 1 LU"; if al is well (which is my experience) the dropdown should skip the empty values.

Automation Master
Automation Master

Whoa, that's a neat point, ‌ -- I don't have much experience with Calculated Columns (other than the basic date items and concatenating) so I appreciate seeing how others could use them!

I wonder if this idea would leave a lot of white spaces where it was null? Definitely worth a shot! Thanks!  

jpmhuls
Nintex Newbie

Hi Rhia‌,

Null/empty values will not be included in the dropdown

Pyae
Nintex Newbie

This is a great post and it is what I looking for. Love to see the great contribution from the members. Thank you all.

f_falomir
Nintex Newbie

First of all: amazing approach!! I'm just using it...

But be careful, because I think it doesn't work in case of data is repeated in different categories. Let me show the case:

Yo can cascade "Probabilidad--> Consecuencia" but you can't cascade "Consecuencia / Riesgo" because Riesgo has two different values to the same "Consecuencia", so the last lookup are showing you "Bajo" and "Alto"

To avoid it, finally, I'm using the cascading to decide the first and the second column and,  to get the right "Riesgo" I use "unique column" combining the first and the second columns, as "Riesgo_Unico_Calc" column (it could be Calculated field type in Sharepoint)

In the form, using a calculated field you can combine the two columns to get directly the desired data from the list using a lookup formula (sorry, in Spanish)

buscar("Lista_Risk_Assesment",Lista1 + "_" + Lista2,"Riesgo")

  • Lista_Risk_Assesment= list name
  • Lista1=First lookup list
  • Lista2= second lookup list
  • Lista1 + "_" + Lista2 = Concatenate value to get  the third "unique" column
  • "Riesgo"= field to get data

 

That's all!!

Have a nice day

jpmhuls
Nintex Newbie

Hi Francisco‌,

Just out of curiosity: why would you have a Y in the column Cons_Unica for the each extra Consecuencia entries for a value? E.g. Why are both Cons_Unica's set to Y for both "Pérdida seria" entries?

f_falomir
Nintex Newbie

oooppps.

You are right!

Don't need it. Only need the "Prob_Unica" to link the second lookup list.

is there because first I tried to use the two unique columns. Finally, I realized that I couldn't use it due to data distribution.

Thank you very much!