Telerivet supports creating custom data tables, which you can use within automated services built with the Cloud Script API, in "run JavaScript code" actions within the Rules Engine, and via the REST API. Essentially, you can use custom data tables as a simple database without needing to run your own servers or install your own database software.
Custom data tables are primarily used for one of two purposes:
- Building data-driven services that can easily be updated or reused without modifying code
- Storing results from services (such as survey responses, or data extracted from structured messages), and providing a way to easily filter or export data
When using a Poll service, Telerivet will automatically create a custom data table to store the responses for each poll (see Viewing and Filtering Poll Responses). Telerivet will also automatically create custom data tables to store responses for automated services that use "wait for response" actions, with a separate field corresponding to each response handled by the service.
You can also manually create your own data tables and data rows within the Telerivet web app, or create the data table and data rows via the Cloud Script API or REST API.
Manually Creating a Data Table
If the Data Tables tab is not already visible in your project's menu, go to the Services page (after creating an automated service) and click More > Data tables.
From the Data Tables page, click Add new table, then enter a name for your table.
Next, add each of the data fields (i.e. columns) in your table. Like custom contact fields, each data field can have one of the following types:
- Text
- Multi-Line Text
- Secret
- Phone Number
- Email Address
- URL
- Audio
- Date
- Date/Time
- Number
- Yes/No
- Checkbox
- Dropdown
- Radio Buttons
- Route
To update the data fields at any time, open the data table and click More > Manage fields.
In addition, for data tables where rows are associated with a particular contact's response, the data table can optionally keep track of the contact who submitted the response. If you are manually creating a data table with rows associated with a particular contact, click More > Edit table settings then enable "Associate each row with a contact". This setting is automatically enabled when data tables are created to store responses for automated services.
Manually Adding and Editing Data Rows
Once your data table is created and your data fields are defined, you can use the Telerivet web app to add and edit rows in your table.
To add a new row, click Add New Row. To edit an existing row, simply click a column within that row.
Creating and Updating Data Tables via Cloud Script API
In many cases, the most convenient way to create data tables is simply to use the Cloud Script API method project.getOrCreateDataTable(name), which will create an empty table with a given name. Whenever you store a data row via table.createRow(options), or update an existing row via row.save(), Telerivet will automatically create a data field for any of the custom variables in the row (for example, row.vars.precinct_id).
You can also specify a custom type, display name, and other metadata for each field using the Cloud Script API method table.setFieldMetadata(variable, options).
If fields are not defined using the table.setFieldMetadata method, data fields will automatically be created with a particular type depending on the first row added with that field:
- Number if the variable is numeric
- Yes/No if the variable is boolean
- Multi-Line text if the variable is a string with multiple lines
- Text, otherwise
Creating data tables "on demand" within your service is especially convenient for situations where you might reuse a Cloud Script API service within multiple projects, or make it available for other Telerivet users to use in their own projects.
See example code »
Viewing, Filtering and Exporting Data Rows
To view the data in any data table, click the Data Tables tab, then click the table you wish to view.
The checkboxes at left make it easy to filter rows by certain conditions, in the same way you filter contacts on your Contacts page.
To save your data or perform additional analysis, Telerivet makes it easy to export your results to spreadsheet programs like Microsoft Excel. To export your data to CSV, click More > Export data, then open it in a spreadsheet program on your computer.
Saving Filters as Data Table Views
If a particular data table filter needs to be accessed repeatedly, you can save the filter as a "view". Each view appears as a separate tab in the left sidebar when viewing the data table rows.
To define a data table view, select a filter from the left sidebar, then click "Create view".
Viewing Data Table Summary Charts
The Summary tab for each data table makes it easy to visualize the most common values for a field in your data table as a pie chart, bar chart, or table. The Summary tab is visible only when "Show summary charts" is enabled under More > Edit table settings.
Summary charts are most useful for visualizing data in fields that have a limited number of pre-defined values, such as responses to multiple choice questions. The chart will display the most common values stored in a particular field in the table, and the data can be sorted either by the value in alphabetical or numeric order, or the count of rows with each value in descending order.
If the Custom Dashboards feature is enabled for your account, you can add a data table summary chart to the dashboard via More > Add to dashboard.
Viewing Time Series Charts for Data Tables
Telerivet automatically tracks the number of rows stored at the end of each day in each data table, as well as each data table view.
To explore the time series data for data tables, select a data table from the Data Tables page, then click More > Time series chart.
For more information, see Time Series Charts.