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 also add columns to the spreadsheet that would hold 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 in this tutorial we’ll explain its basic configuration. The Action’s more advanced options will be covered in a separate tutorial.
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 that it will insert into a spreadsheet are those that live within a child app.
For example, let’s say that we have a Legal Matters parent app, which has a child app named Tasks. Let’s also say that we want to generate a spreadsheet containing all tasks that exist 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 moving forward. If this all sounds confusing to you, you might want to get more familiarized with parent-child relationships in Onit by checking out 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 that 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 that define where Onit should populate transaction data into.
The exact number of columns will vary based on how much data you want Onit to populate. Below is a simple example of a correctly formatted template that will hold three columns of Onit Field values.
At first glance, this probably doesn’t make a lot of sense. Let 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: As seen in the example template above, each Liquid expression contains loop_atom. What is this? This Liquid says to Onit, “When generating the spreadsheet, run this Liquid expression for each child transaction.” For example, let’s say there are five child transactions that need to get 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’re ready to move on to the next step, (which is 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 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, ensure you 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 click 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 that will store the spreadsheet that gets generated. Users will be able to download the generated spreadsheet by clicking a link that will be populated 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 so it only shows 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 that 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 name of the generated file. 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 in our spreadsheet to populate the child transaction data.
For the Relation Field property, select the Name of the HasMany Field that correlates with the child app that you want data exported from.
Note: Though not covered in this tutorial, note that you can optionally set a Filter in this Action’s Relation Field Filter property. This configures the Action to only 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 long 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, simply 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 disable search navigate to your App's Advanced Designer then click on the Security tab and check the Disable Search box.
