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

Different E-Mail Addresses for Remittances/Invoices/Orders.

Report design issues and solutions.
Post Reply
iStevie
User
User
Posts: 19
Joined: 17 Jun 2011, 07:51
Sage Version: v2013 19 UK/Europe/Africa

Different E-Mail Addresses for Remittances/Invoices/Orders.

Post by iStevie » 02 Apr 2012, 14:07

Hi,

I am trying to get my reports set-up so that different documents (i.e. statements/invoices/quotes) get sent to different e-mail addresses based upon the preferences i set in the Customer Record > Addresses & Contacts > Preferences menu. At the moment everything is 'Registered Office' but i have created contact addresses for each different e-mail address.

I think it is probably something to do with the report Joins but i am not overly familiar with them so would appreciate any help. I have posted the current account joins in raw text incase that is the problem and something needs adding/amending. I tried to copy the joins from a sage default e-mail template assuming it would be pre-configured but it messed my report up so i reverted back.

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

I'm guessing once that has been amended i will be able to set the e-mail 'TO:' address to SALES_DEL_ADDR.E_MAIL and it will send to the address which the preference has been set to?

Many Thanks,

Steve.
- Sage 50 Accounts Professional 2016 Subscription
- Sage 50 Payroll
- Sage 50 HR 2010
- Sage 50 Forecasting 2007

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

Re: Different E-Mail Addresses for Remittances/Invoices/Orde

Post by brucedenney » 03 Apr 2012, 17:55

There is a table called LETTER_TYPE, this is what contains all the addresses.

You need to set a filter on the report eg

LETTER_TYPE.LETTER_TYPE = 6

This "selects" the address you want to use.

Here is a sample join on invoices

[INVOICE]
INNER JOIN [SALES_LEDGER] ON INVOICE.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF
INNER JOIN [INVOICE_ITEM] ON INVOICE.INVOICE_NUMBER = INVOICE_ITEM.INVOICE_NUMBER
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
INNER JOIN [SALES_DEL_ADDR] ON SALES_LEDGER.ACCOUNT_REF = SALES_DEL_ADDR.ACCOUNT_REF
INNER JOIN [CURRENCY] ON SALES_LEDGER.CURRENCY = CURRENCY.NUMBER
LEFT OUTER JOIN [TAX_CODE] ON INVOICE_ITEM.TAX_CODE = TAX_CODE.TAX_CODE
INNER JOIN [COMMUNICATION_ADDRESS] ON SALES_DEL_ADDR.REFERENCE = COMMUNICATION_ADDRESS.ADDRESS_ID
LEFT OUTER JOIN [STOCK] ON INVOICE_ITEM.STOCK_CODE = STOCK.STOCK_CODE
INNER JOIN [LETTER_TYPE] ON COMMUNICATION_ADDRESS.LETTER_TYPE_ID = LETTER_TYPE.LETTER_TYPE
STATIC JOIN [COMPANY]

These are the relevant bits:-

Join delivery address to sales ledger
INNER JOIN [SALES_DEL_ADDR] ON SALES_LEDGER.ACCOUNT_REF = SALES_DEL_ADDR.ACCOUNT_REF

Join communication address to delivery address
INNER JOIN [COMMUNICATION_ADDRESS] ON SALES_DEL_ADDR.REFERENCE = COMMUNICATION_ADDRESS.ADDRESS_ID

join letter type to communication address
INNER JOIN [LETTER_TYPE] ON COMMUNICATION_ADDRESS.LETTER_TYPE_ID = LETTER_TYPE.LETTER_TYPE

Once you do this, SALES_DEL_ADDR.E_MAIL is the field you want to use.

Letter type values are

Reminder 1
Warning 2
Legal threat 3
Statements 4
Orders/Quotes 5
Invoice/Credits 6
GDN/GRN 7
Remittances 8
DefaultLetter 9
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: Ahrefs [Bot] and 14 guests