Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

BOM Issue

Report design issues and solutions.
Post Reply
christina
User
User
Posts: 9
Joined: 16 Feb 2018, 22:08
Sage Version: v2018 24 UK/Europe/Africa

BOM Issue

Post by christina » 21 Mar 2019, 13:57

Hi

I have a product that has BOM on it when it pulls through to 1 of my reports its pulling through a duplicate line of the same item.

On the order number Header, the filter I'm using is STOCK.HAS_NO_COMPONENT = 0 AND STOCK.HAS_BOM = "" which is working as if I remove it I get a duplicate of another product.

The on the details header I am using filter STOCK.HAS_NO_COMPONENT = 1 AND STOCK.HAS_BOM = "" this then pulls through only the items without BOM so not even the line with the stock code (for the BOM product pulls through)

If I leave the filter blank on the details header then it pulls through everything (but duplicates the item with the BOM)

If someone could help me, please.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4123
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: BOM Issue

Post by brucedenney » 22 Mar 2019, 13:45

The BOM and it's use is part of the stock table, so would apply to the line items, if you use it on the order header, then which product code on which line are you expecting it to report on? By default you are looking at the fist line item, product code.

Can you attach the report to a reply so we can see the whole thing in context.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

christina
User
User
Posts: 9
Joined: 16 Feb 2018, 22:08
Sage Version: v2018 24 UK/Europe/Africa

Re: BOM Issue

Post by christina » 25 Mar 2019, 13:35

Hi Bruce.

I have attached 2 sage reports

Sage Report 1 - This report doesn't pull through the stock code (for the product that has BOM attached)
Sage Report 2 - This report does pull through the stock code (but it pulls it through twice) this is only affecting products that have BOM attached to them all other products pull through correctly.

What I need it to do is only pull through the main stock code once (and not pull through any of the components attached to that BOM) it currently isn't doing that but the issue is just that it's duplicating it or not pulling anything through.
You do not have the required permissions to view the files attached to this post.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4123
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: BOM Issue

Post by brucedenney » 27 Mar 2019, 12:59

Hi

I had a look but it is unclear what you are trying to do, with the demo data I couldn't get any results from either of the reports

This is so linked to your data, it is inoperable with anything else.


I strongly suggest that you rework this to make the report generic and to filter based on data.

Expressions such as this

Code: Select all

SOP_ITEM.STOCK_CODE = "CAM001"  OR SOP_ITEM.STOCK_CODE = "CAM002" OR SOP_ITEM.STOCK_CODE = "BER001" OR SOP_ITEM.STOCK_CODE = "BER002"  OR SOP_ITEM.STOCK_CODE = "BER004"  OR SOP_ITEM.STOCK_CODE = "BER005" OR SOP_ITEM.STOCK_CODE = "WHS001" OR STOCK_COMP.COMPONENT_CODE = "CAM001" OR STOCK_COMP.COMPONENT_CODE = "CAM002" OR STOCK_COMP.COMPONENT_CODE = "BER001" OR STOCK_COMP.COMPONENT_CODE = "BER002" OR STOCK_COMP.COMPONENT_CODE = "BER004" OR STOCK_COMP.COMPONENT_CODE = "BER005"
?	"LIQUID PRODUCT BY 16:00"
:  	SOP_ITEM.STOCK_CODE = "PRC010" OR SOP_ITEM.STOCK_CODE = "AQU047" OR SOP_ITEM.STOCK_CODE = "AQU040" OR SOP_ITEM.STOCK_CODE = "AQU010" OR SOP_ITEM.STOCK_CODE = "AQU011" OR SOP_ITEM.STOCK_CODE = "AQU012" OR SOP_ITEM.STOCK_CODE = "AQU013" OR SOP_ITEM.STOCK_CODE = "AQU014" OR SOP_ITEM.STOCK_CODE = "AQU015" OR SOP_ITEM.STOCK_CODE = "AQU016" OR SOP_ITEM.STOCK_CODE = "AQU017" OR SOP_ITEM.STOCK_CODE = "AQU018" OR SOP_ITEM.STOCK_CODE = "AQU019" OR SOP_ITEM.STOCK_CODE = "AQU020" OR SOP_ITEM.STOCK_CODE = "AQU021" OR SOP_ITEM.STOCK_CODE = "AQU022" OR SOP_ITEM.STOCK_CODE = "AQU023" OR SOP_ITEM.STOCK_CODE = "AQU024" OR SOP_ITEM.STOCK_CODE = "AQU025" OR SOP_ITEM.STOCK_CODE = "AQU027" OR SOP_ITEM.STOCK_CODE = "AQU028" OR SOP_ITEM.STOCK_CODE = "AQU029" OR SOP_ITEM.STOCK_CODE = "AQU030" OR SOP_ITEM.STOCK_CODE = "AQU031" OR SOP_ITEM.STOCK_CODE = "AQU032" OR SOP_ITEM.STOCK_CODE = "AQU033" OR SOP_ITEM.STOCK_CODE = "AQU034" OR SOP_ITEM.STOCK_CODE = "AQU038" OR SOP_ITEM.STOCK_CODE = "AQU039" OR SOP_ITEM.STOCK_CODE = "AQU042" OR SOP_ITEM.STOCK_CODE = "AQU045" OR SOP_ITEM.STOCK_CODE = "AQU046" OR SOP_ITEM.STOCK_CODE = "AQU047" OR SOP_ITEM.STOCK_CODE = "AQU048" OR SOP_ITEM.STOCK_CODE = "AQU049" OR SOP_ITEM.STOCK_CODE = "AQU050" OR SOP_ITEM.STOCK_CODE = "AQU051" OR SOP_ITEM.STOCK_CODE = "HYD001" OR SOP_ITEM.STOCK_CODE = "CRU001" OR SOP_ITEM.STOCK_CODE = "CRU002" OR SOP_ITEM.STOCK_CODE = "CRU003" OR SOP_ITEM.STOCK_CODE = "CRU004" OR SOP_ITEM.STOCK_CODE = "TWD003" OR SOP_ITEM.STOCK_CODE = "TWD014" OR SOP_ITEM.STOCK_CODE = "4AC013" OR SOP_ITEM.STOCK_CODE = "AAF080" OR SOP_ITEM.STOCK_CODE = "KLC012" OR SOP_ITEM.STOCK_CODE = "APF002" OR SOP_ITEM.STOCK_CODE = "APF003" OR STOCK_COMP.COMPONENT_CODE = "PRC010" OR STOCK_COMP.COMPONENT_CODE = "AQU047" OR STOCK_COMP.COMPONENT_CODE = "AQU040" OR STOCK_COMP.COMPONENT_CODE = "AQU010" OR STOCK_COMP.COMPONENT_CODE = "AQU011" OR STOCK_COMP.COMPONENT_CODE = "AQU012" OR STOCK_COMP.COMPONENT_CODE = "AQU013" OR STOCK_COMP.COMPONENT_CODE = "AQU014" OR STOCK_COMP.COMPONENT_CODE = "AQU015" OR STOCK_COMP.COMPONENT_CODE = "AQU016" OR STOCK_COMP.COMPONENT_CODE = "AQU017" OR STOCK_COMP.COMPONENT_CODE = "AQU018" OR STOCK_COMP.COMPONENT_CODE = "AQU019" OR STOCK_COMP.COMPONENT_CODE = "AQU020" OR STOCK_COMP.COMPONENT_CODE = "AQU021" OR STOCK_COMP.COMPONENT_CODE = "AQU022" OR STOCK_COMP.COMPONENT_CODE = "AQU023" OR STOCK_COMP.COMPONENT_CODE = "AQU024" OR STOCK_COMP.COMPONENT_CODE = "AQU025" OR STOCK_COMP.COMPONENT_CODE = "AQU027" OR STOCK_COMP.COMPONENT_CODE = "AQU028" OR STOCK_COMP.COMPONENT_CODE = "AQU029" OR STOCK_COMP.COMPONENT_CODE = "AQU030" OR STOCK_COMP.COMPONENT_CODE = "AQU031" OR STOCK_COMP.COMPONENT_CODE = "AQU032" OR STOCK_COMP.COMPONENT_CODE = "AQU033" OR STOCK_COMP.COMPONENT_CODE = "AQU034" OR STOCK_COMP.COMPONENT_CODE = "AQU038" OR STOCK_COMP.COMPONENT_CODE = "AQU039" OR STOCK_COMP.COMPONENT_CODE = "AQU042" OR STOCK_COMP.COMPONENT_CODE = "AQU045" OR STOCK_COMP.COMPONENT_CODE = "AQU046" OR STOCK_COMP.COMPONENT_CODE = "AQU047" OR STOCK_COMP.COMPONENT_CODE = "AQU048" OR STOCK_COMP.COMPONENT_CODE = "AQU049" OR STOCK_COMP.COMPONENT_CODE = "AQU050" OR STOCK_COMP.COMPONENT_CODE = "AQU051" OR STOCK_COMP.COMPONENT_CODE = "HYD001" OR STOCK_COMP.COMPONENT_CODE = "CRU001" OR STOCK_COMP.COMPONENT_CODE = "CRU002" OR STOCK_COMP.COMPONENT_CODE = "CRU003" OR STOCK_COMP.COMPONENT_CODE = "CRU004" OR STOCK_COMP.COMPONENT_CODE = "TWD003" OR STOCK_COMP.COMPONENT_CODE = "TWD014" OR STOCK_COMP.COMPONENT_CODE = "4AC013" OR STOCK_COMP.COMPONENT_CODE = "AAF080" OR STOCK_COMP.COMPONENT_CODE = "KLC012" OR STOCK_COMP.COMPONENT_CODE = "APF002" OR STOCK_COMP.COMPONENT_CODE = "APF003"
?	"NEXTDAY MAILPACK BY 16.00"
:	SOP_ITEM.STOCK_CODE = "4AC006" OR SOP_ITEM.STOCK_CODE = "4AC009" OR SOP_ITEM.STOCK_CODE = "AAF058" OR SOP_ITEM.STOCK_CODE = "AAF059" OR SOP_ITEM.STOCK_CODE = "AAF060" OR SOP_ITEM.STOCK_CODE = "ANG005" OR SOP_ITEM.STOCK_CODE = "KLC002" OR SOP_ITEM.STOCK_CODE = "KLC003" OR
STOCK_COMP.COMPONENT_CODE = "4AC006" OR STOCK_COMP.COMPONENT_CODE = "4AC009" OR STOCK_COMP.COMPONENT_CODE = "AAF058" OR STOCK_COMP.COMPONENT_CODE = "AAF059" OR STOCK_COMP.COMPONENT_CODE = "AAF060" OR STOCK_COMP.COMPONENT_CODE = "ANG005" OR STOCK_COMP.COMPONENT_CODE = "KLC002" OR STOCK_COMP.COMPONENT_CODE = "KLC003" OR STOCK_COMP.COMPONENT_CODE = "KLC006"
?	"NEXTDAY COURIER PACK BY 16.00"
Are not ideal.

Rather than specifying each code in the filter, you would be better off using an attribute of the stock codes, the stock category type or one of the three custom fields. Then when you make a new product you can just put the correct value in the custom field or the correct category and the report will work without modification.

I think that in this case to help progress would require a sample data set with all the correct values etc to make it run.

It is not going to be possible otherwise, without spending a huge amount of time on it.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest