Multiple Table Relationship

[adam19ku]

I am working on a database to calculate costs for our products. My issue is connecting the item costing sheet to both the catalog year info and the costing details.

The tables in question:

CatalogItem (holds all the costing info for that particular catalog item)

CatalogItemYear (holds the year and retail price of the catalog item so I can store previous year's costs)

CatalogItemDetails (holds the various parts that make up the catalog item).

In my layout, CatalogItemYear and CatalogItemDetails are portals. CatalogItemDetails pulls up the correct information but when I create a new CatalogItem and change the year, the info from CatalogItemDetails does not change. Is there a way to do this, or am I just approaching the whole thing the wrong way?

[CobaltSky]

Hi adam19ku,

Welcome to FMForums.

Certainly there is "a way to do it". In fact there are several ways.

However while you've asked if you are "doing this whole thing the wrong way", as far as I can tell, you haven't actually said anything at all about how you are doing it. I.e. we do not know how your three tables are related.

Without that information, it's not really possible to say whether you're going about it the right way. But I can say that you aren't going about asking a forum question the right way (to get meaningful replies, you need to provide a succinct description of the current set up and an outline of the problem. :wink2:

[adam19ku]

good point, bad move on my part - I should know better!

I started typing my relationship structure, but would it be easier to just show a picture?

Basically the current problem is when I enter the Catalog Code, the details reference just fine. Everything pulls up in its place. When I create a new record with the same Catalog Code, the details for that item pull up from the previous record, which is great, but when I change the year and retail price for the new record, the previous record for that Catalog Code reflects the update, rather than the original item.

[CobaltSky]

Hi adam19ku,

It appears that you need to relate to the CatalogItemPrices TO on both CatalogItem AND Year.

To do that, you either need to move the Year field into the CatalogItems table, or relate CatalogItems to CatalogItemPrices through CatalogItemDetail, so you can filter the related price records by year.

Either way, you will require a multi-predicate join, defined as:

CatalogItem = CatalogItem

AND Year = Year

[adam19ku]

Ray

Thanks so much for the help. It's been a while since I've done FileMaker programming this complicated.

I'd like to try relating the CatalogItemPrices and CatalogItem table through CatalogItemDetail, but am confused on how to go about doing this.

Edited December 21, 2007 by Guest

[CobaltSky]

I'd like to try relating the CatalogItemPrices and CatalogItem table through CatalogItemDetail, but am confused on how to go about doing this.

Based on your earlier description of what you are trying to achieve, I would think that something like the following should work: