Relationship question

[Ginxy33]

First of all, let me just say, I have learned so much through this formum...Thank you to everyone.

Now my question.

I have a table which I call ProductDetails and relationshiped to that table I have Sizes and Colors.

Ok, here is the deal. Every Product comes in many different sizes and different colors. I want to be able to show that in portals from the ProductDetails page.

My sizes and colors table will already have static information needed the ProductDetails layout needs only to pick out the choosen colors/sizes.

How do I now add the more than one sizes and colors available through a portal?

Pictures attached to help explain.

I'm hoping not to have to add a ProductID to my sizes and colors table for relationship.

[CyborgSam]

In your relationship diagram, each product has a single ColorID and a single SizeID. These are one-to-one relationships, each product is limited to a single size and color. What's needed are many-to-many relationships:

Each Product can have many Colors and many Sizes.

Each Color can have many Products and many Sizes.

Each Size can have many Products and many Colors.

Let's change field names a bit to make this easier. A Primary Key will be preceded by kp_ and a Foreign Key with kf_. So ProductDetails contains kp_ProductID, Color contains kp_ColorID, and Sizes contains kp_SizeID.

First, get rid of the one-to-one relationships, they are not useful. Then create a separate table that contains kf_ProductID, kf_ColorID, and kf_SizeID. In this table add an ID for each record in case it's needed down the road (e.g. kp_ProductColorSizeComboID). Each record in this table is a single product in a single size and a single color. Now relate kf_ProductID to kp_ProductID, kf_ColorID to kp_ColorID, and kf_SizeID to kp_SizeID.

A layout for a Product would have a portal using the kf_ProductID__to__kp_ProductID relationship. This portal would list all available colors and sizes. There are many techniques that can be used to make the portal look good, you might try Hierarchical Portals, Excelisys has a great demo at http://www.excelisys.com/services/fmp7/tips_tricks.htm

Given this setup, you can create portals to show similar products by size and/or color. I'm guessing that is a common question folks have ('do you have anything else in my size?).

Is this clear enough to get you started?

[Ginxy33]

That was great advice!! I had started to try something like that yesterday. (See attached picture) I don't know why I hadn't thought of using just one table to do all the choices. That will sure make it look more professional than what I had tryed. I will do that. Thank you so much.

[Ginxy33]

One more small question if you don't mind.

Indexing....

When I index a field in one table, do I also have to index it in tables that uses it as a foriegn key?

OK 2 more small questions..

You can't index a calculated field. So can you do a lookup field on a calcluated field or does that cause problems?

Edited November 30, 2005 by Guest
question added

[CyborgSam]

When I index a field in one table, do I also have to index it in tables that uses it as a foriegn key?

That depends on whether you need the relationship to work both ways. In general, the best practice is to define fields with "automatically create indexes as needed" checkbox checked. FileMaker will turn on indexing if the relationship requires it.

You can't index a calculated field.

Calculations can be indexed unless they include a related field, summary field, global value, or a reference to an unstored calculation.

A good read is FileMaker's Help:

Contents > Creating a database > Setting options for fields > Defining field indexing options

Contents > Working with related tables and files > About relationships > About match fields for relationships