rhia

A 1-List Approach to Cascading Look-ups

Blog Post created by rhia Champion on Mar 23, 2018

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

Outcomes