Name change formula needed

[agray123]

I am querying PeopleSoft PS_Personal_Data_Eff_Dt to generate a list of all employee names, id's and effective dates (indicating a change in some facet of their personal data)

I really want to find only the name changes, so I am stuck after creating a count of the name field and then trying to conditionally suppress the field in the group selection area where name>1

I only want data where a name has changed, but also am getting returns where someone changed address (indicating another row of data with the same name).

Any ideas???

 

[mbarron]

I assuming that you are grouping on the ID. If so , sort on the effective date (descending). This will give the most recent name first. Create a formula to count if the name field is the same as the previous name field:

//start//

if recordnumber=1 then 1 else //needed for first record to be counted
if {ID.Field}<>previous({ID.field) then 1 else //used to count a change in group
if {namefield}<>previous({name.field}) then 1 // indicates that the name has changed

//end//


Then suppress if the formula <> 1
What will be displayed is the current for all ID numbers and any of the previous names.



Mike

 

[Ngolem]

What MBarron has given works fine if a new record has been added with the change but I have the feeling that the {table.effectivedates} time stamp is just a record of when the last update to the record was made...

In which case, unless you know what the original record looked like you won't be able to tell what was updated. Jim Broadbent