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 Component Locations

Report design issues and solutions.
BenJDMG
User
User
Posts: 10
Joined: 18 Nov 2019, 11:45
Sage Version: v2018 24 UK/Europe/Africa

BOM Component Locations

Post by BenJDMG » 18 Nov 2019, 15:07

Hi Bruce! Hope you're well and keeping nice and busy?

I am doing battle with report designer, and trying to create some products with BOMs, where each component shows the quantity required, the part number and the stock location.

The problem is have come up against is that location isn't isn't an option, other than for the parent product.

I believe I need to create a joint from Stock_Comp>Component_Code to Stock>Stock_Code so the component stock code can link back to the main stock table and search information from there.

That much makes sense to be, hopefully this is along the right lines?!

My challenge is how to actually use this new and exciting joint - to actually output the location on the report. I can still only find the 'location' item from the dropdowns in the 'add data field' selection, and no version of the join be it inner, outer, parent or child relationship outputs 'Location' as the component location, it simply shows the parent location. I know I am missing something, but can't quite get my brain round it.

I guess this leaves me needing to write an actual formula in order to have it work? Please can you give me a pointer so I can get my teeth into it, and I'll gladly then share my results!

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

Re: BOM Component Locations

Post by brucedenney » 18 Nov 2019, 15:31

Yuo seem to know what to do, you need to add the stock table back in a second time (I would call it BOMStock) and you need to link it to the Component code in Stock Comp.

Then when you want to use the location it is the location field but out of the BOMStock table rather than the stock table.
If you are stuck attach you layout and I will have a look at 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.

BenJDMG
User
User
Posts: 10
Joined: 18 Nov 2019, 11:45
Sage Version: v2018 24 UK/Europe/Africa

Re: BOM Component Locations

Post by BenJDMG » 18 Nov 2019, 16:24

Hi Bruce

It feels like it's close to springing to life, but any combination of links I do either brings up an error of no data for this report or some other variation on not working. I haven't yet managed to see something I recognise, which is quite frustrating!

Thanks for any suggestions, I feel once I have my head found this there will be no stopping me.

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

Re: BOM Component Locations

Post by brucedenney » 19 Nov 2019, 10:28

If you want to attach your layout/report I will have a look and see what I can do to help you figure it out.
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.

BenJDMG
User
User
Posts: 10
Joined: 18 Nov 2019, 11:45
Sage Version: v2018 24 UK/Europe/Africa

Re: BOM Component Locations

Post by BenJDMG » 19 Nov 2019, 11:07

Hi Bruce

I thought I had attached it last time, but it didn't appear on here. Hopefully it's worked this time...

I spent some more time monkeying with it, but still haven't got a handle on it.

I have found that when I add what I think should be the BOM location the quantities multiply by 34, and the location is still picking up the one from parent product.

Thanks for any input you can give :)
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 Component Locations

Post by brucedenney » 19 Nov 2019, 12:02

I have only just twigged which Ben you are!

Nice to hear from you.

I will have a look when I get a moment and come back to you.
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.

BenJDMG
User
User
Posts: 10
Joined: 18 Nov 2019, 11:45
Sage Version: v2018 24 UK/Europe/Africa

Re: BOM Component Locations

Post by BenJDMG » 19 Nov 2019, 14:28

Thank you Mr. Bruce - much appreciated!

I hope sunny Somerset is treating you well :)

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

Re: BOM Component Locations

Post by brucedenney » 19 Nov 2019, 17:13

You dont seem to have added any joins, these table are just static

see https://ask.sage.co.uk/scripts/ask.cfg/ ... ssibility=
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.

BenJDMG
User
User
Posts: 10
Joined: 18 Nov 2019, 11:45
Sage Version: v2018 24 UK/Europe/Africa

Re: BOM Component Locations

Post by BenJDMG » 20 Nov 2019, 15:43

Hi Bruce

I think I had been editing that file and saving over the top of the one I uploaded without realising.

I have tried joins as attached, but I haven't been able to get the data on the component parts to show up, all I seem to win is the parent component attributes displaying.

I have joined a number of things together in various combinations, such as the location, stock code and component code, but have no managed to poke it into action.

Any pointer you can give please would be much appreciated!
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 Component Locations

Post by brucedenney » 21 Nov 2019, 10:22

The links are not right.

The Item Links to the Stock Table INV_ITEM.STOCK_CODE linking to STOCK.STOCK_CODE
this needs to be a parent outer joins, that is to say, when there is no corresponding STOCK.STOCK_CODE to still return the INV_ITEM.STOCK_CODE
this happens when you use the special product codes M S1, S2 and S3
You need to use parent outer joins where ever you have the scenario that the matching field in the table you are joining to might not exist.

The stock table links to the Stock comp. STOCK_CODE matching to ASSEMBLY_CODE
This returns all the Components as multiple records of COMPONENT_CODE

If you want to refer to other attributes of the component from its Stock Record you then need to link the Stock Comp table to a second copy of the STOCK table, which you will refer to by an Alias to differentiate it form the STOCK table for the item on the Invoice.

At this point you should be getting multiple records for each of the components that you can use in the details section of your report.

The item assembly details you will need to be in an INVOICE_ITEM Header if you want them to appear before the components, or in a footer if you want them after the 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.

BenJDMG
User
User
Posts: 10
Joined: 18 Nov 2019, 11:45
Sage Version: v2018 24 UK/Europe/Africa

Re: BOM Component Locations

Post by BenJDMG » 22 Nov 2019, 10:54

Hi Bruce

This still has me scratching my head somewhat - thank you for your suggestions to date!

I have succeeded in showing the locations, QTY and Stock Code for the assembly items on the invoice / pick note I am using to experiment with!

Harrah! I thought to myself...

So I set about a bit of testing... and it seems it's fine for an item with only a single BOM line. Once it has multiple lines, it gets a little messed up. I am not sure how to call each line of the BOM list and show the correct information.

In the product explosion report it appears to just work. In my invoices... not so much. It has be a little perplexed!
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 Component Locations

Post by brucedenney » 22 Nov 2019, 11:43

You are joining the

STOCK.STOCK_CODE to the STOCK_COMP.ASSEMBLY_CODE

So if the Stock code is something that does not have a BOM it will link to nothing and return nothing from STOCK_COMP or any of the tables linked to it.

You need to link the Item stock code to the Stock table stock code as a parent outer, this will return nothing when you use S1 or M but will return stock details for any product.

You then link the Stock.Stock_Code to the Assembly_Code in the stock comp table, this needs to be a parent outer so that if there isn't an entry in the STOCK_COMP table for that product (i.e. it has not got a BOM) it will return the Stock table but nothing from the STOCK_COMP table or any of the tables linked to it.

Start at the beginning, and add bits in one at a time, checking as you go.

Your other issue is that you are putting these STOCK_COMP variables in a footer section for the ITEM, so you will only see the last component for the item.

To see all the STOCK_COMP items you need to be at the details level. (Or in a STOCK_COMP footer/header adjacent to the details section)

It looks like you are making progress, but you need to do things in smaller steps, add in one table at a time, check and make sure it works before adding in the next bit.
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.

BenJDMG
User
User
Posts: 10
Joined: 18 Nov 2019, 11:45
Sage Version: v2018 24 UK/Europe/Africa

Re: BOM Component Locations

Post by BenJDMG » 22 Nov 2019, 14:44

Hi Bruce

I went back to basics, and have it doing what I would like for it to do, which is pretty nice!

My final snag that I can see is that rather than list off the whole selection of assembly items, the invoice re-lists the parent product again for each line of the BOM, making for a very confusing output.

Sorry to keep pestering you, I wonder if you get a moment if you can point me in the correct direction please?

Thank you very much!
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 Component Locations

Post by brucedenney » 25 Nov 2019, 10:07

Well the issue is that you are printing the parent product (which is an invoice item) in the detail section with is at the BOM item level, you need to make an invoice-item header/footer group and put the items details into that header, so it will print the header, then print multiple bom items at the details level.
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.

BenJDMG
User
User
Posts: 10
Joined: 18 Nov 2019, 11:45
Sage Version: v2018 24 UK/Europe/Africa

Re: BOM Component Locations

Post by BenJDMG » 29 Nov 2019, 10:38

Hi Bruce

Thank you again for that - my problems all appears solved when I made that switch! This week has been crazy so I have only just got to look at it.

I thought I should do some testing before pressing this report into action, and all was going nicely with my single line order and various BOM options.

Happy days!

I then thought I should see how it would appear if I added a few more invoice lines...

The totals all appear to work at the bottom of the page, which is nice.

The items themselves however aren't showing on the invoice! I expect I am missing something simple... but if you get some moments to point me in the correct direction please it would be much appreciate. I shall be messing with it in the mean time to see if I can solve it, and will update this thread if/when I succeed.

It also appears if I put multiple items with BOMs in the invoice all of their components show up, but their parent products don't.

Thanks

Ben
Login to access the files attached to this post.

Post Reply

Who is online

Users browsing this forum: No registered users and 15 guests