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.
Solved! Go to Solution.
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.
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?
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.
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.
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
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.
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!