Data from another table, using MAX and LOOKUP?

[danbiaz]

Hello,

I´m trying that one collum of my table is linked and update with data from another tablet, with some conditions.

I have two tables:
Table 1 => academias
Column1 = gym_id (only one data for each gym_id)

Table 2 => operations (This table comes from a cloud database, and is getting new data regurlary for each gym_id)

Column1 = gym_id (number)
Column 2 = kind (1,2,3 or 4)
Column 3 = created_at (date)

As you can see, both tables has the gym_id column.

So, I want to add a virtual collum named max_created_at at the academia table.

That brings the max created_at from operations, that matches the gym_id, and the kind = 4.

I have tried a lot of things, but is not working.

[tony1]

@danbiaz Have you seen these example apps? They sound similar.

https://www.appsheet.com/samples/Initialize-forms-with-most-recent-value?appGuidString=18b39921-b8a8...

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

[danbiaz · Accepted solution]

Thank you Tony, I check it but it was not working using these examples.

But I research a little more and create this formula, that works perfectly!

I created a virtual column with this formula:

MAX(
SELECT(operations[created_at],AND ( [kind] = 4 ,[gym_id]=[_THISROW].[gym_id] ))
)