How to configure the Data Grouping feature in a Register and running totals

We will be using the Gift and Inducements Register as an example to explain how to set up a register for the Data Grouping and running totals.

The first step is to add a new column to use for calculating the running total. For this example, it is called ‘Net Allowance:

• Add a new column, e.g., Net Value
  • Set the Default value here to match the allocated gift allowance
  • Click the UPDATE button.

• Move the column by clicking on the stack icon and dragging it to its desired location.

Configuring the Register columns:

• Go to Settings and click on Registers
• Select the target register e.g., Gifts and Inducements Register.

The default grouping for some columns needs to be set as below – this is to reflect how the data is to be summarised or grouped and displayed in the actual register. Please note that these configurations will affect everyone’s view of this register.

For this register, data will be grouped by the date of the gift, whether it’s been given or received, a count of the number of entries against the estimated value of the gift, a sum of the net value of the gifts (the running total), and who the gift has been assigned to.

To configure the register:

• Click on the column, (e.g., Date of gift) to open the column settings
• Change the Default Grouping to ‘Group by the value’
• Click the UPDATE button to update the settings.

Repeat the steps above for each required column, and remember to change the Default grouping to the required function:

• Group by the value: this will collect the same values together.
• Hide Column: no grouping is applied.
• Show unique values only: this will only show unique values.
• Count of set values: this will show a count of the values for this column.
• Sum of numeric values: this will add up the numeric values in the selected column.

Once all of the columns have been grouped – click SAVE at the bottom of the settings page.

To continue:

• Go to the Status column
  • Add a new value ‘Allowance’ by clicking on the + icon
  
  

• • Mark the column as required (as shown above)
  • Click the UPDATE button.

Configure the rules and ensure that they’re in the right order

New rules need to be created to tell the register to calculate the running total and deduct it from the allowance. It is important that the rules are shown in the order below so the register actions these rules first.

To add a new rule, click on the grey ‘+’ button at the bottom right of the page:

Add to Allowance rule:

• Click on the RULES tab
• Click the ‘+’ to add a rule
• Configure the ‘Add to allowance’ rule as shown in the ‘Details’  screenshot below
  • Tic the Active box
  • Click the UPDATE button
• Click SAVE on the rules page.

• Ensure this rule is at the top of the rules as this will tell the register to perform this function first.

Remove allowance rule:

The ‘Remove allowance’ rule is configured as below and it has two conditions, this rule is used to force the register to calculate the running total on the items that are not set to being the allowance.

• Click the '+’ and add the rule as shown in the ‘Details’ screenshot below.
  • Condition 1
    • Select ‘Any condition matched’
  • Create condition 2
    • Click on the stack icon (this will enable you to add a second conditions to the rule)

• • • The condition will look like the screenshot below. Click on the small arrow to open the selection

• • • Click on the second stack that seems to be blank

Create the condition 2 as shown below:

After the condition have been created, an action needs to be configured – so far this rule is telling the register to find entries that are not the allowance, next the rule needs to know what to do with these entries:

• Click the '+' button to add an action
• Complete as below (see screenshot)
• Click on the Stack icon:

• Click on the stack icon under the one shown above and set up the following parameters and click the next arrow for the final part of the formula:

• Configure the final part of the formula as below:

• Select the Active box
• Click the ADD button
• Move the ‘Remove Allowance’ by dragging the stack to be below the ‘Add to Allowance’ rule, then click on save:

The final rule to set up is to exclude any items that have been deleted or rejected from the calculation:

• Add a new rule, and configure it as follows:

• Click on the stack to add the next part of the condition
• Click on the expand arrow

• Configure the condition as below

• Click on the next arrow and configure the condition as below

• Click on the '+' button to tell the rule what to do with the data

• Select the Active box
• Click the ADD button
• Move the ‘Denied’ rule by dragging the stack to be below the ‘Remove Allowance’ rule
• Click the SAVE button.

The rules need to be in the following order, then click save.

Review the configuration

Navigate to the register itself, for this example the Gifts and Inducements register.

• Add an ‘allowance’ entry for each person using the register

• Click the SAVE button
• Repeat for each user.

The Estimated value of the gift above is actually a count of the number of entries for that person entered on the register.

The date formula for grouping is currently restricted to one calendar year.

When entries are added to the register e.g.,: