Filter to show most recent row entry for every per...

Cameron_Wilson · 12-22-2021 05:20 PM

I’ m trying to filter data: I have a list of volunteers at our stores that fill a form for signing into our stores and when they leave they sign out again. This is for WHS purposes if there is a fire we need to know who is and is not in store.
This creates a spreadsheet with many details:
I have spliced the table to only include columns such as:
Store Name (We have multiple store locations)
Full Name (of staff or Volunteer)
Date/Time (Based on Form submission time)
In/Out
The aim is to only see who is still registered as being in at the store.
I don’t want each volunteer or staff member to be a user of the app, only the fire warden (manager) needs this access, hence I use a form for them to fill out which creates an entry to the spreadsheet (database).

Example: our volunteers come today: there names are as per below:

John
James
Eden
Lisa

At 1.30pm we have an evacuation.

Lisa started at 1.00pm and is in store:
John started at 7am, but signed out at 1.15pm for lunch and is out of the store
Eden started at 7am but was only rostered for 4 hours and is now signed out, home and not in store.
James, Started at 7am, signed out at 12.00 for lunch, but signed back in again at 1.00 when back from his break, he is in store.
I only want to see their most recent entry, but i want to see it for each person in the list.

Appreciate any assistance provided.

Suvrutt_Gurjar

Welcome to the AppSheet community.

Please create a slice with a filter expression something like below

[Key column]= MAXROW(“Table Name”, “DateTime” , AND( [Store Name]=[_THISROW].[Store Name] , [Full Name]=[_THISROW].[Full Name], [In/out]=“In”, DATE([DateTime])=TODAY()))

Date/Time (Based on Form submission time) is assumed to be a DateTime type colymn with name as [DateTime] . [In/out] column name is assumed to capture “In”, “Out” entries. Please change column names, table name, and key column name as you actually have.

The slice will return all the desired entries.

Cameron_Wilson

Hi Suvrutt_Gurjar,

Thank you very much for your prompt reponse.
However this does not address the issue, it only filters the rows to display the people who have signed in. It actually means I will not see if they are out, i want their most recent entry, in or out. that way i will know whether they are in store.

I hope this makes more sense.

IF i use the form and check in in the morning , then i go for lunch and check out, but i return from break and once again check in, the form app should only show my most recent status as in the store (When i checked back in after lunch) i should not see the previous entires in the app.

I hope I have clarified what I am asking better.

Thank you.

What I want to know

Cameron_Wilson

Hi again,

Your help assisted me A LOT!

I made a slight change to your function as seen below. I had to add an or function to allow either in or out functions then the rest of yours still returns their last data entry:

[Submission Id]= MAXROW(“Sign In Sheet”, “Submission Date” , AND( [Store Name]=[_THISROW].[Store Name] , [Full Name]=[_THISROW].[Full Name], DATE([Date])=TODAY(), OR([In/out]=“Start of Shift”,[In/out]=“End of Shift”)))

This works perfectly now thank you.

Suvrutt_Gurjar

Hi @Cameron_Wilson ,

Thank you for the update and good to know it works the way you want.

My suggestion was of course without knowing many minor details ( The in/out terminology you are using or what exactly you wanted, only in or both in/out), The below statement sounded to suggest only users who are signed in are required.

As such minor changes were required. Nice to know that with those minor changes the expression is now perfectly working.

All the best with your app creation.

Edit: Minor edits to the description.

Cameron_Wilson

Yes, you’re right.

It was hard to put it into words, but you nailed it.

Thanks.

Filter to show most recent row entry for every person (name in a list)