Recipient case sensitivity on join

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

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

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

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

Hi,

Is your issue resolved yet?

Please let us know the outcome when you get a reply from the support team, for reference.

Florent

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.

"

Hi,

Please let us know if you have an update on this (if support managed to help you).

Thanks,

Florent