Nintex Forms - lookup with 2 filters

[rauch]

Hi, I have the following problem:

I'm creating a form for ordering stuff. We have different categories in different countries for maximum costs. We have a list where the limits are defined. It looks something like this:

Country | Category | Reference Price

Austria | A | 1000

Austria | B | 2000

Austria | C | 3000

Germany | A | 800

Germany | B | 1600

Italy | A | 1000

Italy | B | 1900

Italy | C | 2200

which means we have category A for a bunch of different countries but with different prices.

Now what I want to do is have a calculated value that tells the price to the person who fills it, which means I have to filter by 2 variables. How can I do that?

I tried calculated value with lookup but I can set just one filter there.

[emha]

you can create one calculated column on the list that will join your key values (country and category) to single value (eg. A_Austria).

then build your filter on top of that calculated column.

[rauch]

sorry for the late answer. I caught a flu last week and was not able to work.

I still don't know what exactly to do. I've tried to make a calculated value like this but I can't see any information in that. Is there some guide on how to do this?

[emha]

define on your list a calculated column like this

result

[rauch]

This doesn't work for my problem , because the values are lookup from yet another list. So I can't choose them for a calculated column.

Here's a little more about my lists:

There's a list "categories", where A,B,C,D is defined. Then there's a list "countries", where country, currency, company name, and permissions are defined. Then there is the policy list, which takes the infos of both this lists and creates a overview with all categories for all countries. In this list is also the reference price I need to gather. And the Form is in a 4th list that will include the workflow outcome. It's pretty complicated but it needs to stay highly customizable, so I have to work with a lot of lookups.

[pwp08a]

I've ran into this before,  OOTB there is not an easy way to have two filters on one column.  Depending on how many choices you have you can filter using a view (not dynamic) using panels to show/hide the different panels containing different lookups filtered by each view.  Then have a filter on the lookup as the second filter.

  Might look into SPServices if you are good with JavaScript. 

[emha]

you haven't mentioned that i your original question...

I've some doubts with your new explanation.

category list and country list - quite clear.

policy list: "takes info of both list" - does it mean it contains a lookup column to category list and it contains a lookup column to country list? or an "info" is stored there some other way? (just to be sure)

the most doubts I've with your "4th" list:

- "that will include the workflow outcome" - original question states you ask for a solution in forms now you mention workflow. I'm confused what exactly do you need...

- I guess "4th" list is somehow related to the above three lists. please specify exactly the relation - does it contain lookups to countries and/or categories or to the "policy list"

- if lookup for price is still needed in forms and not workflows, please specify exactly what inputs do you acquire on the form, either countries and categories (lookups?) or a reference to "policy list" item

if you could post your definitions, it could be helpful

[rauch]

List three takes info from the other 2 lists (Country and category) and adds the reference price.

those information sum up to the list i posted in the first question.

the 4th list is where i do the form, preparing for a workflow (I'll have to do that once the form is working).

It's a request to buy something. So List 4 is where the user will make his request. List 3 is where's written how much it can max. cost (depends on country and category).

So in the 4th list you will choose your country and your category and then I need to show the max. amount you're able to buy. The user will be able to enter the amount regarding to the offer he's got and if it's in tolerance I will let him pass.

[rauch]

To make it easier to understand I've tried to visualize it:

[emha]

ok, a bit clearer.

so I think you may apply cascading drop down logic on your case. there are several discussions on the topic on this forum.

I think here is logically the same case as you haveThree-Tier Cascading Drop Downs: Do Go Chasing Waterfalls!