+ Start a Discussion
joshQjoshQ 

Field defined as Case Sensitive effects query results...?

Hi There,

I noticed something odd… when I changed one of my Text Fields to be case sensitive (unique + external id), quarries against this filed in the WHERE clause started returning ZERO records when one would be expected ….
What gives? Did anyone else notice this? Once I make the field case insensitive all is well…

<This is an ancient piece of code, PHP+API5 that can’t be ported (yet) to a newer API version>

Thx!
joshQ

Ron HessRon Hess
is the external id stored in a field?
is it 15 chars or 18?

do you have the SOQL query to look at , may help understand better what is going on.
joshQjoshQ

Hi Ron,
The query is simple:
" select Id, CaseNumber, External_State__c, IsClosed from case where External_Id__c = 'XXXX-0000003227' ";

When External_Id__c is defined as Unique/External Id/CASE SENSITIVE this query returns Zero records.

 

When I “flip” off the Case Sensitivity on Case.External_Id__c the query returns ONE record (which is correct).

 

This happen in API 8 as well.

 

What I’m doing is integrating SFDC Cases with a 3rd party vendor customer support system where this is a one-to-one relationship between “our” (SFDC) cases to the external system.

Ron HessRon Hess
i understand now, but i'm stumped as to why you would get zero records when set to case sensitive.

is there a chance that you have duplicates in this field where one is upper case and one lower case?

is there a chance that the external ID passed in is getting toLower() somewhere before you put it into the SOQL ?
sorry, i'm grasping at straws here. 
joshQjoshQ

Ron,
No and No... SFDC wouldn't allow me to mark the field Case Sensitive if I had duplicates. There is no toLower, upper or proper… in the code.

joshQjoshQ
btw, I've opened a support case a few days ago... this has been escalated.