How to only find changed records 2

[Bennie47250]

Using Crystal Reports Version 7.0

Trying to create a report that will only show records that have changed.

I’m using a file on our AS400 that has “Journaling” turned on. Basically Journaling is making copies of records when the record is entered into and exited out of so we have a before and after snapshot of the record.

However sometimes the records may not have changed; for example is you go into the file to find the phone number, the journaling makes the entries but we don’t want these records on the report as nothing about the record has changed.

Any thoughts on how to eliminate these records that have not changed from the report? If they can’t be eliminated, how about a way to only highlight the records that actually changes.

Thanks
Bennie

 

[synapsevampire]

There are a few ways you might address this, you can group by (whatever uniquely qualifies these records) and use a count to determine if there are multiples, perhaps even using a group selection formula.

You might also use the Next() and Previous() functions to determine if the next or previous records are related.

If you need to do a field level verification of what was actually changed, you can use the next()/previous(), or perhaps even store values in variables and compare them.

I would atempt to eliminate rows PRIOR to bringing them into Crystal, meaning in a View/Table/SP.

Hope one of these fits the bill.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

Is there no "Date/Time" stamp when records are edited?

if the records are stored sequentially on entering and leaving....how do you avoid a "duplicate key" error?
Is there another key field added to this "journaling" table?

If so...then perhaps you can then run a report using the Primary keys of the original table. There will ALWAYSbe 2 records of each (one entering and one leaving).

Then group by a single formula based on the primary keys

@grouping
//I usually separate each with a "/" for debug purposes
//hopefully the resulting string is less than 254 chars
//if it isn't then you will need more groupings

{table.primarykey1} + "/" +
//make them all text so if any keys are numeric use ToText
totext({table.primarykey2}) + "/" +
...add the rest of the keys...

Now we only want records that have changed so we want to store the first record...then compare the second record and print both records if they are not the same

you can use arrays for this one array for record1 and one for record2. Put a flag in the report header

@SetFlag (suppressed in report header)
WhilePrintingRecords;
numbervar resetFlag := 0;

@initialization (suppressed in the group header)

WhilePrintingRecords;
numbervar resetFlag ;

//make arraystext and have an element for each field of the record
if resetFlag = 0 then
(
stringVar array record1 := ["","","","".....""];
stringVar array record2 := ["","","","".....""];
);

Now a formula to store the array values

@store (suppressed in the detail section)

WhilePrintingRecords;
numbervar resetFlag ;
stringVar array record1 ;
stringVar array record2 ;

if resetFlag = 0 then
(
//assign the record fields to each element of record 1
//if the fields are numeric use Totext
record1[1] := {table.field1};
record1[2] := totext({table.field2});
record1[3] := {table.field3};
record1[4] := {table.field4};
... assign the rest here...
ResetFlag := 1;
)
else
(
//assign the record fields to each element of record 2
//if the fields are numeric use Totext
record2[1] := {table.field1};
record2[2] := totext({table.field2});
record2[3] := {table.field3};
record2[4] := {table.field4};
... assign the rest here...
ResetFlag := 0; //to reinitialize a new array set
)

Now in the group footer place the display of each array
it is probably best to have a footer subsection for each array. In the Group header you can put column labels for each array element.

@displayRecord1 (placed in group footer subsection A)
WhilePrintingRecords;
stringVar array record1 ;

//this assumes the the result is less than 254 chars
Join(record1," "

Put a conditional suppress on the field

WhilePrintingRecords;
numbervar resetFlag ;
resetFlag = 1;

do the same in Section B for the other array

Now in the Section expert for the entire group footer, in the conditional suppress use the formula

WhilePrintingRecords;
booleanVar Suppressflag := true;
numbervar resetFlag ;
stringVar array record1 ;
stringVar array record2 ;
numberVar icount;

if resetFlag = 0 then
(
for icount := 1 to ubound(record1) do
(
if record1[icount] <> record2[icount] then
(
Suppressflag := False;
exit for;
);
);
);

Suppressflag;


There....I think that will give you an idea of one approach. You haven't given a lot of details on the size of these tables...both number of records and number of fields/record.

Of course if there is a timestamp field if the record is edited in your &quot;journaling&quot; table then the above is not necessary...you would only compare timestamps

Hope this gives you some ideas




Jim Broadbent

 

[Bennie47250]

Thanks to both of you for the suggestions.

I'm not certain of the journaling file has key fields but it does have a time stamp. I was considering building a MS Access table and making several of the fields Key to eliminate them as they were being imported into this table. Then just run the Crystal off of it.

So I can expand my knowledge, I going to play around with what has been suggested.

 

[Ngolem]

well in my case if there is a timeStamp to work with
Store the timestamps as one array element in each record

replace the condition supress shown for the Group footer (ie. the last formula) with simply

WhilePrintingRecords;
numbervar resetFlag ;
stringVar array record1 ;
stringVar array record2 ;

resetFlag = 0 and
record1[(tStamp element}] <> record2[(tStamp element}]


this will make the method operate much faster
You can hard code the value of the (tStamp element) since you know which array element it is when you assign the values in the detail formula


Jim Broadbent

 

[Bennie47250]

Jim, Thanks again.

Interesting enough, the date time stamp is the same for when the record is entered into and exited out of so I don't think I can use that for the key field.

What I have done is:

Created 2 groups; Group 1 is Vendor # Group 2 is time stamp

Created a formula that is the name and address information concatenated them together.
@ Compare
(Name+Address1+Address2+City+State)

I have suppressed this in the detail section.

In the Group header #2, I have another formula (@Duplicate) that is using the next function to compare the @compare formula; this is:
if Next ({@Compare}) = {@Compare}
then &quot;NO CHANGE &quot;
else &quot;SOMETHING HAS CHANGED&quot;

So now in the group header I can at least tell that a record has changed or not and what has changed.

The remaining problem is the user does not want to see the records if there is “NO CHANGE”

I think the code you gave me can handle not printing the records if the @Duplicate formula result is “NO CHANGE” but I’m unable to pick this part of the code out. I have tried using the select expert to only select these records but this won’t work due to the evaluation time.

What part of the code will do this or is some additional code necessary?

Thanks

 

[Ngolem]

Actually the code shows both records all the time....But the key to this is the conditional suppress in the section expert...this determines if 2 records are suppressed or not... this is the last formula in my first post...since the timestamp doesn't appear to be useful....go back to my first post.

You cannot use a record select since there is a lot of comparisons that must be made...and those comparisons must be done on sorted data. Jim Broadbent

 

[Bennie47250]

Jim, I hope you are still following this. I'm working my way thru you code and I'm hung up at this point:

@initialization (suppressed in the group header)

WhilePrintingRecords;
numbervar resetFlag ;

//make arraystext and have an element for each field of the record if resetFlag = 0 then
(
stringVar array record1 := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;.....&quot;&quot;];
stringVar array record2 := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;.....&quot;&quot;];
);

What if anything goes between the &quot;&quot; ?
What is the purpose of the ..... ?

Thanks

 

[Ngolem]

sorry....just got tired of additional repeats of ,&quot;&quot;,

Not knowning how many fields are in your tables I could not specify the number of elements of the array...just create/initialize as many elements as you need...to a maximum of 1000

for example if your table contains 10 fields the formula should read

stringVar array record1 := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;];
stringVar array record2 := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;];

any more questions just ask

Jim Broadbent

 

[Bennie47250]

OK so nothing goes between the &quot;&quot; gotcha.

Now the table that I'm reading will have over 75 fields, but the report is only using 10. Do I need this ,'', for all the fields in the table or just the ones I'm using?

Thanks for the help.

 

[Ngolem]

Hi Bennie;

I have re-thought this problem and can see some difficulties.

One problem is that we must bring the records in a certain order, this is to be able to comparison.

A record in a table has indices or primary keys that define a unique record. So my initial thoughts were to group on those primary keys....but are any of those 10 fields that you want to compare primary key fields?

If so then they should not be part of the grouping formula I described earlier...since any change to these values would not consecutively place the records together.

I think since you are only comparing 10 fields there is an easier way to do the comparison.(adapting some of synapsevampire;s thoughts)

try this

1. Prepare and group on a formula based on the Primary keys of the data table...eliminating those fields that the user may change as I had described previously

2. Create a &quot;running total field&quot; , {#testcount}, based on one of the 10 fields you want to compare and do the running total on the basis of count...and never reset. We will use this value later to suppress a detail line. (Place this field in the detail section when you create it then delete the field from the section....that way we can still reference it later)

3. Now in the detail section create 10 formulas and arrange them like this (I am only showing the first 4 pairs

Pfield1 Pfield2 Pfield3 Pfield4
{table.field1} {table.field2} {table.field3} {table.field4}

where Pfield1 is the previous value of {table.field1}

@Pfield1

whilePrintingRecords;
if not onfirstrecord then
previous({Table.Field1})
else
&quot; &quot;;

do this for all 10 fields that you wish to compare.

4. Now in the conditional suppress of the Detail section we will put this formula

WhilePrintingRecords;
If onfirstrecord or remainder({#testcount},2) <> 0 or
(
@Pfield1 = {table.field1} and
@Pfield2 = {table.field2} and
@Pfield3 = {table.field3} and
@Pfield4 = {table.field4} and
@Pfield5 = {table.field5} and
@Pfield6 = {table.field6} and
@Pfield7 = {table.field7} and
@Pfield8 = {table.field8} and
@Pfield9 = {table.field9} and
@Pfield10 = {table.field10}
)
then
true
else
false;

This will suppress the detail section on every odd record or if all 10 previous field values = the current ones.

There is no need for complicated arrays....much simpler this way Jim Broadbent

 

[Bennie47250]

Jim,

Thanks for the new code. As I have struggled thru this I have learned quite a few new things. Got this to work for me and while there are some more concerns I think I will be able to handle them.

 

[Ngolem]

Glad to be of help Jim Broadbent