In this blog post I'd like to provide a step-by-step guide to build a dynamic repeating section with Nintex Forms that can be used in order forms for example. Furthermore this guide will include a solution to check the total price of an order against a predefined budget. The focus of the whole solution will be on the Calculated Value control, the Lookup function, Runtime functions and Rules. I've created this guide by using Nintex Forms 2016 (On-Premises), but it should also work for Nintex Forms 2013 (On-Premises).
In the repeating section the user will be able to choose from a specified list of products, whereby the price of the product, the overall price based on the desired amount and a picture of the product will be shown. At the end of the form the user will see the overall price of all products and furthermore will get an information whether she or he is still in the defined budget or not.
At the end the form will look like this (Display mode):
Creation and configuration of the needed lists
First create a list to store the single products, this list should be called "Products" and it should look like this:
Name = Single line of text
Price = Currency
Picture = Multiples lines or text (Plain text)
Add another list called "Product orders", in this list the orders will be created and saved. The list looks like this:
Title = Single line of text
Total price of order = Currency
Order = Multiples lines or text (Plain text)
In a real world scenario there would probably be more columns to store additional information, like information regarding the customer/user.
General settings and configuration of budget definition
Start to edit the form in the "Product orders" list with Nintex Forms. Remove all controls that are not needed, change the background color to white (#FFFFFF), and change the grid cell height and weight to 20 pixels.
Add a "Label control" at the top of the form and insert "How much money do you want to spend?". Furthermore, add a "Single Line Textbox" control next to it. Name it "Budget," change the default value to "0.00," and set the data type to "Currency."
Configuration of the repeating section
This is how the repeating section should look like at the end:
First add a "Repeating section" control. Name it "Order," connect it to the "Order" column in the list, and change the "Text for add row icon" to "Add additional product."
Add a "Label control" and insert "Position." Furthermore, add a "Calculated Value" control below. Name it "Position." In the "Formula" section, add the following runtime function: currentRowNumber().
Add a "Label control" and insert "Product." Furthermore, add a "List Lookup" control below. Name it "Product" and configure it in the way that it displays all items that are stored in the "Products" list.
Add a "Label control" and insert "Amount." Add a "Single Line Textbox" control below. Name it "Amount," change the default value to "0," and set the data type to "Integer."
Add a "Label control" and insert "Product Price." Furthermore, add a "Calculated Value" control below. Name it "ProductPrice," and in the "Formula" section, add the following runtime function lookup("Products," "ID," Product,"Price"). This function will display the price (List "Products" --> Column "Price") of the chosen product in the "Product" control. Set the "Save as data type" to "Currency" and the "Value prefix" to "$".
If you want some more information regarding the Lookup function, take a look at this blog post by Eric Rhodes - TechRhodes | Blogging about SharePoint, Nintex and life in general
Add a "Label control" and insert "Total Price". Furthermore add a "Calculated Value" control below. Name it "PriceTotal", in the "Formula" section add the following formula ProductPrice*Amount. Set the "Save as data type" to "Currency" and the "Value prefix" to "$".
Add a "Calculated Value" control and name it "Picture". In the "Formula" section add the following runtime function lookup("Products","ID",Product,"Picture"). This function will display the picture (List "Products" --> Column "Picture") of the chosen product in the "Product" control.
At this point many thanks to Steffen Hennig for the picture solution! You can find it here: Populate a Calculated Value Field with HTML/Picture
Configuration of budget check
Add a "Label control" and insert "Total price of your order". Furthermore add a "Calculated Value" control right next to it. Name it "OverallPrice", in the "Formula" section add the runtime function sum(PriceTotal). The "sum" runtime function returns the result of all the values in a set being added together. Set the "Save as data type" to "Currency" and the "Value prefix" to "$".
Add another "Calculated Value" control right next to the "OverallPrice" control, name it "BudgetCheck", in the "Formula" section add the following runtime function If(OverallPrice>Budget,"You spend too much", "You do not spend too much").
If the defined overall price of the order in the "OverallPrice" control is greater then the budget in the "Budget" control, the calculated value will be "You spend too much", if this is not the case it will be "You do not spend too much".
To highlight the result add two rules on the "OverallPrice" control and configure them like that:
After a little bit of formatting the whole form should look like this at the end:
Of course there are many more things that could or must be done to get a perfect order form, like adding validation rules (i.e. if the overall price is greater then the budget) but I hope this gives you a good starting point and has provided you some tips on how to implement your requirements regarding such a form. Maybe I'll add some more features in the future ;-)
If you have any questions, found an error or have a better way to accomplish this, feel free to contact me! :-)
Nintex lookup control lookup function calculated value repeating section runtime functions order rules forms
