Jump to content

BOM problem


This topic is 2242 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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? 

Link to comment
Share on other sites

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. 

Screenshot 2016-04-18 14.37.54.png

Screenshot 2016-04-18 14.38.43.png

Screenshot 2016-04-18 14.48.50.png

Screenshot 2016-04-18 14.48.56.png

Link to comment
Share on other sites

  • 3 weeks later...

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. 

Link to comment
Share on other sites

This topic is 2242 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.