Most Recent Date from another table

MAX works for me, but I have to type MAX(values) - other things I’d expect to work like MAX() or MAX(Date) or just MAX don’t work.

Forgive my ignorance… but Max(Values)… what does that translate to? I tried typing that in and received “0”.

Ah, I got it. I have to make the column a “Roll Up” Next, enter the formula for “Max(values)” in the Rollup formula space. And that does it!

Thanks!

Forgive me, I know this is an old thread, but does this solution still work for you? I’m having an issue where they all just show up “0” in the rollup field, even using the MAX(values) formula.

@Ben_Moore would you mind sharing how you set up your primary field?

I have a table I created for the same purpose as you - to keep track of my follow-ups. But right now I have them all in one table and I am realizing it would be much better to have them as contacts in one table (with phone #s, names, addresses, etc.) and the other table with the follow up fields, conversation notes, when I contacted them last, etc. But when I went to set up it/separate them into 2 separate tables I realized I have no idea what I’m doing I don’t know how to even get started with the name field which I know needs to be unique. :confused:

If you’re willing to share your formula that would be great, if it didn’t take you too much work.

P.S.: Of course the most recent date of contact for each person is awesome too but I’ll cross that bridge when I get to it! Thanks again.

Here’s how I would go about constructing a “Date of last contact” field for a table of clients linked to a table of client interactions.

In the table of client interactions, I have a linked record field for the name of the client, a date field for the date of each interaction, and a primary field which uses a formula to name each record.

In the client table, create a rollup field which uses the linked record field connected to the client interactions table. Select the dates field (column) as the one you’d like to rollup, and set the aggregation function as MAX(values).

If anyone has any other questions about this, I’d be happy to help out!

@Katherine_Duh arggggh I haven’t even tried this yet but I’m so excited right now at the possibility of what you’ve showed me. Thank you so much! :heart_eyes:

I’m going to try it and see (just seeing how you have the tables laid out is a tremendous help.)

What I’ve realized in the last few days of spending many hours trying to figure things out is that databases are much more than spreadsheets and that I know nothing about databases!

Thank you so much for your help.

Katherine, I finally had a chance to sit down and see if I can work this based on your set up and tips.

I have a feeling I don’t have my table set up to be able to make this work.

I need to make clear I have only ONE table right now - clients AND when we contacted them.

So, like this…

Name 1st contact 2nd contact 3rd contact 4th contact
Abby Smith 12/3/2015 2/2/2016 2/3/2016 4/4/2016

(you get the picture - goes up to 7 times of contacting them, then we’ll give up and assume they’re not interested as these are cold calls / emails)

Could it work if I have it set up like this?

I guess in my mind I want to be able to look at a name and see down the line the progression of contacts.

Maybe I’m asking how you set up BEFORE you even go to the max values.

I only see one date field in the Client Interactions table but I do see that in the event field a company can appear more than once which is when you contacted them - I’m not understanding how to get there. :confused:

Would you be able to help clarify this at all?

Thank you Katherine.

You may want to read this article on many-to-many relationships in the context of a database. While the cases detailed in this help article are probably a little more complex than what you’re trying to accomplish, some of the database principles outlined there may be of use to you.

Basically, for your case, you would want to split up the data on each client and the data on each date of contact with a client into two separate tables. The reason why is because you want to store some extra information about each of the contact instances. So you might lay out your two tables like this, for example:

• Clients
• Client address
• Client email address
• Contacts (as a linked record field to the Contacts table)
• etc.
• Contacts/Reachouts
• Date of contact
• Method of contact
• Name of client (as a linked record field to the Clients table)
• etc.

If you lay it out like this, then you can actually see the progression of all of the contacts if you make the linked record field large enough in the Clients table.

E.g. Abbey Realty has four different contacts in this table.

When you go to the Clients table, the Client Interactions column is wide enough that you can see all four interactions in a row, and then the Date of Last Contact column right after it.

If you link it like this, then when you click on each of the links, you can bring up all the data associated with that contact/interaction that’s stored in the other table.

Does this help at all?

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.

@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:

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!

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?

@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.

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!

@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.

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!

Thanks, Zoelle! That worked!

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

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

Thank you Zoelle! Ditto to the same problem and it worked!

What if I want to call up the second to last most recent date? Is there a way to use the roll up field and MAX(values) formula to do this?

Especifically, when a new record is created, I want to be able to rollup data from the previous record.