Using formulas to configure Dynamic Content

With JungleMail, it is easy to create an email template which has Dynamic Content, i.e., content that is automatically imported from SharePoint lists. To create such a template, you need to use formulas similar to the ones you see in Excel. Dynamic Content functionality could be useful for your monthly email report, for example. Furthermore, you can even make each of your sent emails unique for each recipient. In this tutorial, you will learn how to use formulas to configure Dynamic Content.

Let's take a look at two examples which will illustrate how using formulas can help you get more out of JungleMail.

Example A

To calculate sold products we can use the Count and GetView functions. Format example: {Count(GetView("ListName"; "ViewName"))}

We can show this value in percentages as well. All you need to do in this case is multiply by 100 and divide by the total number of items in the list ( AllItems view). Your formula then, should look like this:

{Count(Getview("/Sales/Lists/Licenses/"; "ProductA")) *100/Count(GetView("/Sales/Lists/Licenses/"; "AllItems"))}

If you want more clarity and round numbers, you can round up your results to 2 digits. For this we add the Round(number; digits) function to the formula:

{Round(Count(Getview("/Sales/Lists/Licenses/"; "ProductA"))*100/Count(GetView("/Sales/Lists/Licenses/"; "AllItems")); 2)}

Now, to add the percentage symbol at the end, modify the formula like this:

{Round(Count(Getview("/Sales/Lists/Licenses/"; "ProductA"))*100/Count(GetView("/Sales/Lists/Licenses/"; "AllItems")); 2) & "%"}

The content of your email will look like this when you are editing it:

Click on Preview to check/approve your results.

Once configured, you can make this email job recurrent. An easy and precise way to monitor sales performance every week!

Example B

When you use the functions with additional parameters to filter the results, you can send calculated information to the right recipients at the same time as composing the email.

You can, for example, send details on issued invoices to all selected recipients. Every recipient then will only get the information on the company they are working with at this time.

In the Recipients list there is a lookup column Company and in the Invoices library, you'll find a lookup column JDCustomer. Both columns take information from the Companies list.

Using the FindItems("ListName"; "ColumnName"; "ColumnValue") function we can take items only related to the particular column value (ColumnValue) which is in the column ColumnName in the list ListName.

JungleMail can mail merge columns inside function or formulas. So, in this example, we insert [Company], the mail merge placeholder as a column value, using this function:

FindItems("/JDDemo/Invoices"; "JDCustomer"; [Company])

But we want to calculate the number of invoices issued to a particular company, too. A complete formula will then look like this: {count(FindItems("/JDDemo/Invoices"; "JDCustomer"; [Company]))}

Next, we want to show the sum of all invoices issued to a particular company. For this, we use the function {Sum(arg1, "ColumnName")}. For the arg1, we will use the GetView() function, and for the ColumnName we use the column Subtotal Amount in the Invoices list (JDSubtotalAmount). We recommend using internal column names for best results. The formula would then look like this:

Sum {[Company]} Invoices: {sum(GetView("/JDDemo/Invoices"; "AllItems"; "JDCustomer"; [Company]), "JDSubtotalAmount")}

To list the subtotal amounts of every invoice issued, you should, instead of sum(), use the JoinValues(arg1; "ColumnName"; "free text to separate values") function:

{JoinValues(GetView("/JDDemo/Invoices"; "AllItems"; "JDCustomer"; [Company]); "JDSubtotalAmount"; "; ")}.

The image below shows what the email content would look like:

In the Preview tab, you can check to see if you are satisfied with the result.