How to display latest record for each account

[sbstrand]

Hi all,

I'm relatively new to AppSheet and have some background with databases but not much with UIs. I have an app that I am using for budgeting and I would like to be able to track my account balances over time. I have 2 tables: Accounts and Balances. The Accounts table has the name of the account and the account type. The Balances table references the Accounts table to pull the account name and then I enter the current balance and the date. 

I would like to have a view that shows the name and balance for each account, but only shows the most recent balance for each account. For example, if the table has this data

Account	Balance	Date
Acct 1	$10.00	1/1/24
Acct 2	$15.00	1/1/24
Acct 1	$12.50	1/15/24
Acct 2	$17.50	1/15/24
Acct 1	$18.25	2/1/24
Acct 2	$2.00	2/1/24

Then the view would only show:

Acct 1	$18.25	2/1/24
Acct 2	$2.00	2/1/24

Thank you!

[Markus_Malessa]

The MAXROW() function will likely be your solution here. Where it needs to be applied or implemented is a separate question as it is difficult to say how your data is structured. If for example you have Table1 with two rows Acct1 and Acct2, then you have Table2 with the dates and balances then I would suggest a virtual or real column in Table 1 like [Acct Max Row] with this formula:

MAXROW("Table2", "Date", [_THISROW].[Account] = [Account])

This will fetch the entire row. Then you could do a column for Latest Balance [Acct Max Row].[Balance].

[sbstrand]

Thank you! I tried creating 2 real columns in Table1: "Account Max Row" and "Current Balance". "Account Max Row" is a reference column and "Current Balance" is a Text column, are these types correct?

I added the formula you suggested to "Account Max Row" column, and then added [Account Max Row].[Balance] to the "Current Balance" column but I am getting this error:

"Column Account Max Row in expression '[Account Max Row].[Balance]' does not contain a reference"

I am also not seeing any values in the Account Max Row column, is that expected?

[Markus_Malessa]

You would need to add a temporary column (DateTime type) and add an action that that sets the column to NOW(), that will force calculate the field. This is where virtual columns work a bit better because the formula automatically recalculates.

[MultiTech]

This is a great approach and will get you the record you're looking for 💪, however MAXROW() and MINROW() are actually processed by the system as a nested SELECT() statements‼ 👀 

• Which means we should avoid them at all costs.

Here's a handy trick:

INDEX(
  [Related Whatevers], 
  COUNT([Related Whatevers])
)

• This processes with almost no time, even when the lists are huge (and I mean thousands of records long).

This is assuming the records are in the correct order already, which typically they are - as the records are typically created in order. 

But sometimes they can get out of order (like if you allow for retroactive dating), in these instances you need to handle things a bit differently, adding in one more layer to account for the re-ordering that needs to be done.

INDEX(
  OrderBy(
    [Related Whatevers], 
    [Order_Column]
  ), 
  COUNT([Related Whatevers])
)

• We don't need to reorder the records for the COUNT(), as it doesn't matter what order those are in - we're concerned with the number of them only.

_________________________________________________________________________________

Both of these approaches utilize the efficient system already in place when you create references, greatly reducing the computation overhead - while still allowing you to find the last value in the list.

• From here you can then dereference out the value you need from the most recent record
• [Last_Whatever].[My_Desired_Column]

[AleksiAlkio]

One approach is to add an Event action on the Balances_Form. The action type would be "Execute an action on a set of rows". Every time when the Balance record is added or updated, it triggers the action and finds the related account. Then you would need to have another action in your Accounts table that reads the latest row from Balances. With this approach you don't need to use any virtual column.