Faulty select expression

[SKETCHwade]

Hi,

I have this expression:

ANY(SELECT(Pre Start[Site],AND(MAX(Pre Start[_ROWNUMBER])=[_ROWNUMBER],[Name] = [User])))

Which is used to find the latest Site in the pre start table that was created by the user.
The user is referred to as column Name in the table this expression is from and referred to as User in the pre start table.

My issue is that it doesnt seem to return data. I am using it in initial value. I tried it as a formula and it did not output anything.

Any idea why its not working?

[Lynn]

Hi @SKETCHwade
What do you get when you break it down in the expression tester? Do you get any errors or results?

[SKETCHwade]

Ive been using appsheet for about a full year and did not know about this button. This changes everything haha.

Anyway,
The last row in the pre start table is from Laurie who is currently working at Eildon.
The result it shows is either blank or the user is laurie in which case it returns Eildon.

So I take it I now need to work out how to get the expression to look for the last entry of each name not just the last entry.

[Lynn]

Now you are cooking with gas.

[SKETCHwade · Accepted solution]

I couldnt figure it out so ive had to base it around other requirements.

ANY(SELECT(Pre Start[Site],AND(MAX(Pre Start[Date]) = [Date],[Name] = [User])))

Notably the latest date and the name give you the row and from this I collect the data I need.

[Steve]

MAX(Pre Start[_ROWNUMBER]) finds the highest row number in the entire Pre Start table without regard to the User column value. MAX(...)=[_ROWNUMBER] then asks, is this row’s row number the highest row number in the entire table? If you have multiple users, there’s a chance the highest-numbered row is not for the intended user.

Instead of finding the highest row number in the entire table, you need to find the highest row number of rows for the user:

MAX(
  SELECT(
    Pre Start[_ROWNUMBER],
    ([_THISROW].[Name] = [User])
  )
)

Then your expression would be:

ANY(
  SELECT(
    Pre Start[Site],
    (
      [_ROWNUMBER] = MAX(
        SELECT(
          Pre Start[_ROWNUMBER],
          ([_THISROW].[Name] = [User])
        )
      )
    )
  )
)

or:

LOOKUP(
  MAX(
    SELECT(
      Pre Start[_ROWNUMBER],
      ([_THISROW].[Name] = [User])
    )
  ),
  "Pre Start",
  "_ROWNUMBER",
  "Site"
)

[SKETCHwade]

Thanks @Steve eve for your excellent response!

I am trying to do a very similar expression on a different column, the goal being to find the last row -1 that matches the requirements.

This expression:

IF([How many more machines?] > 0,ANY(
SELECT(Pre Start[Equipment],
([_ROWNUMBER] = MAX(SELECT(Pre Start[_ROWNUMBER],
(AND([_THISROW].[Name] = [User],[_THISROW].[Date_Mo] = [Date]))))))),"")

Works great. But now I want the “find second last row feature”
I tried to add -1 to the rownumber couldnt work out where to put it.

The expression builder would only allow it here:

IF([How many more machines?] > 0,ANY(
SELECT(Pre Start[Equipment],
([_ROWNUMBER] = MAX(SELECT(Pre Start[_ROWNUMBER],
(AND([_THISROW].[Name] = [User],[_THISROW].[Date_Mo] = [Date]))))-1))),"")

Example Data

Row-User-Date
|85 | Bob |1/2
|86 |Todd|1/2
|87 | Bob |2/2
|88 | Gary|2/2

This ends up producing (with the example data from above, assuming we are after bob)
Todd’s data

Any Idea on how to find the second from last row that matches the criteria?

[Steve]

Because a particular user’s rows in the table may not be adjacent, simply subtracting one from a user’s highest row number may not give the row number for another of that user’s rows. If rows have been deleted from the table, it’s even possible the calculated row number may not even exist!

The approach here is to get the list of row numbers for the desired user’s rows, then get the second highest from that list:

INDEX(
  SORT(
    SELECT(
      Pre Start[_ROWNUMBER],
      ([_THISROW].[Name] = [User])
    ),
    TRUE
  ),
  2
)

1. SELECT(...) gathers the row numbers for this user’s rows (...; as we’ve done before).

2. SORT(..., TRUE) sorts the list (...; from (1)) from high to low (in “descending” order; per TRUE) so that the highest value is first.

3. INDEX(..., 2) gets the second item (2) from the list (...; from (2)), which should be the second-highest row number for the user. Note that INDEX(SORT(..., TRUE), 1) (1 rather than 2) is equivalent to MAX(...).

Use the expression above in place of the MAX() expression you tried:

IFS(
  [How many more machines?] > 0,
  ANY(
    SELECT(
      Pre Start[Equipment],
      (
        [_ROWNUMBER] = INDEX(
          SORT(
            SELECT(
              Pre Start[_ROWNUMBER],
              ([_THISROW].[Name] = [User])
            ),
            TRUE
          ),
          2
        )
      )
    )
  )
)

See also:

help.appsheet.com

IFS()

First-match conditional evaluation

help.appsheet.com

INDEX()

One specific list item

help.appsheet.com

SORT()

Sorted list items