The platform has the ability to evaluate formulae just like those you would use in your Forms.

You can read all about what a formula is and how to create them in the Form Screens documentation.

Being able to use a formula on your data output unlocks powerful options to dynamically generate output inside your templates.

For Excel templates, you can even show and hide rows based on a formula’s result.

To use a formula in your template, use the special placeholder syntax to enclose your formula expression:

{( put your formula here )} or {{( put your formula here )}}

Enclosing the formula within Single {( )} or Double {{( )}} curly braces are both supported.

For example:

{( CONCAT('Hello ', {{nameofperson}}, '!') )}  

Which produces: “Hello John!” (assuming the nameofperson answer was John)

Note that you can include the answers for form fields into the formula by using your normal {{ }} placeholder syntax.
This is exactly as you would do in the Form Designer when creating a formula.

Using Excel’s Built-In Formulas

The built-in formulas in Excel are not officially supported. These formulas might work for basic functions like count() or sum(), but we recommend using the Appenate formulas instead, as these are fully supported.

Outputting Multiple Choice Fields

When you allow multiple choices to be selected in a Choices field in your form, the answers chosen by the user are returned in a pipe-delimited format.
For example, let’s say you have a Choices field named mychoices, and the user selects options 1, 2, and 5.
The answer value would be: Option 1|Option 2|Option 5

This doesn’t look great when outputting in a template file, you would probably rather have the options output on new lines or comma-separated.

The way to do this is by using a formula function called substitute() – this function can be used to replace text with new text in a field’s value.
So the placeholder formula to replace the pipe characters with a new line would be:

{(SUBSTITUTE({{mychoices}}, ‘|’, ‘\n’))}

This has the desired effect of replacing the pipe separator with a new line character.

Using Word/Excel Comments to Preserve Template Formatting

Often, when building a template, your formula or placeholder syntax is too long to fit in the designated area of the template.
The result is ugly word wrapping,g and it becomes harder to see how the template layout looks because of the distortion of the placeholder text.

Now, of course, the actual generated result still comes out just fine. Still, we wanted to make it easier to design your templates without having to constantly test the generated output to see if the output formatting remained correct.

What we need is a way to “annotate” the placeholder/formula syntax onto specified text in the template.
Comments were the perfect answer, since these hover above the Word/Excel content without changing the actual layout.

Our template generation engine actively scans for comments that contain placeholder or formula syntax, and replaces the text linked to a comment with the placeholder/formula extracted from the comment itself.

Below is an example of a Word template that uses comments to hold formula syntax:

The output of the above template looks like so: