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
BOM Component Locations
BOM Component Locations
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!
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!
- brucedenney
- Site Admin
- Posts: 4633
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: BOM Component Locations
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.
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.
Re: BOM Component Locations
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.
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.
- brucedenney
- Site Admin
- Posts: 4633
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: BOM Component Locations
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.
Re: BOM Component Locations
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
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.
- brucedenney
- Site Admin
- Posts: 4633
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: BOM Component Locations
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.
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.
Re: BOM Component Locations
Thank you Mr. Bruce - much appreciated!
I hope sunny Somerset is treating you well
I hope sunny Somerset is treating you well
- brucedenney
- Site Admin
- Posts: 4633
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: BOM Component Locations
You dont seem to have added any joins, these table are just static
see https://ask.sage.co.uk/scripts/ask.cfg/ ... ssibility=
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.
Re: BOM Component Locations
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!
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.
- brucedenney
- Site Admin
- Posts: 4633
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: BOM Component Locations
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.
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.
Re: BOM Component Locations
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!
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.
- brucedenney
- Site Admin
- Posts: 4633
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: BOM Component Locations
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.
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.
Re: BOM Component Locations
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!
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.
- brucedenney
- Site Admin
- Posts: 4633
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: BOM Component Locations
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.
Re: BOM Component Locations
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
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.
Who is online
Users browsing this forum: No registered users and 1 guest