Skip to main content

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 1 Unique 1 Tier 2 Unique 2 Tier 3 Unique 3 Tier 4
Animal Y Mammal Y Cat Y Calico
Animal   Mammal   Cat   Manx
Animal   Mammal   Dog Y Pug
Animal   Mammal   Dog   Beagle
Animal   Amphibian Y Tree Frog    
Animal   Ambhibian   Salamander    
Vegetable Y Leafy Y Light Green Y Iceburg
Vegetable   Leafy   Light Green   Romaine
Vegetable   Leafy   Dark Green Y Kale
Vegetable   Leafy   Dark Green   Spinach
Vegetable   Leafy   Purple Y Cabbage
Vegetable   Root Y Brown Y Potato
Vegetable   Root   Orange Y Carrot
Vegetable   Marrow Y Orange Y Pumpkin
Vegetable   Marrow   Yellow Y Spaghetti Squash
Vegetable   Marrow   Yellow   Butternut 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. 

 

214007_pastedImage_27.png

 

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

 

Cheers!

 

Rhia

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

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

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

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!

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

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

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(fUnique 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.

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!  

Hi Rhia‌,

Null/empty values will not be included in the dropdown

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

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

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?

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!

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings