Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Compare two strings Post Reply Post New Topic
Author Message
PDR-Chris
Newbie
Newbie
Avatar

Joined: 12 Apr 2017
Location: United States
Online Status: Offline
Posts: 4
Quote PDR-Chris Replybullet Topic: Compare two strings
    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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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})

Edited by DBlank - 03 May 2017 at 2:55am
IP IP Logged
PDR-Chris
Newbie
Newbie
Avatar

Joined: 12 Apr 2017
Location: United States
Online Status: Offline
Posts: 4
Quote PDR-Chris Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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})
IP IP Logged
PDR-Chris
Newbie
Newbie
Avatar

Joined: 12 Apr 2017
Location: United States
Online Status: Offline
Posts: 4
Quote PDR-Chris Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
IP IP Logged
PDR-Chris
Newbie
Newbie
Avatar

Joined: 12 Apr 2017
Location: United States
Online Status: Offline
Posts: 4
Quote PDR-Chris Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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' "

Edited by DBlank - 03 May 2017 at 8:11am
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.