Relationships broken due to case sensitivity

[mehronx]

I am noticing a problem with our orders database. The customer information in our orders database is pulled from the customers database throuh a relationship based on a concatenated field. The field is the first name, last name, and email address. It seems that the relationship doesn't work if the cases don't match.

How can I correct this? Do I need to change the language in the storage options to something other than English? ASCII?

[falkaholic]

Not sure about language, but you can force it so both sides o the relation are always lower-case (or upper if you want) then there shouldn't be a problem.

[Kurt Knippel]

It depends upon how you have your indexing set. ASCII indexing has seperate codes for upper and lower case characters and so you get a mismatch, or maybe I have it the other way around and it is the English indexing that has seperate codes.

Try the other one, but really the best solution is to set you keys such that they are all upper or all lower, or match the case.

[jasonwood]

Why not have a serial number for every client? Content can change (in this case, e-mail address could change). Serial numbers are guaranteed to be unique and they will never change, so you never have to worry about losing links between records.

If you are using the concatenated field because you want to select customers from a list... there are other ways to do this (use two relationships, one following the other).

--

Jason Wood

HeyWoody.com

[mehronx]

They do have serial numbers.

Every day we import the customers/orders from an online database. So if a customer returns, how do we do a match update based on a serial number?

[Vaughan]

They must have a serial number on the other database, use it in yours for the key field.

[mehronx]

I may end up doing something like this. But currently, they don't have serial numbers in the online MySQL database. Well, not numbers that can be used this way. The reason is that online customers don't have an account on our site, so if they return, they would be assigned a new serial number. We may end up making an account login on our site.