Find duplicates and show them in a Query

weblike

Does anyone now how to create a query which finds duplicates of records?
I tried to use this, but didn't worked:
FIND Customer WHERE (Customer.ID <> ThisCustomer.ID AND Customer.Code=ThisCustomer.Code)

This rule doesn't return anything. I tried to use it i a process too, but no luck.

Any idea will be appreciated.

Thank you

Powerm

Do it in 2 steps, create a Rule for your BO and a new calculated Attribute :

1) If EXISTS Customer WHERE (Customer.ID <> ThisCustomer.ID AND Customer.Code=ThisCustomer.Code)
Then
Customer.Duplicate='Yes'

2) Create a Query showing only Customers where Customers.Duplicate='Yes'

weblike

Thanks Powerm,

This makes sense.
I will try this..

Nice day.

ACDC

Have you tried this one:

FIND Customer WHERE EXISTS Customer WHERE (Customer.ID <> ThisCustomer.ID AND Customer.Code=ThisCustomer.Code)

Powerm

I don't think "ThisCustomer" will work as you want to sow all Duplicates.
"This" is usually used on a Rule or Process.

weblike

The rule works if the customer is created manually.

I have tested to import several .csv files with duplicate records and the rule doesn't work in this scenario.

Does anyone know hoe to avoid importing duplicates records?

I have this format of .csv file: Code;Name;Address;City
also have: customers_day1_import.csv......customers_day2_import.csv

For example:

In customers_day1_import.csv I have:

777;CustomerA;Broadway no.1; New York

In customers_day2_import.csv I have:

777;CustomerA;broadway no.1; New York

From day1 and day 2 we only have the address typed different "Broadway" vs. "broadway".
When I import these 2 .csv files in BO appears duplicates records for this customer.

Does anyone know how to import records and only if "Code" is different then should create new record?

Thank you all.

Powerm

Don't import directly to BO Customer. Create an other temporary BO, like "CustomerTemp" . Import your csv to that BO, with a Rule checking the Customer code and an Attribute like Duplicate Yes/No
Once you have the duplicates marked you can filter or delete them and import the others to Customer BO.

weblike

Hi Powerm,
Thank you for your reply.

The rule works only if the customer is created manually.

IF I import from .csv it doesn't work, like the rule is not there...
I guess for import templates another approach would work. I don't know which one unfortunately.

Powerm

1- Import your csv to the temporary BO
2- Instead of a Rule create a Process using that Rule
3- Have a query showing that temporary rule
4- Launch the Process in order to have duplicates found
5- have an other Process creating Customers out of the selected entries, if you wish

rocketman

Here's a technique I use

Create a Non persistent BO holding the data you want to check for duplicates on (including a unique identifier - could just be the ID attribute)

PICK ONE OR MORE FROM [main BO] WHERE mainbo.attributes = NPBO.attributes AND mainbo.uniqueID <> NPBO.UniqueID (this last bit ensure the one you want to keep does not appear in the list)

DELETE mainbo

Usually I put this in a process attached to a query list (at the side of each line (I call it duplicate check) I then run it occasionally ordering by Customer name so any possible duplicates are together in the list. Click the button against the one I want to keep - job done

It gets a bit more complex if you need to move any referenced attribute data before you do the delete - but this'll do for starters.

rocketman

.... of course it's always best not to let the duplicates happen in the first place

Jaymer

Powerm wrote
Do it in 2 steps, create a Rule for your BO and a new calculated Attribute :

1) If EXISTS Customer WHERE (Customer.ID <> ThisCustomer.ID AND Customer.Code=ThisCustomer.Code)
Then
Customer.Duplicate='Yes'

2) Create a Query showing only Customers where Customers.Duplicate='Yes'

FYI
Cost me about 20 minutes... but in v8.1, & MS SQL, you cannot have an attribute named "duplicate".
Would accept the field in the attr table, but crap out on saving the rule.
Kept thinking I was going crazy - eventually changed the name of the field.

BLOMASKY

It is easy to do in SQL. It requires you to join the SAME table together. I don'w know any way to do that in Aware.

in SQL:

select customer.ID from customers C1 inner join customer C2 on C1.custnumber = C2.custnumber and C1.ID > C2.ID

Bruce

Jaymer

Bruce,
thats basically the standard way WHEN BO IS NEW, to make sure you're not entering a Duplicate.
used in Rules in the standard CRM example.

I ended up using this:

update USERLISTLEADS 
set is_dup = 1
where Email =
  (
  select U.Email
  from USERLISTLEADS U
  group by U.Email
  having count(*) > 1
  )
AND ps_User_RID=@UserID

There's lots of examples online of how to:
1) Delete duplicates in SQL Server
2) Mark duplicates in SQL Server
Most of them leave a record and delete the others.

I needed to mark ALL dups for a user to come along and pick which one to keep. Was, of course, nice & fast to do it as a SP.
jaymer...