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 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 tied to Field values.
- Field Values: The values available within an App’s Combo Field can come directly from an imported List instead of being hardcoded into the Field’s definition in Onit. For example, an imported List could provide the names (and properties) of all managers within a company, which could then be used to give values to a Manager Combo Field. 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 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: The words List and spreadsheet are used interchangeably in this tutorial.
Formatting a List
The use case for a List will affect how it is formatted, but some standard formatting requirements 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 regarding 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. The list will fail if the "L" is not capitalized. - Row 2 is named type. This indicates to Onit that all values in Row 2 (for all columns) define the column’s datatype. The following datatypes are available: string, date, date_time, number, decimal, or boolean.
The 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) include 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 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.
NOTE: 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
Once 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 any specific app’s Advanced Designer page). Go ahead and hop over there.
2. Browse to Import List
Select Lists from the Administration page's left-hand pane, then Import List.
3. Upload Your List
For the Notification Email property, enter an email address to which Onit can send an email confirming that 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 import it originally. Upon import, the old List will be updated based on the matching name and Primary Key of the List versions.
NOTE: Your List’s Primary Key will ensure 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 the 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: