I've run into a circular reference and I'd like some advice on the best way to resolve it. I've created a simplified .qvw file that illustrates the problem.
In summary I have four tables:
Orders - A list of purchase proposals and purchase orders and the item being ordered.
Materials - A list of items added to a work order
ItemMaster - A list of items and their description and type
Order_Material - An association between the Orders and Materials table.
The problem I have is that both the Orders, Materials and ItemMaster all have a field called ItemNumber, so they become associated. However the Order_Material table is what should be creating the association between the Orders and Materials table.
I could rename one of the ItemNumber columns, but then I lose the link to the ItemMaster.
Is the item number on the materials always the same value as the item number on the order? That seems to be the case in your sample data if I'm reading it right. If so, just remove the item number from the materials table, as it is redundant and denormalized information.
Or in your REAL data, is it like a bill of materials, where you have a lot of different items that, together, make up the item that was ordered. In that case, the ordered item really is something a bit different than the material item, and seems like it should be a different field.
As an example, let's say we order 'car' and the bill of materials is 'engine' and 'chassis'. If you select 'car', you do NOT want to exclude 'engine' and 'chassis'. In fact, those are the exact material items you'd want to see. That strongly suggests to me that these are different fields. You basically have an "Ordered Item" and a "Material Item", or however you want to name them. To handle the item master, just create duplicate tables, an ordered item master with all the items that can be ordered, an a material item table with all the items that can be in a bill of materials.
If there is an assocation in the Order_Material table the ItemNumber will be the same for both the Order and the Material.
However, the Order and Material tables can be mutually exclusive. An ordered item could be for stock and not have a material request. A material request could be requested in the future and not have an order created for it (yet).
So its not possible to remove the ItemNumber field from either table.
Your car/engine/chassis is something that I'll have to deal with in the future. We call them assemblies which a whole 'nother can of worms.
We were talking about it here and came up with the same conclusion that you did. Load individual ItemMaster tables for the Order and Material tables. We lose the ability to universally filter off of item type but I don't think that will be a problem.
I think it boils down to a design issue. I'm trying to look at a complex set of data from two different angles. The final document will have a 'Purchasing' tab for the Orders table that only links in the relevant Material entries. And there will be a 'Materials' tab for the Material table that only links in the relevant Order entries.
If I put them into separate documents or used your recommended solution of splitting up the ItemMaster tables the issue should just go away.
