Duplicate records

ghilton

Hello

My marketing database which looks for duplicate email address is working fine but I do have a couple of issues

if an email adress is imported with 2 spaces at the end of the address and I have an exsisting email address which is the same but with out the 2 spaces on the end. AwareIM is not picking these up as duplicates? How can I get around this?
example@example.com
example@example.com(Space)(Space)

2 Some emails are being imported with capital letters how would I search all records and make them lower case?

Thanks

tford

Have you tried TO_LOWER_CASE and REPLACE_PATTERN functions?

ghilton

Hi Tom

yes should of said! I have tried but do not really understand the syntax hence does not work!

tford

Try: BO.attribute_stripped=BO.attribute(REPLACE_PATTERN,,``)

ghilton

sadly not tom it does not like the brackets!

tford

Sorry for my transposed post

BO.attribute_stripped=REPLACE_PATTERN(BO.attribute,,``)

tford

BO.attribute_lower=TO_LOWER_CASE(BO.attribute)

ab042

Support: I've had an issue with Import as well.

Shouldn't IMPORT ALWAYS remove extra spaces at the end of a string?

aware_support

No, it shouldn't. It doesn't know whether it's intentional or not. If you want to remove them, remove them from the file.

ab042

Support: Intentional? What would one good example be of where we may want blank spaces at the end of a string?

I would agree it isn't AwareIM fault that spaces exist in the import file. But I would also say, in the real world 90% of the time you don't have the option on the export to remove them. And if you have a large file its not a simple load in excel and remove them. And if you have

It would be nice if it was a AwareIM import feature or even a default to not have them. Since I can't think of the last time I would have wanted to keep spaces at the end of a string field during an import.

In fact in the last 30 years of programming I don't ever remember wanting to keep spaces at the end of a string. And I can think of a lot of reasons I wouldn't want spaces at the end of a string.

Just my 2 cents. And at the end of the day we'll work with it. You have a great product and we Thank you for it.

ghilton

I agree ab042, not sure why anybody would want spaces at the end of a string? In this case I am comparing 2 strings and marking one as a duplicate ready to delete (if indeed it is already in the database). Sadly the 2 spaces are affecting this so I manage to import duplicates.
As this BO is a marketing tool collecting emails and sending mail shots duplicates are really bad as we would then be sending multiple emails to one company which can be classed as spam which is not our intention!

Support can you put this on the wish list please?

Tom, thanks for the examples I will be working with them this morning and will let you know!

ghilton

Hi Tom

Sorry but the rule cannot find BO.attibute_stripped and BO.attribute_lower?

In My Case -

Contacts.EmailAddress_stripped=REPLACE_PATTERN(Contacts.EmailAdress,' ',' ')

Contacts.EmailAddress_lower=TO_LOWER_CASE(Contacts.EmailAddress)

The lines produce the following -

rule '' uses attribute 'EmailAddress_stripped' which has not been found

rule '' uses attribute 'EmailAddress_lower' which has not been found

Cheers

tford

graham,

those are example attribute names. you have to have attributes in any formula. either create them (using your own names)

ghilton

Hi Tom

I did hey give me some credit 😃 . I have been playing around and am a bit confused

I created a process as below

Process1
Find Contacts Where Contacts.EmailAddress IS Defined
Process 2
Contacts.EmailAddress=TO_LOWER_CASE(Contacts.EmailAddress)

This had some wierd results. It ran and changed ALL the email address fields on all records to be the same as record 1? But at least is was all lowercase!

I removed this process and stuck the next line in the rules of the BO Contacts

Contacts.EmailAddress=TO_LOWER_CASE(Contacts.EmailAddress)

Now when I create/Import all the email address are now lower case as planned. But now the Duplication rule is ignoring the 2 spaces at the end of the address and marking them as duplicates? Great it now appears to be working?

Although this is working on the test system I am not sure why?

tford

I wasn't aware you could have the attribute name on both the left side and the right side of the = sign. Obviously you can since when you placed your rule in Contacts BO, it worked.

To have your rule fire for all Contacts, run a process with:

FIND ALL Contacts
UPDATE Contacts

This will to a one time cleanup of all existing Contacts.

ghilton

It works fine in the BO Rule, but not in a process. Like I said running the process just copied the first email into all the other email fields.
Not sure the mail shot would of been a good idea, sending 12000 emails to the same address!

So the update contacts will run the BO Rule on all existing data in that BO?

tford

yep

ghilton

Cool!

Thanks for the help I am most greatfull........

Cheers