You can leverage data in an Excel spreadsheet to create new (and update) transactions in Onit. This can be a huge time-saver when it comes to importing data from Excel or third-party systems.
For example, let’s say that you have a spreadsheet containing all your legal matters along with their data. You could sync this spreadsheet with Onit, so that one new Onit transaction gets created for each row in the spreadsheet. Not only would Onit create the transactions, but each one’s Fields values would be populated as well.
Despite how powerful this tool is, it’s all pretty simple to set up. We’ll explain how it works in this tutorial.
Note: In this tutorial, the words List and spreadsheet are used interchangeably.
Before We Start ...
This tutorial will assume you understand the following concepts:
Let's Get Started!
1. Building a List
You’ll need to follow the basic requirements outlined in the Formatting, Importing, and Updating a List tutorial.
As described in the tutorial, your List must include columns for each Field you want to push data into. Since we want to create new transactions from this List, and every transaction requires a value for its name and requester_email Fields, we must include these as column in our List, as seen in Columns B and C below.
2. Browse to List
After importing your List, (as covered in Formatting, Importing, and Updating a List), select List from the left-hand pane of the Administration page.
Select the List that you want to create transactions from.
3. Synchronize Your List
At the top of the list, select Synchronize List.
In the dialog that appears, select the App that you want this data synchronized into.
The Notification Email will be the email address that receives a notification when the List is successfully synced.
The Prevent Notifications property refers to the notifications, configured by default in Onit apps, that notify requesters whenever a transaction is created. This property can be used to override this Send Notification Action. If this property is not selected, the requesters identified in your List’s requester_email column will be sent a notification for each transaction that is created under their name. We’ll leave this property selected.
Select Synchronize.
Tip: List Sync can also set the content of a MultiSelect Field. You can use a comma-separated list of values, or if your value has a comma in it we also support JSON: ["Acme, Corp.","Onit, Inc."].
To generate JSON for your MultiSelect Field use the following Liquid guide:
{% assign list = "Coyote|Acme, Corp.|Roadrunner" %}
{% assign list_split = list | split: "|" %}
{% assign json = list_split | to_json %}
{{json}}This Liquid splits the original list on the delimiter (in this case a pipe, '|'), then converts the split list to JSON using the to_json Liquid filter. Plug in your pipe delineated list in the list variable, and the Liquid will do the rest.
Note: if you want to empty out your Multiselect, you can't do so by just putting an empty string in it. You need to put in an empty JSON array, which looks like this:
[]View Your New Transactions
Hop over to the Dashboard of the app that your List was synchronized with. From here, view your new transactions, and confirm that the appropriate Fields are populated with the correct data.
In this example, we synced five new transactions to an app:
Gotchas:
Validations that are applied when transactions are created in the normal way, are not applied to ones created via sync.
If you put a string into a column that corresponds to a Combo or Listcombo Field, that string will be entered into the Field, whether or not it is a possible configured value there. If your Listcombo has its Value Column set to id, and you put a string (such as an email address), this will cause an error which will prevent your new transaction's view page from loading properly.
Similarly, you can use this to put things into Fields of type Email that aren't email addresses.
And, you can leave Required Fields blank, and the transaction will go ahead and be created anyway.
Regex validators, however, are respected: if you try to put data into a Field and the data doesn't match the validator, then an error will be thrown and the new transaction will fail to be created. The email that you receive about the synchronization process will report these errors.
Updating Transactions by Syncing
Note that you can also use this same synchronization process to update transactions, even if those transactions weren’t originally created by syncing.
Let’s say that you have a whole bunch of existing transactions in Onit whose Fields values need to be changed or populated for the first time (i.e., they are currently blank). If you had this data in an Excel spreadsheet, you could use it to sync the Field values into the existing transactions, which would be a lot faster than manually typing in the data.
As with any other List update in Onit, using a Primary Key will allow your List to cross-reference a Field’s value and update the correct transaction. You can learn more about how to use a Primary Key in our Formatting, Importing, and Updating a List tutorial. Note that your Primary Key will need to be an existing Field in the transactions you want updated and one whose value is unique for each transaction. For the spreadsheet used as an example above, we might use the invoice_number Field as a Primary Key since we'd expect its value to always be unique.
Note: If you're only updating transactions (and not creating any new transactions), you will not need to include the columns for the name and requester_email Fields in your spreadsheet.