Only show latest row from each ID

[SKETCHwade]

Hi,

Below are 3 images, the first two are the google sheet the app is connected to. The second is the view within the app.

There are two versions of this app using the same sheet. One for operators to complete an inspection and the other for mechanics to fix issues raised from this inspection.

The app currently shows all rows in which the “Fixed?” column is marked as false.
This is done with a slice row filter condition of:

AND([Fixed?] <> True,ISNOTBLANK([Fixed?]))

The issue you might notice is that it shows ALL rows with this condition, I would like to modify it to only show the latest row for each Equipment ID. The idea being a machine is only listed once for the mechanics, and within that is the latest information for the machine.

[Suvrutt_Gurjar]

Hi @SKETCHwade,

Does an expression something like below help you as slice filter condition

[Key Column Name of the Table]=MAXROW(“Table Name”,“TimeStamp”,AND([Fixed?] <> True,ISNOTBLANK([Fixed?]),[Equipment]=[_THISROW].[Equipment]))

[SKETCHwade]

This works perfectly thank you @Suvrutt_Gurjar

I have now realised an issue with my app though.

If a machine has been marked as faulty every day for a week you are given 5 entries that are faulty. If the mechanics only see the last one and mark it as fixed they will then see the next one and have to mark it as fixed. The mechanics would in theory have to do this 5 times, which is clearly not ideal.

Any ideas anyone?

[Bus_Mom]

Try setting an app formula condition for the [Fixed?] column itself in the Reports Table to automatically change the status to fixed if the most recent entry is set to fixed.

APP FORMULA EQUATION FOR THE [FIXED?] COLUMN IN THE REPORTS TABLE

IF(LOOKUP(MAXROW(“Report Table”,“TimeStamp”,[Equipment]=[_THISROW].[Equipment]),“Report Table”,“ID”,“Fixed?”)=TRUE,TRUE,[Fixed?])

.
Basically, (1) We find the most recent record for that vehicle (MAXROW). (2) We look up if it has been fixed or not (value of [Fixed?] column sing LOOKUP). (3) If it has been fixed, we set this entry (row) to fixed as well (so it will not show up in the slice because all reports are labeled “fixed” at the same time). (4) If it has not been fixed, we keep the current value that has been set for this entry (row).

The only potential pitfall I can see is if different problems were reported for the same equipment. The most recent problem might have been fixed because it was visible to the mechanic, but the problem reported earlier may not have been addressed? Depends on your work flow; if checks are daily, this may not be a problem because the original problem would be reflagged? We may have to play with your data set-up if this is a problem.

FORMULA BREAKDOWN

IF - IF(

…IF CONDITION (if the most recent entry for this equipment is fixed):

… … LOOKUP
LOOKUP(

… … LOOK UP VALUE (the most recent report for this equipment - MAX ROW automatically returns the value from the KEY column, so that’s what we’ll be looking up)
MAXROW(“Report Table”,“TimeStamp”,[Equipment]=[_THISROW].[Equipment]),

… … TABLE IN WHICH TO MATCH VALUE (the reports table - yes, it is referencing its own table)
“Report Table”,

… … COLUMN IN WHICH TO MATCH VALUE (name of the KEY column)
“ID”,

… … COLUMN TO GET VALUE FROM (name of the [Fixed?] column)
“Fixed?”)

… … EQUALS TRUE (most recent entry for that equipment has TRUE in the [Fixed?] column)
=TRUE,

… VALUE IF CONDITION IS TRUE (most recent entry HAS been fixed)
… … SET THIS COLUMN TO TRUE (report automatically also marked as fixed)
TRUE,

… VALUE IF CONDITION IS FALSE (most recent entry has NOT been fixed)
… … USE EXISTING COLUMN VALUE (use whatever is already selected)
[Fixed?])

[SKETCHwade]

@Bus_Mom
That was an excellently detailed response, I really appreciate the time and effort you put in!

I will implement this ASAP, Thank you!

[Bus_Mom]

@SKETCHwade Ooh. Actually, on second thought the formula needs to be set as an action triggered by a workflow, not in the app formula. If there’s an app formula, the user can’t override the value, so mechanics won’t be able to change any record to [Fixed?] = TRUE, including the most recent one. Same exact thought process, just move that to an action and workflow instead. I’ll check it out later for your data and update you. Sorry for the confusion!!