CODEWITHSUNDEEP
crystal-reportsreport
Dagz200
Dagz200

Reputation: 211

Crystal Report show history based only on two field changes

I’m trying to write a report using the history_table. I want to know how would I only bring the records only to show a history if only two fields have changed ex (ss.dem1) and (ss.dem2) in the table If anything else changes like phone or address that are in the table as well to not bring that up or suppress it in the report. I want to see the data only those 2 fields have changed and every time it has changed as a history.
enter image description here

This would be an example of what I'm trying to accomplish.

I also used tried to use a suppression formula but only gave me the last record and not show me the example like I would of like above

not onlastrecord and next(PATID)=PATID and next(ssdem1)=ssdem1 or not onlastrecord and next(PATID)=PATID and next(ssdem2)=ssdem2

Any help is greatly appreciated.

Upvotes: 1

Views: 1215

Answers (1)

Ryan
Ryan

Reputation: 7287

On the DB side you could do a self-join and only keep the records where one of those items you're interested in has changed, ex:

select WHATEVER
from HISTORY H1, HISTORY H2
where H1.PAT_ID=H2.PAT_ID
 and (H1.DEM1 <> H2.DEM1
      or H1.DEM2 <> H2.DEM2
      etc...)

Or, you could order the report by PAT_ID, then by the date/time of the edit and then suppress the rows where nothing changes, which sounds like what you were trying to do. If you post the exact suppression formula I might be able to see where you were going wrong. Should be something like:

not(onfirstrecord) or
({TABLE.PAT_ID}=previous({TABLE.PAT_ID})
 and {TABLE.DEM1}=previous({TABLE.DEM1}) //check all fields you're concerned with for equality
 and {TABLE.DEM2=previous({TABLE.DEM2})
 and etc...)

Upvotes: 1

Related Questions