Print Page | Close Window

Compare two strings

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=22301
Printed Date: 28 Apr 2024 at 5:14am


Topic: Compare two strings
Posted By: PDR-Chris
Subject: Compare two strings
Date Posted: 02 May 2017 at 9:35am
Hello all,
Please do excuse my noobish inquiry, as I am painfully new to CR, and this is my first proper challenge to tackle.

Someone has been running a bot or a script against our site registration form and is dumping new spam accounts into our DB regularly.

Their behavior, thus far, has been to use the same first and last name per record.
example:
record1: Bob Bob
record2: Jack Jack


What I am trying to do:
1.Find records that have identical first and last names and then compile a report of the results.

We can call the established variables {customer.fname} and {customer.lname} for the sake of ease.

I have poured over the help files and haven't found a non-numeric 'equals' operator that makes sense for this task. I appreciate everyone's help in advance. TY.

-PDR-Chris

-------------
There is no such thing as a "former" sysadmin.


Replies:
Posted By: DBlank
Date Posted: 03 May 2017 at 2:54am
in the select criteria just set the criteria as
{customer.fname} = {customer.lname}

You might consider adding a trim to make sure there you are not missing items if they are adding spaces into the names (before or after)

trim({customer.fname}) = trim({customer.lname})

Posted By: PDR-Chris
Date Posted: 03 May 2017 at 3:06am
Originally posted by DBlank

in the select criteria just set the criteria as
{customer.fname} = {customer.lname}


Hello DBlank, thank you for your reply.
When I have used this method, the report will only generate what seems to be numeric results.
Example: 1 1 Which was apparently used at some point in development for testing purposes.
The other thing that I am not sure how to straighten out was that it appeared to produce the same record, many times. They weren't ALL identical, but based on the detail criteria I had established, most of them did appear to be. I have a suspicion that my target report is going to end up being about 23 pages. When using the code above, it is only five. Is it possible that CR is ending the query because it is erroring out somehow?

-PDR-Chris

-------------
There is no such thing as a "former" sysadmin.

Posted By: DBlank
Date Posted: 03 May 2017 at 3:51am
are you pulling in more than one table?
If so, the joins (or lack of) is usually the issue for "duplicate records".
I am assuming your fname and lname fields are string data types.
The = applies to strings as well as 'numbers'. In this case the "1" is a string not a integer.
Did you use the trim function?
"1" does not = " 1" or "1 "
You can also try to use a like function

trim({customer.fname}) + '*' like trim({customer.lname})

Posted By: PDR-Chris
Date Posted: 03 May 2017 at 4:11am
Originally posted by DBlank

are you pulling in more than one table?
If so, the joins (or lack of) is usually the issue for "duplicate records".

I don't believe so. How can I know? (Or is that something better for another thread?)

Originally posted by DBlank


I am assuming your fname and lname fields are string data types.

Yes. They are.

Originally posted by DBlank


Did you use the trim function?
You can also try to use a like function
trim({customer.fname}) + '*' like trim({customer.lname})

Will try both of those suggestions next and post back.
Thank you DBlank.

-PDR-Chris

-------------
There is no such thing as a "former" sysadmin.

Posted By: DBlank
Date Posted: 03 May 2017 at 4:31am
to see your data source(s) go to Database option on the top
select DataBase Expert option
Your tables are listed on the right
there is a link tab to set the joins

Posted By: PDR-Chris
Date Posted: 03 May 2017 at 7:32am
I can see a little more clearly what is going wrong, based on your guidance.
The table links look correct, but what is happening is that the query is basing the output (customer search) on the order table (first). All customer details are linked off of that table for this particular report.
I am sure this is why Frankensteining one report out of an old one is not the ideal process.

The formula {customer.lname} = {customer.fname} works though- that is the good news.
When I mentioned the duplicates before, what it wasn't showing me (because that's not the droid I am looking for...) was that each of those "1 1" user entries were actually for separate (test) orders placed. This method won't work to pull the information I need because the spam bot never places an order, it just registers new users in the DB.
So, if I can just focus the query to start at the customer details table instead of the order one, I think I will be gtg.

-PDR-Chris

-------------
There is no such thing as a "former" sysadmin.

Posted By: DBlank
Date Posted: 03 May 2017 at 8:10am
just create a new report and only pull in your customer table.
this way you are not bothering with orders and can query the larger table.
You also might need to use some other criteria to get rid of false positives.
You likely have a seeded intgeger as a PK in teh cusotmer table where you can use a criteria to remove the test records like "1,1"
"and not ([customer.id] [x,y,z])"
replacing x,y z with the Ids of your test customers.
Or you might be able to exclude records that are inactive or record deleted (depending on how your address the bot records you are finding)
"And {customer.active} = 'Y' "


Print Page | Close Window