MAX is not working as intended

AppSheet AppSheet Q&A
, ,
1

Hi,

I’m trying to find MAX value from the PID column where the first 6 digits of the PID match with the Start Date.

MAX(
  SELECT(
    Plan[PID],
    LEFT([PID], 6) = TEXT([Start Date], "YYMMDD")
  )
)

am I missing something?

2

Is the SELECT() returning the list you expect?

1 Like
3

I don’t think so but when I tried to verify it, I couldn’t figure it out how to do it. Could you please help? PID type is Number so it doesn’t show the List.

4

Ok, I just figured out a way to verify it. The real culprit is SELECT(). what would be the correct way to use SELECT()?

5

Hi Steve,

I think I got this working by adding [_THISROW] in front of [Start Date]

MAX(
  SELECT(
    Plan[PID],
    LEFT([PID], 6) = TEXT([_THISROW].[Start Date], "YYMMDD")
  )
)

but it started breaking my LINKTOFORM() function as described in Solved: LINKTOFORM() not pre-filling value if column has I… - Google Cloud Community

can you please help?

6

The other post is solved, so it isn’t clear to me what the problem is now.