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.

[dhc]

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.

[Ben_Moore]

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

[Ben_Moore]

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!

[Sarah_Cliff]

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.

[C_M]

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

[Katherine_Duh]

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!

[C_M]

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

[C_M]

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.

[Katherine_Duh]

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?