HELP! Slice to get the last record from a group of rows

[Alejandra_Petro]

Hi, community,

I’m trying to create a slice of table “Inspections” to get the last inspection recorded for each rego, but only if that inspection’s status is due or overdue.

So it would be something like:

1. Filter [rego]
2. Select rows where [Status] equals “Due” or “Overdue.”
3. Select the record with the recent date

I have tried different expressions, but none of them has given the expected result.

The last try I did was create a slice “Rows_Due_Overdue” to get all the rows where [Status] equals “Due” or “Overdue” and then create a second slice “Recent_Record” to get the MAX record. The expression I have for “Recent_Record” where “Inspections” is the source table is the following:

IN([ID], SELECT(
Rows_Due_Overdue[ID],
([_ROWNUMBER] = MAX(Rows_Due_Overdue[_ROWNUMBER]))
)
)

It’s not working and I do not know what else to try

It would be great if I can get some help, please, pleeease!

Thanks in advanced!

[Suvrutt_Gurjar · Accepted solution]

If I have understood the requirement correctly, you could try a slice filter expression something like below

IN([ID], LIST(MAXROW(“Table Name”, “Date Column Name”, AND( [rego]=[_THISROW].[rego] , OR([Status]=“Due”, [Status]=“Overdue”)))))

[Alejandra_Petro]

You are a genius! thank you!

[Patrick_Paul]

@Suvrutt_Gurjar Hello!  I'm hoping you can provide additional help.  I'm looking to do basically the same thing the original poster was, but I don't think this actually produces the desired results. Hear me out:

The OP said: "I’m trying to create a slice of table “Inspections” to get the last inspection recorded for each rego, but only if that inspection’s status is due or overdue."  I'm trying to do the exact same thing.  The problem is, this formula produces that last due/overdue record for a given "Rego".  But, the OP wanted the newest inspection record *ONLY* if it's due/overdue.  So in other words, if the last inspection was on-time, you should not receive a result for that Rego, regardless if older records were due/overdue.  However, you will in this formula... you'll get the previous due/overdue one!  Is there a way to only get the record for each Rego if the *newest* one is due/overdue, based only on the newest record and not any prior records? Basically, you want the newest record by date to "override" any previous older ones for each "Rego".

Hopefully my question makes sense, it's a complex thing that I've been racking my brain to figure out how to approach.  I had thought that perhaps a filter or something would work, but so far I haven't found anything that doesn't produce an error or garbage results.  Thanks in advance!

[Suvrutt_Gurjar]

Hi @Patrick_Paul ,

I have not tested below suggestion. I am not at my work desk and I am responding from my mobile.

You may want to try the below expression based on the requirement you mentioned 

AND(

IN([ID], LIST(MAXROW(“Table Name”, “Date Column Name”, AND( [rego]=[_THISROW].[rego] , OR([Status]=“Due”, [Status]=“Overdue”))))),

IN([ID], LIST(MAXROW(“Table Name”, “_ RowNumber”,  [rego]=[_THISROW].[rego] )))

)

Alternatively, you could try the expression below 

AND(

IN([ID], LIST(MAXROW(“Table Name”, “Date Column Name”, AND( [rego]=[_THISROW].[rego] , OR([Status]=“Due”, [Status]=“Overdue”))))),

IN([ID], LIST(MAXROW(“Table Name”, “Date Column Name”,  [rego]=[_THISROW].[rego] )))

)

[Patrick_Paul]

Thanks for the quick reply, and for doing so on the go!  Unfortunately, this seemed to strip out certain rows at random.... What I have found after a lot of throwing pieces of this expression into a virtual column and looking at the "show results"  is that it produces nonsensical results based on the date field that is being used as the target of Maxrow().  I first thought this was because my date field is "datetime" field, so I created a virtual column that uses date() to come up with the "plain date", and If I use that as the target of maxrow() and I strip it back to: 

maxrow("Technical Preparedness Review", "Plain Date", true)

It actually returns the key field of the row with the oldest date.  If I swap it to minrow(), it does the exact same thing.  It appears to just return the very first row in the data set (which happens to be the oldest). I am completely confused at this point.  Seems like a problem with Appsheet?

[Patrick_Paul]

For giggles, I also tried this:  max(Technical Preparedness Review[plain date])

It returns the key field for a row with the a date in late November, and we records up through today.  Seems to be picking a row at random?!

[Suvrutt_Gurjar]

I did some more testing. Yes, it sounds that when one uses an AND() inside a MAXROW() expression, the results are not exactly as expected. I does sound like a bug. 

Based on my latest testing, you could try a filter expression of 

AND(

[ID] =MAXROW(“Table Name”, “Date Column Name”,  [rego]=[_THISROW].[rego]),

OR([Status]=“Due”, [Status]=“Overdue”)

)

Basically, in this latest suggestion, the additional conditional checking of [Status] has been moved outside the MAXROW() expression. Thereafter, the results are as expected. 

This means, the latest suggested expression returns a record for a [rego] only if the latest record by date for that [rego] has status of "Due" or "Overdue". If the latest record by date for a [rego] has a status other than "Due" or "Overdue" , no record for that [rego] is returned.

[Patrick_Paul]

Thank you so much, sir.  This does appear to work as expected!

[Suvrutt_Gurjar]

Thank you for the update. Glad to know  the suggested solution works the way you want.