Recipient case sensitivity on join

vendimb15716755

25-10-2017

HI

we have mixed case email address stored on the recipient table. on the blacklist table ( nms:Address) the emails are all lower case. when we try to build a workflow the join is not filtering what there since they dont match the case. Any suggestion on how we can handle this:? can this be done by modifying the join there or through extentions? using it on the Query( by using lower) is not an option as it slows down

<element advanced="true" desc="Information about the email" externalJoin="true"

integrity="neutral" label="Info on the email" name="emailInfo" revIntegrity="neutral"

revLink="recipient" target="nms:address" type="link">

<join xpath-dst="@address" xpath-src="@email"/>

thanks

Accepted Solutions (1)

Accepted Solutions (1)

vendimb15716755

30-11-2017

HI Florentlb

we just run a full update on all schemas to make everything lowercase. going further we are making sure everything that comes from ETL process goes lowecase

Answers (7)

Answers (7)

Jean-Serge_Biro

MVP

26-10-2017

Firstly, if you are hosted On Cloud, how did you get such email values with uppercase? By default, Adobe Campaign forms and import tool template use the enumeration value Data Policy email to force lowercase on the mail. And so on nms:addresses values as well if use of unsubscribe services, the email is forced to lowercase.

Of course, with On Premise instances, there are often other scenarios to import directly data in the database bypassing Adobe tools and so these ETL tools (Extract, Transform, Load) should do the job of lowercasing the email...

Regarding the case-insentive functionality:

Yes please raise your question to Adobe support, even though I am afraid they will ask for Adobe consulting or perhaps refuse it for an On Cloud hosting, I don't know at all.
Postgresql LC_Collate is locale setting so would be shared to all customers.
Alternative citext type field is not good idea.

Alternative index on nms.recipient.sEmail with lower would be quite good but limited to this element only.

So I guess Adobe would advise to cleanup/lower the sEmail field (and Delivery addresses as well) for current records, for instance with a mass-update GUI action or easier, a workflow.

Sorry I am not able to give you more information, being hosted On Premise.

Regards
J-Serge

vendimb15716755

06-11-2017

Hi florentlb

no the issue hasnt been resolved yet. Adobe support is trying to extend the recepient schema to add an email field with uppercase value which doesnt solve our problem. 

"

If you want to store the values in certain way, you can certainly extend the table (in this case, the recipient table) to match your requirement.

"

vendimb15716755

26-10-2017

Thanks JS

I have opened a case with Adobe as I am not sure how it allow to have records with upper case and lower case. All I know that one of the system is sending uppercase emails but I assume that Adobe should convert to lowercase automatically when saving. I am afraid that even I cleanup I will end up with uppercase issues . is there anywhere else I should look on the recipient schema that it might be overwritten to allow uppercase?

Also I do see that the datapolicy is set for email  on the common schema

thanks

vendimb15716755

26-10-2017

HI JS

this is hosted on Cloud so No I dont have access to the database . the database its postgress SQL. So according to your answer we should ask support to change the Collation setting ti case insensitive right?

thanks

Jean-Serge_Biro

MVP

26-10-2017

Hi Vendimb,

Is your AC hosting On Cloud or On Premise?

Do you accede to the DB ?

A Best Practice for an Adobe Campaign DB is to define the DB instance with a character set collation set to Case Insentive. All  RDBMS (such as SQL Server, Oracle, mySql, Postgresql) support this function.


So, whatever the search/comparison (Uppercase/lowercase/smartcase and even with special characters / diacritic signs or accent) it does automatically the normalisation in the indexes and everywhere: no slow down impact, and avoids using such lowercase/uppercase/other conversion in your queries, the sort algorithm takes also into account.

Regards
JS