Jump to content
Sign in to follow this  

Retrieve date from most recent portal record

Recommended Posts

I'm working with two databases - the first "CustomerData" contains a portal to "ItemSales".

CustomerData is customer contact information, billing address, shipping address, etc.

ItemSales is purchase information

So when I view CustomerData I can see their purchase activity from within the portal.

My problem is: I'm trying to define a calculation field in CustomerData that returns the last InvoiceDate (the most recent one) from the ItemSales data inside the portal.

For example if I have 3 sales records for a customer showing inside the portal, each record begins by showing the InvoiceDate field....

1. 6/4/03, .........

2. 12/15/03, .......

3. 3/5/04, ........

I'd like to retrieve the 3rd date, 3/5/04, in my calculation field in CustomerData.

Any ideas would be greatly appreciated.


Share this post

Link to post
Share on other sites

You can utilize the Max() function to isolate the newest date per Customer.

Create a calculation (date) with: Max(ItemSales::Date)

Share this post

Link to post
Share on other sites

While MoonShadow's Max is right, there is another function that is faster. But the data has to have been entered in chronological order, ie., the last entered and the latest are the same. With Invoices and items this would be true, I imagine.

It's found in the Repeating functions section, 'cause if was first used with repeating fields.


Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.