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"))}

Note: if your list is not on the root (home) page, you need to provide a path to it. For example, if you want to count all items in the ProductA view in the Licences list which is on the Sales subsite, your formula would be {Count(Getview("/Sales/Lists/Licenses/"; "ProductA"))}

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)}

Note: functions are specific commands given to JungleMail. You can combine multiple functions into a formula.

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.

Note: additional parameters in GetView() and FindItems() functions work only with equal conditions and when they are combined by operators. Supported additional unlimited parameters also work with additional date conditions (time is not included, only dates). You can learn more about formulas and functions in JungleDocs tutorials. To execute formulas in JungleMail you should surround them with {} in your email body.

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.

Important: formulas containing GetView or FindItems functions will only work if SharePoint list or Subscriber list are selected as recipient sources in the campaign builder.

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us