Dec 08, 2015 07:01 PM
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.
Dec 09, 2015 04:28 PM
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.
Dec 09, 2015 08:19 PM
Forgive my ignorance… but Max(Values)… what does that translate to? I tried typing that in and received “0”.
Dec 09, 2015 08:25 PM
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!
May 13, 2016 10:06 AM
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.
Aug 08, 2016 07:57 PM
@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.
Aug 09, 2016 10:57 AM
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!
Aug 10, 2016 09:48 AM
@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.
Aug 11, 2016 11:00 AM
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.
Aug 11, 2016 03:51 PM
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:
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?
Aug 16, 2016 10:11 AM
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.
Aug 23, 2016 05:11 PM
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:
Sep 08, 2016 01:27 PM
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!
Nov 02, 2016 01:12 PM
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?
Apr 25, 2017 02:03 PM
@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.
Apr 28, 2017 05:48 AM
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!
May 11, 2017 11:58 AM
@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.
May 11, 2017 12:09 PM
Hi Arlo! There’s a way to do this, but it’s a bit convoluted, so bear with me :slightly_smiling_face:
IF({Most Recent Transaction Date}={This Transaction Date},{Transaction Value},BLANK())
May 11, 2017 12:36 PM
Thanks, Zoelle! That worked!
I also found this helpful from @Matt_Bush: Embedding other data in 'most-recent' date
May 12, 2017 06:36 PM
Thank you so much zoelle! I had same problem. That worked.