Calculate summary of related items in SharePoint forms

Introduction

Associated Items column is a great help when you want to use your SharePoint as an application development platform. Simply put: it makes SharePoint relational. You can create related items, tasks or documents directly from the parent item, which can then be viewed, monitored or updated from any Display or Edit form of the parent item and any list view of the parent item's list. You can easily manage tasks related to your project, add repeating sections to your forms or upload and assign documents to your RFPs.

How to link lists

You can manage your related items in two ways:

1. Simply create a lookup column in the child list, pointing to the parent list. It will filled out automatically and used to manage the relationship.
2. Use one of the provided Associated content types. By using a set of hidden relationship columns, the connection is created and maintained in a completely transparent fashion. The advantages of this method include the ability to create multiple different Associated Items columns in the same list (think separate corrective and preventive actions) and also the ability to create related items for a parent item that is still in the middle of its creation process (such as add line items to an invoice that hasn't been saved yet).

Associated items can be presented in a variety of ways, including read-only and editable grids, repeating subforms and even as charts. You can also use validation rules on the parent form to ensure child items are entered before the parent can be saved.

Summary columns

Associated Items Summary column works together with the Associated Items column to calculated summaries based on the related items:

• Count the number on related items (e.g. how many lines are in the current invoice or how many tasks belong to the current project)
• Calculate sum, average or even standard deviation of any number/currency column in the related items (such as, invoice total or average support ticket handling time per client).
• Concatenate text values into one, separated by commas.

The value is calculated automatically, updated each time related items are added, modified or deleted and is stored within the parent item itself.

Example of settings:

You can even calculated summary of only specific child items using filters, such as the number of open tasks (count tasks where Status does not equal Completed). And using Forms validation rules, you can use these values in input validations, such as prevent a project from being closed when there are still open tasks.

Summaries are calculated both on the form in real time and behind the scenes. So if you add, update or delete any child items outside of the parent form, the update will still happen and the parent form will be modified behind the scenes in (almost) real time.

Summary

Relationships are everywhere and it just makes sense that your SharePoint forms know how to create and manage relationships. Automatic calculations of summaries and total is a great feature, which has uncountable real world applications.