Add sums to collections in Merge Templates

One of the most common custom fields added to forms in ProjectTeam.com is the collection field type. The benefit of a collection is that it can hold multiple rows of similar data in a table-like view. When a column holds number data, there are many cases where it becomes important to add a sum or total to the bottom of the column. This page shows a step-by-step example of adding sums to the bottom of collections in your merge templates. 

Step-by-Step Example

Note: This page assumes you have a basic understanding of creating merge templates. If not, first visit the Merge Templates page.

Let's say you have added a collection to your form in ProjectTeam.com that looks something like this:

Now you're ready to create a merge template that shows this data. Using the merge tags provided by the system during the merge setup wizard, copy and paste the tags to your table in MS Word. Remember to add the necessary tags to the first and last columns to signify repeating data. 

If you've added the correct tags and uploaded your merge template successfully, then when you go back to your form and perform your merge you should get a table on your resulting merge document that matches what you started within ProjectTeam.com. 

But now, let's say you'd like to add a "Unit Total" to the right of the table and then you'd like to sum the total number of units and sum the total of all "Unit Totals". So in the end, you'd like you're resulting merge document to look like this:

This is where you need to start using Smarty Tags to do some simple calculations. 

Let's start with the Units column first.

Back on your MS Word document, add a new row to the bottom of your table. 

Next, you need to define a new variable outside of your table. Let's create a new variable called "unitsum" and initialize it to zero. Add the assignment above your table in MS Word as shown below:

• Copy: {assign var=unitsum value=0}

Next, inside your "Units" column, add a formula that sets your variable to the sum of each row. 

• Copy: {assign var=unitsum value=$unitsum|floatval+$field.projectteam_1002|floatval}

• Note: Edit the field tag to match your field (NOT $field.projectteam_1002)

If you were to run this now and see all the calculations take place, it would look something like this:

Start: unitsum=0
Row 1: unitsum=0+2=2, therefore unitsum=2
Row 2: unitsum=2+3=5, therefore unitsum=5
Stop: no more rows

The only problem is that we haven't put the final output anywhere on our table. So all you have to do is add the tag somewhere on your document. For this example, we'll add to the second column in the last row.

• Copy: {$unitsum}

And that's it! After uploading your new merge template, if you go back to your original form and run the merge, you should get a result that looks something like this:

Now that you know how to add a sum to the bottom of the merge template collection, let's take it a step further. The following steps will help you make the table match the third image above. 

First, add the currency format Smarty Tag to your "Price Per Unit" column to make it show as USD. 

• Copy: |currency_format:”$”

• Note: Don't forget the pipe symbol "|". Paste inside the field brackets.

If you were to run your merge template you'd see the last column shows "$10.00" and "$20.00" in rows 1 and 2 respectively.

Next, we need to add a new column. This column will be titled "Unit Total". We should also take the {/tablerow} tag and put it in the fourth column.

Using Smarty Tags, add a new calculation that takes Units x Price Per Unit. And since we also want this to show in the USD currency format, we need to create a new variable and then apply the currency format to the variable. The new variable will be called "unittotal".

• Copy: {assign var=unittotal value=$field.projectteam_1002*$field.projectteam_1003}

• Copy: {$unittotal|currency_format:”$”}

• Note: Edit the field tags to match your fields (NOT $field.projectteam_1002 and $field.projectteam_1003)

At this point, if you were to upload the merge template, then go back to your form and run the merge, you should get a result that looks like this:

Finally, using what we already know about adding sums, let's create a new variable outside of the table called "unittotalsum", add the formula to the fourth column to get the needed sum, then put the variable tag in the last cell.

• Copy: {assign var=unittotalsum value=0}

• Copy: {assign var=unittotalsum value=$unittotalsum+$unittotal}

• Copy: {$unittotalsum|currency_format:”$”}

If you save your template, go back to your document and run your merge, you should get a final table that shows everything you need: