List Lookup filter another List Lookup?

R

Hello, I'm working with Nintex forums and I've ran into a problem that I can't figure out. My form (on a different list and is acting at the main form) currently has two list lookups which are connected to one list named currencies. In the currencies list, there is a currency and amount value. Currently, when the user fills out the forum they pick a currency and an amount, independently. For example, they can choose currency "X" and amount "Y".  What I want is for the amount lookup to be filtered based on the currency they picked. 

 

For example, If the user picked "USD" as their currency then the second list lookup should say "5", "10", "20" .... but if they choose "EUR" then I want the second list lookup to display "4", "8" , "12".....

 

Is there a way to make this dynamic relationship with Nintex Forms or a relationship setting in the lists? 

 

Thank You

13 replies

G
Userlevel 6
Badge +13

The key is the data in your currency list. You'll need to have every number you want represented in the lookups with all the currencies that can be selected for that value to be present in the "value" lookup in a column.

My approach would be to split the list in to 2. One for Currencies and One for Values.

In Currencies just list all your available currencies.

USD

EUR

GBP etc

In Values list all your values and also create a lookup column to the currencies list which allows multiple selections

1   USD,GBP, EUR

2   GBP

3

4   EUR

5   USD, GBP

and so forth. Then when you add your list lookups to your form you can filter your 2nd list llokup based on the value of the first on the lookup column you created.

Is this the working towards the solution you require?

L
Badge +9

Hi Roshan Patel‌,

Set filter condition on 'Amount' field like below.

You don't want the Currency as lookup column here, have a choice column type it will be enough.

Thanks,

Lakshmi Narayana C‌

G
Userlevel 6
Badge +13

It does depend on how often the customer might want to make changes to the values and the currencies. If he can be sure they'll never change then fine, "hard code" them this way. However, I've always thought that better practice is to use lists to manage the values of choices like this in lists. That way, if this is an application, he can hand ownership of the lists over to an admin to add currencies and values as they require, rather than the user coming back to him asking him to reconfigure the form for him each time.

"You don't want the Currency as lookup column here, have a choice column type it will be enough."

It's not always wise to say one idea over another, without knowing the users full scenario. When offering an alternative solution it would be better for you to say why the user would take your suggestion over another, rather than just telling them.

R

Could you send a picture of how your list is setup please? 

L
Badge +9

Hi Roshan Patel‌,

Currencies List (Lookup List)     

 

Main List: If you keep Currency1 also as a lookup column then all entries (even duplicates) it will list out. so suggesting choice (drop down) for this.

I did not comment anything about Ryan Greenaway approach here. you can try that also.

Thanks,

Lakshmi C‌

R

I was able to replicate this successfully! I am trying to take it one step higher by adding an award option.

The full process would be the user picking an award then a currency and finally an amount. Each award has different set amounts.

For example award 1 might be $25 and its equivalence in other currencies. 

I expanded on your method and altered the currencies list to have 3 fields (award, currency, amount) and the main list to have (award1(choice), currency1 (lookup) and amount1 (lookup)). To achieve my affect, I would need to do a double filter to get my amounts. The currency lookup will be filtered based on the choice picked in the awards field. Then the amount field will be filtered based on the currency field. The problem I'm facing is that the amount lookup field displays nothing when being filtered based on the currency field. The currency field filters correctly based on award.

Is it not possible to filter a lookup field based on a filtered lookup field? 

Thank you!

L
Badge +9

Hi ,

No, this wont work in your case. See below.

        

 

then if you select 'EUR' you will get

But you need only value 4, this is why because we cannot apply more than one filter condition for a lookup field.

Cascade lookup Dealing with Cascading Lookups in Nintex Forms will work in this case.

Thanks,

‌

B
Badge +2

I am starting to be convinced this only works if you are a wizard and only on one day of the year when a hidden door opens.

B
Badge +2

Does one section ALWAYS need to be unquie?

What if I had like...

Accounting Team - Blue Pens

Accounting Team - Red Pens

Business Team - Orange Pens

Business Team - Blue Pens

Business Team - Black Pens

Sales Team - Black Pens

Sales Team - Purple Pens

See how they criss-cross there?

J
Badge +4
@burnsmh i have this same problem - did you find a solution to it?

One list has the same product group but different product names, but if i run list lookups from the same list then the product group shows the same thing 3 or 4 times

e.g. one list has

Team List
Accounting Team
Business Team
Sales Team

Pens by Team
Accounting Team / Blue Pens
Accounting Team / Red Pens
Business Team / Orange Pens
Business Team / Blue Pens
Business Team / Black Pens
Sales Team / Black Pens
Sales Team / Purple Pens

the Pens by Team list above has two columns (one called Team and one called Colour of Pens).

if I run the Team List Lookup using the Team Column in the Pens by Team list, it shows double or triple entries of the same thing (because it identifies each Team entry as a different selection).

I'm not sure how to get only unique entries in the list or cross reference the two lists.

J
Badge +4

@emha 


@cassymfreeman 


do you know any posts which could help with this?


 


my example shown above has two lists 


 


Product Group


Loan


Deposit


Transaction account


 


Product Names            Sub-type


Loan                            - mortgage


Loan                            - business loan


Loan                            - personal loan


Deposit                         - at call


Deposit                         - term deposit


Transaction account     - saver


Transaction account     - no fees


 


How do i run a list lookup on Product Group which then filters which SubType is available ?


 


I have spent the last hour trying to connect various combinations using the Filter by control's value but either I get repetition of the Product Names 2-3 times or the filter doesn't work and all of the subtypes are displayed ... :(

E
Userlevel 5
Badge +14

have a look on these posts


 


https://community.nintex.com/t5/Nintex-for-SharePoint/How-to-filter-a-look-up-column-in-nintex-forms/m-p/10809/highlight/true#M9066


https://community.nintex.com/t5/Community-Blogs/Three-Tier-Cascading-Drop-Downs-Do-Go-Chasing-Waterfalls/ba-p/82985?collapse_discussion=true


 

EuanGamble
Userlevel 5
Badge +13

Hi all,


 


I have put together a video showing how the new Responsive designer will allow filtering of unique values. Hope this will solve the challenges you are experiencing.


 



 


Cheers,


Euan

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings