show / hide menu

Aggregate Functions in SharePoint

Aggregate functions perform a calculation on a set of values and return a single value. Except for ‘COUNT’, aggregate functions ignore all null values. All these functions are deterministic; hence they return the same value any time whenever called by using a specific set of input values.

In ClaySysAppForms, aggregate functions are available when a FormDataSource control is configured with SharePoint List. It helps the user in the consolidation of data in the list.

1)Create a form to implement Aggregate Function as shown below.

Designer

Preview

2)Create a list with respect to the form created. [Below given is the sample list of the form illustrated above].

3)Add values into the list.

4)Click on ‘Create DataSource’ in the AppForms window.

  • Select the corresponding list from the ‘Available Sites’ and ‘Available Lists’.
  • Click on the ‘Select’ option and then click on ‘Add’ button in the’ DataSource’ window.
  • ‘SharePoint Commands’ window gets open up.
  • In this window, specify the criteria in ‘Query Criteria Mapping’ session that based on which the data should be generated in the Preview.
  • After that, click on the ‘Aggregate Column’ button in order to configure the aggregate functions with form data source.
  • List of aggregate function available in AppForms
    • Sum : – This function will retrieve the sum of all values in a selected column.
    • Min : – This function will retrieve the minimum of all values in a selected column.
    • Max : – This function will retrieve the maximum of all values in a selected column.
    • Average: – This function will retrieve the average of values in a selected column.
    • Count : – This function will retrieve the number of values in a selected column.
    • Value : – This function will retrieve the value of the last row of a selected column.

  • Select the column name in the list, the textbox name to which the value has to be viewed and the required aggregate function under the Data Source Column, Mapping value and Aggregate Function respectively, in the ‘Aggregate Column’ window.
  • Click on the ‘Save’ button.
  • Later click ‘Ok’ button in the ‘SharePoint Command ‘window.
  • After that, add the trigger and then click ‘Finish’ button.

5)On the ‘Preview Form’, click on the button, that is triggered and the result can be viewed in the textbox.