Jump to content

Non-case sensitive relationship


mehronx
 Share

This topic is 6538 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6538 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.