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
  • 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.

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?
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails


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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now