Show a specific value in another table related to a record

[nikolo00]

Dear friends, I need again your help.

I am creating a database in which I will add information about patients with a specific disease. In a main table, I will summarise the most relevant information about each patient, and each row will correspond to a patient (record) with a specific ID number. In another table called "visits" I will add data about all the outpatients visits (each per row with a specific date). Each patient on the first table can have more than 1 visit but each visit can be associated only with 1 specific patient. I correctly linked the 2 tables, and now in the main table, I can see all the visits for each patient. 

What I need to do now is to visualise in a field of the main table specific information contained in a specific field of the "visits" table. In detail, I need to visualize the status of the disease in the most recent visit of that specific patient (disease-free, progression, regression). I tried creating a rollup field and using the formula MAX(values) but what I see as output is a "0" and not the status of the disease in the most recent visit.

Can someone please help me?🙏

[TheTimeSavingCo · Accepted solution]

You're on the right track, you'll just need to use `MAX()` slightly differently.  Here's an example to get you started

To recreate the base above:
1. Have all the records linked to a single record called `Rollup`
2. In the `Rollup` table, create a rollup field on the `Created Date` value from `Table 1` with the formula `MAX(values)`
- This will give us the latest date  
3. In `Table 1`, create a lookup field to pull over the most recent` Created Date` from the `Rollup` table
4. Use a formula field to check the `Created Date` value against the most recent date pulled in via step 3
5. In the `Rollup` table, create a lookup field with a conditional that checks against the field from step 4