Virtual Column to return most recent log for activity

[Ms_Margo]

Hi AppSheet Family!

I have a database for customers and each activity they are engaged in. With each activity we have a log to track where each activity is.

Table: Customers (Key=Key; Name = Label)

Table: Customer Activity (Key=Key; Activity Name = Label)

Table: Customer Log (Key=Key; Activity = Label)

I need to create a virtual column in the Customer Activity Table that returns the most recent Customer Log Entry for that specific activity. I have been playing around with MAXROW and SELECT but for the life of me i can’t get it to work.

MAXROW(“Customer Activity Log Entry”, “Timestamp”, select(Customer Activity[Key], in([Related Customer Activity Log Entries], [_thisrow].[Key]))

Am I on the right track? Any suggested apps i can poke around under the hood?

Thanks!

[Marc_Dillon]

Ms_Margo:

MAXROW(“Customer Activity Log Entry”, “Timestamp”, select(Customer Activity[Key], in([Related Customer Activity Log Entries], [_thisrow].[Key]))

The third argument for MAXROW() needs to evaluate to a Yes/No value. SELECT() returns a list.

Probably need something like this:

MAXROW(“Customer Activity Log Entry”, “Timestamp”, [Customer Activity] = [_THISROW].[key] )

[Marc_Dillon]

Marc_Dillon:

MAXROW(“Customer Activity Log Entry”, “Timestamp”, [Activity] = [_THISROW].[key] )

Changed “Customer Activity” to just “Activity” per your column name in Customer Activity Log Entry table.

[Ms_Margo]

You ROCK!

[Steve]

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

How are FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() related? FILTER(), LOOKUP(), MAXROW(), and MINROW() are all built upon SELECT(), so they all share similar capabilities and limitations. FILTER("table", select-expression) is equivalent to: SELECT( table[key-column], (select-expression) ) LOOKUP(match-expression, "table", "match-column", "result-column") is equivalent to: ANY( SELECT( table[result-column], ([match-column] = (match-expression)) ) ) N…

[Ms_Margo]

Getting closer but still no dice…

MAXROW(“Customer Activity Log Entry”, “Timestamp”, Customer Activity[Related Customer Activity Log Entrys] = [_THISROW].[Key] )

Maybe the attached screenshot will help?

I am going in circles because [_THISROW].[Key] will be contained in the Customer Activity[Related Customer Activity Log Entrys] and not equal. Thoughts?