Details section needs last record date from within its group and more

susnewyork
susnewyork used Ask the Experts™
on
I need my report to show the last service date from within its group. Ex:

GH: Smith, John
Service Date----------Last Service Date-------------
4/18/2012--------------3/26/2012-----------------------
3/26/2012--------------2/12/2012-----------------------
2/12/2012--------------11/10/2011--------------------

But...

If they only have one service date, then it would need to pull a different date field named PastHistory(which is a formula that evaluates which of 6 fields has the most recent date). ex:

GH: Smith, John
Service Date----------Last Service Date-------------
4/18/2012--------------3/26/2012-----------------------
3/26/2012--------------2/12/2012-----------------------
2/12/2012--------------11/10/2011--------------------

GH: Jones, Allen
Service Date-------------PastHistory--------------
4/18/2012-----------------10/14/2011-------------

The details section will keep growing over time, but needs to always show the last service date from the previous record within the group or the PastHistory field if there isn't a last Service Date.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Is LastServiceDate a field in the record or with the data sorted as above, do you  need the NEXT({ServiceDateField})?

If you need the next then try a formula like

If Not NextIsNull({GroupField}) AND Next(GroupField}) = {GroupField}) then
       Next({ServiceDateField})
Else
     {@PastHistoryField}

mlmcc

Author

Commented:
I think this did the trick. I'm going to keep this one open until my company tests the report, in case anything comes up with this formula. Thanks once again mlmcc.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial