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 )}
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 there.
Using Excel's Built In Formulas
Using the built in formula's in Excel are not officially supported. These formula's might work for basic functions like count() or sum() but we recommend using the platform formulas instead as these are fully supported.
Outputting Multiple Choice Fields
When you allow multiple choices to be selected on a Choices field in your form, the answers chosen by the user are returned in a pipe delimited format.
For example, lets say you have a Choices field named mychoices and the user selected 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 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, but we wanted to make it easier to design your templates without having to constantly test the generated output to see if 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 is using comments to hold formula syntax:
The output of the above template looks like so:
