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"
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.
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
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?
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.