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

Show Delivery Add on Statement

Report design issues and solutions.
Post Reply
jonsc
User
User
Posts: 13
Joined: 26 Feb 2014, 12:30
Sage Version: v2012 18 UK/Europe/Africa

Show Delivery Add on Statement

Post by jonsc » 28 Mar 2014, 16:02

Hi,
I'm having a problem with adding "Delivery Name" to my statement report. So far I have tried linking INVOICE.DEL_NAME to AUDIT_SPLIT.DETAILS (in the hope that it would replace the invoice description with the Delivery Name) and also I have tried linking INVOICE.DEL_NAME to COMPANY.DEL_NAME but in neither case does it return any information.

Am I missing something or doing something wrong or indeed can the delivery info, 1st line, from the invoice be displayed on the statement.

Many thanks for any help.
Jonsc

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

Re: Show Delivery Add on Statement

Post by brucedenney » 28 Mar 2014, 18:23

There is no single delivery Address, there are lots of them, and there are several associated with each customer.

Which one do you want to use?
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.

jonsc
User
User
Posts: 13
Joined: 26 Feb 2014, 12:30
Sage Version: v2012 18 UK/Europe/Africa

Re: Show Delivery Add on Statement

Post by jonsc » 29 Mar 2014, 14:47

Its the first line on the Invoice Delivery address (INVOICE.DEL_NAME according to the A4 Inv\Crd Unit Price layout).

Jonsc

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

Re: Show Delivery Add on Statement

Post by brucedenney » 31 Mar 2014, 15:33

You don't always have a single invoice on a statements, there could be several or there could be just a payment on account, so which one of the invoices is it that you want?

Can you attach your layout and I can see what you have done.
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.

jonsc
User
User
Posts: 13
Joined: 26 Feb 2014, 12:30
Sage Version: v2012 18 UK/Europe/Africa

Re: Show Delivery Add on Statement

Post by jonsc » 02 Apr 2014, 10:36

Sorry for slow response.

I am attaching copies of the Statement Layout and the Invoice layout. I am trying to get the Deliver Name from the Invoice Layout into the Statement in place of Details.

jonsc
Login to access the files attached to this post.

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

Re: Show Delivery Add on Statement

Post by brucedenney » 02 Apr 2014, 10:54

The layout as it stands is picking the delivery address on the customer record that is associated with COMMUNICATION_ADDRESS.LETTER_TYPE_ID = 4 not anything to do with the delivery addresses on any invoices on the statement.

To set the address to use for statements...

Open the "Customer Record and go to "Addresses and Contacts" select "Preferences" in the drop down box next to Statements pick the delivery address you want to use.

-----
We could link the invoices to the statement and then read the delivery address off the invoice

The issue is that there could be several invoices on the statement and each of them could have a different delivery address.

There is no way to pick the 4th invoice on a statement, the only way to do it would be based on the last line of the statement but, the last line might not be an invoice.

Why are you trying to do this, perhaps there is another approach that could get you the same result?
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.

jonsc
User
User
Posts: 13
Joined: 26 Feb 2014, 12:30
Sage Version: v2012 18 UK/Europe/Africa

Re: Show Delivery Add on Statement

Post by jonsc » 02 Apr 2014, 11:16

We need to create regular statements for clients that clearly show the delivery address against each inv, so on the statement body we need to have invoice number, date, amount and the delivery address (first line only). This is the information which is entered on the invoice creation second tab and is printed on the invoice layout in the bottom left box.

jonsc

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

Re: Show Delivery Add on Statement

Post by brucedenney » 03 Apr 2014, 16:49

Ok, so you want to replace the line description on each invoice with the delivery address line 1, that is no big deal.

See the attached sample

I have only done a few things.

1/ Add the Invoice table to the report
2/ Create a join to the Inv Ref Numeric field in the audit header. It has to be the numeric field if you link to the Inv ref field it is not numeric so even through it ciontains a number it will not find the corresponding invoice number.
3/ Make the join a parent outer, this is because we still want data returned even if there is no matching invoice eg if the item on the invoice is a payment.
4/ Add the invoice Del add1 field to the layout.
5/ Conditional formatting on the added field to only print it when the item type is SI, if it is a Payment and the reference matches an invoice we don't want to print the delivery address.
6/ Conditional formatting the normal text to suppress it when the transaction type is SI as we will print the delivery address in this case.
7/ Place the two fields on top of each other.
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.

jonsc
User
User
Posts: 13
Joined: 26 Feb 2014, 12:30
Sage Version: v2012 18 UK/Europe/Africa

Re: Show Delivery Add on Statement

Post by jonsc » 04 Apr 2014, 15:53

It Works! thank you very much for your help.

Jonsc

angelic_21
User
User
Posts: 1
Joined: 01 Feb 2019, 11:53
Sage Version: Other

Re: Show Delivery Add on Statement

Post by angelic_21 » 01 Feb 2019, 11:56

Attachments
BDA Statement Bruce.layout
sample statement with delivery address
(857.16 KiB) Downloaded 22 times

I cant open the above....

Can anyone help please. Im looking to set this up asap

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

Re: Show Delivery Add on Statement

Post by brucedenney » 01 Feb 2019, 12:32

Once you log in you should be able to, if you can't please contact me and I will email it to you.
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.

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

Show Invoice details on Statement

Post by brucedenney » 08 Feb 2022, 12:29

It appears the original layout is not working with current versions of sage 50.

Here is a new similar things which puts details from the invoice as an extra line on the statement
STATOSPLAINA4withInvoiceDetails.LAYOUT
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.

Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests