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.
