Using Formulas in Templates

Using Formulas in Templates

The platform can evaluate formulas within your templates exactly like it does inside your Forms. This allows you to dynamically generate and manipulate output data. For Excel templates, you can even use formulas to conditionally show or hide rows.

Formula Syntax

To include a formula in your template, enclose your formula expression using either single or double curly braces combined with parentheses:

• {( your formula here )}
• {{( your formula here )}}

You can reference form field answers inside your formula by nesting the standard {{fieldname}} placeholder syntax.

Example:

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

Output: “Hello John!” (assuming the user entered “John” in the nameofperson field)

Excel’s Built-In Formulas While Excel’s native formulas (like SUM() or COUNT() may work for basic functions, they are not officially supported. We strongly recommend using FormsFly formulas to ensure full compatibility and support.

Formatting Multiple-Choice Outputs

When a user selects multiple options in a Choices field, the platform returns the answers as a single string separated by vertical pipes (|).

Example Raw Output: Option 1|Option 2|Option 5

To make this data reader-friendly (e.g., displaying options on new lines), use the SUBSTITUTE() formula function to replace the pipe characters.

How to split choices into new lines:

Use the following formula to replace the pipe (|) with a new line character (\n):

{(SUBSTITUTE({{mychoices}}, '|', '\n'))}

Preserving Template Formatting with Comments

Long formulas and placeholder syntax can cause text to wrap awkwardly, distorting your document’s layout during the design phase.

To solve this, you can “annotate” your formulas by placing them inside Word or Excel Comments. Because comments hover over the document, they do not affect your visual layout.

How It Works:

1. Highlight the text block in your template where the data should appear.
2. Insert a standard Word or Excel comment on that text.
3. Paste your placeholder or formula syntax inside the comment.
4. During generation, the engine scans for these comments, applies the formula, and replaces the highlighted template text with the actual result.