It’s common to want to display items in a one-to-many, or parent/child relationship in a Nintex form. In this post, I’ll detail a simple way to achieve this using a Nintex Calculated Value control.
It’s common to want to display items in a one-to-many, or parent/child relationship in a Nintex form. In this post, I’ll detail a simple way to achieve this using a Nintex Calculated Value control.
The completed Purchase Order form with line items in a table format.
I’ll use a Purchase Order with line items that detail the individual parts of the purchase as an example for this post, but there are many similar examples that could be used.
Other than the lookup field, SharePoint does not natively provide any mechanism for working with a one-to-many data relationship. The lookup field is very useful for providing a separate, reusable, and manageable drop-down choice list. However, it would not be helpful for listing the details of a Purchase Order.
Nintex has the Repeating Section control to allow a user to enter many detail items associated with one parent list item. However, the Repeating Section control obscures the detail data in an XML format that needs to be parsed with a complicated workflow to do anything with it.
So how can we provide a detail list of lines items found in a typical Purchase Order, and have those details in a separate child list connected to the parent Purchase Orders list with a one-to-many data relationship like you would find in a traditional database application?
The PurchaseOrders list in SharePoint.
The PurchaseOrderLineItems list in SharePoint.
There are a few ways I’ve either discovered or invented over the years, including using Nintex Calculated Values controls as I’m going to show in this post, right up through using the TekDog Productivity Controls Subform and Add New controls which were designed specifically for solving this problem.
So how can we show child list items with a Calculated Value control in a Nintex form? At the very least, we can simply display the calculated value output of a lookup() function, which will display array string syntax.
A simple formula using only the lookup() runtime function.
The result of the lookup() function is an array.
The child items are technically visible, and this might be useful for a small reference to another list, but how do we show multiple columns and row as we would expect in a Purchase Order line items table?
My method here is to use multiple Calculated Value controls, one for each column, and then use a formula to look up the child list item values and format them into rows.
Labels, a panel, and Calculated Value controls in the Nintex Forms designer
The formula for each Calculated Value control will use the lookup() runtime function to retrieve the values from the PurchaseOrderLineItems list, and a replace() runtime function to format the results. Note that the item property ID of the parent list item is used to find the associated child list items by filtering on the matching IDs in the PurchaseOrderID column.
An easy improvement to the given result of the lookup() runtime function is to replace the comma with an HTML break. Additional adjustments might need to be made if you need to force the row height to an absolutely value, for instance if you know some multiline text will be in the results.
I’ve improved on that a step further with formula to create an HTML table to hold the line item values.
A formula that includes an HTML table to hold the results
Here is the text of the formula. The [ID] will need to be replaced with the list item ID from the Item Properties tab, and the list name, filter column name, and output column name (the first, second, and fourth parameters of the lookup() function) will need to be replaced with the names according to your lists and the output column you want to show in the control:
"<table><tr><td>" + replace(lookup("PurchaseOrderLineItems","PurchaseOrderID",[ID],"Title",true),",","</td></tr><tr><td>") + "</td></tr></table>"
I put the Calculated Value controls in a Panel control and put simple Label control above each Calculated Value. Also note that I created the alternating grey and white background using an image in a panel. The pixel height of each row in this case happens to be 19px so I created the image with one band of white, 19px high, a second band in light grey, 19px high, and then let that repeat vertically as the panel background image. The Site Assets folder in SharePoint is a good place to upload an image like this.
A background image for the panel, created in MS Paint.
Be sure to set the “Recalculate formula on view mode” setting to Yes so that the list items show in display mode for the form.
Notice this can be done in Nintex responsive forms as well as classic pixel-perfect forms. The same formulas can also be used in Calculated Value controls in Nintex for SharePoint on-premises.
This solution does use a formula and not code so it can be considered a low-code or no-code solution. However, you can also use some JavaScript expressions in the formula, for instance, we can use a join() function on the array instead of comma replacement.
Pro tip: you can replace the identifier column in the lookup filter parameter to a specific hardcoded value while testing so that you can preview the results without publishing.
Unlike some of the other solutions for working with one-to-many data relationships, this one does not immediately include any way to automate the mapping of the relational identifiers. This solution simply displays relationally mapped parent/child data in a table from value that have already been recorded by another means, possibly by using a lookup control on a child form so the user can relate the item to the correct purchase order, or by using a popup child form with the identifier passed in the URL, or by using the TekDog Productivity Controls Subform or Add New button.