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