Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Adding record

Posted on 2003-11-05
Medium Priority
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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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 320 total points
ID: 9734685
Here is an ultra basic demo of how this could be done: http://mariano.petersonpages.com/demo/fmp/one2many.zip

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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…
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…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

580 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