Skip to main content

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​

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.
Scheduled Trigger

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.
Notification Email

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.
Tickets App

Completed examples​

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