How can I fix this badly designed database structure...???

[angelleye]

I'm a web developer with a client that is using Filemaker. He gave me a set of Filemaker files that are empty that he's designed over the years and wants to use it. One major problem with this configuration, though, is that he's using the phone number as the ID for customers which pulls billing/shipping information into the invoices based on what's in there. I might as well explain in detail...

Here is a look at the customers file: http://angelleye.sytes.net/dealsdirect/temp/customers.jpg

Here is a look at the invoices file: http://angelleye.sytes.net/dealsdirect/temp/invoices.jpg

Now, there are 2 things I don't like about this and they both kinda go together. The first thing is that he's only got a Name field. Not FirstName and LastName. That's not quite as important to me as this, though...

He's using the phone number as the relationship between the customers table and the invoices table. When you type in a phone number that exists in the customers table, it automatically inserts the rest of the billing information for that person based on what's in the customers table for that phone number.

Now, the problem is this...let's say you have an invoice for a person and both the billing and the shipping address the same. Well, later they buy something and they ship it to a different address. So, you're filling out the invoice and you first put in his billing phone number, it fills in his billing address for you. Great. But then, you put in a different shipping address for this invoice and what happens...it changes the shipping address in the customers file for that phone number and in turn changes the shipping address on all previous invoices associated with that phone number!!! Not good!

Is there a way in Filemaker I can create an autoNumber field and call it customerID and actually use this to relate the two files (or tables, as I normally think of them) so that the problem doesn't exist any more, but still allow it to use a phone number, or an email, or anything in that customers record to lookup and fill the information for me?

At the same time splitting up the FirstName and LastName would be cool too.

Is this an easy fix??? Any information I can get would be greatly appreciated. Thanks!

[coconutt2000]

I wonder if you could create a field in the invoices table that will contain the customer id number. Make it a lookup auto entry to pull the customer id via the phone number relationship.

Then use a script to cycle through the invoices to force a relookup.

You should be able to disassociate the phone number relationship and assign a new relationship between the invoices and customer tables based on the id number after that. This is only a theory though. Never had to do this myself.

[coconutt2000]

Oh, while you're at it... You may want to change the name and address fields on the invoices layout from related fields to lookup fields.

That means you'll have to create fields in the invoice table to correspond to the tables in the customer table, which will pull their content through the relationship. Once the data is in, it can be edited without changing the data in the customer table. It will also stick if the data in the customer table is changed. Which means that in the future if the customer's address changes, the data in their old invoices won't change when you update the customer record. If you want to update the data on an invoice, just force a relookup.