Latest transaction date for customer

s111ppg · 10-05-2023 07:45 AM

I've been looking at this for some time and must be very close but I'm not getting results back

I have two tables - People and Activity

People has the following fields

ID (Text) Key field

Last Transaction (Date) - Formula

Activity has the following

Invoice ID (Number) Key field

Contact ID (Text)

Transaction Date (Date)

I've tried using both of the below formulas to get the maximum transaction date from activity for each people record

MAX(SELECT(activity[Transaction Date],([_THISROW].[ID] = [Contact]) ) )

LOOKUP(MAXROW("Activity","Transaction Date",[Contact]=[_THISROW].[ID]),"Activity",“Contact”,"Transaction Date")

In both cases, no result is shown either in the test window of the app view.

I'm guessing I've got the connection between the two tables wrong ?

Markus_Malessa

Try the following:

LOOKUP(MAXROW("Activity", "Transaction Date", [Contact ID] = [_THISROW].[ID]), "Activity", "Invoice ID", "Transaction Date")

Not sure which is correct, but in your list of columns you have [Contact ID], but in your LOOKUP() formula you have just [Contact]. You will want to look at the LOOKUP() formula in relation to the MAXROW() formula. So in this case MAXROW() returns the key column for the "Activity" table, then LOOKUP() says take this key value look it up in the "Activity" table in the "Activity" table key column and return the "Transaction Date" value. You will note that your proposed LOOKUP() formula is attempting to lookup the Activity key value in the "Contact" column instead of the key column.

JuneCorpuz

If you can share a screenshot of your data structure then we can tell you the correct formula to get the latest transaction date.

s111ppg

I've just discovered what the issue is, the dates are structured as DMY but the locale on the sheet is USA. I discovered it when viewing the information from a record on a different form.

The formulas then work fine.

FWIW though, I did stumble upon this template which pretty my covers exactly what I'm trying to do.

https://www.appsheet.com/templates/This-app-shows-how-to-get-the-most-recent-related-row-for-a-table...

Markus_Malessa

Something else to consider, it appears that [Contact ID] (Text) in the "Activity" table stores a reference to the "People" table? If that is the case you should set [Contact ID] to (Ref) type instead of (Text) type.