Page 1 of 1

Joining Tables in Report Designer

Posted: 24 Oct 2011, 14:24
by boyallgra
Hello,

I'm new to this forum so please bear with me.

I have joined the Sales Order table to the Goods Despatched Note table using the Order Number field and the Parent Outer condition.

I needed certain informtion that wasn't available throught the GDN table (the Text from my Sales Orders and the Stock Quantity Remaining to be despatched).

The join however is now giving me 3 copies of each line on the GDN.

Is there any way to prevent this?

Thanks,


Scott

Re: Joining Tables in Report Designer

Posted: 25 Oct 2011, 09:07
by happiness
Sales Order information on a GDN layout is something that needs to be written using the Raw Text Editor. Sage can do this for you but it's probably going to be a report design request.

If you have SageCover Extra, you get 3 free reports per year.
If you have SageCover, they're £100 + VAT per report.
If you have no SageCvoer, they're £200 + VAT per report.

Re: Joining Tables in Report Designer

Posted: 25 Oct 2011, 14:45
by brucedenney
There are potential problems here, I have seen Sage renumber the items on sales orders, if they are edited, so not sure if the links to the item numbers would survive if you edited the order anyway, you could try linking on the product code, but that would only work if there was only ever one use of a product code on any order. So there is a choice to make link on the order number and stock code or the order number and item number.

I would imagine that you would need to do something like have an order with 2 items, deliver the second item then remove the first item to break things.

I have had a quick go at this and I got the same thing, repeating lines.

It appears that there is no filter on which address is being used so I added SALES_DEL_ADDRESS.ADDRESS_TYPE=0 as a filter and it works fine, bar my caveat above.

I also used and inner join as the sop_item should always be there.

I did my report using the normal report designer, perhaps happiness could enlighten us about the raw text editor I have no idea what it is.

@happiness what is this raw text editor? do you work for sage?

Re: Joining Tables in Report Designer

Posted: 01 Dec 2011, 14:58
by boyallgra
Thanks,

Managed to get this working with the instructions you posted, can't get it to do everything I wanted but beats paying a hundred quid to Sage :shock:

Re: Joining Tables in Report Designer

Posted: 01 Dec 2011, 16:21
by brucedenney
I charge less than Sage for report writing. :-)