Adding record

Posted on 2003-11-05
Last Modified: 2010-04-27
I'm doing a database system for a servicing company. How do i add a new service record to an existing customer within the same file in below manner

Customer 1 : Service 1.1 ( The Added Record)
                   Service 1.2
                   Service 1.3
Customer 2 : Service 2.1
                   Service 2.2
                   Service 2.3
I tried duplicating customer 1 each time when adding new service which looks like this
Customer 1 : Service 1.1 ( The Added Record)
Customer 1 : Service 1.2
Customer 1 : Service 1.3
 But everytime when i delete let say customer 1: Service 1.1, all the other customer 1 services get deleted too. and so i lose customer 1's data completely. what should i do? Help. I'm sure there must be a better way of doing this without duplicating
Question by:treynathan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4

Expert Comment

ID: 9696040
To do this properly, you need two tables (files): Customer and Service.  You'll have to setup a relationship from Customer to Service reflecting the one-to-many relationship between the two (one customer has many services, a service can only have one customer).  You need to setup a primary key in the Customer table and a foreign key (keyf_customer) in teh Service table, that links that service to a customer.

Then to display the data, just draw a portal on a layout in the customer file that uses the relationship you created between the two files to show the related services for that customer.

This idea of splitting the data into seperate tables is called "normalization".  Since it sounds like you're new to databases, I recommend you read a book about database design before you proceed, so that you don't dig yourself into a hole.  FileMaker book generally don't cover normalization very well... I'd recommend you read the first chapter or two of any SQL book.

Good luck,

Author Comment

ID: 9698967
All that i mentioned previously were done by relationship and autolookup function. Maybe i did one to one relationship. Hmnn... i should be trying one to many relationship as suggested by you.

Author Comment

ID: 9698968
All that i mentioned previously were done by relationship and autolookup function. Maybe i did one to one relationship. Hmnn... i should be trying one to many relationship as suggested by you.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 9718885
Can you please describe your database in more detail?

When you write that you used a relationship and an auto-lookup function, what do you mean?
What are your tables like?
What relationship have created between them?
Which key fields is that relationship based on?
Which table have you create the lookup field in?
What other table and field does that lookup field reference?
What relationship does that lookup field use to reference the related table and record?

When you tried duplicating the customer, which table did you duplicate within?
When delete, which table are you deleting in?  The customer table or the services table?

Author Comment

ID: 9719556
for your information, this question and the other question is for the same database.
I set relationship A in the service table( relating customer profile) and define a few fields as auto-enter: Lookup-value using the the same relationship. so when i open the service table and key in the company name, the contact info of the company ( which is the info entered in the customer table) will be shown here. And from there i'll add new service for the company by duplicating the company and editing the service like this
Service File
Customer 1( retrived from Customer file using relationship A) : Service 1.1 ( Added service)
Customer 1 : Service 1.2 ( Added service)
Customer 1 : Service 1.3 ( Added service)

Having the deleting record problem solved, i just would like to keep it this way. Is this a dangerous method? i don't wish to lose my data. What would u suggest?

Expert Comment

ID: 9727663
> "And from there i'll add new service for the company by duplicating the company and editing the service like this..."

I think you're confusing the COMPANY record with the SERVICE record.  Given the scenario you described, you wouldn't want to duplicate the company.  If anything, you'd want to duplicate the service so that you have a new service record which still points back to the same single customer record.  You end up with a one-to-many relationship from customers to services.  (One customer can have many services)

> "everytime when i delete let say customer 1: Service 1.1, all the other customer 1 services get deleted too."

It sounds like you have a relationship that is deleting related records.  If you delete the CUSTOMER, and the customer table (file) has a relationship to services, and that relationship deletes related records, then every time you delete a customer you'll be deleting all the service data for that customer as well.  This is usually good - you delete "orphan" records.  

However if the relationship is based on the customer name, and you have 2 customers with the same name, then deleting one customer will delete all the service records for both customers.  The problem there is that neither customer has an unique relationship to the service records - both customers share a single set of service records related to them by name.

This is a bad design.  The customer records should not be related to the service records by significant data (the customer name).  Rather, customers should be related to service records via insignificant data (such as a serial number).  By relating them via a serial number, you can change the name of the customer without breaking the link to the service records.  Breaking the link creates "orphan" records - those are child records without parents (services without a customer).  

Further, if you used a serial number to relate the records, then you wouldn't have the problem of loosing all the service records when somebody deletes a duplicate customer (because the two customers would have different serial numbers even though they share the same name - so each customer would be able to maintain a set of related service records independent of the other duplicate customer).

To fix the problem you have now - the problem of deleting duplicate customers without also deleting service records they share - you must first change the name of the duplicate company and THEN delete the duplicate company once it has a different name.  This way, the second company will no longer relate to the service records since the name has changed, and then when you delete it won't delete any service records because it doesn't match any.

As a general rule, every table you make should have a single serial number field that acts as the primary key in that file.  Your customer table and your service table should have one.  Also, your service table will need a "keyf_customerID" field that stores the ID of the parent customer record.

Accepted Solution

Mariano_Peterson earned 80 total points
ID: 9734685
Here is an ultra basic demo of how this could be done:

Be warned that this is just a very basic demo on how to create a one to many relationship.  There are many many things that should be improved upon in this design.  This is just to give you an idea.


Author Comment

ID: 9761405
thank again.. your demo really helped me.. now i understand how to solve my problem..

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can't open Filemaker Server 12 Start page 1 541
Filemaker Output 6 225
Filemaker 13 Pro - Script to verify user makes entry into portal 3 82
Add 2 fields and get a totla 3 16
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question