relationships or somethin else

[anserine]

Let me appologize in advance, please dont flame me. I am very new to Filemaker and although I love the power of the program Im finding it very troublesome to use.

I just purchased filemaker about 2 weeks ago, to track my clients and their warranty claims. I have 2 tables set up within the same file, One is for clients and there information (Name, Address Warranty Number & Expiration Dates) This information is imported from my accounting program and is basically set.

The other table is for the claims, this table holds the same information as the first but also additional information about the claim.

What I want to happen is for the user to be able to look up a warranty # in the clients database after verifying its there, the user could then either hit a button or change to the claims layout and have all the information copy itself from the particular record found to the claims database and continue inputting the claim information. It is important that the claims database doesnt hold every record in the clients database, only the clients that have claims. Ive tried using relationships with poor result, Ive read the book that came with the software 5000 times and Im not getting a clue. Ill keep looking on the forums but any help at all will be appreciated.

[aldipalo]

As a newbie several months ago (not that I'm still not a newbie ) I experienced the same problems and frustrations. Have you downloaded the BPS solutions that are free with FM8? Take those files apart and you see how layouts, scripts and relationships work in filemaker.

Here's some reading material and added info that was also quite helpful:

http://www.foundationdbs.com/Downloads/WhitePaperForFMPNovices.pdf

http://www.foundationdbs.com/Downloads/DatabaseDesignForFMP.pdf

http://www.dwdataconcepts.com/

http://www.newcenturydata.com/downloads.php

http://www.nightwing.com.au/FileMaker/demos.html

http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_alp.php

I would also suggest that you post a copy of your file so it can be viewed and suggestions can then be made.

Hang in there FM is truly a great product and the folks here are very helpful.

Al

[anserine]

Ok I was making too much of that I realized that I dont have to switch layouts. I can just use the relationship to pull the info off the clients database into the claims database using the warranty number as the primary key, the user then may continue filling out info in the claims database.

My problem now is pulling info from the clients database using something other than the warranty number. For instance, if the customer calls in and doesnt have his warranty number, how would one search by last name through the claims database pulling the info off the clients database?

[aldipalo]

If I understand you correctly you want to be able to see the customers warranty claims from the customer layout? Since you have already set the relationship you could set up a portal on the customer layout showing the warranty claims and set a button to take you to the warranty layout using GTRR (Go To Related Record) command.

On the other hand you may be better off setting a separate CustomerID field as well as a warrantyID field. What if the customer has more than one model or product? This then allows you to keep the claims vs separate product as well.

I attached a quick and dirty file to show you what I am talking about

BTW, the beauty of a relational database is you don't need to duplicate data in the 2 tables. You can draw the info from customer into claims and vice versa.

Warranty_1.zip

[anserine]

Thats not exactly what Im talking about but i think its a great idea and Im going to try to incorporate it. Customers only have one warranty number, for each warranty they buy, if they buy another they get another warranty number. 1 Unit goes with 1 warranty number if they have a claim it gets filed under the warranty number for the corresponding unit. So every warranty number is unique and is stored as a separate person even if they have multiple warrantys.

I have figured out with a relationship, i can use the warranty number to pull information into the claims databse. Now Im trying to figure out how i can pull that same information using a different field. If a customer calls with a warranty claim, and they dont have their warranty number, is there a way without going back to the customer database and staying in the claims database. Can I use another field in the warranty database to search within the customer database besides the warranty number that is already linked to the customer database. 2 ways to pull the same record

Thanks again,

[aldipalo]

In the example I gave you all you have to do is add the customer name to the warranty layout. Go into Layout mode. Click on "Insert" then "Field." Choose "Customer table" then double click on "Customer Name." You can now do a "Find" on the warranty layout for customer name and get the record you are looking for.

Although you are looking at this as a one to one relationship you are also saying the customer could have more than one warranty. Therefore, IMHO, I would still suggest that you set up your tables as a one-to-many relationship using the customer table as the parent and the warranty table as the child. You can still do your finds from the warranty layout. If there is more than one warranty your find will return all warranties under that customer.

BTW, you can add as many fields from the customer database as you wish. Once you have your relationship set with CustomerID in both tables. So you can show name, address, csz, customer number. phone..... whatever.

If you'd like me to upload a sample let me know, but it is pretty simple to add the fields you want to either table.

Al

[anserine]

Yes i understand how to do that, for some reason i am only able to make a many to many relationsip, i see you used a one to many. Check out my testing file

TEST.zip

[comment]

IIUC, each customer can have only one claim (or none). If so, it should be very simple (see attached). Note that there's no need to duplicate information between tables.

TEST.fp7.zip

[aldipalo]

I agree with [color:orange]COMMENT. You are duplicating data and you are using the wrong key for your join. The relationship should be Customer to warranty claim. You can only have one customer related to one warranty claim in your example. Even one customer to many warranty claims or warranty policies would still be best joined by customerID. The warranty number is unique to the warranty record and belongs to the customer.

As you see by his layout you can mix data fields without having duplicate data in either table, other than the CustomerID.

I hope this helps.

Al

[anserine]

I appreciate your help but Im lost now. The customer can and probably will have more than one claim. They can have 0 claims up to a million claims. The warranty ID is unique, i dont understand why i cant use it as the related field. I dont mind using a field called customer ID but the problem is if i do that can I search one table through another table using more than one field? Obviously if the customer calls he wont have the Customer ID that i recently made up for him. Hell have to give me his last name or his Warranty Number. When I use the warranty number as the relational field It wont let me search by last name, only warranty number. Will that happen if I use Customer ID? I dont mind if the information is copied or not, I just need to bring up records by either warranty number or last name.

Thanks for being patient with me, for some reason I cannot find explanations for anything, I even bought a book on filemaker.

[comment]

Can you provide a bit more background about your setup in general? What is a warranty, how does it get issued? What is a unit? What exactly is a claim? If a person bought(?) a single unit, and (presumably) got a single warranty number - can he have more than one claim against this warranty?

[anserine]

Ill use car mufflers for example, Mufflers have a way of rusting and rotting, say I warranty car mufflers. its warrantied for 4 years starting from the day of purchase.

If you purchase the warranty, you now have a 4 year warranty on the muffler of your car. If it gets a hole and its repairable then Ill repair it at no cost, if it has to be replaced then Ill replace it still at no cost.

A Claim would be a person using his pre purchased warranty to get his muffler repaired.

A Unit would be the car or muffler I am warrantying

If a person bought 1 unit (car-muffler) he gets 1 warranty and 1 warranty number

The person may use that warranty number as many times in 4 years to get the muffler on that car repaired/replaced as he/she needs. It would be filed under the same warranty number but a different claim number.

Edited March 14, 2006 by Guest

[comment]

OK, that's much clearer. But you haven't answered this: where do the warranty ID's come from?

[anserine]

I made them up and the local printer prints them on the paper warranty's. They are sequential and unique.

[comment]

Examine these two files. The first one is extremely simple. It depends on your warranty ID's being indeed unique.

The second one is a bit more elaborate. Customers can have more than one warranty. Although here the tables are linked using auto-entered serial numbers, you can still search (from the warranties layout) for either a customer name or a certificate ID (the yellow fields). You can create new warranties for a customer, or new claims for a warranty by typing into the first empty line in the respective portal.

Archive.zip