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 manually kept 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 some extra formatting to your spreadsheet template, you can 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 information set. To do this, we’ll reference Onit’s id variable, which assigns a unique ID to each Record launched in an App by default.
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 can 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 indicate that we want the updated values for invoice_total, due_date, and ID.
Note: To update the value of MultiCurrency Fields, enter the currency amount followed by the three-digit currency code. (e.g., 100 USD)
Note: You can be selective about which Fields you want the spreadsheet to update. However, it is essential 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 we created in the last step.
In our example, that's Row 1. We'll select the cells in this row and create a new named range, which we'll call update_fields.
We're done formatting, and you can now save and upload your spreadsheet to your environment again.
Note: Remember that file generation templates are uploaded in the Assets section of the environment. Before uploading this new version, delete the old version of this spreadsheet.
Configuring an Update Related Atoms from Spreadsheet Action
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 changed the generated spreadsheet they downloaded from a transaction, they 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.
2. Configure an Update Related Atoms from Spreadsheet Action
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 to reference 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 you created initially 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.
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 that users can fire it after uploading 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.