Jump to: navigation, search

Data Tables

Go to Business Controls > Data Tables to view and manage your data tables.

Des business controls data tables.png

What is a data table?

A data table contains values that can be read by a Designer application. It has rows and columns and looks similar to a spreadsheet (in fact, you can even export a data table and edit it in a program such as Microsoft Excel), but operates more like a database. Each data table has at least one primary key column, which Designer uses to lookup and retrieve (or store) a value from the table.

When should I use a data table?

Data tables are useful when you want an application to refer to values that are stored outside of the application, or if you want Designer to update values without actually changing them in the application.

For example, you might want customers from a specific region to receive a different welcome message than other customers. Or you might want to specify additional routing handling based on a particular condition being met, such as the business hours for that day or a customer's ID.

Also, certain Business Controls (such as Business Hours and Special Days) have corresponding data types, which means you can add them to a data table and then create and edit their values directly from within the data table.

How do I add a data table to my application?

When you want Designer to reference a data table, just add a Data Table block to the application flow and select the data table you want from the list. For more information about using this block, see the Data Table block page.

Creating a new data table

To create a new data table, click Add Data Table and enter a unique name for it. Click Create to save the new data table and return to the main Data Tables page, or click Create and Open to save it and start configuring the data table properties.

Some things to keep in mind when planning or creating a 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.
  • A data table is not intended to be used as a full-scale database—there is a limit as to how much data can be stored. Focus on data that is frequently updated or critical to your operations.

As an example, let's create a data table that tells the application to perform some special handling for a voice call based on a dialed number (DNIS). We'll call it Joules Coulomb Data Table:

Des data table add new.png

Click Create and Open to create the table and open it for editing. Our table does not have a schema (structure) yet, so Designer asks us to create one.

Defining the column settings

When a data table is first opened for editing, Designer asks you to define the structure, or "schema".

Click Des gear icon.png Manage Schema to define the Column Settings. (If the data table already has a schema defined, you can get to the column settings by clicking Column Settings when the data table is open for editing.)

In this example, we want to use the DNIS as the lookup key. The lookup key is the column that holds the value that Designer will search for when referencing the data table. We'll create a column called Dialed Number and select it as a Key column. We can then define the additional properties for this column, as follows:

Des data table key column.png

Key?

Indicates if this column is a key column used to look up a row of values. In the example above, we want our column to be a key, so we've selected this option. Note: You can only select this option after you have entered values for the other column properties.

Clearly define the lookup keys, as these are important for searching for (and locating) the target data.

Tip
Composite Keys: You can select more than one Key column to create a composite lookup key. But note that if a key column is used for a lookup query and contains a blank or invalid value, Designer ignores it when returning the results. In effect, Designer treats it as if it is a wildcard, not as a "no match".
Important
You can't use a value of "0" (zero) in a numeric or integer key column. This causes a validation error.

Column

The name of a column to add to your data table. In the above example, we've entered DNIS.

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.

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). In the above example, we've entered Dialed Number (DNIS).

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, we've selected string.

Tip
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.

Optional Restrictions

For certain data types, you might want to specify any special restrictions. For example, you could limit a string value to a certain number of characters.

Finishing up

Add and define any additional columns that are needed. When done, click Save. You now have a data table with a key column of Dialed Number. You can go to Editing data tables for information about how to make changes to the data table, such as modifying its settings and adding rows and values.

Here is our example table with some additional columns added:

Des data tables column settings key.png

When you are ready to use the data table in an application, click Publish Table and use a Data Table block to add it to your application.

Editing a data table

To open a data table for viewing or editing, go to Business Controls > Data Tables and click the link for the data table you want to edit. (If another user has the data table open for editing, you will only be able to view it in read-only mode.)

Tip
Some browsers might display a script error or seem to freeze when opening large data tables. This is normal, and usually only temporary. You can let the script continue or wait for the browser to finish loading the data table.

From the editing mode, you can

Don't forget! You can save your changes, but they won't take effect until the data table is published.

Adding or 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.

Tip
For best system performance, limit the number of rows to 1000 and the total size of the data table to no more than 10,000 cells.

Changing column settings

Click Des gear icon.png Column Settings to add new columns or update the properties of existing columns.

Des data tables column settings2.png

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 types or whether to enable Enforce non-overlapping dates for datetimerange types.

Use the options under Actions to change a column's position in the grid or delete it.

Important
If you change the data type of a column, make sure that after saving the data table, you refresh or reload the page before entering or editing any cell values. Otherwise, the cell values under the modified columns might not display correctly. 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.

Changing data table values

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 displayed in blue:

Des data tables edit value.gif

Updating Business Hours and Special Days

You can change these directly in the data table. If you click on a Business Hours or Special Days value, you can select a different item or create and add a new one.

One thing to keep in mind — if you add new Business Hours or Special Days to a data table, the new business object is local to that data table. In other words, it can only be used by the data table it was created in. It won't appear in the global Special Days list and it won't be available to select in other data tables.

Local business objects appear in bold with a (local) label:

Des specialday picker.png

Searching the data table

There are a couple of ways you can quickly locate a specific value or row:

  • Each column header has a search box. As soon as you start typing, Designer shows only those rows that contain a match to what you have entered.
  • The Row Count box at the bottom of the page lets you jump directly to the specified row number.

Reviewing your changes

At the bottom of the data table, a tracker displays how many 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 the data table

When you are ready to commit your changes, click Save Table. Make sure to review your changes! After you click Save Table, the changes can't be undone.

Designer validates your changes and lets you know if there are any errors.

Des data tables save.gif

Tip

During the save operation, you might see some values (particularly for Business Hours or Special Days) suddenly change to N/A. This is just temporary, and the correct values will re-appear after the save completes.

Don't forget! Saving a data table only preserves the changes you have made. To activate the changes, you must publish the data table.

Publishing the data table

Click Publish Table to publish (i.e. activate) the data table in the live production environment. Designer will ask you to confirm that you want to publish.

Des publish application.gif

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.

Warning
When importing the edited CSV file, do not change the schema (structure) of the data table in Designer. The data table schema must remain unchanged between the export and the subsequent import. Otherwise, the import will fail.

Export

Click Export in the Actions column to export a Data Table from Designer into a CSV file.

Des datatable export.png

Below is a sample Data Table, its generated CSV file, and the CSV file in Microsoft Excel.

Data Table

Des export data table csv.png

CSV File

Here is how the row that is highlighted above would appear in the exported CSV file:

Des csv text.png

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).

Tip
Items in CSV files are separated (or delimited) by commas. If you need to use a comma within a value (such as for the text in a script dialog) you must enclose it in double-quotes (",").

Data Table in Microsoft Excel

Here is how the CSV file appears when viewed in a program like Microsoft Excel:

Des csv excel.png

After you have edited the CSV file, you can import it into Designer.

Tip
While you can edit any item listed in the CSV file, it is more practical to edit items referenced by resource IDs from within the actual data table.

Import

To import a CSV file into a data table, locate it in the Data Tables list and click Import in the Actions column.

Des datatable import.png

Important
  • 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, make sure that the CSV file and the data table use the same table headers. If the headers do not match, Designer displays an error.
This page was last modified on January 18, 2019, at 14:22.

Feedback

Comment on this article:

blog comments powered by Disqus