DEV Community

Cover image for Building an Intelligent Reimbursement Tracking App using OCR with Gemini API + ToolJet πŸš€
Aman Regu for ToolJet

Posted on • Edited on • Originally published at blog.tooljet.com

Building an Intelligent Reimbursement Tracking App using OCR with Gemini API + 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:

Here is a quick preview of our final application:

Image description

Image description


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.

Image description

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.

Image description


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}}
Enter fullscreen mode Exit fullscreen mode
  • 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}}"
          }
        }
      ]
    }
  ]
}

Enter fullscreen mode Exit fullscreen mode
  • 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.

Image description

  • 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.

Image description


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)

Collapse
 
rsr0 profile image
Sahil Rana

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." } ] } ] } } "

Collapse
 
amanregu profile image
Aman Regu ToolJet

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!

Collapse
 
rsr0 profile image
Sahil Rana • Edited

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

Thread Thread
 
amanregu profile image
Aman Regu ToolJet

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!