BOM problem

[jt196]

Folks been scratching my head on this for an afternoon and was hoping I could pick your brains. 

I've tables invoices & line items (one to many relationship) and products and materials (also one to many). Each product can have multiple materials and each invoice multiple line items. 

I'd like to be able to open up a bill of materials for each line item and have various calculations based on the total number of items on the line items and the weight/quantity of the various materials. 

So far I can't seem to get this to behave properly - the line items total quantity doesn't seem to tie in with the materials section and thus calculations come out all over the place. I've tried changing how I get this line items quantity - to a calculation in the materials table. What am I doing wrong here? 

[Steve Martino]

Could you post a screenshot of your relationship graph?

[jt196]

So, I've attached the screenshot of the relationships. The idea is that I go into an order/invoice and open up a work order from each one using a "related items" script. 

What's happening is that the tables seem to just using the figure 1800 as the calculation, which isn't even any of the historical orders so I've no idea where that came from! You can see that on the second picture. I'm hoping to be able to open up the BOM and have the correct figure for inserts/boxes etc appear in the "Qty Per" field. 

In order to make that "Qty Per" figure I've chucked in an IF calculation according to the material category - was wondering whether that calculation and what table/fields I'm pulling the figures from might be the cause? It's done in the context of the main "materials" table rather than the duplicate one I've got set up to join the line items table. 

[jt196]

Any ideas folks?

[jt196]

So I got around this finally by a bit of a hacky method. Whatever it is, it works and that's enough for me!

I made a field in the materials table called "Quantity Ordered" which is a global storage. The script that opens up the BOM from a line item portal sets the global storage field to match the "Quantity Ordered" on the line items with a set field calculation. 

That figure is then used in all the calculations for the different materials. Hope that makes some sense and is a sort of sensible workaround for this database connection thing. Couldn't figure out how to do it with regular connections - too many tables within tables Inception style.