Lookup Runtime in Nintex Forms for Office 365


Badge +9

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:

186602_pastedImage_2.png

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:

186603_pastedImage_3.png

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.

186604_pastedImage_4.png

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:

186605_pastedImage_10.png

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:

186606_pastedImage_11.png

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

186608_pastedImage_13.png

 

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.


24 replies

Badge +2

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

Userlevel 6
Badge +12

Very happy this has made its way to 365!

Badge +9

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!!!

Badge +4

Great, that's what I am needing!

Badge +2

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

Badge +8

Thanks for sharing Sean Fiene​, this is awesome!

Userlevel 3
Badge +9

Can this be done in a repeating section?

Userlevel 3
Badge +9

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

Badge +7

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​.

Userlevel 5
Badge +13

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

Badge +11

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.

Badge +7

Hey Euan Gamble​,

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

Userlevel 5
Badge +13

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

Badge +9

No more JavaScript needed. Yahoo !!!

Badge +4

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.

Badge +2

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 :-)

Badge +6

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

Userlevel 6
Badge +13

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.

Userlevel 6
Badge +13

 

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

Badge +1

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

Badge +5

Thank you SO MUCH for explaining this so clearly

Badge +4

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 ??

Badge +11

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

Badge +4

Yes I have but it did not work.

Thank you,

Kathy 

Reply