| Author |
Message |
PDR-Chris
Newbie
Joined: 12 Apr 2017
Location: United States
Online Status: Offline
Posts: 4
|
   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 Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
PDR-Chris
Newbie
Joined: 12 Apr 2017
Location: United States
Online Status: Offline
Posts: 4
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
PDR-Chris
Newbie
Joined: 12 Apr 2017
Location: United States
Online Status: Offline
Posts: 4
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
PDR-Chris
Newbie
Joined: 12 Apr 2017
Location: United States
Online Status: Offline
Posts: 4
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
|
|
