How can I select the most recent record from ...

vicodu_Odu · 12-24-2018 04:00 PM

How can I select the most recent record from a list?

Suvrutt_Gurjar

For getting the reference to the most recently created record in the same table, please refer to MAXROW() function described in the article below. The function returns the "Key " of the most recently created record.

help.appsheet.com - List Expressions and Aggregates List Expressions and Aggregates help.appsheet.com

Suvrutt_Gurjar

If you are looking to get the most recent child record from a child record in a parent table for each parent record , then please refer to the sample app below

appsheet.com - Most Recent Child Row - This app shows how to get the most recent related row for a table Most Recent Child Row - This app shows how to get the most recent related row for a table appsheet.com

Steven_Coile

To get the row at the bottom of the spreadsheet, the row with the highest row number:

MAXROW(“TableName”, “_ROWNUMBER”)

replacing TableName with the name of your table.

If your table has a timestamp column (Timestamp), and you want the row with the most-recent timestamp:

MAXROW(“TableName”, “Timestamp”)

vicodu_Odu

@Suvrutt_Gurjar

+Steve Coile

Awesome thanks

Pointer

Most Recent Child Row

In this example, how do I select the largest dependent of the column condition, not the largest of all column values?

MAXROW(“Child”, “Timestamp”, [_THISROW] = [Parent])

Steve

The example you provided will return the row of the Child table with the highest value in the Timestamp column from among only those rows where the Parent column value is equal to the current row’s key column value. How is that not “the largest dependent of the column condition”?

MultiTech

Hmmmm… as a complete aside and note to @Steve: while the syntax above works, I can see now why people in the community are confused about when and where to use [_thisrow].

Usage like this is confusing to people that don’t have a foundation in relational databases; they understand that the object of a record is represented by a key value.

Something that I’ve found makes more sense to the general public is:

MAXROW(“Child”, “Timestamp”, [Parent] = [_thisrow].[Key_Column])

In this example I’ve changed the way we’re calling the Key value for the record the formula is evaluating on; instead of saying to the system, “Give me the object that represents the record,” we’re saying, “give me the [Key_Column] value from [_thisrow]” making use of the familiar de-reference syntax.

Just noticed the syntax in the app and two things clicked together - thought I would point it out.

Pointer

Unfortunately, I was not successful, I do not get the error, but I can not get the result I want.

MAXROW(Child, checkin date, [_THISROW(add)]=[key].[add col.(b)])

not result:b

Pointer

Can you help me?

Steve

Please provide a screenshot of the columns list for the Child table from the Data > Columns tab of the app editor.

rmsmeltz

Yeah, I’m one of the ones that doesn’t get it. Where are we putting the MAXROW(“TableName”, “_ROWNUMBER”) expression? I’ve tried the _RowNumber column, security filter on the table, filter on the slice and filter view. No dice.

Pointer

You must add the expression by adding a virtual column to the table you want to show. If you add a normal column you will need to update the table.

rmsmeltz

Pointer

Please check here.

Marc_Dillon

The MAXROW( table , col ) expression is technically a “wrapper” for:
ANY(FILTER( table , [col] = MAX( table[col] ) ) )
That is why the error mentions the expression MAX().
Did you by chance change the Data Type of the _RowNumber column for your 2D Create Table?

rmsmeltz

Thanks, Marc.
I had and I switched it back, but I’m still having difficulty. Maybe I need more coffee…

Steve

A table’s security filter expression cannot reference itself; i.e., the security filter expression for the 2D Create table cannot reference the 2D Table. The expression can only reference other columns in the same row, or values from other tables.

rmsmeltz

I wish my brain would help a little.