cancel
Showing results for 
Search instead for 
Did you mean: 

Lookup Runtime in Nintex Forms for Office 365

Nintex Employee
Nintex Employee
15 24 12K

If I could fill the title with exclamation points, I would. The lookup() runtime function for Nintex Forms for Office 365 is now available. For those of you that have used Nintex Forms for SharePoint 2010 or 2013, you know how powerful this function is, and for those just new to Nintex Forms, let's take a look at what you will be taking advantage of soon!

In this example, I want to build an order form that will include a Lookup control pulling Item Names (Title Column) from an Inventory list. In the order form, I want to have read-only values for the item color and the item number. Let's start building!

Here is an overview of the Inventory List. Notice the column names and values:

Now that we have built an Inventory List, we need to create a list to contain the order form and open the Nintex Forms Designer. I've replaced the Title single line text box control and added a List Lookup control with the following settings:

Next, we are going to use the output of our List Lookup control ("Item"), to show the read-only values from our Inventory List. To do that, we need to use the Calculated Value control.

Add two Calculated Value controls (with labels), one for Item Number and one for Item Color.

First, we will configure the Item Number Calculated Value control using the lookup() runtime function. Double-click on the Calculated Value control to open the Control Settings. From there, click on the 'Formula' field to open the 'Insert Reference' pane on the right-hand side. Under 'Runtime Functions' you will see the lookup function.

The lookup() runtime function expects the following parameters:

lookup("list title", "column to filter on", value to filter on, "output column")

Great! So what does that mean in our case???

Remember, the values of: List Title, Column to Filter On, and Output Column are in regards to the source list, which in our example is "Inventory". So the "list title" is Inventory, and the column to filter on is "Title". The value to filter on is the output of the Named Control "Title" (which is our List Lookup control) and the output column is "Item Number".  When completed, the formula should look like this: lookup("Inventory", "Title", Item, "Item Number")

Here is the output when we select a value:

So why is this blank instead of showing an item number? That's because we are using a List Lookup control for the value to filter on, and List Lookup controls will add an item ID prefix to the value.

NOTE: To see this value, just add a Calculated Value control to a form and add the Lookup Named Control to the Formula.

Using the parseLookup() runtime function will strip the ID prefix and give us a real value to work with in our formula. So the new formula will appear as:

lookup("Inventory", "Title", parseLookup(Item), "Item Number")

Now that we have made that change, let's test the form again:

Cool!!! Now all that is left to do is add the Item Color formula and we have a finished form!

Not too bad and a ton of value add! Now it's time to start building more awesome forms in Nintex Forms for Office 365.

Tags (1)
24 Comments
lukegullifer
Nintex Newbie

Hi Sean,

This is a real life saver for us with the migration from on premise to nintex online it was a feature that we definatley needed and a timely release

Cheers,

Luke

Chris_Ben
Nintex Newbie

Very happy this has made its way to 365!

Warwick
Nintex Newbie

Great to see this feature released Sean Fiene​ - nice write up.

Let's hope now the tap is open the features and improvements for Office 365 keep flowing!!!

makesense
Nintex Newbie

Great, that's what I am needing!

brilong
Nintex Newbie

THANK YOU!!!!!!!!!!!!!!!!

patrickabel
Nintex Newbie

Thanks for sharing Sean Fiene​, this is awesome!

brentless
Nintex Newbie

Can this be done in a repeating section?

brentless
Nintex Newbie

Answered my own question by doing it, yes it work great!

rbachmann
Nintex Newbie

Great improvement for the product and fantastic Blog Post by Sean Fiene​!

Personally, I prefer working with the item's ID instead of the Title-Column. So I often extract the ID with this little regular expression here: Extract Lookup ID​.

Nintex Employee
Nintex Employee

Hi Raphael Bachmann​,

The parselookup runtime function can either remove the ID or the Value. Just use parseLookup([named control], [optional bool]).

True will only show the value whereas False will only show the ID.

Cheers,

Euan

shaunlub
Nintex Newbie

This is such a life-saver. This coupled with the Task Forms now being able to open in Edit Mode really bring Nintex 365 much closer to being full replacements for InfoPath. PDF output button in this month's update also useful.

rbachmann
Nintex Newbie

Hey Euan Gamble​,

How cool is that?! Thanks for letting me know.

Nintex Employee
Nintex Employee

Here is an example I was working on. This is a cascading lookup with examples of the output of a lookup control and the filtered output using the parseLookup.

parselookup.png

christopheraucq
Nintex Newbie

No more JavaScript needed. Yahoo !!!

wholland
Nintex Newbie

There's an issue with the lookup() function.

The list I'm looking up, "Sites", to has a Text column called "Site Number" and another text column named "Site Name".

I've placed a Text input box on my form and named the control "SiteNumber_Input". I then added a calculated value control to my form called "SiteName_Calculated".

For the Formula in the calculated value control, I have the following: lookup("Sites", "Site Number", SiteNumber_Input, "Site Name")

When I publish the form and type in a valid Site Number, such as 123456, no results are returned. The issue is that the lookup function is parsing the value of it's third parameter (value to filter on) as a number instead of a string (seen by looking at the HTTP Request payload that gets created). If, instead of 123456, I type TESTSITE , I actually get a result.

Since there is no "Number" control, perhaps it would be best to have an option in the control configuration to parse the input as a particular type, much like there is to parse the result.

marty
Nintex Newbie

Great stuff!

This is really easy to use - I've got employee leave balances showing on the leave request form, and the business is really happy with the outcome :-)

suniltamanekar
Nintex Newbie

Great article.

I tried this with Nintex Form 2010.  parseLookup behaves differently in Nintex 2010.  As guided by Euan Gamble I tried parseLookup as below:

- parseLookup(Item)            :   Result - ID,#Item name

- parseLookup(Item, True)   :   Result - ID,#Item name

- parseLookup(Item, False) :   Result - nothing displayed

I need only value and not id.  Will you please guide?

Sunil

greenawayr
Nintex Newbie

I can confirm that ‌ is correct here. You can only perform a lookup on a Number column. It looks like the fact there are no quotes around the filter value, the function treats it as a number. If you put quotes around the named control it takes it literally and tries to filter on the named control reference, rather than the value within the named control. That's my hypothesis anyway, sure someone better informed than me can figure it out.

‌ I'm not sure whether this comment was missed or not, but the issue still persists as far as I can tell.

greenawayr
Nintex Newbie

 

I found the solution to my own problem. Not sure it's intended and whether it's documented anywhere, but it works.

ivorybanks
Nintex Newbie

FINALLY.  This is an invaluable blog post! Thank you so much!

alice_oneill
Nintex Newbie

Thank you SO MUCH for explaining this so clearly

kbsitz
Nintex Newbie

At times my returned lookup value would be blank; an empty string -- using the lookup function I get a #Value! on my form -- how do I hide that ??

shaunlub
Nintex Newbie

Kathy - have you tried putting an IsNullorEmpty(ControlName) rule that will hide any blank answers?

kbsitz
Nintex Newbie

Yes I have but it did not work.

Thank you,

Kathy