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

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

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?

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

Jonsc

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.

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

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?

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

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.

It Works! thank you very much for your help.

Jonsc

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

Once you log in you should be able to, if you can't please contact me and I will email it to you.

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