A Data Table is similar to a spreadsheet. It is a two-dimensional array that is filled with values that can be read by a Designer application. Each data table has at least one key (primary key) column. The value of the key column is used to look up a row in the table.
Data Tables are useful if you want an application to refer to values that are stored outside of the application, or if you want an application to update values in a table without changing values in the application.
Clicking a Data Table opens it for editing. Hovering on it will let you choose whether to open it in Edit or view Read-only mode.
- If another user has a Data Table open for editing (locked), you can still open it for viewing in Read-only mode.
- If you prefer to work on your Data Table in another program, such as Microsoft Excel, you can export the Data Table into a CSV file. See Exporting and Importing Data Tables.
- You can create and modify Special Days and Business Hours directly from within a Data Table. The changes take effect when you save the table.
Recommendations and guidelines
Here are some important things to keep in mind when working with data tables:
- Some browsers might display a script error or temporarily freeze when opening large Data Tables. This is normal, and you can let the script continue or wait for the browser to finish loading the data table.
- Limit the number of rows to 1000 and the total size of the data table to no more than 10,000 cells. If the number of rows is less than 1000, you can increase the number of columns until the 10,000 cell limit is reached. For example, a 200 row table can have up to 50 columns, and a 1000 row table can have a maximum of 10 columns.
- There are limits to how much data can be stored in a data table (a data table is not meant to be used as a full-scale database). Therefore, try to focus more on data that needs to be changed or updated frequently, or are critical to your business.
- Clearly categorize the data that you want to store. For example, if you are storing customer profiles, some various categories could be Name, Address, and Phone. Then you could set up Name and Address as a string data type and Phone as a numeric data type.
- Clearly define the lookup keys, as these are important for searching for (and locating) the relevant data.
- Note that you can't use a value of "0" (zero) in a numeric or integer key column (this returns a validation error).
- Take advantage of Designer's ability to let you carefully review and verify your changes before committing them to the data table.
- After a data table is published, you cannot change the data types of the existing columns. You can, however, still modify the schema of the data table and change the data types of columns that have not yet been published.
Adding a Data Table
To add a new Data Table, click Add Data Table and enter a name for the Data Table. For example, you can create a Data Table to segment customers based on a DNIS key.
After you click OK, Designer creates the Data Table and it appears in the Data Tables list.
Next, click your Data Table to open it. Designer prompts you to define a scheme for your new Data Table (for example, the names of columns for your table).
Click Manage Schema and configure the following options:
- Column - The name of a column to add to your Data Table. For this example, enter dnis.
- Display Name - Lets you customize how the column name is to be shown in the Data Table (this does not overwrite the actual Column value). For this example, enter Dialed Number Identification Service.
- Key? - If enabled, this column is a key column and is used to look up a row of values. For this example, enable this check box.
- Data Type - Specifies the type of value(s) that will be used by this column. Supported data types include string, numeric, boolean, announcement, integer, datetime, datetimerange, skillexpression, timezone, businesshours, and specialdays. For this example, select numeric. (When specifying integer values, the numeric data type does support integers, but data table lookups complete faster if you use the integer data type for these values.)
- Description - An optional description of the column.
Click Add a column and add two more columns: segment (type is string) and welcome_message (type is announcement). Do not enable the Key? check box.
When done, click OK. You have created a Data Table with a key column of dnis and value columns of segment and welcome_message. Click Add to add a row in your Data Table. You can now click the cells under each column to enter values.
Once you have created a Data Table, you can place a Data Table block in an application.
Editing Data Tables
You can change the value of a table cell by clicking on it. As soon as you start editing a cell, the row is automatically selected and the updated text is in blue.
If you expand an updated row, the original values (crossed-out) are shown below the modified cells. This lets you compare the new value to the one that was previously saved.
- Each column has a search box you can use to look for a specific item. As soon as you start typing, Designer hides all other rows except for the ones that contain matches to what you have entered.
- At the bottom of the page, there is a Row Count box you can use to jump directly to a specific row number.
Note that if you add new Business Hours or Special Days to a data table (for example, you click a cell under a Special Days column and the Select Special Days picker opens), the new business object is local only to that data table — that is, it won't show up in the global Special Days list and be available to other data tables. Local business objects are in bold and have (local) beside them.
Adding and Removing Rows
To add a row, click Add Row. To remove a row, select it and click Mark For Deletion. Any rows that you mark for deletion are removed the next time you save the table.
Changing Column Settings
To make changes to the columns, click Column Settings. You can add new columns, or update the properties of existing columns. For example, you can update the Display Name of a column, indicate if it is mandatory, or specify any Optional Restrictions for that particular data type, such as a maximum string length (for string) or whether to enforce non-overlapping dates (for datetimerange).
Use the options under Actions to change a column's position in the grid or delete it.
Reviewing Your Changes
At the bottom of the data table, a color-coded counter keeps track of the number of rows you have added, modified, or marked for deletion.
To view only the rows that were added, changed, or marked for deletion, check the Show Modified Rows Only box. Uncheck it to go back to editing mode.
You can also review changes to local business objects by clicking Display Business Object Diffs and selecting Business Hours or Special Days. You can then select an item from the list to view the original version side-by-side with the revised version. On the original version, edited properties are highlighted in red to indicate edits and deletions. On the revised version, edited properties are highlighted in green to indicate edits and additions.
Saving and Publishing
When you are ready to commit your changes, click Save Table.
When you save the data table, Designer validates your changes and lets you know if there are any errors. Canceling discards all unsaved changes and restores the table back to its previously-saved state.
Saving a data table only preserves the changes you have made. To activate the changes in the live production environment, click Publish. After you publish the data table, the applications that reference it have access to the latest changes.
Exporting and Importing Data Tables
You might prefer to use another program, such as Microsoft Excel, to edit Data Table values. If so, you can export a Data Table from Designer into a CSV file that can be edited in Excel. When you are done, you can import the edited CSV file into Designer.
Click Export in the Actions column to export a Data Table from Designer into a CSV file.
Below is a sample Data Table, its generated CSV file, and the CSV file in Microsoft Excel.
Here is how the row that is highlighted above would appear in the exported CSV file:
Note that some of the items are represented by their resource ID and not their actual name. For example, the audio resource AZ_IN_Open Greeting appears as "dbc63d70-37d6-11e6-a888-e53edc8cf09b". This ensures that the correct resource is being referenced (names of resources can be changed, but their assigned resource IDs always remain the same).
Data Table in Microsoft Excel
Here is how the CSV file appears when viewed in a program like Microsoft Excel:
After you have edited the CSV file, you can import it into Designer.
Click Import in the Actions column to import a CSV file into a data table.
- Import is disabled for data tables that contain menu data types. If you do not see the Import icon in the Actions column for a data table, it indicates that the data table is using the menu data type.
- If you are importing a CSV file into an empty data table, Designer designates the first column as the key column.
- If you are importing a CSV file into a populated data table, you must ensure the CSV file and the data table use the same table headers. If the headers do not match, Designer displays an error.