Non-case sensitive relationship

[mehronx]

I currently have a customer database that populates an orders database and aside from that I have a mailing list database that shares some of the same data.

The relationship is created by a field with combined first name last name and email address.

However, the relationship always seems to be case sensitive. No matter how I display the records (upper or lower or title), the database retains the actual format that it was initially entered as. So for example, the layout might show JOHN SMITH but the entry is acually John Smith.

How can I make sure that John smith will match john smith in my different databases?

Is this something that must be changed in the storage options for the combined fields? Also, the relationship must work with the @ symbol due to the email addresses.

[stanley]

Mehronx:

The real problem you should be considering is what happens when you've got two identical names in there. Your choice of "John Smith" as an example is apt. Your relationships should always be based on key fields which are guaranteed to be unique and non-modifiable. For this reason, every record in every database in your solution should have an auto-entered serial number; this is what you would base relationships such as orders-to-customers on.

So, when you are working in your orders database, the data you'd copy over from the customers database would just be the serial number, and then you can pull all the other data from the customers database based on that.

-Stanley

[mehronx]

I do realize the flaw in this design. It's not perfect, but that is why I also use the email address.

The problem with the key / serial number is that all of this info is being imported from an online database that doesn't provide a serial number. The customers don't log in to the site and therfore returning customers would be assigned a new serial number.

[stanley]

Mehronx:

Okay, so you're using a combination of Name and Email for the relationship... that'll do okay, and given what you're doing, it makes sense. I didn't realize you were using both.

One thing you could do is parse the names to do something like strip the capitals out... you can do this using Set Field ["textfield", "(Lower(textfield)"]... There is surely also a way to parse through and add capitals back, but because you can set the field to display as Title, I think you're best off stripping the caps out, then if you want to display them with caps, display as Title.

Hope that helps

-Stanley