I’ve written several blog posts on handling data relationships in SharePoint in Nintex. In particular, the one-to-many or parent-child data relationships are found in many business solutions. Beyond the lookup column type, there really is not support for this kind of data relationship in SharePoint.
I’ve written several blog posts on handling data relationships in SharePoint in Nintex. In particular, the one-to-many or parent-child data relationships are found in many business solutions. Beyond the lookup column type, there really is not support for this kind of data relationship in SharePoint. Nintex forms have the repeating section control, but it is limited by generating XML rather than having a data relationship with another list. Other solutions I’ve used for clients and written about usually involve code. One solution that is better than any of these is the TekDog Productivity Controls (TPC).
The TekDog controls allow you to have data relationships between SharePoint lists, and controls to support those relationships, out of the box. No programming is required. The data relationships are maintained automatically by the TekDog controls. The controls include a grid-like subform control, a button for a popup child form, and an aggregate function control that will display math results based on the child list data. If you are using Nintex for SharePoint on-premises, the TekDog Productivity Controls are hands-down the best solution for presenting a one-to-many SharePoint lists data relationship to your users.
The Subform Control
A subform is a form that is included inside another form. The subform appears as a data grid or data table that displays child list items in rows.
The TekDog Productivity Controls subform shows a recipe list item, chili, with ingredients list items from a child list
The huge advantage here compared to any other solution is that, without writing code, you can let your users add, edit, and delete child list data that are saved in an actual SharePoint list and a data relationship to the parent list item is automatically maintained.
The recipe subform with an ingredient in inline edit mode
History in MS Access
The subform concept comes from Microsoft Access, where you could easily design a grid in your form, a form within a form, that connects a one-to-many data relationship between two Access tables.
The main form shows data from the "one" side of the relationship and the subform shows data from the "many" side of the relationship. Image from Microsoft Support, “Create a form that contains a subform (a one-to-many form)”.
Configuring the TekDog Subform
The TekDog subform is easily configured in the Nintex form designer as a grid that shows related child items in a different SharePoint list. For instance, the examples pictured are of two SharePoint lists: a Recipe list and an Ingredients list.
Internally, the subform control creates columns in both lists to hold a GUID value that is used to connect the child list items to the related parent list item.
The TekDog subform control in the Nintex form designer
The Control Settings for the TekDog subform in the Nintex form designer
You can specify the child list and view in the subform’s control settings and the data relationship with the parent list item is automatically maintained using GUID columns created by the TekDog controls. The GUID information is hidden from the user, but as a designer you can create your own SharePoint views to see the GUID relationships or use them for reporting. Unlike the Nintex Repeating Section control, the data relationship is maintained with actual SharePoint list columns and identifiers so you can build reports and analysis of the related data as you would expect from a database application.
Recipe list has a custom view that shows the GUID that identifies the chili recipe
The Ingredients list also has a custom view that shows the foreign key to the Recipe list as well as additional GUIDs for each ingredient list item
Multiple Subforms
In addition, you can have multiple subforms in the same Nintex form that connect to different child lists. For instance, if you have a Projects list you might have multiple child lists related to each project: Materials, Facilities, and Employees are each lists that represent entities related to the project. You need only to set up the lists in SharePoint and configure each subform to with its child list name, and the TekDog control will handle the plumbing involved in maintaining the data relationships. Each new Project list item will be assigned a hidden GUID, and each time a Materials, Facilities, or Employees list item is created using the subform or other TekDog controls, the Project GUID will be automatically entered as a foreign key from the child list.
The Add New Child Button Control
Additional TekDog controls in the Nintex form designer toolbox
An alternative to using the inline editing mode of the subform is to use the TekDog Add New Child Button Control.
The Add New Child Button control and the subform command icons removed
The Add New Child Button presents a popup to the user where the child list item can be added. An advantage to using the popup is that it is a form that can be designed in Nintex instead just using the inline subform grid. The Add New Child Button automatically provides the necessary GUIDs in the URL to the popup window so that the data relationship can be maintained.
The popup for adding a new ingredient to the recipe subform
The subform after “Onions” is added with the Add New Child Button control
The Aggregate Function Control
Another TekDog control that is useful with data relationships is the Aggregate Function Control. This control displays a mathematic result based on the child list items, such as a simple count, or a sum. Other aggregate options include average, minimum, maximum, standard deviation, and variance.
The Aggregate Function Control displays the ingredient count
The configuration of the Aggregate Function Control
In conclusion, the TekDog Productivity Controls are a no code solution that allows you to design data relationships into your Nintex forms for SharePoint on-premises to create real-world applications for you users.
.