Onit can generate Microsoft Excel spreadsheets that contain data from Onit transactions.
For example, maybe you’d like to offer your users the ability to click a Button in Onit to generate an Excel spreadsheet on the fly. The generated spreadsheet could contain all transactions of a certain type -- one row per transaction. You could add columns to the spreadsheet holding each transaction’s Field values. In addition, you could format and structure the spreadsheet to look however you like.
The Onit Action that provides this functionality is Generate Spreadsheet. It’s one of the more advanced Onit Actions, so we’ll explain its basic configuration in this tutorial. A separate tutorial will cover the Action’s more advanced options.
Before We Start ...
This tutorial will assume you understand the following concepts:
Parent-Child Relationship
Before we explain the step-by-step process of generating spreadsheets, there’s one critical concept to understand: While the Generate Spreadsheet Action should be created in a parent app, the transaction data sets it inserts into a spreadsheet live within a child app.
For example, let’s say we have a Legal Matters parent app, which has a child app named Tasks. We also want to generate a spreadsheet containing all tasks for a specific legal matter.
In this situation, we would create the Generate Spreadsheet Action within the parent Legal Matters app. When this Action ran, it would insert one row into the spreadsheet for each child task related to the specific legal matter in question.
It’s important to fully understand this point before proceeding. If this sounds confusing, you might want to familiarize yourself with parent-child relationships in Onit by reading Creating a Parent-Child Relationship.
Note: The Generate Spreadhseet Action does not work for Many-to-Many relationships.
Building an Excel Template
The first step is to create an Excel template, which essentially teaches Onit how to generate your spreadsheets each time the Action runs. More specifically, the template defines how the generated spreadsheets should look and which rows/columns the Onit transaction data should be populated into.
1. Build the Template
Open Microsoft Excel and create a blank spreadsheet.
Next, add columns and rows to your spreadsheet, defining where Onit should populate transaction data.
The exact number of columns will vary depending on how much data you want Onit to populate. Below is a simple example of a correctly formatted template with three columns of Onit Field values.
At first glance, this probably doesn’t make a lot of sense. Let's break down each row:
- Row 1 is the column’s header. Onit doesn’t care what values appear in this row -- they are purely for your users to see a header that clarifies what data lives in each column. (Technically, you could exclude this row.)
-
Row 2 tells Excel what type of data it should expect to populate into this column. For example, if Onit will be populating this row with text Field values, you can enter a sample string (e.g., Foo). Or, if a number will be populated, enter a sample number (e.g., 1). The actual value that you enter in this row (for each column) doesn’t matter (e.g., instead of Foo, you could have entered Bar or Baz or any other word). What matters here is the value’s type.
Tip: If you need to render data in a very specific format, such as when populating a column with DateTime values, it can be easier to set the cell's data type with Excel's Format selector, as seen in the screenshot below:
-
Row 3 does the heavy lifting! It contains a Liquid expression that tells Onit which transaction data to insert. There are two critical concepts to understand here:
- Looping: The example template above shows that each Liquid expression contains loop_atom. What is this? This Liquid tells Onit, “When generating the spreadsheet, run this Liquid expression for each child transaction.” For example, five child transactions need to be inserted into the spreadsheet. That means that loop_atom will run five times. The Liquid that follows loop_atom tells Onit what value to insert. For example, loop_atom.invoice_number tells Onit to always insert the value of the invoice_number Field into this column.
- Context: Context is king when it comes to Liquid. In this case, any Liquid that you insert into the spreadsheet lives at the level of the child transaction. In other words, {{loop_atom.field_name}} will only work if the Field in question lives within the child app.
Once you’ve formatted your Excel columns, you can move on to the next step (also completed within Excel).
2. Create a Named Range
We need to specify which cells Onit should insert data into. We do that by defining a named range.
Select the two rows of your spreadsheet that contain the datatype examples and the Liquid expressions. In the screenshot below, this would be Row 2 and Row 3:
Note: You technically don’t select the entire row (as doing so would select a bunch of empty cells). You only select the rows/columns in which you’ve inserted data. In our example above, that is Cells A2 through C3.
With the appropriate cells selected, define a name for this range using Excel’s Name Box. This box is located in the top-left corner of the program. In the screenshot below, we’ve named our range invoices_data.
Tip: After typing a name into this Excel text box, press ENTER to save it.
After you create your named range, save your spreadsheet template and move on to the next step.
Upload the Excel Template to Onit
Upload your template as an asset to your Onit environment.
1. Browse to Administration
Browse to your Onit environment’s Administration page.
2. Browse to Assets
From the left-hand pane of the Administration page, select Assets.
3. Upload Your Template
Select your spreadsheet template and Upload.
Note: If you need to update your spreadsheet template in the future, delete the existing version first, and then upload the new version.
Add a Field, Button, and Action
1. Add a New Field
Create a new Field in your parent app to store the generated spreadsheet. Users can download the spreadsheet by clicking a link in this Field.
Navigate to your parent app’s Wizard and add a new Field, providing it with a Type of Attachment.
Change the field’s Display In property to only show on the View Page.
Need help creating a new Field? See Step 5: "Fields" section of Building Your First App.
Browse to the last screen of the Wizard and click Update.
2. Add a New Action
Browse to the Advanced Designer page of your parent App. From the left-hand pane, select the Actions node.
Select Generate Spreadsheet from the Add dropdown.
Provide this Action with a Name.
For the Action’s Template Name, provide the full file name of your spreadsheet template, including the extension.
In the Attachment Property dropdown, select the Attachment Field you created above.
Note: The Template Name property is case sensitive.
Tip: You can use Liquid for the Template Name property to conditionally use different spreadsheet templates in different situations.
Next, point your Action to the correct data to be exported. Select the Add button above the Data To Export property section.
For the Saved File Name property, you can optionally provide a value to be used as the generated file's name. Note that this property accepts conditional Liquid.
In the Excel Named Range property, enter the Excel named range that you created earlier. (For us, that's invoices_data.) This tells our Action where to populate the child transaction data in our spreadsheet.
For the Relation Field property, select the Name of the HasMany Field that correlates with the child app from which you want data exported.
Note: Though not covered in this tutorial, you can optionally set a Filter in this Action’s Relation Field Filter property. This configures the Action only to export a subset of child transactions. For instance, you may only want your spreadsheet to include child transactions in a certain Phase. Filters will be covered in a separate tutorial.
To learn more about Filters, see Creating Raw Filters.
Tip: You can export more than one set of child transactions into a single spreadsheet. To do so, add multiple Data To Export sections to the Action. These data sets can be from the same or different child apps, and each data set will need its own named range.
Save your Action by selecting OK.
3. Link Your Action to a Button
To fire your newly created Action, create an Action Button and associate it with your Generate Spreadsheet Action.
Note: Need help creating an Action Button? See our tutorial Creating a Button to Execute an Action.
Test It Out
You’re done! At last, it’s time to put this Action to the test.
Visit a parent transaction that has child transactions. Click your Button and watch for a new file to appear in the parent transaction’s Attachment Field.
To generate an updated version of the spreadsheet, select the button again, and the old spreadsheet will be replaced with a new version.
If your Generate Spreadsheet Action is silently failing, try disabling search in your App. To do so, navigate to your App's Advanced Designer, click on the Security tab, and check the Disable Search box.
