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

Re: BOM Component Locations

Post by brucedenney » 29 Nov 2019, 12:29

You have the line item data in a page header so it will get printed once per page, it needs to be in an item_number header so it will be printed once per item. But this is incidental because the source data isn't right

In addiction to this you have not got the source data right.

You made the join between the stock code and the assembly code an inner join, so it will only return data where the value exists in both tables.

The Stock Code will not exist in the Stock comp table unless it has a BOM so this will only return data when the Stock Code has a BOM associated with it.

You really want to get the joins and the source data right before you start laying things out.

As everything is visible in the details section I would put one field from each table into the details section and make sure I got the data I expected first. You will want to make some test data to cover all the types of data that you will see, so you want an Item that is not in the Stock Table eg a Message Line, You want an Item that doesn't have a BOM and an item that does have a BOM.

I would then use the details section and print the StockCode out of the Item Table, run that check all of them print.
Add in the Stock table do the join and run that.
If the Message line didn't print then I would resolve that by fixing the join between the Item and the stock, making it a parent outer join so that it would still give me the item stock code even when it didn't exist in the stock table and so on table by table join by join until I had all the right data available in my report.

Only then I would start adding header/footer groups and laying things out.

Hope this helps.
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, 14:47

Hi Bruce

You are very helpful kind sir, I got methodical on it, and appear to have cracked it.

Now to learn how to drive the if statements to have items showing based on quantities in stock. Wish me luck!

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, 17:17

On a side note... invoices moving over to multiple pages throws up some random challenges!

This is all now happy, following some testing... I had to add a calculation into the QTY field in order for it to show up on the second page, without that it just refused to display.

I haven't enjoyed great success with 'IF' statements yet, I plan to have the 'Ordered' text and box be hidden if it's in stock, but for now it can just be there :)

Thanks again for all of your help!

Our of interest, to product a bespoke report like this one, what would your going rate be?
Login to access the files attached to this post.

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

Re: BOM Component Locations

Post by brucedenney » 29 Nov 2019, 17:31

To hide a box if it is in stock, you don't need to use ifs, you use conditional formatting, which has an implicit if, you set the condition and then tell it to suppress print or not suppress print on that condition.

I charge my time for reports.

How long things take depends on what needs doing.
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 10 guests