How do I de-refernce a value from the row returned with MAXROW()?

[WillowMobileSys]

Just thinking this must be simpler.

I am cycling through a set of Actions to insert a computed number of Teams. So, I know the number of teams to add and to determine if I have inserted the correct number, I am tracking a Team # value. Once this Team # matches my expected number of team to be inserted, I know I am done.

With each iteration, I want to grab the Team row with the maximum Team # and then simply add 1 to it.

I can get the maximum Team # row like so:

MAXROW("Teams", "Team #", [Challenge] = [_THISROW].[Challenge ID])

This returns a row reference to that Team row. Is there some straight-forward way to dereference the Team # value from row reference?

So far the best I could figure out is that I need to do the below but it seem unnecessarily repetitive.

ANY(SELECT(Teams[Team #], 
     [Team ID] = MAXROW("Teams", "Team #", [Challenge] = [_THISROW].[Challenge ID]))
) + 1

[Steve]

WillowMobileSystems:

Is there some straight-forward way to dereference the Team # value from row reference?

Nope.

[Marc_Dillon]

New Formula Please: LOOKUP(MaxRow(...), ...) Hibrid Feature Requests

Many times when I’m build an app, I need to know: What was the last of something, and what was X value from that something. In order to accomplish this now, I have to either: build in an additional column (to hold the result of the MaxRow()), or “double-up” formulas (which is not the most advisable thing, especially if you have large tables) For Example: Let’s say I have an Orders table, and I want to know the “Sales Person” that entered the last order. To accomplish this inside a single…

[WillowMobileSys]

I agree with your observation to be able to do something like:

(Function Ref Result).[Ref table column]

The funny thing is that the parser already supports this!!

And I don’t think it would take much to then include:

[Ref Column].[Ref Column].[Ref Column]...[Column_I_Want]

I fairly certain this construct is supported in the source code used to build out the AppSheet platform. It’s just a matter of tapping into it from expressions.

[Steve]

WillowMobileSystems:

The funny thing is that the parser already supports this!!

In that it doesn’t complain about it. That doesn’t mean it supports it. There are at least several constructs the parser allows but doesn’t support. For instance:

table[column1].[column2]

Not supported, but allowed by the parser. Equivalent to:

[column1].[column2]

In fact, the table name there is completely ignored. You could put literally any word there, table name or not.

[WillowMobileSys]

Steve:

In that it doesn’t complain about it.

Yeah that’s really what I meant.

[MultiTech]

WillowMobileSystems:

[Ref Column].[Ref Column].[Ref Column]...[Column_I_Want]

Chaining of refs is already here. But it won’t work from a MAXROW() (It has to be a legit ref (or maybe enum-ref would work).

I’ve not played with ref chaining much - I always forget it’s a thing

Chained Derefs Questions

So… uh… How long have we been able to do this? [image]

Praveen’s answer is classic (^_^)

[Bahbus]

Yeah, we need the ability to deference from any singularly returned Key value.

[WillowMobileSys]

Bahbus:

Yeah, we need the ability to deference from any singularly returned Key value.

Definitely will save a lot of coding effort to be able to utilize such a capability.

[WillowMobileSys]

MultiTech_Visions:

Chaining of refs is already here.

Awesome! I have a use case right now to try it out.

Been out since Nov? I’m not even going to think of all the work around code and extra columns I added in the past several months simply because I THOUGHT I couldn’t chain references!! I know there were several.

Oh well, c’est la vie! … and software development!!

[MultiTech]

WillowMobileSystems:

Been out since Nov? I’m not even going to think of all the work around code and extra columns I added in the past several months simply because I THOUGHT I couldn’t chain references!! I know there were several.

Oh man… for real

EVERY time I come back to the thought that we can do this now, I’m like… ugh. SOOOOO much updating work to be done.