Create Ticket My Tickets Post Discussion
Welcome
Login  Sign up

Dynamic Output Using Form Formulae

TABLE OF CONTENTS

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 it 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}}, '!') )}  

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 as you would do in the Form Designer when creating a formula there.

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.

Hyperlinks can be included on output templates using the LINK syntax on the output combined with the FILEURL function in a form field. More information about this can be found in our Attachment Field Type article.

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:


Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.