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

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: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 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 :- 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.

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.
Login to access the files attached to this post.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 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 :- 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.

Post Reply

Who is online

Users browsing this forum: No registered users and 14 guests