Find latest row that matches these criteria

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?

What does this mean?

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

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

Capture

1 Like

Extra parentheses:

image

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. :confused:

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.

I think you want this:

2 Likes

Thank you Steve, this worked great.

I will have to bookmark this tip :+1:

1 Like