Karan Rathod for ToolJet

Posted on May 16 • Edited on Jul 1 • Originally published at blog.tooljet.com

Building a Personal Finance Tracker Using ToolJet

#webdev #javascript #beginners #tutorial

Building apps on ToolJet is fast and convenient, but it gets even better when you couple it with ToolJetDB (ToolJet's Internal Database). In this tutorial, we will build a Personal Finance Tracker application using ToolJet and ToolJetDB.

Here's a preview of what we are going to build:

Prerequisites:

ToolJet (https://github.com/ToolJet/ToolJet) : An open-source, low-code platform that streamlines the process of building and deploying internal tools. Sign up for a free ToolJet cloud account here.
Basic JavaScript: Knowledge of basic JavaScript can be handy for implementing dynamic functionality in your ToolJet applications.

1. Creating the Database

Let's jump right into the development process. Log in to your ToolJet account and click on the ToolJet Database icon in the left sidebar.

Create a new database table with the following columns and rename it to personalFinanceTracker:

id (primary key/auto-generated)
month(varchar)
income (int)
description (varchar)
expense (int)

Add some dummy data to the table so that we have something to work with when we begin the app-development process.

2. Developing the UI

Once the database is ready, click on the Apps icon to move to Applications section and create a new application named Personal Finance Tracker.

Now we will utilize ToolJet's pre-built components to quickly spin-up the UI of the app.

Let's begin with the header of the application.

Drag and drop a Text component on the canvas from the components library on the right and rename it to header.
Click on the Text component to see its configuration options in the Properties Panel on the right.
Enter Personal Finance Tracker under its Data property, change its color to dark blue (Hex code - #36405B) and font size to 20.

Time to create the income and expense input sections.

Drag and drop a Container component. Containers are used to group related components in the App-Builder.
Inside the Container component, add a Text component for the Income header.
Under the Text component, add a Text Input component followed by a Number Input component.
Rename them to incomeDescriptionInput and incomeInput respectively. Set the default value of incomeInput component to 0.
Add a Button component below the Number Input component and rename it to addIncomeButton. Also change its Button Text property to Add/Update Income.

Renaming the components can be handy when you want to refer the values inside them in queries.

Copy the Text, Text Input, Number Input and Button components and paste them right below the Income section to create the Expense section.
Rename the Text Input, Number Input and Button components to expenseDescriptionInput, expenseInput, and addExpenseButton respectively.
Also add two Statistics Components below the addExpenseButton component.
Rename the Statistics components to monthlyIncome and monthlyExpense. We will use them to display the monthly income and expenses.

We also need to list all the income and expense entries. We can handle that with the Table component.

Add a Table component next to the Container component. Rename it to entriesTable and remove the sample data under its Data property.

3. Fetching and Binding the Data

We had created a database table named personalFinanceTracker with some dummy data in the first step. Let's use the low-code query builder to create a query that fetches the data from the database. We will then use it to populate the Table component with it.

Expand the Query Panel at the bottom and click on the Add button to create a query.
Rename the query to listEntries.
Select personalFinanceTracker as the Table name and List rows under Operations.
Enable Run this query on application load? to ensure the query runs every time the app is loaded.
Click on the Run button to run the query and check the response under Preview.

To populate the entriesTable(Table) component using the data returned by the listEntries query, follow the below steps.

Select the entriesTable component.
Navigate to its properties and pass the below value under its Data property: {{queries.listEntries.data}}
Delete the id column under the Columns property and re-arrange the columns so that we have the columns in the following order: Month || Income || Expense || Description

In ToolJet, you can use double curly braces to refer dynamic values or use custom code.

Let's create two more queries to add income and expenses. We will bind them to the addIncomeButton and addExpenseButton respectively.

Create a new query named addIncome with the below configuration:

We have used the following mappings in the query:

month - {{moment().format('MMMM')}}
income - {{components.incomeInput.value}}
expense - 0
description - {{components.incomeDescriptionInput.value}}

Create another query and name it addExpense with the below configuration:

Below are the mappings:

month - {{moment().format('MMMM')}}
income - 0
expense - {{components.expenseInput.value}}
description - {{components.expenseDescriptionInput.value}}

Now let's bind these queries to the addIncomeButton and addExpenseButton components.

Select the addIncomeButton, navigate to its properties and create a new event by clicking on New event handler.
Select On click as the Event, Run Query as the Action and addIncome as the Query.

Add another event to the addIncomeButton that runs the listEntries query on click.

Now clicking the addIncomeButton triggers the addIncome and listEntries queries. This setting adds a new income record to the database and refreshes the entriesTable with updated data.

Use the same logic for the addExpenseButton and trigger the addExpense and listEntries queries when the addExpenseButton component is clicked.

Now every time you enter values and click on the related buttons, new entries will be added to the table.

The main functionality of our application is ready. Let's update the Statistics components so that they display the monthly overview.

Create a new Run JavaScript code query and rename it to monthlyOverview.
Paste the below code in the code input field:

await queries.listEntries.run();

let allData = queries.listEntries.getData();

function calculateCurrentMonthTotals(data) {
    // Get the current month in 'MMMM' format, e.g., 'May'
    const currentMonth = moment().format('MMMM');

    // Initialize sums for income and expense
    let totalIncome = 0;
    let totalExpense = 0;

    // Iterate through each data entry
    data.forEach(entry => {
        // Check if the entry's month matches the current month
        if (entry.month === currentMonth) {
            // Sum up income and expense
            totalIncome += entry.income;
            totalExpense += entry.expense;
        }
    });

    // Return the calculated totals in an object
    return {
        income: totalIncome,
        expense: totalExpense
    };
}

const currentMonthTotals = calculateCurrentMonthTotals(allData);
return currentMonthTotals

This code retrieves all entries from a database, then calculates and returns the total income and expenses for the current month.

Pass the returned data to the Primary value property of the Statistics components.

Enter {{queries.monthlyOverview.data.income}} for the monthlyIncome component and enter {{queries.monthlyOverview.data.expense}} for the monthlyExpense component.
Adjust the color of the monthlyIncome component's primary value to light green( hex code #36AF8B), and set the monthlyExpense component's primary value to dark red (hex code - #D0021B).

With that, our personal finance application is fully complete🎉.

Summary

With the completion of this tutorial, we now have a functional Personal Finance Tracker application, built using ToolJet and ToolJetDB. This application demonstrates how you can use ToolJet's visual app-builder and low-code query builder to create apps 10x faster. To explore more, checkout ToolJet docs or join us on slack.