MAXROW not working as expected

[Paras_Sood]

Hi community

I have a table "Daily Log" with key column "Work Order ID". I am trying to find the most recent entry in the app based on the timestamp, a column labelled as DateTime Start.

MAXROW(“Daily Log”,"DateTime Start")

When I run this formula, the key column returned is NOT for the most recent date. 

But when I change the parameter on top of this page to show 10,000 rows instead of 1,000, I get the right key column returned.

Why is this happening? I noticed that the key column returned in the first screenshot is the key column of the 1000th row.

I get same results when I replace DateTime Start with _RowNumber.

The problem is that I have a bot that is supposted to get triggered only if there is an entry for today. I am using the formula:

LOOKUP(MAXROW(“Daily Log”,"DateTime Start"),"Daily Log","Work Order ID","Date Start") = TODAY()

This does not work because the value returned by MAXROW function is not the most recent key value.

When I change the paramter to show more than 1000 rows I dont get any output.

Any ideas how to fix this? Thanks!

[Marc_Dillon]

There are some cases where you can't trust the result from the test screen, because it only takes into account a portion of your table. This is one of them.

I believe your actual problem is because you're trying to compare a DateTime against TODAY(), which is just a Date. Wrap your LOOKUP in DATE().

[Paras_Sood]

Hi Marc

Thanks for your response. That is not the problem because I have already created a virtual column called "Date Start" which is already DATE() wrapped around the "DateTime Start" column. Thats why in the test output you can see the date is 04/10/22. What could be the problem?

[Marc_Dillon · Accepted solution]

Ahh, missed that. Well, not sure. But in any case, why not just do DATE( MAX( table[datetime] ) ) instead of using LOOKUP(MAXROW( ?

[Paras_Sood]

Same result

It's interesting that this entry which always shows up is the 1000th record in the list.

[Paras_Sood]

I just tested this and it gave me a "Y" when equated to "Today()" so I'm guessing this should work. Let's see in the next few days if the bot gets triggered. Thanks Marc!

[Paras_Sood]

I don't know why yours works and mine doesn't but the bot got triggered today and that's all that matters, thanks Marc!

[SkrOYC]

My take is that you should test this on a real life scenario instead of just the Test page since that one takes into account some filtering.

Check this:

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Spreadsheet-to-allow-for-AppSheet-Expression-tes...