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

Email generate invoices as pdf attachment files for a list of customers

Report design issues and solutions.
Post Reply
Nacho
User
User
Posts: 1
Joined: 19 Oct 2019, 12:32
Sage Version: v2018 24 UK/Europe/Africa

Email generate invoices as pdf attachment files for a list of customers

Post by Nacho » 23 Oct 2019, 18:10

I am using Sage 50 Accounts Professional (v. 24.2.228.0) with Windows 7 Professional (32-bit).


Email generate invoices as pdf attachment files for a list of customers

I am trying to create a report that will email-generate (overdue, unpaid or in between dates) invoices as pdf file/s attachment/s for all or a filtered list of customers in the customer module.

The process will be similar to automatically emailing customers their statement of accounts or another report, as attachment pdf files.

The report will automatically generate 'one email per customer' and for each customer it will contain either:

one single pdf file attachment: one pdf file with all the overdue, unpaid or in between dates invoices for that customer,
or
multiple pdf file attachments: one pdf file for each overdue or so invoice document for that customer.

I have renamed an Invoice layout file as a report and moved to the reports folder.
I can run that report from the customer module and that will run all invoices for all customers.

Now I am trying to add whatever tables and built the join relations to be able to set this up, I am quite lost as I don't know which tables and which joins are required to do this...? .

I am guessing I need to filer or set up a report criteria for the required conditions of the invoices which I want to select like: 'overdue', 'unpaid', or 'in between dates', invoices. Therefore, when I run the report I can select which option of invoices I want to email to my customers.
This assuming I can filter query in my customers module a subset of customers, like: those who have a balance greater than 0.00.

Anyway, I am not a Sage report expert and I need help to achieve this.

Many thanks.

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

Re: Email generate invoices as pdf attachment files for a list of customers

Post by brucedenney » 24 Oct 2019, 10:07

There is nothing in the invoicing table to tell you if an invoice has been paid, so you need to link to the AUDIT_HEADER table Joining the INVOICE_NUMBER to the INV_REF and there you can read the OUTSTANDING PAID_FLAG or PAID_STATUS

There are 2 issues to solve in doing the join.

First the INV_REF field in the Audit Header is a text field and the INVOICE_NUMBER in the invoice table is a number, so you can't join them, to get around this you use the field INV_REF_NUMERIC

The Second issue is that you could have a supplier invoice with the same INV_REF as one of your sales invoices and if it is unpaid then .... So you need to add a filter to the report to make sure the AUDIT_HEADER.TYPE is "SI" (or "SC") You will want to include a filter based on OUTSTANDING or PAID_STATUS or PAID_FLAG as well.

Have you thought through all scenarios?

What happens when an invoice is part paid?

What happens with credit notes?

What happens where a Credit note and an Invoice are left unmatched on an account?

Finally you need to be aware that this could be quite a lot of data and it could take a long time to run.

I hope this helps and good luck.
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 8 guests