Many customers to many phones

[mluka]

Hi all.

I'm having trouble (among many things) getting my head around this problem of relating many customers to many phones.

The context is this: we're a drycleaner. Customers can have many phones (home, work, cell, etc.). A phone number can be used by several customers (husband, wife, children; employees working for the same company, etc.)

If we need to call a customer, we'd like to display all their phone numbers.

Also, when customers walk in the store, they give their ("main") phone number; at that point, I'd like to be able to enter the phone number and see ALL the customers that share that number.

I've set up tables for Costumers and Phones, with a "join" table in between.

If I create (in separate steps) a whole slew of customers, and a whole slew of phone numbers, and then assign phone numbers to customers, no problem. I can look at all customers that share a phone number and I can look at all phone numbers that are used by a customer. But that is not the way things happen in real life.

In real life, a (new) customer walks in, gives us a phone number and that phone number is NOT in the Phones table.

No sweat: the relationships I've set up allow the creation of the record in the join table and in the Phones table.

The problem occurs if I try to add a customer that is going to give me a phone number that is already in the Phones table. I've set this up so that phone numbers are unique (as they should be, I feel). So I get an error msg from FM.

So how do I get around the problem of adding customers that use a pre-existing phone number???

[One way out that I can think of: 1- do NOT allow creation of Phone records via the relationship; 2- for customers that are going to use an existing number, no problem, just pick the number from a value list based on the Phones records; 3- for customers that are going to use a NEW number, add new customers in a two-step procedure: first add the phone number in the Phones table, then add the customer. Stinks, IMHO!

So, that's where I'm at.

Any suggestions on how to tackle this?

Thanks!

P.S. I've joined a skeleton file (that some of you have already seen thousands of times, probably).

Customers.fp7.zip

[aaa]

Hi, mluka!

I little edited your attachment. May be this help?

Customers.zip

[comment]

Did we not go through this before?

Anyway, this is how I would do it.

CustomerPhones.fp7.zip

Edited June 15, 2006 by Guest

[mluka]

Hi, aaa. Thanks for the response.

I don't know... When I get a new customer that has a phone number that is NOT already in the Phones table, how does the new record (in Phones) get created???

Thanks

[mluka]

Hi, Comment.

Super answer!

I'm still taking it apart to understand it fully. It certainly does everything I need (and more!). Thanks a million.

(Although I will admit that when I first opened your file and looked at the relationships, I got scared!!!) :confused:

[comment]

In view of this thread I have replaced the attached file with a modified version.