Most Recent Date from another table

[Ben_Moore]

First—FINALLY! I have spent months looking at various online tables and database systems, and would get partway through, and felt like it just didn’t offer what we needed. I stumbled on Airtable and it is the only one that did what I was hoping with EASE and without hours and hours of figuring things out.

I have 2 tables. Table 1 is client data information. Table 2 is a dates of contact. Is there a way for table 1 to display the most recent date of contact from table 2 for each individual. I attempted Max, but it did not work. I do have a look up of all the dates taken, but it shows them all, not just the most recent. I’d appreciate any suggestions.

Thanks.

[C_M]

Katherine, I am so sorry for the delay in acknowledging your post. Once again I haven’t yet had the time to sit down and put your suggestions to work (computer problems being one of the reasons) but I will. And in quickly looking over your post, it will definitely help!

I’ll be back - thank you so much.

[C_M]

@Katherine_Duh

Hi Katherine - finally sat down to create the new database using your suggestions.

I think I need to brush up or learn more basic skills than even what I’m asking.

So I created a base with Prospects table, a Reachout table, and a Follow Up table (which may not or may not be needed, not sure).

Here are some questions that maybe would shed some light into what I need to learn (maybe books I can read on the subject?)

I sit down to my Prospects table and decide I am going to do a mass email to 250 records (I am thinking I would need a custom View that shows me all records with email address)
I then export those 250 records into my mail merge program
I then need to record into the Reachout Table that I sent an email on that date to those records.
How do I get those 250 records (or whichever #) into the Reachout table? Is it a copy/paste of those records?

Then I decide that I am going to call some of the Prospects in the Prospects table with do not have an email address (so I would need a custom View for this right?/)
Same question - Do I copy those records and paste them into the Reachout table?

In between I’ll need to figure out ways to record what’s happened - who responded and how, and when, and notes, etc.

But like now, how do I accurately know where I “left” off in my Prospects Table? (never contacted yet) - so I can start the whole process again the next time I want to reach out to the next “set” of people?

Sorry if this is sounding discombobulated and confusing - like I said I feel like I am missing a chunk of what needs to happen before even the questions I asked.

Thanks. :confused:

[Dan_Brady]

Katherine, your example was great and I’ve been able to use it to identify the last date of an interaction with a client. I’m now wondering how to grab a value from that last interaction and use it in a calculation.

For example, if I want to calculate the change in weight for a client since their last visit, how do I grab the old weight recorded during their last interaction and use it to calculate any change from their current weight?

Thanks so much!

[Colby_Waters]

So I’ve figured out how to use rollup to bring in the most recent date from another field, but now I want to return another value associated with the record of that most recent date. In Excel, I would just vlookup the result of most recent against the table that has all the date records, but I can’t figure out how to do this in Airtable.

To keep following this example of contacting clients, let’s say I track all my calls and I also include call duration. Now I want to link or lookup so I can see when my last call was with the client and how long it was. Does that make sense?

[Arlo_Haskell]

@Katherine_Duh this thread is old but still useful – thank you! I have a situation where I need to get the most recent date from a formula-type field in another table. Is that possible?

I have a client table and a transactions table. Each transaction gets a date in a date-type field. But I also use a formula-type field to calculate an expiry date for certain types of transactions (e.g. IF the transaction is a charitable donation and not a purchase, the formula adds one year to the transaction date.)

In my clients table I want to display this expiry date (so we can send donation requests to clients on their anniversary). Using Max(values) in a rollup field doesn’t work because expiry date is a formula-type field. Is there another way?

As a workaround I tried to use ArrayCompact with the rollup to get all the expiry dates for each client, but now I would need to find the most recent date from that string. Attempts to perform Max(values) on the string in a formula-type field have not worked either. I’m grasping at straws…

UPDATE / SOLVED – I used DATETIME_PARSE in my expiry date formula so that the result is a date format instead of a text string. Easy! Now I can use a rollup to display the expiry date for a client’s most recent donation.

[shanti_rubens]

You could try autonumbering each entry and then do a rollup to another table and then use MAX Value formula to find which entry (in the rollup) has the highest auto number. Then if the auto number on your initial entry matches the highest auto number, it’s the latest entry!

[Arlo_Haskell]

@Colby_Waters did you ever figure out a way to get other field value from the most recent record? In my clients table, I’m trying to display the dollar amount of the most recent transaction from the transactions table.

[Zoelle_Egner]

Hi Arlo! There’s a way to do this, but it’s a bit convoluted, so bear with me :slightly_smiling_face:

1. Use the MAX(values) trick described above to pull the most recent transaction date into your clients table.
2. Create a lookup field in your transactions table that looks up the most recent date you just calculated in the client table.
3. Create a formula field in your transactions table that compares the most recent transaction date that you just looked up to the date of this specific transaction. If they’re the same, have the formula display the dollar amount of the transaction. If they’re not, have it display blank. The formula will look something like this:

IF({Most Recent Transaction Date}={This Transaction Date},{Transaction Value},BLANK())

4. In your client table, look up the formula field you just created. It will only display the value of the most recent transaction!

[Arlo_Haskell]

Thanks, Zoelle! That worked!

I also found this helpful from @Matt_Bush: Embedding other data in 'most-recent' date

[Sehen_Gratis]

Thank you so much zoelle! I had same problem. That worked.