I am trying to create a "picking note" that on some occasions will have a product containing a BOM product code, the issue I am having is that when there is a BOM product the weight and quantity is incorrect, it seems to add up all the product codes as well as the BOM products also.
I did tag on to the following but did not workout how to get it to work viewtopic.php?t=4366
Is there a way to rectified this? or can someone point me in the right direction?
If have created a kind of work around by using a sub report, but ideally I would like to be able to use the BOM and multiply by the QTY.ORDER to work out the correct number of items and weight
Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals
Report with BOM
-
evansandreid
- User
- Posts: 5
- Joined: 08 Nov 2023, 16:05
- Sage Version: v29 UK/Europe/Africa
-
brucedenney
- Site Admin
- Posts: 4651
- Joined: 28 Feb 2006, 09:56
- Sage Version: v30 UK/Europe/Africa
Re: Report with BOM
Please attach your report so we can see what you have done.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
-
evansandreid
- User
- Posts: 5
- Joined: 08 Nov 2023, 16:05
- Sage Version: v29 UK/Europe/Africa
Re: Report with BOM
Sorry here it is. thought I had attached my layouts
The picking note with sub report shows the correct weight and number of items, but the sub report will not multiply the sop_item.qty_order by stock_comp.component_qty
the picking note with bom is the one we would like to use so we can multiply the sop_item.qty_order by stock_comp.component_qty to show the total number of components needed if the quantity ordered is greater than 1 and calculate the number items ordered
Since I sent the first messaged I've managed to get the weight to calculate correctly, not sure if I've done it the best way but the end result calculates correctly, I tried to apply the same method to the number of items hoping that would work but it did not.
ideally the report will show the weight and number of items ordered (ignoring the bom components) as in the pdf example
The picking note with sub report shows the correct weight and number of items, but the sub report will not multiply the sop_item.qty_order by stock_comp.component_qty
the picking note with bom is the one we would like to use so we can multiply the sop_item.qty_order by stock_comp.component_qty to show the total number of components needed if the quantity ordered is greater than 1 and calculate the number items ordered
Since I sent the first messaged I've managed to get the weight to calculate correctly, not sure if I've done it the best way but the end result calculates correctly, I tried to apply the same method to the number of items hoping that would work but it did not.
ideally the report will show the weight and number of items ordered (ignoring the bom components) as in the pdf example
Login to access the files attached to this post.
-
brucedenney
- Site Admin
- Posts: 4651
- Joined: 28 Feb 2006, 09:56
- Sage Version: v30 UK/Europe/Africa
Re: Report with BOM
You do not want to use a sub report.
Just to be clear you want the total quantity of items on the order (excluding BOM components).
Just to be clear you want the total quantity of items on the order (excluding BOM components).
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
-
evansandreid
- User
- Posts: 5
- Joined: 08 Nov 2023, 16:05
- Sage Version: v29 UK/Europe/Africa
Re: Report with BOM
Yes, total quantity of items on the order (excluding BOM components).
If using a sub report is easier, I did manage calculate the total number of items when using a sub report. However I couldn't get the sub report to multiply qty order and the BOM components
If using a sub report is easier, I did manage calculate the total number of items when using a sub report. However I couldn't get the sub report to multiply qty order and the BOM components
-
brucedenney
- Site Admin
- Posts: 4651
- Joined: 28 Feb 2006, 09:56
- Sage Version: v30 UK/Europe/Africa
Re: Report with BOM
You do not want to be using a sub report.
The problem you have is that it is adding up the Qty_Order for all the lines in the details section (where the BOM is printed)
You only want to add it up when the Sop_Item changes.
To do this select the QTY_ORDER expression in the Page footer (I would typically do this in an ORDER_NUMBER footer as if you have an order that goes over 2 pages you will get nonsense) then in the properties find the Evaluate Section and set that to the ITEM_NUMBER Footer, this means it will only add the number when it is in the footer, so the appearance of it in the details section will no longer have any effect.
The problem you have is that it is adding up the Qty_Order for all the lines in the details section (where the BOM is printed)
You only want to add it up when the Sop_Item changes.
To do this select the QTY_ORDER expression in the Page footer (I would typically do this in an ORDER_NUMBER footer as if you have an order that goes over 2 pages you will get nonsense) then in the properties find the Evaluate Section and set that to the ITEM_NUMBER Footer, this means it will only add the number when it is in the footer, so the appearance of it in the details section will no longer have any effect.
Login to access the files attached to this post.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
-
evansandreid
- User
- Posts: 5
- Joined: 08 Nov 2023, 16:05
- Sage Version: v29 UK/Europe/Africa
Re: Report with BOM
Absolute legend, exactly what we need.
Thankyou so much
Thankyou so much
Who is online
Users browsing this forum: No registered users and 2 guests