Need help with expression 2

Hi! I will explain a few things about what my goal is…

Table: Stores

Table: Daily Report
Function: Every time an edit is made to the “Stores” table, a row is added to this table
Column 1: Key
UNIQUEID()
Column 2: Today
Records the date of the edit
Column 3: Visitor
Records the name of the person who edited the row using this function…
LOOKUP(USEREMAIL(),“User Managment”,Email,“Full Name”)

Table: User Managment
Function: A list of users of the app
Column 1: Full name
Column 2: Email
Column 3: Last Store Visit
This is the column that I need a formula for

This formula must return the last date that each person edited the “Stores” table. Each person will have a different date. If user #1 edited “Stores” today, May 6th, but User #2 last edited the “Stores” table on April 15th, then the row should return May 6th in the Last Store Visit column for User #1, but April 15th for User #2. If User #2 were to then edit the table tommorow, May 7th, then the column would automatically update in his row to “May 7th”.

Is a function like this possible? I’ve tried unsuccessfully to use MAXROW(), but i’m also not very experienced with this function, so maybe i’m using it wrong.
I also tried to use SORT() and INDEX() together, to try to get the top value of a descending sorted list of the Daily Value table, but that didn’t work for me either.

Thank you very much in advance to whoever can help me out!

Could you please try following expression in the [Last StoreVisit] column of the User Management table

MAX(SELECT( Daily Report[Today], [Visitor]=[_THISROW]. [Full Name]))

I suggest that you may change the name of the column [Today] in the table Daily Report to a suitable other name such as [Visited] because Today() is also a function in AppSheet. Typically it is a best practice to avoid reserved names in column names.

You may also need to ensure that Full Name is unique in the User Management table. Generally it is a better practice to use emails because names are always likely to be duplicated unless you have ensured that the table cannot take duplicate names.

3 Likes

Thank you very much! The only problem encountered with your solution is that the column only updates when you make an edit to each row. Is there any way to fix this, so it just shows automatically?

Got it. Can you please make the column a VC.

3 Likes

That worked. Thanks!

1 Like