"Automatic" record creation feasible?

[mluka]

Hi all!

I'm trying to set up a customer file. I'd like to let FM handle as much of the work (of creating records) as possible, using relationships between various tables. The requirements are as follows:

• each customer is unique (well... of course!!!)

• each customer may have several phone numbers

• each phone number may point to several different customers

I thought of using three tables: Customer, Phone_Numbers; to which I thought I would add a third table (say "Cust_Phone_Numbers") that would just be the link between the other two tables; and only contain the keys to the other two related tables.

I'd like to be able to see, for each customer, its phone numbers.

I'd also like to see, for each phone number, the list of customers that use that phone number.

So far, so good. I've found an example in the Templates that came with FM ("Email Campaign Management") that behaves the same way: Groups are made up of several Contacts and Contacts can be members of several groups. On one table's layout you can see the Contacts that make up a Group and on the other table's layout, you can see the Groups a Contact is a member of.

However, that example assumes that Contacts have already been created, prior to the creation of the Group.

I would like to able to create a Phone_number while I'm creating a Customer, WITHOUT SCRIPTING. Of course, I wouldn't want to create the phone number if it already exists... Just the "relationship" record in the "Cust_Phone_Numbers" table.

In other words, is it possible to create the Phone_number(s) related to a customer by using the parameters at the bottom of the "Edit Relationship" dialog???

Is this feasible?

Am I on the right track or is there a simpler approach?

[i can fairly easily envision a way to do this WITH some scripting; but I'd like to use the "automatic" capabilities of FM as much as possible and I'm afraid of doing scripting that isn't really necessary.]

Thanks for your comments and suggestions!

[aldipalo]

I'm confused, what do you mean by many customers can have the same phone number? Do you mean many contacts within the same customer site (Company)?

In other words you have Ajax Company with Mary, John, Paul and Patti all at 212- 555 -1515 who order items from you?

If so, Then you have a one to many relationship between Company and Contact and Phone number is just another field. A field that you can sort and search on, however.

Please explain further.

[mluka]

Hi, Aldipalo. Thanks for the response.

Here's clarification:

Mary has these phone numbers:

212-555-1212 (Home)

212-666-7777 (Work)

John has:

212-555-1212 (Home)

416-488-4444 (Work)

514-978-8585 (Cell.)

Paul has:

212-666-7777 (Work)

This has to be an interactive application; I don't think I'd have time to sort/find/etc.

Besides the displaying of the data is not a problem: it can all be displayed using portals.

It's the record creation that I'd like to make as automatic as possible.

Hope this clarifies my question.

Thanks!

[aldipalo]

So, Mary and John live together? You can't use the phone number as your primary key because in this case it is not unique.

There is no way, that I know of, and believe me there are a lot more people on this forum who are more knowledgeable than I, that you can just enter data into a field and either get the record or create a new one without scripting.

I use a filtering portal to check to see if a contact exists, if it exists I click a button and go to the customer record, if it doesn't exist, I click a button and create the new record. The script grabs the basic info I have already entered and starts the new record with it.

I've attached the demo I used to build from. I hope this gives you some ideas. If you'd like to see the scripts let me know and I'll send them to you.

Al

filter.zip

[coconutt2000]

You may have to use what is called a join table to link multiple unique contact records to phone numbers that they may or may not share with other contacts.

This will require an interface for linking the phone numbers to a contact, and for creating new phone numbers.

Personally, I'd be wary of trying to get contact records to share a single phone number record.

Let each contact create its own set of phone number records in the phone numbers table. If you want to use a relationship to link people who share the same phone number, try create a new relationship between a TO for a contact's specific phone numbers and a new TO of the phone table, but using an (=) join using the phone number field. All contact ids who share any phone number with the contact you're viewing should be visible through that new relationship. All you'll need is a new portal using the new table occurrence.

It is actually pretty nifty. Don't forget to tag on a new contact table occurrence on the other end of the new table occurrence so the new relationship can pull the contact data attached to the matching phone number.

Edited April 24, 2006 by Guest

[T-Square]

Personally, I wouldn't bother with the additional complexity. I'd just have a Phone Numbers table that links directly to the clients table. Realistically speaking, it's easier for a user to type in a duplicate phone number for each client than it is to have them search for a pre-existing entry in your Numbers table and attach to that record.

It is true that you'd have duplicate phone numbers, but I don't see how this would matter much. I can't imagine a situation where you'd need to know that more than one person had the same phone number (which you could nonetheless extract from the table anyway). The records will be small, disk storage is cheap, and I can't see any other downsides.

David

[mluka]

Hi, all.

coconutt2000 wrote

You may have to use what is called a join table to link multiple unique contact records to phone numbers that they may or may not share with other contacts.

What's a "join table"???

Is it like the third table ("Cust_Phone_Numbers") I imagined using?

----------------

T-Square:

I can't imagine a situation where you'd need to know that more than one person had the same phone number

We get that all the time! (We're a dry cleaner and customers will simply walk in and throw their phone number at us. I can tell fairly easily if I have Mary or John in front of me ;-), but they do have separate accounts and I'd like to know is he/she has an order that she/he could pick up and vice-versa.)

---

At any rate, some of the comments you guys made got me to thinking about having several table occurences (for the same tables). I think I'll try a few things in that direction.

------

Also, it's not really a big deal for me to have a script that creates phone records just prior to creating customer records. And I may yet decide to just do it that way and forget about the "automatic" part.

Thanks all.

[comment]

Having a join table between people and phone numbers is a really good idea - if you're a phone company. Otherwise, I don't quite see the advantage of the added complexity. In any case, if you're trying to identify a customer by their phone number (by find or by relationship), you will get both Mary and John - with or without a join table.

The real difference comes to play when Mary gets tired of John and moves out.

[coconutt2000]

mluka,

I think what you need to do is sit down and really think out what you're trying to achieve. If all you need to do is find someone by their phone number, a simple "Find" will work. No need for the added complexity of a join table, which works exactly the way you think it does.

The join table comes into play only if you want to associate one phone number record with multiple person records. With phone numbers, such methods are usually more work than they're worth. Let every person have their own set of phone numbers, and then just use a find to locate people with a particular phone number. You can page through the found set and decide if John or Mary wears that particular sky blue silk skirt.

My bet is on John... Always knew he was a cross dresser.

[mluka]

My thanks to all who offered comments, ideas, suggestions. I think we'll keep things as simple as possible.

Thanks.