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?
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.
In Values list all your values and also create a lookup column to the currencies list which allows multiple selections
1 USD,GBP, 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?
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.
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.
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?
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.
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?