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

Joining Tables in Report Designer

Sage 50 general help forum - Free help and support for all general issues
Post Reply
boyallgra
User
User
Posts: 2
Joined: 24 Oct 2011, 13:23
Sage Version: v2012 18 UK/Europe/Africa

Joining Tables in Report Designer

Post by boyallgra » 24 Oct 2011, 14:24

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

happiness
User
User
Posts: 18
Joined: 15 Oct 2010, 16:55
Sage Version: v2009 15 UK/Europe/Africa

Re: Joining Tables in Report Designer

Post by happiness » 25 Oct 2011, 09:07

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.
[Happiness]

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

Re: Joining Tables in Report Designer

Post by brucedenney » 25 Oct 2011, 14:45

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?
Login to access the files attached to this post.
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.

boyallgra
User
User
Posts: 2
Joined: 24 Oct 2011, 13:23
Sage Version: v2012 18 UK/Europe/Africa

Re: Joining Tables in Report Designer

Post by boyallgra » 01 Dec 2011, 14:58

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:

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

Re: Joining Tables in Report Designer

Post by brucedenney » 01 Dec 2011, 16:21

I charge less than Sage for report writing. :-)
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