Aman Regu for ToolJet

Posted on Jun 6, 2024 • Edited on Jul 4, 2024 • Originally published at blog.tooljet.com

Building an Intelligent Reimbursement Tracking App using OCR with Gemini API + ToolJet 🚀

#gemini #ai #lowcode #tooljet

Introduction

This tutorial will guide you through building an intelligent reimbursement tracking app with OCR using ToolJet and the Gemini API. The app will allow users to upload images of receipts, extract text from the images using OCR, and store the extracted information in a database. We'll also add an AWS S3 integration to store the receipt images.

Prerequisites:

ToolJet (https://github.com/ToolJet/ToolJet) : An open-source, low-code business application builder. Sign up for a free ToolJet cloud account or run ToolJet on your local machine using Docker.
Gemini API Key : Log into Google AI Studio using your existing Google credentials. Within the AI Studio interface, you'll be able to locate and copy your API key.

Here is a quick preview of our final application:

Building our UI

Login to your ToolJet account. Navigate to ToolJet dashboard and click on the Create new app button on the top left corner. ToolJet comes with 45+ built-in components. This will let us set up our UI in no time.
Drag and drop the Container component onto the canvas from the component library on the right side. Adjust the height and width of the Container component appropriately.
Similarly, drag-and-drop the Icon and two Text components inside your container. We'll use these two Text components for our header and instructional text.
Select the Icon component, navigate to its properties panel on the right and select the ZoomMoney icon under its Icon property.
Change the font size and content of the Text component appropriately.
Drag and drop the File Picker and the Button component inside your container. We'll use the File Picker component to allow users to upload images of their receipts. The Button component will be used to trigger the OCR process.
Rename the File Picker component to fileUploader.
Adjust the width of the File Picker component according to your preference.
Change the colour and text of the Button component according to your preference.

We have implemented the user interface for uploading receipts. The next step is to create the UI for administrators to approve or reject expense submissions.

Click on the Pages icon on the left side of the screen. On the header of the Pages Panel, click on the + button to create a new page.
Drag and drop the Container component onto the canvas. Adjust the height and width of the Container component appropriately.
Drag and drop the Icon and a Text component inside your container. We'll use these two components for our logo and header text.
Drag and drop the Table component inside the container. We'll use the Table component to display the list of expense submissions. We'll later also add Action Buttons to approve, reject, and view the receipt image for each submission.
Drag and drop the Modal component inside the container. Open the Modal component and drag and drop the HTML component inside the Modal. We'll use the HTML component to display the receipt image.

Creating Queries

ToolJet allows connecting to third-party APIs using its REST API query feature. We'll use this to connect to the Gemini API to extract the text from the uploaded receipt images. ToolJet comes with built-in integration with AWS S3, we'll use this to store the receipt images.

Using ToolJet's Workspace Constants feature, create a new constant named GEMINI_API_KEY with your Gemini API key.
In the query panel, click the + Add button and choose the REST API option.
Rename the query to extractTextFromImage. In the Request parameter, choose POST as the Method from the drop-down and paste the following URL.

https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-pro:generateContent?key={{constants.GEMINI_API_KEY}}

Navigate to the Body section of the extractTextFromImage. Toggle on Raw JSON and enter the following code:

{
  "contents": [
    {
      "parts": [
        {
          "text": "In this image of a receipt, analyze the receipt image and return the following information in JSON format without any formatting or syntax highlighting: total_amount, date."
        },
        {
          "inline_data": {
            "mime_type":"image/jpeg",
              "data": "{{components.fileUploader.file[0].base64Data}}"
          }
        }
      ]
    }
  ]
}

Next, we'll create a query to save extracted text to the ToolJet database.
Click on the ToolJet logo on the top left corner and select the Database option.
Click on the Create new table button and name the table reimbursement_requests. Add the following columns to the table: id, name, email, total_amount, status and receipt_date.

Navigate back to the Query panel and click on the + Add button. Choose the ToolJet Database option.
Rename the query to addReceiptData.
Select the reimbursement_requests table name from the drop-down.
In the Operation drop-down, choose Create row and enter the following data for the columns.

Column Name	Key
email	`{{globals.currentUser.email}}`
name	`{{globals.currentUser.name}}`
total_amount	`{{JSON.parse(queries.extractTextFromImage.data.candidates[0].content.parts[0].text).total_amount}}`
receipt_date	`{{JSON.parse(queries.extractTextFromImage.data.candidates[0].content.parts[0].text).date}}`

Next, we'll create a query to store the receipt image in the AWS S3 bucket.
Create a new data source to connect to the AWS S3 using ToolJet's built-in AWS S3 integration.
Click on the + Add button in the Query panel and choose the newly created AWS S3 data source.
Rename the query to addToS3 and select the operation as Upload object.
Add the following data for the columns:

Column Name	Key
Bucket	bucket name (should already exist)
Key	`{{"reimbursement_id" + "_" + queries.addReceiptData.data[0].id}}`
Content Type	`{{components.fileUploader.file[0].type}}`
Upload Data	`{{JSON.parse(queries.extractTextFromImage.data.candidates[0].content.parts[0].text).date}}`
Encoding	base64

Next, we'll create the query to fetch the list of expense submissions from the ToolJet database.

Click on the + Add button in the Query panel and choose the ToolJet Database option and rename the query to getReimbursementRequests.
Select the reimbursement_requests table from the drop-down.
In the Operation drop-down, choose List rows.
To ensure that the query runs every time the application loads, enable the Run this query on application load? toggle.
Next, create two more ToolJet database queries to approve and reject the expense submissions, named approveRequest and rejectRequest.
For both of these queries, select the reimbursement_requests table and choose the Update row operation and to use the filter field to match the id of the row to be updated using the {{components.reimbursementRequestsTable.selectedRow.id}} variable.
In the columns field, update the Status column to approved for the approveRequest query and rejected for the rejectRequest query.

Let's add our last query to fetch the receipt image from the AWS S3 bucket.

Click on the + Add button in the Query panel and choose the AWS S3 data source and rename the query to getReceiptImage and enter the following data.

Column Name	Key
Operation	Signed url for download
Bucket	bucket name
Key	`{{"reimbursement_id" + "_" + components.table1.selectedRow.id}}`
Expiries in	3600

Binding Queries to the UI Components

Now that we have successfully built our UI and queries, the next step is to integrate them.

Select the Button component and navigate to the properties panel on the right. Click on the + New event handler button. Change the Action to Run query and select the extractTextFromImage query.
Next, navigate to the extractTextFromImage query and click on the + New event handler button. Change the Action to Run query and select the addReceiptData query.
Navigate to the addReceiptData query and click on the + New event handler button. Change the Action to Run query and select the addToS3 query.
Our upload receipts feature is now complete. You can test this out by uploading a receipt image and verify the extracted data in your ToolJet database.

Next, we'll implement the admin approval feature.

Navigate to the Admin page and select the Table component. In the properties panel on the right, enter {{queries.getReimbursementRequests.data}} in the Data field.
Click on the + New action button in the properties panel and create three new actions buttons: Approve, Reject, and View Receipt. Bind the approveRequest and rejectRequest to the respective action buttons using Event Handlers.
Change the background colour and text of the action buttons according to your preference.
Click on the View Receipt action button and Add a new event handler. Change the Action to Open Modal and select the displayReceiptImage modal.
Navigate to the displayReceiptImage modal and in the properties panel on the right, uncheck the Use default trigger button toggle.
Inside the modal, click on the HTML component and enter <img src={{queries.getReceiptFromS3.data.url}}> in the Raw HTML field.
Our admin approval feature is now complete. You can test this out by uploading a receipt image and verifying the extracted data in the Table component. You can click on the Approve or Reject buttons to approve or reject the expense submission. You can also view the receipt image by clicking on the View Receipt button.

Conclusion

Congratulations! You've successfully built a powerful reimbursement tracking app with OCR capabilities using ToolJet and the Gemini API. You can now track and manage expense submissions with ease. Feel free to customize the app further by adding more features and functionalities.
To learn and explore more about ToolJet, check out the ToolJet docs or connect with us and post your queries on Slack.

Top comments (4)

Sahil Rana • Jul 3 '24

When try to run extractTextFromImage query, got this error.
statusCode:400
responseBody:"{ "error": { "code": 400, "message": "Invalid JSON payload received. Unknown name \"\": Root element must be a message.", "status": "INVALID_ARGUMENT", "details": [ { "@type": "type.googleapis.com/google.rpc.BadRequest", "fieldViolations": [ { "description": "Invalid JSON payload received. Unknown name \"\": Root element must be a message." } ] } ] } } "

Aman Regu ToolJet • Jul 4 '24

Hey Sahil! Thanks for pointing this out. I've updated the JSON payload. Could be please try again? Let me know if you have any other issues!

Sahil Rana • Jul 4 '24 • Edited

Thanks. @amanregu
In addReceiptData I got below error:
status:"failed"
message:"Invalid input syntax for type double precision: " ""

Aman Regu ToolJet • Jul 4 '24

Hey @rsr0. Thanks again for pointing this out! I think the values I was testing with only contained numbers. That's why I had set the total_amount column data type as float. You can change the data type for total_amount to varchar in the ToolJet database and this would resolve your issue. Could also please recheck and update your keys for the queries addReceiptData and addToS3. Please let me know if you face any other issues!