Microsoft Excel spreadsheets can be imported as Lists. Apps can take advantage of imported Lists in a variety of powerful ways, such as:
- Lookups: When an App runs, it can perform real-time lookups into imported Lists. The data returned from the List can be used to set a Record’s variables, and even to make workflow routing decisions. For instance, it’s common for a List to act as a Decision Table, which defines how a Record should be routed based on a complex set of rules that are tied to Field values.
- Field Values: The values available within an App’s Combo Field can come directly from an imported List, as opposed to being hardcoded into the Field’s definition in Onit. For example, an imported List could provide the names (and properties) for all managers within a company, which could then be used to provide values to a Manager Combo Field. By using this List-based approach, an App’s creator wouldn’t have to type each manager’s name one-by-one into the Manager Field when creating the App -- instead, the App would automatically pull from the imported List. In the future, any changes to the List could be made directly within the Excel spreadsheet, which provides a richer interface for viewing and modifying column/row-based data.
- Creating New Records: Lists can be used to quickly create a set of new Records from every row of data in a List.
Before importing an Excel spreadsheet into Onit as a List, it must be formatted in a specific manner so let's start there and then we'll walk through how to import and update that List.
Note: In this tutorial, the words List and spreadsheet are used interchangeably.
Formatting a List
The use case for a List will affect how it is formatted, but there as some standard formatting requirements that must be observed for all List types.
1. File Type of .xslx
Lists must be .xslx files, not .xls.
2. Required Rows and Columns
Below is a sample screenshot of a correctly configured Excel spreadsheet:
Note: You cannot import BelongsTo relationships using a list.
In this example, Column A (outlined in blue above) is doing the heavy lifting in terms of how Onit parses the spreadsheet’s data.
Within Column A, note the following:
- Row 1 is named fieldLabel. This identifies to Onit that all values in Row 1 (for all columns) define the name of the Onit app Field that the column maps to. The Field you enter here should match the Field’s name in Onit exactly.
Note: Cell A1 must be fieldLabel. If the "L" is not capitalized the list will fail. - Row 2 is named type. This identifies to Onit that all values in Row 2 (for all columns) defines the column’s datatype. The following datatypes are available: string, date, date_time, number, decimal or boolean.
Valid format for the date_time data type is as follows:
YYYY-MM-DDTHH:MM:SS+ZZ:ZZ
E.g., 2016-06-29T16:35:00-05:00 is 4:35 PM CDT (i.e., UTC -5) on June 29, 2016. If no time zone is specified the default is UTC. Note that after the list is imported, the UI will convert from UTC to your time zone, so if you don't provide a time zone offset, what you see in the list data grid will be different from what was in your spreadsheet.
- Row 3 and beyond contain the word data. This identifies to Onit that all values in these rows (for all columns) contains the actual values that an Onit app can retrieve.
Lastly, a List needs a column for a Primary Key. A Primary Key is the mechanism Onit uses to cross-reference column data between List versions when a List is updated to prevent rows of data from being duplicated.
You can use any column from your List as a Primary Key as long as each piece of data within it is always unique. The best practice is to create a column specifically for this purpose, though, with a fieldLabel of “Key” and sequential values (“k1”, “k2”, “k3”, etc…) in each row, as shown below.
To put a Primary Key to work, you’ll reference it when you name the tab of your List. See Naming Your List below for this naming convention.
The formatting rules described above apply to all List types. That said, some List types -- such as Decision Tables and Lists used to create transactions -- have additional formatting requirements.
3. Naming Your List With a Primary Key
Upon being imported into Onit, each worksheet within a workbook formatted as detailed above will become a separate List within Onit.
For example, if your Excel spreadsheet contained two Onit-configured worksheets, the spreadsheet would become two separate Lists upon being imported.
To use a Primary Key, your worksheet’s name must include your Primary Key’s fieldLabel in the following format:
<list_name>-<primary_key_fieldLabel> E.g., mylist-key
Importing a List
Now that your List is formatted correctly, it’s ready to be imported.
1. Browse to Administration
Lists are imported from the Administration page (not from any specific app’s Advanced Designer page). Go ahead and hop over there.
2. Browse to Import List
From the left-hand pane of the Administration page, select Lists, then Import List.
3. Upload Your List
For the Notification Email property, enter an email address where Onit can send an email confirming the import process went smoothly.
Next, click Browse and select your .xlsx file. Click Upload.
Viewing an Imported List
Once your List has been successfully imported, you can view it within Onit by selecting Lists from the left-hand pane of the Administration page, selecting your List.
Updating an Imported List
You can always reimport a List after you’ve made changes to it by following the same steps used to originally import it. Upon import, the old List will be updated based on the matching name and Primary Key of the List versions .
Your List’s Primary Key will ensure that the correct rows are updated, dropped, and/or added.
Gotchas
- If the type is "string" but the cell content is a number, or if the type is “Boolean” and the cell content is either “true” or “false”, prepend these values with a tick-mark character: '
E.g., '100.1, rather than just 100.1, ‘false, rather than just false.
If these values lack a tick-mark, Excel will deliver the data in a way that breaks import of the entire row, and your List after import will lack that row.
- Special characters must be escaped with backslashes within a spreadsheet.
E.g., Finance Related \(Loan\), Software \& Hardware Agreements
- If a fieldLabel ends with _id, Onit will attempt to strip the _id from the fieldLabel. Therefore, you must append a second _id to a fieldLabel ending in _id.
E.g., contract_id_id
Now What?
You can use a List in a few different ways. Check out the following tutorials to learn how: