Transaction date question

[MHann]

Hello,

I'd like to pull the most recent promotion date for each employee in an organization but if a person has been promoted more than once get multiple lines. How can I only get the most recent date and a blank field if there is no promotion?

Here is what I'm seeing:
ID HIRE DATE TRANSACTION DATE
123 3/21/2000 PROMO 9/4/2010
123 3/21/2000 PROMO 6/5/2006
123 3/21/2000 PROMO 6/5/2003
234 1/15/1996 PROMO 9/4/2011
234 1/15/1996 PROMO 9/4/2009
234 1/15/1996 PROMO 9/4/2008
234 1/15/1996 PROMO 9/4/2004
234 1/15/1996 PROMO 9/4/2001

Here are the final results I want

ID HIRE DATE TRANSACTION DATE
123 3/21/2000 PROMO 9/4/2010
234 1/15/1996 PROMO 9/4/2011
345 5/17/2010 ____ ____

Thank you in advance

 

[lbass]

Create a formula {@transdate} like this:

if isnull({table.transaction}) or
{table.transaction} <> "PROMO" then
{table.hiredate} else
{table.transactiondate}

Then insert a group on {table.ID} and go to report->selection formula->GROUP and enter:

{@transdate} = maximum({@transdate},{table.ID})

You can replace the promotion date field with this formula:

//{@promodt}:
if isnull({table.transaction}) or
{table.transaction} <> "PROMO" then
date(0,0,0) else
{table.transactiondate}

Replace the transaction field with this:

//{@trans}:
if isnull({table.transaction}) or
{table.transaction} <> "PROMO" then
"" else
{table.transaction}

Or you could suppress those two fields with a formula:

isnull({table.transaction}) or
{table.transaction} <> "PROMO"

-LB