If Statement and Conditional Calculation in Custom Column

[ranadeep]

Hi,

I am new to Retool and I am trying to build a basic Income Expense Register. My app is connected to Google Sheet.

I have a column Entry Type whose options are 'Income' and 'Expense'. Then I have an Amount column and finally I want to create a Custom Column named Overall Balance that will calculate the value based on Entry Type.

If Entry Type is Income, it will add the Amount value of that row to the Overall Balance value of the previous row and if the Entry Type is Expense, it will deduct the Amount value of that row from the Overall Balance value of the previous row.

As in the table, the Overall Balance value for the three rows should be 20000, 12800 and 12780.

Can this be done?

[ScottR]

@ranadeep
Welcome to the forum!
In your custom column you would have to write the logic using a ternary operator to determine if the previous row is an expense or income and then deduct or add to the overall balance....
I am going to work on this and will post again...

OK here is what I was able to do but it is limited....
The limitation is figuring out how to store the balance as it increases or decreases so that the next row uses the previous row.... see below for code and screenshots... note that the reason also that my example is limited is because I am hardcoding the array into the table as an example...
{{currentRow['Entry Type'] == 'Income'? currentRow['Overall Balance'] +=currentRow.Amount: table8.selectedRow.data['Overall Balance'] - currentRow.Amount}}

[sbgermanm]

Try something like this, where BancoTable is my table. This get all the records, filter by date < this.date and then sum those amounts.

{{  BancoTable.data.filter(d => d.Date < currentRow.Date).map(d => d.Amount).reduce(function (sum, currentValue) {
    return sum + currentValue;
}, 0 ) + currentRow.Amount   }}