GDN Report produces multiple pages for a single data line

[pauljj]

Version - 22.1.10.394 of Sage50
Running Windows 10Pro 64 Bit

Hi, I am currently logging in and using the practice version to setup and configure various reports and documents prior to us putting our inventory onto the live system.
I am fairly familiar with report designer but not exactly an expert.
As i am in practice mode i have updated the data sources to my current version and have managed to produce custom Sales/Purchase orders, invoices, credits and such. I have edited the Joins too based on the information i have read, so bringing in the SOP table and amending the links to the GDN table.
Where i am at now is Delivery Notes and the GDN report. Both of which i am finding extremely challenging.

I have finally managed to customise a Dispatch Note based on the transient data it pulls in at the point of creating the dispatch. As i understand it now, i cannot reprint that note using that same process, but rather save a PDF and reprint from that. Which is fine.
So, i'm now looking at the GDN report in the hope that i can at least "re-create" the look of the original Dispatch Note, the main reason for this is purely backup. Some people do forget to save the PDF in the first place, or there is a system crash or some such. So, i'm thinking the GDN report could provide as a useful tool to produce the same looking document at least.

The issue i'm having is that i go into the Dispatched Tab, highlight a GDN entry, in this case an order that has multiple dispatches against it, I press Print GDN and it does bring up my amended report and runs. What i'm seeing though, is where i want to see the one data line of the goods shipped on one page, the report is producing 4 other blank pages after it. So, i created a few more transactions against the same order and repeated the process, and the while i can see the line of data i'm expecting to see, the blank pages just seem to increase.

It looks to me like its trying to produce either the same data line as many times as there are transactions against the entire order, and then repeating them over and over or something similar. The extra lines are blank on the report, and i think it might be down to some expressions i have written to filter out values in the quantity despatched fields that may contain a zero value. I put these in in the hopes that it would filter out the repeated lines, as my first run resulted in the same data line being repeated over and over.

This post will probably beg more questions you may need to ask of me as i'm not entirely sure what information you would need in order to either 1-understand what on earth i'm going on about (most likely ) or 2-Not enough detail to explain what i'm attempting to setup.

In any case, any help would be greatly appreciated.
Many Thanks
Paul

[brucedenney]

I suspect the joins are incorrect but it is really hard to tell from your description.

What did you start from a blank report or did you use the GDNPRNT.report or something else

Can you attach the report and tell me which directory the report was in, if it is not in \SOP\My SOP Reports

I can then have a look and see.

[pauljj]

Hi, and thanks for getting back. Yes a little hard to explain.
I started with the original GDNPRINT.report from it's default location on my C: drive
C:\ProgramData\Sage\Accounts\2016\Company.000\Reports\SOP\Goods Despatched Reports

I will attempt to upload the report for you now, but it's near my close of business currently. This is what I've pulled from the raw text editor in the joins. Not sure if this is useful ?

[SALES_ORDER]
INNER JOIN [SALES_LEDGER] ON SALES_ORDER.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF
INNER JOIN [COMMUNICATION_ADDRESS] ON SALES_ORDER.ACCOUNT_REF = COMMUNICATION_ADDRESS.ACCOUNT_REF
INNER JOIN [LETTER_TYPE] ON COMMUNICATION_ADDRESS.LETTER_TYPE_ID = LETTER_TYPE.LETTER_TYPE
INNER JOIN [SOP_ITEM] ON SALES_ORDER.ORDER_NUMBER = SOP_ITEM.ORDER_NUMBER
LEFT OUTER JOIN [STOCK] ON SOP_ITEM.STOCK_CODE = STOCK.STOCK_CODE
INNER JOIN [GDN_ITEM] ON SOP_ITEM.ORDER_NUMBER = GDN_ITEM.ORDER_NUMBER
INNER JOIN [SALES_DEL_ADDR] ON COMMUNICATION_ADDRESS.ADDRESS_ID = SALES_DEL_ADDR.REFERENCE
STATIC JOIN [COMPANY]

thanks Paul

[brucedenney]

So what filter do you have on the report, can you upload it?

[pauljj]

I have to shut down now, but I've copied this out very quickly
GDN_ITEM.QTY_DESPATCHED <> 0.0
this is within data source properties, I've also included a criteria within
GDN_ITEM.GDN_NUMBER
In an attempt to just pull one report per GDN at a time, mainly because it was easier than running every record.

Thanks for this and i'll attempt a proper report upload on Monday. Have to battle the traffic now.
Thanks Paul

[brucedenney]

I think this is where the problem is happening, you have all these linked in communication addresses and not a filter to select the one you want.

Personally, I hate the way sage have done this, it slows down reports a lot. I just rip of the delivery address and communication letters tables and make things simple, most people do not even know you can pick different addresses for different purposes, I am sure it is brilliant for those who need it but for the other 95% of users a simple and quick solution would be better.

You need to either remove those tables or put a filter on the letter type,I think it is 7 for this, refer back to the original to check, I am not at a pc at the mo.

[pauljj]

Hi, thanks for that reply.
So, i tried the filter of 7 (you were correct with that- GDN/GRN) in letter type and i was just getting the error of no data could be returned. So, i went back to the joins and removed the link to communication addresses, disabled the criteria GDN_ITEM.GDN_NUMBER and re ran. The reports worked but with all GDN items showing, and for some reasons the Billing address started throwing incorrect names, on that i took the tables out of the joins completely and replaced the Billing address fields with those from SALES_LEDGER table, and that appears to work. I no longer have multiple blank pages being produced so for that alone i'm saying thanks

So, if what I've done is correct (?) i'm at the "beginning" where i need to filter out All GDN items per dispatch note. I put the criteria on GDN_ITEM.GDN_NUMBER back on, and yes i have the correct number of pages, but it repeats the GDN data line i type but produces the information multiple times over the amount of GDN transactions there are over the entire dispatch note (hope that makes sense?) for example, if i have a sales order with 10 dispatch lines recorded against it, then i see the GDN number i enter 10 times.

Also, is there a way to upload the file to you ? I looked a the PDF version and it looks a bit limiting in terms of being able to read the expressions in each field.

This is the what my Joins looks like now.
[SALES_ORDER]
INNER JOIN [SALES_LEDGER] ON SALES_ORDER.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF
INNER JOIN [CURRENCY] ON SALES_LEDGER.CURRENCY = CURRENCY.NUMBER
INNER JOIN [SOP_ITEM] ON SALES_ORDER.ORDER_NUMBER = SOP_ITEM.ORDER_NUMBER
INNER JOIN [GDN_ITEM] ON SOP_ITEM.ORDER_NUMBER = GDN_ITEM.ORDER_NUMBER
LEFT OUTER JOIN [STOCK] ON SOP_ITEM.STOCK_CODE = STOCK.STOCK_CODE

[pauljj]

Hi again,

Just to add - do i need something like a new section for the GDN Item Unique ID ? To limit the data lines being produced maybe ?
Thanks Paul

[pauljj]

Hi Bruce, finally got round to attaching my report template. Hope this is correct?
I have left the suppress duplicates in Details on the stock code and description fields just as an example of the different things i am trying.

Thanks Paul

[brucedenney]

You seem to have linked both the SOP_ITEM table and the GDN_ITEM table to the Sales order number. So for each GRN you will get back all the GDN_ITEMS with that number and ALL the SOP_ITEMS.

You need to link the SOP_ITEM to the GDN_ITEM so you can calculate the outstanding items.

You need to link on two fields between GDN_ITEM table and the SOP_ITEM table, the "Order Number" and the "SOP Item Number" to do this you use a comma separated list.

For each GRN_ITEM this will now return the just the one SOP_ITEM which has a matching ORDER_NUMBER and ORDER_ITEM number.

Here is a sample I made to show this.

[pauljj]

Hi, and thanks for this - i'm following the advice and working through each field - I followed the advice from sage itself regarding the linking of the SOP_ITEM table and the GDN_ITEM table to the Sales order number. Probably followed that wrong So, i am working through the changes and below is what i have done and what i'm noticing as it's now not pulling any data currently.

So, I've put back the communication address and related tables and added in the LETTER_TYPE.LETTER_TYPE = 7, filter expression.
I've followed your example in terms of the joins and they match now. Thanks for clearing that up, it's beginning to make more sense.

I've made the "Billing Address" field look back to the original SALES_DEL_ADDR table - i had changed that.
I've removed the expressions in the Stock Code and Description fields in the ITEM_NUMBER footer, again back to the original GDN table fields. so, GDN_ITEM.STOCK_CODE & GDN_ITEM.DESCRIPTION

I also added in GDN_ITEM.ITEM_NUMBER into the details section as i didn't have this.

I'm wondering though, as my template has to pick up any service items, whether this could be causing an issue, or maybe the group headers ?
So, in the first ITEM_NUMBER Header in group expression it says SOP_ITEM.ITEM_NUMBER.
Details has - SOP_ITEM.SERVICE_FLAG <> 0 in the filter.
The next 3 ITEN_NUMBER Footers have the following
4 - SOP_ITEM.SERVICE_FLAG = 0
5 - SOP_ITEM.SERVICE_FLAG = 0 AND SOP_ITEM.COMMENT_1 <> ""
6 - SOP_ITEM.SERVICE_FLAG = 0 AND SOP_ITEM.COMMENT_2 <> ""

Attachment shows where i am upto currently. Not quite sure where to go from here you see.
Thanks Paul

Addendum - I've noticed also the any Reset Sections cannot be changed to Details as i'm guessing this works via the footers?
Addendum 2 - I removed the LETTER_TYPE.LETTER_TYPE = 7 and the report runs all the GDN's (?) but it's still showing all items per Sales Order, not per despatch (?)

[pauljj]

Hi Bruce,

Well i think I've sorted it.

I double checked all the joins, fields etc from your sample to mine and was still getting the wrong results. So, i went and checked the page and group headers and i could see that i didn't have a GDN_NUMBER header or footer. I did have a Page header and footer, so i removed that and added in the GDN_ITEM.GDN_NUMBER as a group header and footer, set the visibility to False, and moved the Group up the report.
I also set
Page Break Before and Repeat On Each Page (Behaviours) both to True as they were set to false.
Re-ran the report and voila !!! It worked ! I am going to go through all those types of settings one by one just to make sure everything is consistent, but hopefully i now have a a GDN report that now acts as a delivery note.

So, all i need to fix now is the Service Items from repeating and everything should be good. I've attached my latest report setup and if you wouldn't mind having a quick look for me, as i'm unsure how to set up the service items from repeating. for example i tested a one line service dispatch on a two line Sales order, and when i print the from the dispatched tab, it produces the same service data line 7 times on 1 page (?) could this be down to the setup of SOP_ITEM.SERVICE_FLAG in ITEM_NUMBER header, resetting after ITEM_NUMBER footer ? as the GDN_ITEM.ITEM_NUMBER in the same section resets after Details section.

THANK YOU THANK YOU THANK YOU for your help on this !!!
Paul

[brucedenney]

You need to add a filter to only include records where the LETTER_TYPE.LETTER_TYPE is = 7 (This is the "GDN/GRN" address)

see http://www.makingithappen.co.uk/CI/stat ... ETTER_TYPE

[pauljj]

Hi, yes i had that in originally but i get the error No Data was returned for this report (?) I took it out and it runs.

[brucedenney]

That could be because that customer does not have a default GRN/GDN address set, if you fiddle around in addresses and make sure one is set for GRN/GDN it will come good.

We see the same thing all the time with statements.

You could remove all the links to communication addresses and letters, simplify things. If you don't need to use that function.