TABLE OF CONTENTS
- Using Excel's Built-In Formulas
- Outputting Multiple Choice Fields
- Using Word/Excel Comments to Preserve Template Formatting
The platform has the ability to evaluate formulas just like those you would use in your Forms. You can read all about what a formula is and how to create it at these links: Creating a Formula and Dynamic Output Using Form Formulae. 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}}, '!') )}
This 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 what you would do in the Form Designer when creating a formula there.
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 about 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.
Using Excel's Built-In Formulas
Using the built-in formulae in Excel is not officially supported. These formulae 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, let's 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 be 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 uses comments to hold formula syntax:
NOTE: More than a single comment linked to the same text is not supported.
The output of the above template will generate the following output:
