Print Page | Close Window

searching for duplicate field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8152
Printed Date: 04 May 2024 at 10:59am


Topic: searching for duplicate field
Posted By: draven422
Subject: searching for duplicate field
Date Posted: 28 Oct 2009 at 2:24pm
I am trying to create a formula in a report that will display a flag if a certain field is duplicated.
Right now I have:
if Name = previous(Name) then "Y"
 
Since that will only look at the previous return, I am limited to them side-by-side.  Is there something that can be written to set the flag if it matches any of the returns?  I was going to sort by that Name field, but the listing needs to be in a specific sort order (sequential).
 
Any help is greatly appreciated.


Replies:
Posted By: lockwelle
Date Posted: 29 Oct 2009 at 6:25am
a variable, I like shared, but global would work.
Create a delimited string of, in this case names, and then search if the name exists.  Something like:
shared stringvar allNames;
 
if instr(allNames, "|" + Name + "|")>0 then
  "Y"
else(
  allNames := allNames +  "|" + Name + "|";
  "N"//or could be "" the empty string
)
 
you create a string that allows for unique identification of a name and then you search it. If you find it, you set your flag, if not, you can set or not set a flag--and you add the value for future searches.
 
HTH

Posted By: draven422
Date Posted: 29 Oct 2009 at 7:13am
You da man!
 
Thank you so much.  That worked perfectly.

Posted By: draven422
Date Posted: 29 Oct 2009 at 7:22am
OK, I have to throw a monkey wrench into it already.
 
Would it be easy to have the flag show what record it matched with?  Something like "Duplicate to record 123."
 
Thanks again!

Posted By: lockwelle
Date Posted: 29 Oct 2009 at 8:59am
yeah, just need to add the record number in as another field, so you can change it to:
shared stringvar allNames;
shared stringvar rec;
local numbervar ind :=  instr(allNames, "|" + Name + "^");
local numbervar carat;
 
if ind>0 then (
  carat := instr(ind, allNames, "^");
  ind := instr(carat, allNames, "|");
  rec := mid(allNames, carat+1,  ind - carat - 2);
  "Y"
)
else(
  allNames := allNames +  "|" + Name + "^" + RecordNumber;
  "N"//or could be "" the empty string
)
 
then in another formula you can access the recordNumber via
shared stringvar rec
 
Glad I could help.

Posted By: draven422
Date Posted: 29 Oct 2009 at 11:30am
Thanks again lockwelle.
 
I must've missed something.  I get an error stating, "String length is less than 0 or not an integer."  It then opens the formula highlighting the rec variable.  I'm assuming it was hitting on the AllNames variable, because when I set it to null, it doesn't error, but it doesn't flag the duplicate.

Posted By: draven422
Date Posted: 29 Oct 2009 at 12:57pm

I think I fixed it.  I put in a check to make sure it wasn't going into a negative number.

 
shared stringvar allNames;
shared stringvar rec;
local numbervar ind :=  instr(allNames, "|" + Name + "^");
local numbervar carat;
if ind>0 then (
  carat := instr(ind, allNames, "^");
  ind := instr(carat, allNames, "|");
  if carat > ind then (
    rec := mid(allNames, carat+1,  carat - ind - 2);
    "Y";
  ) else(
    rec := mid(allNames, carat+1,  ind - carat - 1);
    "Y";
  )
)
else(
  allNames := allNames +  "|" + Name + "^" + RecordNumber;
  ""
)

Posted By: lockwelle
Date Posted: 30 Oct 2009 at 6:10am
oops!
allNames := allNames +  "|" + Name + "^" + RecordNumber;
should be:
allNames := allNames +  "|" + Name + "^" + RecordNumber + "|";
it's what I envisioned when I was writing the equation.  The string was |Name|, then adding the record # was to change it to |Name^Record|
 


Print Page | Close Window