Tracking duplicate records? 
Author Message
 Tracking duplicate records?

I have a problem that should be fairly easy to
slove, but I cant figure it out. Below is a
procedure that should check to see if the record
just added (always the last record in the table)
is a duplicate transaction. Thre are 4 tables in
the procedure.

TrackingTable - Master table for the data. Has
header info from the imported transactions, only
unqiue field is "DOC KEY" which is the index I
use for the master-detail stuff

TrackingKeysTable - Detail table for data. Has
all the information that was read in from the
imported transaction.

DupCheckTrack - A differnent pointer in to the
Tracking table, not a new table at all.

DupCheckKeys -  A differnent pointer in to the
TrackingKeys table, not a new table at all.

I have like 7 varibles called "Filterstring" they
are just because I am too stupid to be able to
build the filterstring, and all the ( )'s
correctly in one pass. A transaction is a
dupilicate if and only if all the values in the
keys table are identical. The tracking table
which is mostly header information will quite
often have dupilicate records, that is to be
execpted. The filter string is just to narrow
down the number of records to check, based on the
header info. Basicly it is a waste of time to
look at any record if the header info does not
match up. All I need to check for is one
dupilicate, I do not need to find out how many
dupilcates there are. I apply a filter of "NOT
[DOC KEY] ..." so that I do not check the record
against itself. When the procedure is called the
current record in the TrackingTable is the newly
appended record that needs to be checked. The
DupCheck Tables should be pointing at the very
first record.

Now heres the problem, the results are totaly
unpredictable. Some times it is right, some times
it is wrong.

TIA, Vincent

{*************************************************
*******************}
Procedure CheckDup();
//Once we get here we know the current record in
TrackingTable is
// new and has not been checked to see if it is a
duplicate file yet.
//
// By having 2 pointers in to the same table
(DupCheckTrack, Trackingtable
// are the same table)  We can compare the
Current record with all the other
// records in the talbes. As the table's will
likly get huge, we will filter
// the table as much as we can before we start
searching it.

Var
 FilterString  : String;
 FilterString1 : String;
 FilterString2 : String;
 FilterString3 : String;
 FilterString4 : String;
 FilterString5 : String;
 FilterString6 : String;
 FieldString   : String;
 IsDup         : String;

Begin
  AddToLog('Running Duplicate Check.');
  //tracking table has the record to check , so
filter the dupcheck on
  //the values in the tracking table
  With DataModule1 do
  Begin
  FieldString := TrackingTable['From Partner
Qual'];
  FilterString1 := ' [From Partner Qual] = ' +
Char(39) + FieldString +
Char(39);
  FieldString := TrackingTable['From Partner'];
  FilterString2 := ' [From Partner] = ' + Char
(39) + FieldString + Char(39);
  FieldString := TrackingTable['To Partner Qual'];
  FilterString3 := ' [To Partner Qual] = ' + Char
(39) + FieldString +
Char(39);
  FieldString := TrackingTable['To Partner'];
  FilterString4 := ' [To Partner] = ' + Char(39)
+ FieldString + Char(39);
  FieldString := TrackingTable['In/out bound'];
  FilterString5 := ' [In/out bound] = ' + Char
(39) + FieldString + Char(39);
  FieldString := TrackingTable['Gentran Status'];
  FilterString6 := '  [Gentran Status] = ' + Char
(39) + FieldString +
Char(39);
  FilterString  := '(((' + FilterString1 + ')
And ' + '(' + FilterString2 +
')) And ' +
                   '((' + FilterString3 + ')
And ' + '(' + FilterString4 +
'))) And ' +
                   '((' + FilterString5 + ')
And ' + '(' + FilterString6 +
'))';
  FilterString := '(' + FilterString + ') And (
Not ([DOC KEY] = ' +
IntToStr(TrackingTable['Doc Key']) + '))';
  AddToLog(FilterString);
  DupCheckTrack.Filter := FilterString;
  DupCheckTrack.Filtered := True;

  DupCheckTrack.Refresh;
  DupCheckKeys.Refresh; // refresh should not be
needed, but is here to
remove doubt
  DupCheckTrack.First;
  IsDup:='No';
  While Not DupCheckTrack.Eof Do
  Begin
   DupCheckKeys.First; //reset pointer
   TrackingKeysTable.First;
   While Not (DupCheckKeys.Eof OR
TrackingKeysTable.Eof) Do
    Begin
     If DupCheckKeys['Key Field Name'] =
TrackingKeysTable['Key Field Name']
Then
      If DupCheckKeys['Key Field Value'] =
TrackingKeysTable['Key Field
Value'] Then
       IsDup := 'Yes'
      Else
       Begin
        IsDup:= 'No';
        Break; //one no is all we need to be sure
it is not a dup
       End
     Else
       Begin
        IsDup := 'No';
        Break;
       End;
     DupCheckKeys.Next;
     TrackingKeysTable.Next;
    End;
   IF IsDup ='Yes' Then Break; // no need to keep
looking, one match is all
we care about
   DupCheckTrack.Next;
  End;
 TrackingTable.Edit;
 TrackingTable['Is Dup'] := IsDup;
 TrackingTable.Post;
 If IsDup = 'Yes' Then
  AddToLog('Duplicate Transaction Found');
 End;
End;

{*************************************************
****************}

Sent via Deja.com http://www.*-*-*.com/
Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT  
 Tracking duplicate records?
Okay nevermind I figured it out. Although the code in the post is not
very good, and I have re-written it using only 2 tables instead of 4.
The problem had nothing to do with this code seg. I had called
DisableControls before entering in to this procedure. I did not know
(and just found out) that that breakes master/detail relationships.
That is why the results were highly varible, the tables were out of
sync.

I now have a much better routine to do it that uses local varibles to
do the compares with and has 1/2 the database lookups in it. Dont know
if it is the best way, but it works.

Ah well...


Quote:

> I have a problem that should be fairly easy to
> slove, but I cant figure it out. Below is a
> procedure that should check to see if the record
> just added (always the last record in the table)
> is a duplicate transaction. Thre are 4 tables in
> the procedure.

> TrackingTable - Master table for the data. Has
> header info from the imported transactions, only
> unqiue field is "DOC KEY" which is the index I
> use for the master-detail stuff

> TrackingKeysTable - Detail table for data. Has
> all the information that was read in from the
> imported transaction.

> DupCheckTrack - A differnent pointer in to the
> Tracking table, not a new table at all.

> DupCheckKeys -  A differnent pointer in to the
> TrackingKeys table, not a new table at all.

> I have like 7 varibles called "Filterstring" they
> are just because I am too stupid to be able to
> build the filterstring, and all the ( )'s
> correctly in one pass. A transaction is a
> dupilicate if and only if all the values in the
> keys table are identical. The tracking table
> which is mostly header information will quite
> often have dupilicate records, that is to be
> execpted. The filter string is just to narrow
> down the number of records to check, based on the
> header info. Basicly it is a waste of time to
> look at any record if the header info does not
> match up. All I need to check for is one
> dupilicate, I do not need to find out how many
> dupilcates there are. I apply a filter of "NOT
> [DOC KEY] ..." so that I do not check the record
> against itself. When the procedure is called the
> current record in the TrackingTable is the newly
> appended record that needs to be checked. The
> DupCheck Tables should be pointing at the very
> first record.

> Now heres the problem, the results are totaly
> unpredictable. Some times it is right, some times
> it is wrong.

> TIA, Vincent

> {*************************************************
> *******************}
> Procedure CheckDup();
> //Once we get here we know the current record in
> TrackingTable is
> // new and has not been checked to see if it is a
> duplicate file yet.
> //
> // By having 2 pointers in to the same table
> (DupCheckTrack, Trackingtable
> // are the same table)  We can compare the
> Current record with all the other
> // records in the talbes. As the table's will
> likly get huge, we will filter
> // the table as much as we can before we start
> searching it.

> Var
>  FilterString  : String;
>  FilterString1 : String;
>  FilterString2 : String;
>  FilterString3 : String;
>  FilterString4 : String;
>  FilterString5 : String;
>  FilterString6 : String;
>  FieldString   : String;
>  IsDup         : String;

> Begin
>   AddToLog('Running Duplicate Check.');
>   //tracking table has the record to check , so
> filter the dupcheck on
>   //the values in the tracking table
>   With DataModule1 do
>   Begin
>   FieldString := TrackingTable['From Partner
> Qual'];
>   FilterString1 := ' [From Partner Qual] = ' +
> Char(39) + FieldString +
> Char(39);
>   FieldString := TrackingTable['From Partner'];
>   FilterString2 := ' [From Partner] = ' + Char
> (39) + FieldString + Char(39);
>   FieldString := TrackingTable['To Partner Qual'];
>   FilterString3 := ' [To Partner Qual] = ' + Char
> (39) + FieldString +
> Char(39);
>   FieldString := TrackingTable['To Partner'];
>   FilterString4 := ' [To Partner] = ' + Char(39)
> + FieldString + Char(39);
>   FieldString := TrackingTable['In/out bound'];
>   FilterString5 := ' [In/out bound] = ' + Char
> (39) + FieldString + Char(39);
>   FieldString := TrackingTable['Gentran Status'];
>   FilterString6 := '  [Gentran Status] = ' + Char
> (39) + FieldString +
> Char(39);
>   FilterString  := '(((' + FilterString1 + ')
> And ' + '(' + FilterString2 +
> ')) And ' +
>                    '((' + FilterString3 + ')
> And ' + '(' + FilterString4 +
> '))) And ' +
>                    '((' + FilterString5 + ')
> And ' + '(' + FilterString6 +
> '))';
>   FilterString := '(' + FilterString + ') And (
> Not ([DOC KEY] = ' +
> IntToStr(TrackingTable['Doc Key']) + '))';
>   AddToLog(FilterString);
>   DupCheckTrack.Filter := FilterString;
>   DupCheckTrack.Filtered := True;

>   DupCheckTrack.Refresh;
>   DupCheckKeys.Refresh; // refresh should not be
> needed, but is here to
> remove doubt
>   DupCheckTrack.First;
>   IsDup:='No';
>   While Not DupCheckTrack.Eof Do
>   Begin
>    DupCheckKeys.First; //reset pointer
>    TrackingKeysTable.First;
>    While Not (DupCheckKeys.Eof OR
> TrackingKeysTable.Eof) Do
>     Begin
>      If DupCheckKeys['Key Field Name'] =
> TrackingKeysTable['Key Field Name']
> Then
>       If DupCheckKeys['Key Field Value'] =
> TrackingKeysTable['Key Field
> Value'] Then
>        IsDup := 'Yes'
>       Else
>        Begin
>         IsDup:= 'No';
>         Break; //one no is all we need to be sure
> it is not a dup
>        End
>      Else
>        Begin
>         IsDup := 'No';
>         Break;
>        End;
>      DupCheckKeys.Next;
>      TrackingKeysTable.Next;
>     End;
>    IF IsDup ='Yes' Then Break; // no need to keep
> looking, one match is all
> we care about
>    DupCheckTrack.Next;
>   End;
>  TrackingTable.Edit;
>  TrackingTable['Is Dup'] := IsDup;
>  TrackingTable.Post;
>  If IsDup = 'Yes' Then
>   AddToLog('Duplicate Transaction Found');
>  End;
> End;

> {*************************************************
> ****************}

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. duplicate records in join

2. Duplicate Error on insert record

3. Handling Duplicate records

4. Using SQL to delete duplicate records

5. Duplicate records

6. Duplicate records in Interbase

7. Duplicating Records

8. Duplicating records D4 & Paradox

9. How to Duplicate Records with TTable Component

10. duplicate records in TDBGrid

11. append records without duplicates keys

12. Duplicate record problem D3 C/S

 

 
Powered by phpBB® Forum Software