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.
- 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.
- Then, I create a view: Tier 1 - Unique. Filter when Unique 1 = Y.
- 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".
- 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.
- 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