MAXROW() not returning key

[alphacp]

I am using MAXROW to return the last record added to a child table but with an expression included:

MAXROW(“Current”, “_ROWNUMBER”, ([_THISROW].[Key] = [Member]))

“Current” is actually a slice of a table called History because I needed to conditions two my expression and I got the same result when trying that so I added a slice. Ideally my expression would be this:

MAXROW(“History”, “_ROWNUMBER”, (AND([_THISROW].[Key] = [Member],“Current” = [_Filter]))

I have a Members table and for each Member I have a history of status changes in a History table. The data in my History Tables is as follows:

_RowNumber Key Member Status Section _Filter
[Number] [Text] [Ref] [Ref] [Ref] [Text]
2 8cc13f6a 284bc465 I d3be1b10 Future
3 9a8847f9 284bc465 I be46f2a0 Future
4 76fc95db 284bc465 O be46f2a0 Future
5 d2685b57 284bc465 S be46f2a0 Current
6 38927c8f 284bc465 O eef91aea Future
7 3032901f 7d792ad5 S d3be1b10 Current
8 a3a3487a 7d792ad5 S be46f2a0 Current
9 b9146e21 7d792ad5 O eef91aea Future

In testing, I am returned the value “7d792ad5” which matches the Key of the member and not “a3a3487a” which is the key of the row in History table.

I was hoping actually that I would get the value “Started” but if I have the row key I can then do a LOOKUP to get the full text string.

Any thoughts?

[LeventK]

So you have this expression in Members table or History table?

[alphacp]

In the Members table

_RowNumber Number
Key Text
Name Name
Status Ref = MAXROW(“Current”, “_ROWNUMBER”, ([_THISROW].[Key] = [Member]))

[LeventK]

So Member table is the parent and the History table is the child, correct?

[Steve]

alphacp:

I needed to conditions two my expression

Huh?

alphacp:

“Current” is actually a slice of a table called History because I needed to conditions two my expression and I got the same result when trying that so I added a slice.

Shouldda figured this out. Adding the extra slice just complicates things.

alphacp:

_RowNumber Key Member Status Section _Filter
[Number] [Text] [Ref] [Ref] [Ref] [Text]

This notation is confusing: you’ve put brackets around column types rather than around column names.

alphacp:

In testing, I am returned the value “7d792ad5” which matches the Key of the member and not “a3a3487a” which is the key of the row in History table.

Not possible if you used the expressions above.

alphacp:

I was hoping actually that I would get the value “Started”

The value Started doesn’t occur in your data set.

[alphacp]

Yes Member is the Parent and History is the Child.

Sorry Steve, when I pasted my data it is was nicely formatted by all my spaces got removed. Here is the data structure of Member and History as screenshots

And the data in History as a screenshot

[Steve]

alphacp:

In testing, I am returned the value “7d792ad5” which matches the Key of the member and not “a3a3487a” which is the key of the row in History table.

Possible you confused the label for the key?

[LeventK]

@Steve
He has the same column in the History table as well which is a ref to Members table. That’s why he is referring to it via [_THISROW].[Key] = [Member] in his MAXROW expression.

However, as History is a child table to Members, he should have a [Related Historys] Virtual Column in the Members table. So I believe he can retrieve the value querying that column easily, right?

[Steve]

Ah, I see where you’re going now. Yeah, that should be possible, I’d think.

[alphacp]

Just to confirm, yes I have the [Related Historys] Virtual Column in the Members table

[LeventK]

So below expression might work

ANY(
	SELECT(
		[Related Historys][Your_Query_Column],
		[_RowNumber]= MAX([Related Historys][_RowNumber])
	)
)

[alphacp]

I changed [Your_Query_Column] for [Status] as that is what I want to retrieve and that returns nothing. However the challenge I have is that I want the last entered status for that Member but only if _Filter is set to Current. The query you have, given my dataset should have returned the very last row for the member but the _FIlter is set to Future on that row.

I guess I could create a virtual column that is a join of the member and the Filter and then look for that instead? Would that work? Seems messy but…

[Steve]

alphacp:

I guess I could create a virtual column that is a join of the member and the Filter and then look for that instead? Would that work? Seems messy but…

Very messy. Or you could just use AND().

help.appsheet.com

AND()

Are all true?

[alphacp]

Steve is correct. The label is what is being returned. Changing that makes my original query work

[LeventK]

ANY(
	SELECT(
		[Related Historys][Your_Query_Column],
		[_RowNumber]= MAX([RelatedHistorys][_RowNumber],"Current" = [_Filter])
	)
)

[alphacp]

LeventK:

ANY(
	SELECT(
		[Related Historys][Your_Query_Column],
		[_RowNumber]= MAX([Related Historys][_RowNumber],"Current" = [_Filter])
	)
)

Sorry Levent, I get “MAX function is used incorrectly”

[alphacp · Accepted solution]

This worked:

LOOKUP(MAXROW(“Current”, “_ROWNUMBER”, ([_THISROW].[Key] = [Member])),“History”,“Key”,“Status”)

I suspect I could now get rid of my slice but after many hours on this, I will stick for now. Perhaps tomorrow I will re-insert the AND()

Many thanks for the help - very much appreciated

[LeventK]

Excellent. Glad to hear that you figured it out.