Updating field based on a calculation

[Josh-7496]

Hi,

I have a client list table and a transaction list table. For each client there is an associated client ID, 1,2,3 etc and in my app I can add a transaction to a specific client. For example, for client ID 2 I can add a transaction that adds $50 to their account (cell in the client table). If I want to add a new transaction of $30 to Client ID 2 how do I update their account/cell so the cell will automatically sum the values, making the cell for Client ID 2 $80.

Thanks

[AndrewB]

Would need more specifics to give you the exact formula, but it would be something like this:

1. Add an overwrite step.

2. Take data from :
   

3. and write to:
   

Step 2 uses the findrow()[col] to get the current value in the cell, and then adds it to the value that I assume is in a data cell I named newTransaction.

Step 3 takes that sum, and overwrites the current value of the celll.

[Josh-7496]

Hi, sorry I should have been clearer in my request. I have two tables as follows:

Table - Clients

Table - Transactions

When a new row is added to the Transactions table, I would like to update [TotalCredits] field in the Clients table as the sum of all [Credits] with an idential [ClientID] in the Tranactions table.

I presume I need to do this as an automation when a new row to the Transaction table is added but I need help with the formula please.

Thank you for your continued help.

[Razi]

You can use the power of automatic formula recalculation within the table to sum the total credits.

The formula will automatically get recalculated whenever data in the transaction table changes - including when rows are added, when they are deleted, and when the value in the Credits column changes. There is no need for you to write any automation, or do any other special handling.

The best way to do this is to specify a Column formula. You can do this by selecting the Total Credits column (click in the column header), and then pressing the Formats button in the toolbar, and specify the following Column Formula:

=SUM(Filter(Transactions,"Transactions[ClientID]=%",[ClientID])[Credits])

This sets the formula for each cell in the TotalCredits column to have the above formula. The way this formula works is the following - shown in parts. The inner Filter is:

Filter(Transactions,"Transactions[ClientID]=%",[ClientID])

The above Filter formula collects all of the rows from the Transactions table. If you were to add a column in the Clients table with the above formula, you will see a filter with the matching rows.

We then sum the Credits column for all matching rows of the filter expression as follows:

=SUM(FilterExpression[Credits])

This will sum the Credits column from the filtered expression and place the value in the Clients table column.

Once you have specified this as a Column Formula, then changes in the Transaction table will result in a recalculation, and automatically get updated - no other special handling is needed.

Hope this helps.

Thanks,
Razi

PS. Generally we recommend using Rowlinks in Honeycode instead of ID's.

[Josh-7496]

Thanks so much for you for your help and your very comprehensive answer. Your solution worked perfectly. So impressed with the help from the Honeycode specialists.