Ticketing Workflow and App

This laboratory focuses on creating a lightweight Extract, Transform, Load (ETL) Retool workflow process to pull ticket information from an external public API into Retool Database. Once the data is accessible in Retool Database, a Retool App can display ticket details.

Steps

Execute the following steps to perform ETL and visibility of tickets through a Retool App.

Create a Retool account and login to your account as shown below.

Create a table in Retool Database

Create table / define schema

To store ticketing information, RetoolDB will be employed.

Once you are logged into the Retool Home Screen, select Database > +.
Create a table called tickets. Select Add field to add user_name, ticket_summary and ticket_description fields.
Select Create to create the table.

The following shows using the AI Assist to use a natural language prompt.

Create a Retool Workflow

Retool Workflow provides a way to develop automated tasks. In this example, we are interested in getting a ticket using an API and storing within RetoolDB.

Select the Retool icon to return to the Retool Home screen.
Select Workflows.
Select Create new > Workflow. This will create a base workflow with a Start block and Code block.
Select the Code block, right-click and select Delete.

Modify start block to use Schedule

The Start block supports webhook and a scheduler to initiate a workflow.

Select the Start block, select Edit triggers and enable the Schedule.
Specify a time to run this workflow as shown in the following figure.

Create Resource query block to call API

In this step, we will add a Resource query block to invoke the ticketing system API.

Select Add > Resource query and place to the right of the Start block.
Connect the Start and Resource query block.
Rename the Resource query to callTicketInfoAPI.
Update the Resource query with the URL
- https://rg6skkbvfe.execute-api.us-east-2.amazonaws.com/PROD/getTicketInfo
Select the Run icon to confirm the API returns a result.

Create AI Action block to summarize

In this step, we will leverage an AI Action block to take the ticket description from the previous step and summarize it.

Select Add > AI Action and place to the right of the Resource query block.
Rename the AI Action to aiSummarizeDescription.
Connect the Resource query and AI Action block.
Select Summarize Text.
Specify the input as {{callTicketInfoAPI.data.caseDetails}}.

Create Resource query block to email notification

In this step, we will create a notification using Retool Email.

Select Add > Resource query and place to the right of the AI Action block.
Rename the Resource query to emailUser.
Connect the AI Action to the Resource query block.
Specify To with your email address.
Specify Subject with Received new ticket.
Specify Body with The following is a ticket summary: {{aiSummarizeDescription.data}}

Create Resource query block to insert ticket into RetoolDB

In this step, we will perform a database insert into RetoolDB table tickets.

Select Add > Resource query and place to the right of the AI Action block, below the Resource query block, emailUser.
Rename the Resource query to insertTicketIntoDB.
Specify the Resource as Retool Database.
Specify the following SQL to insert the ticket into the RetoolDB:

insert into tickets (user_name,ticket_summary,ticket_description) values ({{callTicketInfoAPI.data.user}},{{aiSummarizeDescription.data}},{{callTicketInfoAPI.data.caseDetails}});

Run Workflow to populate RetoolDB

Once the workflow is complete,

Select the Run button to execute the workflow.
Examine the Workflow logs to confirm all steps executed properly.
Examine Retool Database tickets table for new entries.
Select the Run button several more times to generate several more records in RetoolDB.
Confirm you are receiving emails for each run as shown in the following image.

Create a Retool Web App

With the Retool Workflow inserting tickets into Retool Database, a Retool Web App is the final piece to providing a ticket dashboard.

Create Retool Web App by selecting the Retool icon > Apps > Create > App
Select the X to remove the Get started panel. This should clear the canvas.
Change the name to TicketsApp from "User - Date" that it defaults to.

Create query to get ticket information from RetoolDB

To populate the Retool App, a query to get all tickets needs to be created.

Select Add > Code > + > Resource query
Change query name to getTickets and specify a resource of Retool Database.
Select AI Assist
Enter the prompt get all data from the tickets table. This will display a SQL Query. Select Accept.
Select Save & Run

The table and current contents will display in the Query Editor.

Minimize the Query Editor by selecting the - in the upper right corner of the dialog.

Create table and set to query results

Next a table component needs to be added to the canvas and have its to data set to getTickets.

Select Add > Components > Table(New) and drag to the canvas.
Select the table1 tag. This will expose the component inspector on the right.
Select Data source > getTickets

Modify table to support expanded rows and add container

This step will help introduce expandable rows in the table, allowing verbose information to be hidden / exposed.

In table settings enable Enable expandable rows
Expand a row and Add component > Container.
Within container1, select Add component > Text.
In component inspector for text1, specify a value of {{table1.data[i].ticket_description}}.
Update containerTitle1 to specify value of ##### Ticket Detail.

Update table to improve UX

The default table component will now be modified to show the ticket id, the support engineer, and ticket summary.

Select the table1 tag. This will expose the component inspector on the right.
Hide the ticket description.
Change the id label to Ticket ID
Change the user_name label to Support engineer.
Change the column type for Support engineer to Tag format.
Change the ticket_summary label to Ticket summary.

Completed examples

You can import these completed examples to compare but it will require that you configure the database table tickets:

Workflow: etl-tickets.json
App: TicketsApp.zip

Steps​

Create a Retool account and login​

Create a table in Retool Database​

Create table / define schema​

Create a Retool Workflow​

Modify start block to use Schedule​

Create Resource query block to call API​

Create AI Action block to summarize​

Create Resource query block to email notification​

Create Resource query block to insert ticket into RetoolDB​

Run Workflow to populate RetoolDB​

Create a Retool Web App​

Create query to get ticket information from RetoolDB​

Create table and set to query results​

Modify table to support expanded rows and add container​

Update table to improve UX​

Completed examples​