SageForum for UK versions of Sage 50- Help and support.

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.

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!

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?

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.