Lookup Function based on 3 (or multiple) Values


I don't think this is possible, but I thought I would ask the question to see if anyone has any ideas.

 

In a New Responsive form, I am trying to set the value of a Variable based on the Lookup() function.  I am looking up the value from a list based on 3 values.  However, the Lookup() function allows for only 1 Filter Value.  In the past, I have created a column in the Lookup List that concatenated the 3 values, and passed the concatenated Filter Value in the Lookup() function and that worked great.  However, the Lookup list I am using this time is updated daily, so if I add a column today, it will be gone tomorrow.  Does anyone have any ideas on how I can accomplish Looking up a value based on 3 values?


13 replies

Userlevel 5
Badge +14

You're correct about the lookup runtime-function working that way (single filter values only), however to be able to give an answer / work-around, I'd like to know a little more. 


 


Does this concern 2 different lists? That is, if you're Lookup Control is pointed to List A, will this other lookup be done to List B using the values from List A's Lookup Control as a filter, or are you just trying to get a different column's information that is ALSO on List A? 


 


I think if you can answer this for me, then I should be able to work up some sort of example. 


 


 

Thanks for your response. 


The form is on a list called "Employee Data"


There is only 1 Lookup list called "Salary Steps"


The form has 3 controls - CurrentSalaryStep, CurrentGrade, and CurrentAdminPlan 


The "Salary Steps" list has columns [Salary Step], [Grade], [Admin Plan].  Based on these 3 columns, I can pull [Hourly Rate Current], [Hourly Range From], [Hourly Range To].


Does that help?

Userlevel 5
Badge +14

would I be correct in saying that the Salary Steps list looks something like this mockup, where it's populated with every combination of [Salary Step], [Grade], and [Admin Plan], and the corresponding Hourly info per row? 



 


 


 

Yes, pretty much.  Sorry, I should have explained better.  Each Salary Plan will have multiple Grades.  And each Grade will have multiple Steps.  As you demonstrated, each Salary plan/grade/step will have Rate, Range From, Range To.

Here is example data:



 

Userlevel 5
Badge +14

One more question. Are the controls that are giving you the filter values (for Admin Plan / Grade / Step) Lookup Controls or are they just Choice Controls? 

[Admin Plan] and [Grade] are stored in variables (which were previously picked up by other Lookups) and [Step] is a form control input by the user, which is also stored into a variable.  

Userlevel 5
Badge +14

but all three controls are on the form in question yes? 

Admin Plan / Grade as Lookup Controls and the last one (Step) as what? A Choice, or a Single Line Text? 

Yes, all 3 are on the form.  All 3 are single line text. 

Userlevel 5
Badge +14

I originally replied to this thread because I saw "Responsive Form" and mistakingly was thinking On Prem. Now that I've spent a day messing around with a free O365 Dev deploy and Temp Nintex Forms install, I have come to the conclusion that this might be impossible... Not only is it incredibly restrictive but it also just doesn't have many of the features that exist on every other version of the form system. 


 


My original idea was to approach this like one might solve it for the On Premises Responsive Form, but that doesn't work because you can no longer use javascript outright in the Rules or Variable formula builder.


 


My second idea was to use the two known values you had populating from the people picker to narrow down a lookup to the correct choices. That doesn't work though because you cannot use the Rule creator to actually set the value of a lookup control!


 


My third idea was to do this with variables because you thankfully *can* set them fairly easily, but the lookup runtime function doesn't work like the rest of Nintex lookup runtime functions, in that it doesn't allow for multiple values to be returned! Very impressive! 


 


The Fourth Idea I had was to create a Web Request Control and simply use the REST api url to return back some json or xml that could be used for a drop down control. Since you can use multiple filters in the url, and it worked in the browser, I felt like it would be an easy fix. However! Sharepoint requires authentication to handle those calls, and the Web Request has no way of allowing you to do that, so while it might work in the browser as an already authenticated user, it doesn't work from the Form Control (because it calls things anonymously). Additionally I discovered that, for whatever reason, if you try to return values that start with or are just numeric data, it just... like, won't... I don't know what that's about but it seems like a pretty big issue!


 


The Fifth idea I had was that because you had a List that I know contains an Employee name and at least two of the filter fields, you could use a workflow to just get the only valid Step values from the Hourly Rate list. This is actually very easy to do, but I immediately ran into problems parsing this in the form. Because you're limited to using the built in Nintex functions, there is no real way to *use* that collection of data in any meaningful way in real time!


 


So! Automating this on the form might just be out of the question for the time being. This is an absolutely TRIVIAL thing to do in Classic Forms and On Prem Forms (both classic and responsive), but the design of this new Responsive Form experience is I think just too restrictive to allow for this type of, what I think of as, a pretty basic automation operation.


 


In conclusion, you can solve this problem in every way besides real-time (using a workflow after the fact it would be easy to get the correct values and populate them on the item). If you all determine that you still want do this, and are willing to use a Classic Form (for this one thing lol!), then it should be easy to accomplish.


 


Sorry I couldn't be more useful in this endeavor


 


If someone else can come up with something to do this in O365 Responsive Forms, I'd be interested!


 


 

Dear @MegaJerk  - My goodness.  What a fantastic reply.  Thank you so much for all of the thought and time you put into this.  You are amazing considering I am a complete stranger.  Using Nintex Responsive Forms is a requirement for me, so, for now, it does not look like there is anything I can do.  Perhaps Nintex will add this functionality soon.  Again, thank you so much!

Userlevel 5
Badge +14

Not a big deal at all, that's why I'm here! It really seemed like such a simple thing, and heck, it was good of an excuse as any to see if there was a way to go about getting a free trial of all this tech that I've been wanting to play with for a while now.


 


If you ever hear of something changing, let me know!


 


Good luck out there! 

Badge

Hi @LoveNintex ,


 


I'm not 100% sure this will work with your situation, but in similar situations like this I create a calculated field in SharePoint (like on Excel sheets with multiple vlookup targets) that concatenates all 3 fields into one field value, like Value1^Value2^Value3. I then create a variable in Nintex that concatenates the fields from the form. That should allow you to do what you want, if I am understanding correctly.


 


Thanks!


 


Jeff

Reply