Relationships between tables, teach me something please.

[SKIN]

I have a database I have been designing and I can not figure out how to get a relationship to allow me to type in fields that are on the main table. Here is my breakdown. I have a main customer table, I have an orders table and I have a customer visit table. I want my customer info stored on my customer table, and from my orders table I want to be able to type a customers name in the name field, and that automatically creates a new customer entry if the customer isn't already in the list. I want the same to happen if an employee enters a customer name in the customer visits layout. I don't want my employees to have to go to the main customer table and create a customer every time, then switch over to the orders layout.

Is it wrong for me to want it to work this way?

[RalphL]

I would start in the Customer Table, find the customer or create new one and then switch to the order table make a new order with Customer ID.

[SKIN]

I don't want my employees to ever even see the customer id. Our point of sale has its own customer id that we have been using for ever. The filemaker customer id will not match up and it will just end up confusing out employees.

I want the employee to type the name of the customer in either the customer visit layout, or the order layout and then what ever information is entered gets saved in the customer table. If the customer already exists, it automatically brings up their information.

I am new to filemaker, I have the relationships setup. It just won't let me type in the customers name on either of the visit, or order table. I can only type the name on the customer table. Is there a way to be able to edit fields that are the foreign key and not the primary key. This is a many to many relationship and I would assume I can do this. I just don't know how.

[comment]

I want the employee to type the name of the customer in either the customer visit layout, or the order layout and then what ever information is entered gets saved in the customer table. If the customer already exists, it automatically brings up their information.

Well, it's possible - with some extra work on your part - but you need to consider what happens when the employee mistypes the name of an existing customer. Surely you wouldn't want Ms. Siobhan Hewings to have as many records as the ways employees found to misspell it?

Another issue with names is that they are not necessarily unique, so you also need a plan for that. For example, suppose you already have a John Smith and now a new customer with the same name appears.

Once you figure out the exact workflow you'd like your solution to follow, the technical implementation can be discussed.

---

http://www.listal.com/list/most-difficult-names-spell-andor

Edited January 11, 2012 by comment

[SKIN]

I have thought about that. I thought about making the customer's unique identifier the customer's telephone number.

[RalphL]

I assume the relationship is Customer ID = Customer ID. This never has to be displayed on layouts seen by users. Now if the user starts in the order table and makes a new record there is no Customer ID in this record, therefore there is no relationship. You need to make a global field to enter the name and use that to search your customer table.

I don't think this is a many to many relationship but 2 one to many relationships.

[SKIN]

Okay Ralph. I think you have pointed me in the right direction. I will go read up on global fields. I will post back...

Thank you!

[comment]

I thought about making the customer's unique identifier the customer's telephone number.

I am not sure about your business, but it's not uncommon for 2 customers to have the same phone number - e.g. spouses. In any case, you should have the employee enter the name and/or the phone number into global fields, then make your script assess the situation (existing/new/duplicate/problematic) and proceed accordingly.