Previous Unique Data Record

[kharismatik]

I am attempting to retrieve the previous positions/job data within a table. However, I am having problems because the table has instances where the position/job is duplicated. Below is the sample data:


Name Position Action Effective
John Doe 2559 Correction 10/6/2010
John Doe 2559 Supv. Change 10/6/2010
John Doe 2559 Update 10/1/2010
John Doe 2558 Promotion 3/15/2010
John Doe 2558 Update 1/4/2010
John Doe 2556 Promotion 10/5/2009


I am looking for a formula that will return only the instances of Promotion with the corresponding position information. The result should be:

John Doe 2558 Promotion 3/15/2010
John Doe 2556 Promotion 10/5/2009


Please advise. Thanks!

 

[lbass]

Why not just limit records to those with:

{table.action} = "Promotion"

?

-LB

 

[kharismatik]

My apologies, I did not accurately state the issue. Below is the sample data:

Name Position Action Effective
John Doe 2558 Correction 10/6/2010
John Doe 2558 Supv. Change 10/6/2010
John Doe 2558 Update 10/1/2010
John Doe 2558 Promotion 3/15/2010
John Doe 2556 Reorg 2/5/2010
John Doe 2556 Update 1/4/2010
John Doe 2556 Promotion 10/5/2009
John Doe 2555 Correction 1/1/2008

I am looking for a formula that will return the position information prior to the promotion. The result should be:

Name Position Action Effective Previous Pos
John Doe 2558 Promotion 3/15/2010 2556
John Doe 2556 Promotion 10/5/2009 2555

I apologize for the confusion.

 

[lbass]

Since you have the records sorted in descending order by date, create a formula {@prevpos} like this:

next({table.position})

Add this to your detail section. Then go to report->selection formula->GROUP and enter:

{table.position} = "Promotion"

-LB