Find latest row that matches these criteria

[SKETCHwade]

Hi,

I am trying to create an expression that I can use in a columns initial value.
I want it to return the latest row that matches column Equipment.
I have this:

ANY(
SELECT(Pre Start[Equipment],
([_ROWNUMBER] = MAX(SELECT(Pre Start[_ROWNUMBER],
([_THISROW].[Equipment] = [Equipment]))))))

Which is interpreted by appsheet as:

One randomly chosen value from this list (
…The list of values of column ‘Equipment’
…from rows of table ‘PRE START’
…where this condition is true: ((The value of column ‘_RowNumber’) is equal to (MAX(
…The list of values of column ‘_RowNumber’
…from rows of table ‘PRE START’
…where this condition is true: ((The value of ‘Equipment’ from the row referenced by ‘Timestamp’) is equal to (The value of column ‘Equipment’))))))

I tried to do this:
ANY(
SELECT(Pre Start[Equipment],
AND(([_ROWNUMBER] = MAX(SELECT(Pre Start[_ROWNUMBER]))),
([_THISROW].[Equipment] = [Equipment]))))

But the select function becomes invalid

Any ideas on how I could select the latest row where equipment matches equipment?

[Steve]

SKETCHwade:

But the select function becomes invalid

What does this mean?

[SKETCHwade]

When I put in the expression I was referring to Appsheet says:
SELECT has invalid inputs

[Steve]

Please provide a screenshot of Expression Assistant showing both the expression and the error.

[SKETCHwade]

[Steve]

Extra parentheses:

And the AND() isn’t needed.

Try this:

ANY(
  SELECT(
    Pre Start[Equipment],
    (
      [_ROWNUMBER] = MAX(
        SELECT(
          Pre Start[_ROWNUMBER],
          ([_THISROW].[Equipment] = [Equipment])
        )
      )
    )
  )
)

Which is the first expression in your original post.

SKETCHwade:

I have this:

ANY(
SELECT(Pre Start[Equipment],
([_ROWNUMBER] = MAX(SELECT(Pre Start[_ROWNUMBER],
([_THISROW].[Equipment] = [Equipment]))))))

[SKETCHwade]

When I use the formula you recommended it doesnt seem to work.
Although It looks like it did attempt to input data.

Attempting to go to the next page when all answers are required shows that the app believes there is an input for the column.

I believe the formula is looking for the highest row number in the table without taking into account whether that cell contains data. I think it is returning blank due to this. I just dont know how to fix it. This is why I was trying to get the formula to look for the highest row number AND contains the same equipment value.

[Steve · Accepted solution]

I think you want this:

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

How do I get a column value from the last row of this thing only? LOOKUP( MAX( SELECT( My Table[_ROWNUMBER], ([_THISROW].[Thing] = [Thing]) ) ), "My Table", "_ROWNUMBER", "Wanted Column" ) Replace My Table with the name of the table from which you want the column value; Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID); and Wanted Column with the name of the column whose value you want. See also: MAX()

[SKETCHwade]

Thank you Steve, this worked great.

I will have to bookmark this tip

[111139]

Hi steve

How can i create a virtual column in which can select the last row (last updated) of a related list.

Now what i got is to show all the colomn from the related list

([Related checklists][status]) like this

But i dont know how to make it show only the last row (last update status)

Thank you

[Steve]

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

How do I get the last row of this thing only? MAXROW( "My Table", "_ROWNUMBER", ([_THISROW].[Thing] = [Thing]) ) Replace My Table with the name of the table whose row you want; and Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID).

[nick62]

Thanks Steve.  This solved my query.  I have 2 tables with a parent-child relationship.  One record from the parent can have many records in the child.  I wanted the status update from the child to update the parent, but only the last update from the child, so ignoring previous updates.  I had to tweak your expression above in one way: The first "thing" is from my parent table and the second "thing" is from my child table.  Works like a charm

[jason290699]

I don’t want it to return me a “randomly” chosen value. I want it to return me the last Status of a Tracking Number from the Log Sheet. Updates of the status of each tracking number will be added, thus the same tracking number will be present in the Log Sheet, but with (the same) or different status.

It also says “Note, this expression could significantly impact sync time.” How significantly will it impact the sync time?

Should I just use the following?

MAXROW(
“My Table”,
“_ROWNUMBER”,
([_THISROW].[Thing] = [Thing])
)