View source for DES/Current/Designer/DataTables

{{Article
|Standalone=No
|DisplayName=Data Tables
|TocName=Data Tables
|Context=Learn about data tables and how you can use them in your applications.
|ComingSoon=No
|Platform=GenesysEngage-cloud
|Role=Administrator
|Application=Designer
|Section={{Section
|alignment=Vertical
|structuredtext=Go to '''Business Controls''' > '''Data Tables''' to view and manage your data tables.
[[file: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 {{Link-SomewhereInThisVersion|manual=Designer|topic=DataTables|display text=export a data table|anchor=exp_imp}} 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 {{Link-SomewhereInThisManual|topic=BusinessObjectsBar|anchor=top|display text=Business Controls}} (such as {{Link-SomewhereInThisManual|topic=DesBusinessHours|anchor=top|display text=Business Hours}} and {{Link-SomewhereInThisManual|topic=SpecialDays|anchor=top|display text=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 {{Link-SomewhereInThisManual|topic=DataTable|anchor=top|display text=Data Table block}} to the application flow and select the data table you want from the list. To learn more about using this block, see the {{Link-SomewhereInThisManual|topic=DataTable|anchor=top|display text=Data Table block}} page.
|Status=No
}}{{Section
|sectionHeading=Creating a new data table
|anchor=add
|alignment=Vertical
|structuredtext=To create a new data table, click '''Add Data Table''' and enter a unique name for it. 

You can then click '''Create''' to save the new data table and return to the main '''Data Tables''' page, or '''Create and Open''' to save it and start configuring the data table properties.
{{AnchorDiv|limits}}

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&mdash;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''':

[[file: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, which we'll do in the next step..
{{AnchorDiv|defcol}}
===Defining the column settings===
When a data table is first opened for editing, Designer asks you to define the structure, or "schema". 

Click [[file: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:

[[file: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. 

{{NoteFormat|
'''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".|2}}

{{NoteFormat|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 <tt>DNIS</tt>.

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 <tt>Dialed Number (DNIS)</tt>.

====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'''. 

{{NoteFormat|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.|2}}

====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 {{Link-SomewhereInThisVersion|manual=Designer|topic=DataTables|display text=Editing data tables|anchor=edit}} 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:

[[file: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 {{Link-SomewhereInThisManual|topic=DataTable|anchor=top|display text=Data Table block}} to add it to your application.
|Status=No
}}{{Section
|sectionHeading=Editing a data table
|anchor=edit
|alignment=Vertical
|structuredtext=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.)

{{NoteFormat|
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.|2
}}

From the editing mode, you can

*{{Link-SomewhereInThisManual|topic=DataTables|anchor=addrow|display text=add or remove rows}}
*{{Link-SomewhereInThisManual|topic=DataTables|anchor=chgcol|display text=change column settings}}
*{{Link-SomewhereInThisManual|topic=DataTables|anchor=chgval|display text=update values}}
*{{Link-SomewhereInThisManual|topic=DataTables|anchor=dtsearch|display text=locate a specific value or row}}
*{{Link-SomewhereInThisManual|topic=DataTables|anchor=save|display text=save}} and/or {{Link-SomewhereInThisManual|topic=DataTables|anchor=publish|display text=publish}} the data table

'''Don't forget!''' You can save your changes, but they won't take effect until the data table is {{Link-SomewhereInThisManual|topic=DataTables|anchor=publish|display text=published}}.

{{AnchorDiv|addrow}}
===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.

{{NoteFormat|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.|2}}

{{AnchorDiv|chgcol}}

===Changing column settings===
Click [[file:des_gear_icon.png]] '''Column Settings''' to add new columns or update the properties of existing columns.

[[file: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.

{{NoteFormat|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. }}

{{AnchorDiv|chgval}}
===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:

[[file: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 {{Link-SomewhereInThisManual|topic=DesBusinessHours|anchor=top|display text=Business Hours}} or {{Link-SomewhereInThisManual|topic=SpecialDays|anchor=top|display text=Special Days}} value, you can select a different item or create and add a new one.

One thing to keep in mind &mdash; 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:

[[file:des_specialday_picker.png]]

{{AnchorDiv|dtsearch}}

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

{{AnchorDiv|review}}
===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.
{{AnchorDiv|save}}
===Saving the data table===
When you are ready to commit your changes, click '''Save Table'''. Make sure to {{Link-SomewhereInThisVersion|manual=Designer|topic=DataTables|display text=review your changes|anchor=review}}! After you click '''Save Table''', the changes can't be undone.

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

[[file:des_data_tables_save.gif]]

{{NoteFormat|
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.
|2}}

'''Don't forget!''' Saving a data table only preserves the changes you have made. To activate the changes, you must {{Link-SomewhereInThisManual|topic=DataTables|anchor=publish|display text=publish}} the data table.

{{AnchorDiv|publish}}
===Publishing the data table===
Click '''Publish Table''' to publish (i.e. activate) the data table in the live production environment. Click '''Confirm Publish''' to start the publishing process.  

[[file:des_publish_application.gif]]

If you are publishing changes to an existing data table, Designer also lists the differences between the previously published version of the data table and the one you are publishing now. You can review these changes before clicking '''Confirm Publish'''.

[[File:des_data_tables_confirm.png]]

After you publish the data table, the applications that reference it have access to the latest changes.

{{NoteFormat|Designer may not always be able to display a complete list of detected changes on the confirmation page. However, you can still proceed with publishing the data table.}}
|Status=No
}}{{Section
|sectionHeading=Viewing the history
|anchor=history
|alignment=Vertical
|structuredtext=Click '''View History''' in the '''Actions''' toolbar ([[File:des_actions_toolbar_history_icon.png]]) to open the history view (i.e. audit log) for a data table.

The history view shows you a list of each time the data table was viewed, edited, or published, the user who made the change, and the new and previous value of any properties that were changed.

You can use the buttons on the page to view the results for a specific time period (for example, '''last 1W''' to see the results for previous week), or use the date fields to specify a custom date range. Results can be sorted or searched and you can use the '''Export''' button to export the results as a CSV file.

You can drill-down further into each results item by double-clicking it. This opens an audit window opens that displays additional details for that particular event.
|Status=No
}}{{Section
|sectionHeading=Exporting and importing data tables
|anchor=exp_imp
|alignment=Vertical
|structuredtext=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 {{Link-SomewhereInThisVersion|manual=Designer|topic=DataTables|display text=import the edited CSV file|anchor=imp}} into Designer.

{{NoteFormat|
* DO NOT open an exported data table in Excel before reading the information contained in {{Link-SomewhereInThisVersion|manual=Designer|topic=DataTables|anchor=excel|display text=Editing exported data files in Excel}}. This will help you to avoid any unexpected data changes in your file.
* When importing the edited CSV file, do not change the column headers in Designer. The headers of the data table columns must remain unchanged between the export and the subsequent import. Otherwise, the import will fail.|3}}

{{AnchorDiv|exp}}
===Export===
Click '''Export''' in the '''Actions''' column to export a Data Table from Designer into a CSV file.

[[File:des_datatable_export.png]]

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

====Data Table====

[[File:des_export_data_table_csv.png]]

====CSV File====

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

[[File:des_csv_text.png|border]] 

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 "<tt>dbc63d70-37d6-11e6-a888-e53edc8cf09b</tt>". This ensures that the correct resource is being referenced (names of resources can be changed, but their assigned resource IDs always remain the same).

{{NoteFormat|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 (<tt>","</tt>).|2}}

====Data Table in Microsoft Excel====

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

[[File:des_csv_excel.png|border]]

After you have edited the CSV file, you can {{Link-SomewhereInThisManual|topic=DataTables|anchor=imp|display text=import it}} into Designer. 

{{NoteFormat|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.|2}} 

{{AnchorDiv|imp}}
===Import===

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

[[File:des_datatable_import.png]]

{{NoteFormat| 
* 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 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.}}
|Status=No
}}{{Section
|sectionHeading=Editing exported data tables in Excel
|anchor=excel
|alignment=Vertical
|structuredtext=If you use Microsoft Excel to edit a CSV file, the program might re-format some of the data without any indication that these changes are being made. This can cause unexpected issues when the file is later imported back into Designer.

You can use the following guidelines to safely open a CSV file in Excel for editing. (The steps as described might differ slightly from your version of Excel. If you get stuck, you can use the help tool in Excel to find more information about how to perform that step in your version.)

*In Excel, open a new blank workbook. Go to the '''Data''' tab and select '''Get Data''' > '''From Text'''. 
*:{{NoteFormat|If you only have an option for combined '''Text/CSV''', you can enable the legacy wizard for importing a text file by going to '''File''' > '''Options''' > '''Data''' and enabling the '''From Text (Legacy)''' import wizard. Once enabled, you can then select '''Get Data''' > '''Legacy Wizards''' > '''From Text (Legacy)''' to open your file.|2}}
*Browse to the CSV file for the data table you want to open and select '''Import'''.
*In the '''Import Wizard''', choose '''Delimited''' as the original data type and click '''Next'''.
*:[[File:Des_data_tables_excel_02.png]]
*Select '''Comma''' as the '''Delimiter'''. Make sure to deselect any other checkboxes that are selected. Click '''Next'''.
*:[[File:Des_data_tables_excel_03.png]]
*In the '''Data preview''', the first column should now be highlighted. Hold down the '''Shift''' key and click on the last column to select and highlight all columns. You might need to scroll to the right, depending on how many columns your data table has.
*With all columns selected, select '''Text''' as the '''Column data format'''. Click '''Finish'''.
*:[[File:Des_data_tables_excel_04.png]]
*If prompted, in the '''Import Data''' dialog, select '''New worksheet''' for the data destination and click '''OK'''.
*:[[File:Des_data_tables_excel_05.png]]

The file is now opened safely for editing.
|Status=No
}}
}}