SageForum for UK versions of Sage 50- Help and support.

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.

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