Finding Most Recent Date

[elizabethb]

Hello,

I have searched for an answer to my problem in the archives, but haven't quite found a solution, or else I just can't see it.

I need to find the most recent contact date for a given Client and generate a warning if they haven't been contacted in more than 30 days.

For example, ClientA was contacted 9/1/2004, 10/1/2004, and 11/1/2004, but not since. I need to grab the 11/1/2004 to use for the warning calculation and have a "Client hasn't been contacted in 30 or more days" warning show up on the client's record. Each contact is a separate record and I need to do this across all of the clients. Am I posting in the wrong place and this is really a scripting question? Should I be using a relationship? I tried using a self join, but couldn't make that work either. Any help would be greatly appreciated as I'm feeling thick-headed.

Elizabeth

[transpower]

If the records are in order, you can use the Last function. Last ( relatedTable::field )

[elizabethb]

Thanks so much, Transpower. I knew there had to be a way using a function. I tried using Max(Contact_Date), but didn't know about Last(). Now I just have to figure out a way to make sure those records stay sorted. I guess I could add a sort to the opening script. Again, thanks very much!

[comment]

You don't need a script.

In the Clients table, create a calculation field (result Text) =

Case (

Get ( CurrentDate ) > Contacts::Date + 30 ;

"Client hasn't been contacted in 30 or more days."

)

Make sure the relationship to Contacts is defined as sorted by Date, descending.

(If you prefer to see the contacts in ascending order, define that in the portal setup.)