Excel Document

Available on the Advanced and Enterprise tiers:

Essentials

Advanced

Enterprise

Contents

Excel Document

About

TrueContext allows you to design and build a custom document entirely in Excel. When a form submission is made, it will generate an Excel file with your desired formatting and send it to the people and places configured with data destinations.

By adding simple references to your form questions right in the Excel file, the answers to those questions can appear exactly where you need them to. All conditional formatting will continue to work within the Excel file, allowing you to continue to manipulate data in a familiar way.

Excel documents allow you to:

Easily highlight your own brand.
Build familiarity—Collect data in the Mobile App, but display the data in ways your stakeholders are familiar with.
Meet industry or government regulations for document formatting.

Sample Outputs

The following examples show a simple invoice and a vehicle inspection report that were generated using Excel output documents.

Tip:Need to generate custom documents that can't be edited by your customers or employees? TrueContext can write into “protected” sheets in Excel.

How it Works

When you set up the Excel Custom Template in TrueContext and fill it with DREL Data Reference Expression Language (DREL) is used to get form data and metadata and add it to a string, such as dates, usernames, or answers to questions in forms. references, you are creating the pattern for future documents. The DREL references will pull information from form submissions based on question Unique IDs, and put that information into a document based on your template.

Set up the Excel document with DREL references to any questions that produce text as an answer.
Map any images into the document using the TrueContext web portal.
Fill out a form in TrueContext and submit it.
TrueContext will create a document based on the template and your form submission.

Set up an Excel Template

Document Basics

Give the document a unique name, and optionally provide a description.
Define the document filename.

This name applies to all instances of the document, including downloaded documents and documents sent by Data Destinations.
Tip:A recognizable file name can help you quickly search for and identify files when:
- You download files from multiple form submissions so that you can work locally.
- You or your customers receive multiple messages generated by Email Destinations.
Choose the Document Time Zone Source. This controls which timezone any date/time answers will be shown in.

Mapping Text-Based Answers

Once you have your custom document set up in Excel, it's time to map your form questions to the appropriate locations in your sheet. TrueContext uses Data Reference Expression Language (DREL) to take answers from form submissions and insert them into the desired cells in the Excel file.

Note:Mapping with DREL only supports answers with text (freetext, numbers, etc.) as the answer. Read below on how to map images.

For more information on DREL, please read:

An example of a simple Invoice form built in Excel, and with DREL added is shown below:

Using DREL References in individual cells is the most common use, as shown above: any question that shows up in a form submission can be put into a cell with the correct DREL string. The most common DREL string in an Excel document is:
```
%a[unique ID]
```
This string will give the form submission's answer to that question. For example, if we plan to put the answer to Customer Name in cell B8, we select B8 in Excel and enter %a[Customer Name].
Cells may have multiple DREL strings. For example, while your form may have separate questions for City, State, and Zip/Postal Code, you can set up a single cell using a string like:
```
%a[City], %a[State] %a[ZipCode]
```

Manually Mapping Cells (for Cells with Validation or Dropdowns)

Some cells in Excel cannot contain DREL, such as dropdowns or cells containing validation. Even though the answer that will be mapped may pass that validation, the DREL expression will not, which will prevent you from saving your template.

If you wish to push data from your form submission to these cells, you can manually map them in our Manual Cells section of the document configuration:

Cell Reference: This uses standard Excel cell references, e.g. C2.
Worksheet/Tab Reference: To refer to different sheets inside a single file, use the standard format of SheetName!Cell Reference
- For example, to refer to Cell B2 on Sheet 2, it would be: Sheet2!B2
- Please note that if you have a leading or trailing space in your tab/worksheet name, TrueContext will create a duplicate tab when creating the document. Please ensure your worksheet name does not have leading or trailing spaces.
Cell Type: There are three options:
- Text: This is "General" formatting in Excel.
- Number
- Boolean

Mapping Images

Add reference images to your Excel document, using the photos from Image, Sketchpad, or Signature questions. Rather than setting this up in the Excel file itself, this is set up in the Configuration tab of the document in the web portal.

Image Aspect Ratio

The aspect ratio (height vs. width) will automatically be retained.

Image Size

The size the images are displayed in the Excel document is controlled by the cell range they are mapped into, as the entire image is fully contained within the range.

If the cell range is a single cell, the image will be contained in a single cell
If the cell range covers many cells, the image will span many cells

The document will always prioritize the maintenance of the aspect ratio. If your cell range does not match that aspect ratio, the image will be resized so that the longest edge of the image fits within your cell range.

Example:

In the example below, the mapped Cell range is A2:D14, but the image is quite tall and narrow, so the image is sized so that the long edge (height) fits within the cell range, and it does not take up the full width.

Cell Range Reference: These are the cells that will hold the image file.

Cell Anchor: These options do NOT impact how the images appear in the document when it is generated. They DO impact how the images are tied to cells if you manually manipulate the document after it is edited.

Note: See the Cell Anchor example images in the section below.

Options:
- Move and Resize
- Move, No Resize
- No Move, No Resize

Form Label:

Mapping a Question that Collects a Single Image: simply insert the question's unique ID.
Mapping Multiple Images from a single question -- i.e., when an image or sketchpad question is allowed to collect multiple images -- is done in much the same way as mapping repeatable section A Repeatable Section is a subform that contains a set of related questions. The data captured is “repeating”, because the field user can complete the same subform more than once, which creates multiple entries. data: using index numbers starting at [0]. These must each be manually mapped. As an example:
- This example has two images attached to the same question: TakePhotoWorksite. The first image would be TakePhotoWorksite[0], and the second would be TakePhotoWorksite[1]. If the question is allowed to collect up to 10 images, be sure to map all of them.

Cell Anchor Examples

Move:

A move is considered inserting a new column or row into the generated document, so that the column or row the image currently appears in shifts over/down.

If MOVE is enabled, and the document is manually manipulated as described above, the image will stay anchored to the cells it was originally mapped to, and stay aligned with other content.

If MOVE is not enabled, the image will stay in its previous visual location in the document, rather than shifting to make room for the inserted column.

Resize:

Stretching the cell, column, or row the image appears in so that it is larger or smaller.

If RESIZE is enabled, the image will shrink or stretch to fit the new range. This may distort the aspect ratio.

If RESIZE is not enabled, the image will stay the same size as before.

Mapping Repeatable Sections

Repeatable sections allow users to enter a variable number of entries for the same set of questions -- typically used for entering things like parts, labor, or material line items, such as those shown in this invoice form on an Android device:

When using data from repeatable sections in your Excel document, you must use the correct DREL. Unlike standard Unique IDs (using %a[unique ID]) which only have one answer to one question, repeatable sections have multiple answers to the same question. Using %e[Repeatable Section Unique ID A Unique ID refers to the specific identifier of a question, form page, form section, or Data Destination. Unique IDs are used as reference points when pulling data for conditional logic, Analytics projects, Data Destinations, and Documents.] will give you all the answers to that question. In most Excel documents, we only want one answer per cell.

This is done by DREL and indexing. The format for calling a single answer to a repeatable section question is:

%a[Unique ID][index number]

Repeatable section index numbers start at 0. Therefore, the first answer to a repeatable section question would be %a[Unique ID][0]. In our document example, we use this format to put single answers into the table:

Which results in a table that looks like this in a form submission:

For more information, read: DREL for Repeatable Sections

Please note that TrueContext cannot dynamically add rows to the Excel document based on the number of repeatable rows. To ensure you have enough rows to display all the data, please set up your document to have the maximum number of rows you believe will be used. Any rows not entered in the form submission will be left blank in the Excel document.

To add a Custom Excel Document to your account, in the TrueContext Web Portal The TrueContext Web Portal is a web application used to manage security settings, forms, FormSpaces, other users, Data Sources, and Data Destinations.:

Create and name the document as normal, selecting Excel Template from the list.
On the Configuration tab, select Choose File and select your Excel document.
Configure any manual mapping and images (see below) and select Save.