Using Roll Up Fields to Calculate Totals

At the last Skill Station we learned how to track line items to record expenses that are being submitted for approval. We also configured a Line Item grid to list and calculate the total. This removed the need to ask a user to enter the total amount being requested, so we can remove that field from the form. However, we need the total amount to show in the request form that the manager receives. We also need it to appear in the task details. Let’s learn how to do that.

Start by opening the latest version of your Expense Approvals stream. Unlock it to make changes.

In your Components Catalog, open the Request data component. In the Edit Data Component  go to the Fields tab. In the Amount Field popup, the amount field is currently of the type “Currency.” Underneath that field you will see the text “Unlock Type Field.” Click on that, so we can change its type. In the Type pull-down menu, you will see many options. We are interested in the “Rollup” option. Select that.

Rollup fields are a special type of field. In Pulpstream they can be used to store as line items the sum, min, max, count, or average of information that is present in a child document list field.

Here are some of the rollup functions:

For our purposes today we want to sum the amounts present in the line items.

In the pull-down menus below the Rollup Type one, select Line items for the Document. For the Document menu, select Line Items. The Function will be sum. The Field will be Amount.

There are also fields for Rollup Criteria. We will learn about those soon. First, save the changes to this form. Now we have an amount field that stores the total amount of the expenses that are being requested for approval. However, what if the approver wants to separate each expense by category? For example, total requested for travel, means, and boarding. What if in the future the business needs a report run about all the expense requests to see what was requested in travel, boarding, means.

To support that, let’s create a new Field for the Travel Amount. We will populate this field with the total of the the travel amount across all line items using a rollup field. Select that in the type pull down menu. In the Amount Field in the Requests Data Component configuration popup. In the Rollup Criteria, we will set the Field to “Category” and the Value to “Travel.”

Add 2 more fields. We will add one for “Meals Amount,” and another for “Boarding Amount.” The Type of information this fields will contain be “Rollup.” The Document will be “Line Items.” Function will be “Sum.” The Field will be “Amount.” For the Rollup Criteria, in the Field select “Category.” The Value will be equal to the expense category.

Do the same for the Boarding Amount. Make the value for this one Boarding. Save your changes.

Back on the Stream Definition Canvas, open the Request form to edit it. Delete the Amount field. Hover your mouse over the field to reveal the dotted lines and the x icon in the top right. Click on the x to delete it. Save your changes.

Next, open the Review Request step. On the General tab of this User Task, let’s add the ability for the description text to be dynamically generated. We will do this with merge field values, similar to the way we use them for the email templates. In the Instructions to assignee for completing this task field, add the merge field values for Total Amount Requested, Amount for Travel, and Amount for Boarding. Click on the magnifying glass icon to access the finder tool.

When you are done it should look like this:

That is it. Save your changes. Save the Process. Publish the stream.

It is time to submit a brand new request so we can see our changes.

After we submit our new request, go to Your Records. Go to the Submitted by you tab. Click on the Tasks tab of the record to see the changes. You will also see that the rollup fields are being calculated here under the Review Request text.

Task owners can also see the descriptions and rollups in their Tasks Menu.

And the approval form also shows the rolled up value.

Thus concludes our visit to this Skill Station. At the next and final Skill Station in this line, we will learn how to auto attach supporting documentation to the emails that are sent to the people approving expense requests.