Solved

Nintex Forms - lookup with 2 filters

R
Badge +4

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.

icon

Best answer by rauch 7 March 2017, 13:25

View original

27 replies

E
Userlevel 5
Badge +14

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.

R
Badge +4

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?

E
Userlevel 5
Badge +14

define on your list a calculated column like this

result

R
Badge +4

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.

P
Badge +4

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. 

E
Userlevel 5
Badge +14

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

R
Badge +4

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.

R
Badge +4

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

splist.png

E
Userlevel 5
Badge +14

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!

R
Badge +4

Thanks,

this looks promising. I will test it and keep you updated on the outcome.

R
Badge +4

Update: The cascading drop down didn't really help me. I have found this now: Trying to filter a list lookup based on two drop down selections

I included the JS from there to my form and my list to create identical, unique values on both sides with my joined key values (like you mentioned in your firs answer).

Now I have fields on List 3 and List 4 witch 'country'_'category'. This works fine with a lookup in a dropdown. I can change Location and Category in my form and the dropdown will show me 1 value to select (the right one) but still no outcome in a calculated value.

this is my (working) lookup:

lookup.PNG

this is my calculated value:

lookup("List Policy", "HiddenLocCat", HiddenField, "Reference Price")

It just stays empty.

HiddenLocCat in List 3 is an indexed column with unique values.

E
Userlevel 5
Badge +14

before I dig deeper into you configuration, two questions.

- isn't it that your calculation doesn't work in VIEW form? have you checked ON 'recalculate formula on view mode' option?

- isn't HiddenLocCat text type field/variable and HiddenField a lookup?

R
Badge +4

that's true. I just looked at it in preview mode. When I create an item with the published form it calculates the value as soon as I save it.

R
Badge +4

HiddenLocCat and HiddenField are single line textboxes. The're filled by this JS (similar on HiddenLocCat):

NWF.FormFiller.Events.RegisterAfterReady(function() 
{ 
    NWF$("#"+LocationControl).change(ProcessConcatenation); 
    NWF$("#"+CategoryControl).change(ProcessConcatenation); 
    function ProcessConcatenation() 
    { 
        var teamValue = NWF$("#"+LocationControl).find("option:selected").text(); 
        var typeValue = NWF$("#"+CategoryControl).find("option:selected").text(); 
        NWF$("#"+HiddenField).val(teamValue+"_"+typeValue);         
        NWF$("#"+HiddenField).trigger("change"); 
    }
});

LocationControl and CategoryControl are the lookup fields

E
Userlevel 5
Badge +14

ok.

may I consider by your previous comment you have resolved your issue?

R
Badge +4

Not really. I still need it to be calculated on change of the field, because I need the value to calculate something else. But I guess I'll have to do this with JS again

E
Userlevel 5
Badge +14

do you mean it doesn't recalculated on HiddenField change?

try to give the HiddenField control focus and then steal it, that might help.

R
Badge +4

it doesn't recalculate or calculate at all on change of HiddenField. When I change the value (even if i write something there manually) nothing happens. The place where my value should be is just empty. When I save the form and look at it again the value is calculated.

E
Userlevel 5
Badge +14

you posted you use following formula

lookup("List Policy", "HiddenLocCat", HiddenField, "Reference Price")

try to remove spaces from there, they might be considered as part of the string to compare.

R
Badge +4

Removing the spaces didn't do anything. But i'm one step closer now. I removed the "Connected To" value of HiddenField and now my calculated value will change if I focus/steal focus on HiddenField.

I now just need something to trigger this automatically, because in production this field won't be visible to the user.

E
Userlevel 5
Badge +14

you can do it in your ProcessConcatenation() function after setting HiddenField's value

NWF$('#' + HiddenField).focus();

NWF$('#' + SomeOtherControl).focus();

R
Badge +4

It works! Thank you so much for your effort!

I just used NWF$('#' + HiddenField).focus(); so when the user hits the next field it'll calculate the value.

A
Badge +4

Double-filtering is a question I had too. I was able to solve a double-filter scenario by adding another column (e.g. "Region_2") to the source list, copying the values from the original column (e.g. "Region") I wanted to filter on to the new column (e.g. "Region_2"), and if the value of the new column (e.g. "Region_2") is BLANK/empty, then it'll just exclude that row from the form dropdown at runtime. The form dropdown now uses the "Region_2" values to filter, and excludes rows with an empty value for this column. The reason I did it this way was to preserve historical data where preserving the original "Region" column value was critical. Wiping out the "Region_2" value doesn't affect historical data, so I was able to basically use a blank/empty value very effectively here.

You might be able to go the workflow "Update XML" route to automate the emptying of the field, but I just advised users to empty out the row's "Region_2" value when the row is no longer active.

E
Userlevel 5
Badge +14

hm. typical scenario for use of list views on lookup control, if they worked correctly ....

E
Userlevel 5
Badge +14

Hi Marco Ploerer‌,

was your problem resolved?

if so, could you mark the question answered?

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings