Search for latest date in another table

Hi! I’m trying to figure out how get this expression.

There are two table, A and B
In table A I have information about some product.
In table B, which is ref table A, I have information about servicing product and date of that service
I want to search table B for latest date of service and get this date in table A
If someone get new row in table B with new date of service, it will be visible in table A

And another problem :smiley:
I have this date and in another column I want to have this same date + 2 years.

I trying type [column with date] + [730] but it won’t work.

@Aleksander_Rogowski
Below expression will give you the last record’s row number for any TableB data

MAX([Related TableBs][_RowNumber])

You can then bind this with a LOOKUP(…) expression to get your Date value

LOOKUP(MAX([Related TableBs][_RowNumber]),"TableA","_RowNumber","Date")
2 Likes

Finding the max then using a lookup is spot on. If you have lots of products in table A, you’ll probably want to search for the max date from that specific product only, so I would use MaxRow to filter the result by product.

Use this formula in the App Formula for the “Most Recent Service” column in Table A:

LOOKUP(MAXROW(“Table B”,“Date”,[Product]=[_THISROW].[Product]),“Table B”,“ID”,“Date”)

BREAKDOWN

LOOKUP - LOOKUP(

LOOKUP VALUE (MaxRow returns the KEY for that row, so we’re getting that key for the lookup)
Find the row with the maximum value - MAXROW(
… … Look in Table B - “Table B”,
… … Find the maximum value in the date column" - “Date”,
… … Where the value of the Product column equals the value of the Product column in this table - [Product]=[_THISROW].[Product]),

LOOKUP SEARCH TABLE - “Table B”,
LOOKUP SEARCH COLUMN (which column to find the value in, so the name of the KEY column in Table B) - “ID”,
LOOKUP RETURN VALUE (column that has the value you want to see in Table A) - “Date”)

To add 2 years, use EOMONTH. EOMONTH finds the last date of the month, and you set how many months offset by (so 24 months). To get the actual date for that month (instead of the last day), find the number of days from the work date to the end of the month and subtract that.

The formula below should work for you.

EOMONTH([Date],24)
-(DAY(EOMONTH([Date],24))-DAY([Date]))

@Bus_Mom
If you have a lot of rows in your table, then use of MAXROW(…) expression will cause sync time to increase, as it will be calculated for each row of the table. Provided you can pay attention, I have referred to the the [Related RefColumnNames] column specifically to prevent that. MAX([Related TableBs][_RowNumber]) expression will return the latest record’s Row Number for each product in any particular row in TableA

3 Likes

Oh nice!! I see what you’re doing there. It makes a lot of sense that you can reference a virtual table that was automatically created through relationships, but I hadn’t realised that yet. What an awesome new way to understand the AppSheet data connections. Thanks for the tip, @LeventK !! I love how much I learn in these conversations. :partying_face:

P.S. I know my breakdowns are sometimes long (sorry), but if you can include the explanations when you have time, please, it’s really helpful for understanding concepts and applying them everywhere. I really appreciate this explanation - it’s going to help me redesign some of my app formulas too. Woohoo!!