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

How to get delivery address *country* onto reports

Report design issues and solutions.
Post Reply
mpfj
User
User
Posts: 8
Joined: 16 Sep 2010, 08:58
Sage Version: v2015 21 UK/Europe/Africa

How to get delivery address *country* onto reports

Post by mpfj » 30 Sep 2016, 09:13

I am trying to get the delivery address *country* onto our delivery / invoice reports.

I can get the customer's country code, using COUNTRY_CODE.NAME, but this only gives me the customer registered address.

I currently show the delivery name and address (lines 1-5) using:-

INVOICE.DEL_NAME
INVOICE.DEL_ADDRESS_1
INVOICE.DEL_ADDRESS_2
INVOICE.DEL_ADDRESS_3
INVOICE.DEL_ADDRESS_4
INVOICE.DEL_ADDRESS_5

But I can't work out how to access the country code of the delivery address.

Here's the current joins table:-

[INVOICE]
INNER JOIN [INVOICE_ITEM] ON INVOICE.INVOICE_NUMBER = INVOICE_ITEM.INVOICE_NUMBER
LEFT OUTER JOIN [TAX_CODE] ON INVOICE_ITEM.TAX_CODE = TAX_CODE.TAX_CODE
INNER JOIN [SALES_LEDGER] ON INVOICE.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF
INNER JOIN [CURRENCY] ON SALES_LEDGER.CURRENCY = CURRENCY.NUMBER
LEFT OUTER JOIN [STOCK] ON INVOICE_ITEM.STOCK_CODE = STOCK.STOCK_CODE
LEFT OUTER JOIN [PROJECT] AS [INVOICE_ITEM_PROJECT] ON INVOICE_ITEM.PROJECT_ID = INVOICE_ITEM_PROJECT.PROJECT_ID
LEFT OUTER JOIN [COUNTRY_CODE] ON SALES_LEDGER.COUNTRY_CODE = COUNTRY_CODE.CODE
STATIC JOIN [COMPANY]

Any help would be greatly appreciated !!

Regards
Mark J.

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

Re: How to get delivery address *country* onto reports

Post by brucedenney » 03 Oct 2016, 14:03

It can't be done there is no good key between the two data sets, you could try using the delivery address fields all concatenated, but it would be a flaky solution and any change would break things.
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.

maxmillion
User
User
Posts: 7
Joined: 13 Jul 2011, 22:14
Sage Version: v2016 22 UK/Europe/Africa

Re: How to get delivery address *country* onto reports

Post by maxmillion » 10 Dec 2016, 19:45

Because foreign addresses do not match UK address format, I always added the foreign country name to the POSTCODE field which is address field 5.
For most of our foreign customers, they rarely use all 5 fields.

Post Reply

Who is online

Users browsing this forum: No registered users and 13 guests