Relating

[glenhest53]

I am trying to get my head around this relationship business:

I have 3 tables in this database:

Orders

PhotoSizeInformation

PhotoCost

"OldPhotoCostList" field is the way it was handled in the database

using a calculation.

So to allow the user to edit that data (in case of price changes) I decided to separate the calculation onto a third table "PhotoCost" with a lookup from the "PhotoCost" field in the "Orders" table.

Now I read that the best practise in creating relationships was to have a unique number field on the one side of a one to many relationship. I have created those fields but am at a lost on how to use them effectively in this case.

Am I linking this up correctly?

Any suggestions appreciated.

TestSO1.fp7.zip

[BoomC]

I'm not sure I can help but I'm going to give it a try anyway.

First off, when I look at your tables I see information being duplicated (Photo Size, Ad Type etc.) across several tables. As you probably know, the wonderful thing about a relational database like FileMaker is that you can group like (and unique) information into seperate tables and then link those tables to each other through relationships. All the Customer info into a Customer Table, Pricelist info into PriceList Table etc. Think about this in your design.

Second, and in answer to your question, the advantage to a unique, serialized, number field is that it's garanteed to be, well... unique. If you set it up right, the ID Number field will be automatically filled in and the person doing the data entry won't be able to change it. The other advantage is that there is less kb overhead storing a related customer number "1" than storing related customer "Bynerman and Stevens, Inc."

I worked up an example database to give you the idea. It has 4 tables: Customer, AdPriceList, Orders and OrderDetail (I added Customers just to help show relationality - you may not need it). Orders is the main table and the form that ties all the tables together is the Orders layout. When you look at the Orders layout, notice that information from the Customer, AdPriceList and OrderDetail is all present on the form but most of this info isn't stored in the Orders table - it's stored in the other tables. Only the key identifiying numbers are actually stored in Orders and OrderDetail. Check out the Relationship Graph, and the Orders and OrderDetail tables (table view) and you'll see what I mean.

I hope this helps. Let me know if you have questions about the attached example file.

TestSO1_Suggested.fp7.zip

[BoomC]

GlenHest,

I forgot to mention that I've never done order or invoicing databases before so the cost/total cost fields are just tacked on. You would need to figure out how to get them to work properly. Sorry.

[glenhest53]

Thanks BoomC for taking the time to reply and for creating such an instructive database.

I can now see and understand the rational for unique ID's.

One more question. Each AdType will have multiple Photo Sizes and equivalent Photo Costs.

This would seem to indicate the creation of another table to hold these fields and the creation of a field in the Portal on the Orders layout to display them.

So that when a selection is made for say Birthday then a field called PhotoSize in the portal would display a value list of relevant records from the PhotoSize layout.

I tried to do this but for the life of me I cannot get it to work.

Honestly I have read and read about relationships but it still does not entirely sink in.

Edited January 25, 2006 by Guest

[glenhest53]

I have created a sample database.

So from the Invoice table I have a portal to the LinkTable that contains 2 drop down menus. One to select the Ad Type and the other the Photo Size. So if I select Birthday from the first drop down then only those records that pertain to Birthday should appear in the second drop down menu.

Right now all the records from the PhotoCost table appear in that drop down menu.

How do I "sort" these records in the drop down menu.

Again, thanks in advance to anyone who can help.

TestSOTables.fp7.zip

[BoomC]

I'm afraid you're getting into the parts of FileMaker that are making me flail so I can't be much help anymore. I sure don't want to guess and steer you wrong!

I did look at your sample db and can make a few recommendations. First off, in your case I don't see a need to separate out the invoice order number and create a special Link Table. The invoice order is already the table that joins the other related tables. Second, instead of splitting Adtype and Photosize into seperate tables how about just moving AdType from Photosize to OrderDetail (my sample db). That way, you can set the AdType for each item ordered just as you do the quantity (or whatever). Try that out in TestSO1_Suggested.fp7 and see if it does what you want.

As for your other problem you said:

"...So that when a selection is made for say Birthday then a field called PhotoSize in the portal would display a value list of relevant records from the PhotoSize layout."

I think what you are talking about here is a thing called "conditional value lists" and requires more than just relationships to do the trick. Look on the Database Schema > Value List forum for help with that. The Pro's have given some excellent, detailed answers (and sample db's) for this already so spend some time prowling the forum before asking a question. Do a forum search for the terms conditional value list and you should get all the threads on the topic. Good luck!

[comment]

Try it this way (attached).

I sure don't want to guess and steer you wrong!

I am afraid that's just what you did by recommending to eliminate the "link" table (which I have renamed as LineItems). Without it, it is impossible to issue a single Invoice for several products.

TestSOTables2.fp7.zip

[glenhest53]

Thanks Comment, that is exactly what I want.

Wow, the elegance of how you display the value lists in the Invoice table is fantastic.

[BoomC]

Comment,

Point taken and I will keep out of the fray until my bio says ... Skill: Advanced. Bad advice is worse than no advice.