Manage database tables & table data
You can create database tables to store and retrieve information. During bot conversations, these tables help in storing user-specific data and displaying information in response to user inputs. This streamlined process greatly simplifies both data insertion and retrieval.
Some examples are creating a table containing city, pin code, and address columns to exhibit store addresses according to the chosen city or pin code, archiving user feedback within a database table, and managing product details in a table to exhibit information based on the selected SKU or name.
This article covers all the concepts remated to managing database tabbles in Studio.
We have either two or three environments, depending on the specific bot: Development, Staging, and Live. When it comes to database tables, they play a crucial role in storing and organizing data for your bot. These tables are typically created in the first environment, and when you publish the bot, they seamlessly move to the next environment in the hierarchy, as configured.
Create database table
- You can create a db table only in sandbox/development environment.
- Please verify that there are no duplicate values present and then proceed to store the data.
- You cannot delete the columns after you have added them.
- You can add unlimited columns to the table.
Go to Studio > Database.
Navigate to the base environment (Staging or Sandbox).
Click Create new table.
In Table name, enter a name for your table.
In Field name, enter a name for the field that you want to insert.
In Type, choose the datatype of the field. You cannot modify the datatype once the table is created.
Enable Searchable to make the field searchable inside the database node in flows. These fields are available for filtering and summarization in data explorer. You cannot modify this once the table is created.
Click Create table.
Edit database table
You can either add new columns to the current table or rename existing fields. Additionally, you can establish relation associations between tables.
To edit a table:
Go to Studio > Database and navigate to the base environment as per your environment hierarchy.
Select the table to edit and click Table actions > Edit table.
Modify the required columns.
You cannot modify the datatype or searchability of a column once created.
Enable Case insensitive search to find the field regardless of the case used in the search phrase.
To set up relations with other table, click Add relations.
- Column: Choose the column of the current table that you want to map.
- Name: Enter a name for the relation.
- Type: Choose the type of association.
- Related table: Choose the table that you want to associate.
- Related column: Choose the column that you want the current column to associate with.
Click Update.
Publish the bot to observe changes in the subsequent environment within the hierarchy. For instance, you can deploy updates from the Sandbox to Staging, and then further push changes from Staging to the Live environment.
Manually add records to a database table
You cannot transfer data from one environment to another. Any data present in an environment remains confined to that specific environment.
Add individual records to db table
To add each record manually:
Choose the environment where you want to add record.
In Studio, click Database, navigate to the table where you want to add a new record.
Click Add new record.
Enter values for each entry and click Add record.
Import bulk data into a db table
Adding multiple records manually to a table can be tiresome, but you can easily upload multiple entries through a CSV file.
To import table data:
- Create a CSV file with the data you want to upload.
Sample CSV file
To easily create a CSV file, first download the table (Table actions > Download) and add the records that you want to upload.
Go to Studio > Database and click the table for which you want to import data.
Click
.
- Select the file from your local system and upload it.
You will see a new record(s) created in the table post upload.
Import comes in handy when you want to a database table from an external source to Yellow.ai
Manage database records using the Database node
Insert new records or values to a database table
You can directly pass field values to the table from bot flows. For instance, in a lead generation flow, you might need to collect the user's email address, phone number, and interested product and store them in a datable table.
To insert data into the table, use the Database node and follow these steps:
In Select type, choose Insert.
In the in drop-down, choose the respective table (where to insert the value).
Click Add Records to select the respective column name.
Map each field with the respective value.
Update database table records using the database node
To update an existing field value of a table use the Database node and choose Select type as Update along with other configurations.
Fetch database records using the Database node
To search for a particular piece of information in a bot conversation, use the Database node and choose Select type as Search.
In Select columns of field, choose the columns to be searched
Under Response, In Sort By choose the column which will be rendered as a response to the user. Choose the order, ASC/DSC.
Enable More options for more enhancements.
Enable Create URL for extracted records to create a URL for the information exctracted from the search.
In Filter distinct, choose the column that should serve as distinct parameter in the search.
Under Pagination, enter the Page number and Size limit to display the records to the user. The page number and size limit can also be fetched from variables. If you do not want to use pagination, you can leave the fields empty.
The search reponse will be in the form of object. You can use the snippet {{{variables.variablename.records.0.fieldName}}} in a text node to display the response.
Filter table data
When a table contains numerous records, it can be challenging to scroll through each field to find specific information. However, you can simplify the process by filtering records based on fields containing a specific keyword.
To filter a certain set of data:
Go to the specific table and click Inserted date. The drop-down displays all the columns of that respective table using which you can search.
Choose the column in which you want to filter specific information, and use the Search box to filter records.
For example, the following screenshot says in the table
b_two_b_queries, get records whose company_name (column) has bites in it.
To get the records that were inserted or updated in a specific period, choose Inserted date or Updated date and select your preferred duration as shown here.
Mask database columns
The Super Admin of the bot can mask certain database columns. For example, passwords or any PII data stored in plain text. The data of the masked column will be visible as ***** instead of real values.
The masking of data happens at the application level in the backend. You cannot access it in any other way.
To mask columns:
Go to Table actions and choose Mask columns.
Choose columns that store sensitive information.
For example, you can see the column named ‘test’ with **** instead of real values.
Delete database table fields or table
You can delete a db table only in sandbox/development environment.
Go to Studio > Database and select the table to edit
Click Table actions and choose the preferred option.
- Truncate: Deletes all the data that exists in a specific table without removing the structure of the table.
- Drop: Deletes the entire table structure along with all its the records.