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

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

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])
)