4 tables relationship report problem

[SilverReed]

Dear FM Developers,

I do have a problem to build a report to show the ingredients which are necessary for order.

 

The structure of the relationship is as follow

 

[order]

customerID

orderID

 

[orderLines]

orderLineID

orderID (relationship to order)

productID

quantity

 

[products]

productID (relationship to orderLine)

recipeID 

 

[recipes]

recipeID (relationship to product)

 

[recipeLines]

recipeLinesID

recipeID (relationship to recipe)

quantity

materialID (points to productID in TO of products named materials )

 

 

ok real example would be

 

the customer places an order for 

 

#order 1 

-- Green Drink - 2 bottle

-- Red Drink - 1 bottle

 

the green product has an recipe

 

Recipe 1 

-- water 1 qty

-- green dye 1 qty

-- bottle 1 qty

 

the red product has an recipe

 

Recipe 1 

-- water 1 qty

-- red dye 1 qty

-- bottle 1 qty

 

 

I would like to make and report showing

 

# order 1 ingredients necessary:

-- water 2 qty

-- green dye 1 qty

-- red dye 1 qty

-- bottle 2 qty

 

 

The problem is I do not know how to make an report to show the ingredients and summary via the following structure:

 

[order:: orderID] <-> [orderLines:: orderID | productID] <-> [products:: productsID | recipeID] <-> [recipes:: recipeID ] <-> [recipesLines:: recipeID | materialID] 

 

is there any chance to make that kind of aggregation?

 

many thanks for any suggestions.

 

Luca

 

 

 

 

[comment]

This is a difficult subject. I suggest you read some previous discussions, for example:

http://fmforums.com/forum/topic/57260-summarizing-grandchildren/

[SilverReed]

Dear comment I have read the conversation about the grandchild field summarization but still has no idea how to implement the solution for printed report (without the portal solution).

 

I had the same problem in access and it was possible to make via the sql command to get the data. I prefer the filemaker as it is more user friendly but cannot get a clue how to make the pass throught.

 

Is it really possible to make the schema order <-> car <-> car specifications <-> car parts report, ie.

 

I order 2 sport cars, I have in the end

 

2 sport cars

list of parts:

 

1. Tires - 8

2. seats - 4

3. mirrors 4

 

etc...