Adding record

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
treynathanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mariano_PetersonCommented:
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,
Mariano
0
treynathanAuthor Commented:
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.
0
treynathanAuthor Commented:
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.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Mariano_PetersonCommented:
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?
0
treynathanAuthor Commented:
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?
0
Mariano_PetersonCommented:
> "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.
0
Mariano_PetersonCommented:
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.

-Mariano
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
treynathanAuthor Commented:
thank again.. your demo really helped me.. now i understand how to solve my problem..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.