Jump to content

Many customers to many phones


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

Recommended Posts

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?


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


Link to comment
Share on other sites

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???


Link to comment
Share on other sites

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:

Link to comment
Share on other sites

This topic is 5655 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

  • Create New...

Important Information

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