This PHPMaker 2024 project will show you how to create financial balance based on the transactions table via a Database View and calculate the value of Balance on each row.

There is only one table in this project. It is transactions table. Based on this table, then we can create a Database View named financial_balance which will change the view of the original table.

In Financial Balance menu, we will see the In and Out columns which originally came from Type field in transactions table. In addition, we create another new column named Balance.

Later, we will change the value in Balance column based on the value in In or Out column. Each row will produce the Balance information, and we fill-in the Balance column for each row by using ListOptions_Rendered server event.

We can also calculate the Total of each these three columns: In, Out, and Balance by optimizing the Aggregate function in PHPMaker. As you can see, the Total in Balance column is the result from the subtraction of In and Out column.

By using this project, then we will learn so many things in PHPMaker 2024:

  1. We can create a Database View in MySQL database in order to change the output of a table.
  2. We can create some new columns in a Database View in order to calculate the Balance on each row via PHPMaker project side.
  3. We can calculate the Balance based on the Balance and transaction type in the previous row of List Page.
  4. We can change the Balance while PHPMaker is rendering the row in List Page by using ListOptions_Rendered server event
  5. We can initiate a global variable for Balance in ListOptions_Load server event.
  6. Since those three new columns are created from Database side, then we can Export this data as usual using PHPMaker built-in feature.
  7. We can create a custom report that suits to our needs based on transactions table.