Recipient case sensitivity on join

[vendimb15716755]

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

[Jean-Serge_Biro]

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

[vendimb15716755]

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]

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]

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

[florentlb]

Hi,

Is your issue resolved yet?

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

Florent

[vendimb15716755]

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.

"

[florentlb]

Hi,

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

Thanks,

Florent

[vendimb15716755 · Accepted solution]

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