Keeping Records Updated with a Generated Spreadsheet

by Christina Moore Updated Oct 03, 2019

Using the Generate Spreadsheet Action, users can generate an Excel spreadsheet of Record data from a custom spreadsheet template. But what happens when you’ve updated that generated spreadsheet’s content? It'd be a pain if you had to manually keep your Record data in sync with this spreadsheet as it’s edited.

This is where Onit’s Update Related Atoms from Spreadsheet Action comes in. After adding a little extra formatting to your spreadsheet template, you’ll be able to leverage this Action to push updated data in a generated spreadsheet back to your child Records.

Before We Start...

This tutorial will assume you understand the following concepts:

Formatting the Spreadsheet Template

In addition to the formatting requirements detailed in our Generating a Spreadsheet tutorial, you'll also need to make the following adjustments to your spreadsheet template:

1. Add a Transaction ID Column

We need to tell our spreadsheet which Record a data row corresponds to so that the Record can be updated with the correct set of information. To do this, we’ll reference Onit’s id variable, which by default, assigns a unique ID to each Record launched in an App.

Following the column formatting detailed in Step 1 of the Generating a Spreadsheet tutorial, add a new column that provides an example of the ID data type (i.e., a string) and the Liquid script that will loop over each Record to find its ID.

2. Update the Field Data Named Range

Since we've added a new column to our spreadsheet, we'll want to include it in the named range we created in Step 2 of the Generating a Spreadsheet tutorial. Update this named range so Onit knows to also insert Field data into this column's cells.

3. Add a Row Specifying Child Record Fields to be Updated

We now need to tell our spreadsheet which Fields in our child transactions we want updated. To do so, we'll add a new row above the rows we created in Step 1 of the Generating a Spreadsheet tutorial. This row will contain the Names of the Fields we want the spreadsheet to update.

In the example below, we're indicating we want the values for Fields invoice_total, due_date, and id to be updated.

Note: To update the value of MultiCurrency Fields enter the currency amount followed by the three digit currency code. (e.g., 100 USD)

Note: Notice that you can be selective about which Fields you want the spreadsheet to update. However, it is important to include the id variable in this row.

4. Create a Second Named Range

Finally, we need to create a second, separate named range to designate the row containing the child Field names that we created in the last step.

In our example, that's Row 1, so we'll select this row's cells and provide it a new named range, which we'll call update_fields.

We're done formatting and you can now save and upload your spreadsheet into your environment again.

Note: Remember that templates for file generation are uploaded in the Assets section of the environment. Make sure you delete the old version of this spreadsheet before uploading this new version.

Once your updated spreadsheet template is uploaded to your environment, you can move on to configuring the Action to utilize it.

1. Create an Attachment Field to Upload Your Updated Spreadsheet

After a user has made changes to the generated spreadsheet he/she downloaded from a transaction, he/she will need a place to upload it back to the transaction.

Create a new Field with a Type of Attachment in the Wizard for this purpose. We'll name this Field updated_spreadsheet.

Update your app.

Browse to the Actions node of your parent App's Advanced Designer.

Add a new Update Related Atoms from Spreadsheet Action.

Provide this Action with a Name so you can reference it later when you create the Button to fire it.

Next, for the Field Label Named Range property, provide the name of the named range that designates which Fields need updating. For our example, that's the named range called update_fields, (which we created in Step 4 of the Formatting the Spreadsheet Template section above).

Additionally, for the Field Data Named Range property, provide the name of the named range that you originally created to specify which cells Onit should insert data into. For us, that's the named range called invoices_data.

Provide the name of the Attachment Field that you created above for the Attachment Property.

And finally, select the name of the Field that draws a relationship between your parent app and a child app for the Relation Property. For us, that's a HasMany Field named Invoices.

Save this Action by selecting OK.

3. Create a Button to Fire the Action

Now we need to create a new Action Button and link it to the Action we just configured so a user can fire this Action after they've uploaded their updated spreadsheet to a Record.

Note: If you need help creating an Action Button, you can see our Creating a Button to Execute an Action tutorial.

Test It Out!

Great! We're done configuring our Update Related Atoms from Spreadsheet Action. It's time to test it out.

Jump over to one of your parent app's transactions that has child transactions related to it. Generate a spreadsheet from this data, using the Button you originally configured in the Generating a Spreadsheet tutorial. Edit some of the data in this generated spreadsheet and save it.

Next, upload this updated spreadsheet to the appropriate Attachment Field. For us, that's our updated_spreadsheet Field.

Finally, select the Action Button you created in Step 3 above to update your child transaction data with the edits you just made to your spreadsheet. Visit one of the child transactions to ensure the data you edited in the spreadsheet was updated in the transaction.

Previous Article Generating a Spreadsheet
Next Article Generating a Word Document

© 2020 Onit, Inc. contains proprietary and confidential information owned by Onit, Inc. that is subject to copyright. Onit presents it exclusively to you for your sole use in conjunction with using Onit products. No portion of the materials contained herein may be used for any other purpose. No portion of the materials contained herein may be shared with third parties or reproduced in any form.