Suppress duplicate address records? 1

[Crazycritter1]

HI there -

I am really new to Crystal 10 - basically fighting my way through things.... I'm currently trying to eliminate records that have duplicated addresses....

My current selection is by account number, for instance, if Mr. Smith has an account number 1234, and an address of 333 S. Main, whereas Mrs. Smith has an account number of 5678, and lives in the same residence, I'd like for the report to eliminate the record of Mrs. Smith because her address matches Mr. Smith. And, likewise if the Smith children have other accounts - I don't want them reported either.

Is there a good way of creating a formula to evaluate the contents of any given address field for duplicates in order to suppress any matches - so that only 1 occurrence of the address is reported?

If you are able, please be descriptive in your answers, as I am very new at this, and don't know the lingo yet!!

I'd appreciate any and all support...

Thanks!!!

 

[moonknightt]

if you have access to the database or could change the database record you could have a field called primary member where all the primary members could be marked with Y or N. And mr. Smith will fall into the primary member category. then on your report you could select only records where primary member = Y

 

[lbass]

You could concatenate the relevant fields in a formula {@concat}:

{table.address}+{table.city}+{table.state}+{table.zip}

Then use this as a sort field. Then go to format->section->details->suppress->x+2 and enter:

{@concat} = previous({@concat})

-LB

 

[kutoose]

Create a formula - Which is combination of address and city

Group on the formula.

In this case only one instance of the Address will be displayed.




kutoose@yahoo.com

 

[Madawc]

You'll also find that the same address has been entered marginally differently in a few cases. No way that I can think of to get a computer to spot this reliably, but the sort will show most of them.

Madawc Williams (East Anglia, UK)

 

[Crazycritter1]

Hello again -

Thank you for all of the wonderful suggestions...

On the first one - with regards to sorting as a primary member, all of my records would be considered "primary", as each of them have their own record/account number, and aside from them having identical addresses, these records aren't associated with each other anywhere else.

The concatenate suggestion could work better - but I am having trouble with building the formula, as crystal is refusing the {@concat}:{table.address}+{table.city}+{table.state}+{table.zip} as a valid string.

However, even if I did this process - would the suppression remove the entire entry, or would it simply "blank" out the address from displaying, leaving the name visible?

Thanks again - I truely appreciate the help!

 

[lbass]

What do you mean by "Crystal is refusing the {@concat}:{table.address}+{table.city}+{table.state}+{table.zip} as a valid string." Did you get an error message? Please provide the error message. These fields are all strings, aren't they? If so, there should be no probelm in creating the formula. You are creating this in the insert->formula->new formula area, correct?

Since you would use my suggestion in the section expert, it will remove entire rows that meet the criterion, not just fields.

-LB

 

[Crazycritter1]

If I go into the new formula area, and enter:

{@concat}:{table.address}+{table.city}+{table.state}+{table.zip}, substituting my table values, and try to save and close, I get the error message of:

"There is an error in this formula, do you want to save it anyway?" - and gives you buttons for Yes and No to click.

If you click on No - it gives you a 2nd box, saying that this field name is unknown, and highlights the {@concat} portion of the string.

 

[lbass]

The formula should be:

{table.address}+{table.city}+{table.state}+{table.zip}

Please note that {@concat} is just a name I gave the formula.

-LB

 

[Crazycritter1]

Ok,

I was able to get the formula to concatenate, but am having trouble with the suppression...

I was able to enter the suppression string in the formula editor under "Suppress if Duplicated", and asked that it display a string called "Delete", however it flags every entry for suppression... and leaves me with nothing...

I think we're on the right track.... am I doing it wrong, or does it need to be tweaked a bit?

Thanks Again!

 

[lbass]

Go to the section expert (format section, not format->field). Then highlight the details section->suppress->x+2 and enter the following formula there:

{@concat} = previous({@concat})

-LB

 

[beanbrain]

Crazycritter:

You might try this in a formula.

WhilePrintingRecords;
StringVar Address;

If {table.address} in Address then
  Address = Address
else
  Address = Address + {table.address};

"In" is a very powerful directive. It instructs Crystal to look for the value to the left of "in" within the variable to the right of "in.

 

[synapsevampire]

Slight change there:

WhilePrintingRecords;
StringVar Address;

If {table.address} in Address then
true
else
Address := Address + {table.address};

Note the :+ instead of =, and the use of true rather than assigning it to itself.

-k

 

[synapsevampire]

Sorry, that should have said:

Note the := instead of =, and the use of true rather than assigning it to itself.

-k

 

[Crazycritter1]

[maroon]lbass -

Thank you for all of your help! I was able to get the suppression to work properly, and it was exactly what I needed.

To those of you who also had excellent suggestions: synapsevampire, beanbrain, Madawc, kutoose, and Moonknightt -- I really appreciate your time and suggestions.... thanks again! [/maroon]