I am currently building an app that would allow quotes to be created, with the quote containing discrete "Systems" which are in turn made up of product bundles (which I call "Components") which are in turn made up of individual "Line Items" (like a cable or a power supply, etc).
I have to use many-many relationships to achieve this because a "Line Item" might be used in more than one "Component" and a "Component" will definitely have more than one "Line Item". This works ok and I can build up "Systems" from "Components" which have "Line Items" and pull through pricing via Summary fields up from the "Line Items" table all the way through to the "Systems"
FYI, the basic structure looks like this
System < System Components > Components < Component Lines > Line Items
There are currently around 300 "Line Items", 30 "Components" and potentially a large number of "Systems"
My initial question is:
Is it possible for me list on a report all the "Line Items" that make up a "System"? I need to be able to do this so that purchasing know what to buy.
I can create a Report Link from "System Components" to "Component Lines" but not from "Systems" to "Component Lines", since there is no field that is common in both tables.
Any help would be appreciated. It may be that I'm structuring this all wrong.