Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

Delivery Address

Report design issues and solutions.
Post Reply
jagreilly
User
User
Posts: 7
Joined: 09 Mar 2022, 17:33
Sage Version: Other

Delivery Address

Post by jagreilly » 09 Mar 2022, 17:48

Hi Newb here!

I have made a copy of "A4 Statement, Grouped & All Items - Plain Paper (Print or Email)" have branded it and am tweaking the layout to suit how we want our customer statements to look.

1) I am trying to get delivery address onto the main statement rows and from reading another thread have already made a parent outer join between AUDIT_HEADER INV_REF and INVOICE INVOICE_NUMBER. I was under the impression this should have allowed me to pull the delivery address data from the invoice through to the statement but its showing up blank when statement is previewed/printed :|

2) Same for customer order number.

I've checked in the invoices for the customer I am using and the information is all in there, I just can't seem to pull it through for some reason on the editor.

At the moment I've just stuck the delivery address in to see if I can get the data to pull through, when I can I will tidy up and align with regards to table headers etc.

Many thanks in advance for your assistance.
You do not have the required permissions to view the files attached to this post.

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

Re: Delivery Address

Post by brucedenney » 10 Mar 2022, 10:50

To join tables you need to use the same data types, so numbers join with numbers and text joins with text.

You have joined the INV_REF (text) to INVOICE_NUMBER (number)

You can fix this easily

In the AUDIT_HEADER table you will find there are 2 INV_REF fields, INV_REF and INV_REF_NUMERIC.

INV_REF is a text field, the INV_REF_NUMERIC is the numeric version of that field.

If you join using the numeric version it should work.
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.

jagreilly
User
User
Posts: 7
Joined: 09 Mar 2022, 17:33
Sage Version: Other

Re: Delivery Address

Post by jagreilly » 10 Mar 2022, 11:15

Thanks for the reply Bruce,

I was up all night trying to get this working, I found more of the threads you've responded to previously and it seems this is a common query, so I had corrected that join as I understand now both items in the join should be numeric or it will not work. See attached screenshot.

It still just comes up blank when the statement is previewed/printed with the join set this way. I've attached the file so maybe you can see where I've gone wrong.

I know the data is definitely there in the invoice table, it seems it just can't link to the invoice in question.

Many thanks!
You do not have the required permissions to view the files attached to this post.

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

Re: Delivery Address

Post by brucedenney » 10 Mar 2022, 12:14

Try this.
You do not have the required permissions to view 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.

jagreilly
User
User
Posts: 7
Joined: 09 Mar 2022, 17:33
Sage Version: Other

Re: Delivery Address

Post by jagreilly » 10 Mar 2022, 12:32

Thanks Bruce,

I've just ran the statement for the customer I'm testing with and those fields still come up blank when previewed/printed. I've double checked in the invoices and credits menu and the invoice I have in for them (which is displaying on the statement) has an order number in the details tab and a delivery address in the order tab. Neither pull through.

Thanks :?
You do not have the required permissions to view the files attached to this post.

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

Re: Delivery Address

Post by brucedenney » 11 Mar 2022, 10:51

The account ref should show at the very least, are you using the one I posted with BD on the end of the name?
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.

jagreilly
User
User
Posts: 7
Joined: 09 Mar 2022, 17:33
Sage Version: Other

Re: Delivery Address

Post by jagreilly » 11 Mar 2022, 11:32

Hi Bruce, Yes am using the one you sent back with BD at the end. See attached how it prints (have taken customer name out intentionally). I don't get either of the two fields you've added in for me show up when previewed/printed.
You do not have the required permissions to view the files attached to this post.

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

Re: Delivery Address

Post by brucedenney » 11 Mar 2022, 14:26

The filename of the PDF is not correct for the layout I uploaded.

It does not have BD at the end of it.

Can you double check the report you ran was the one i altered please.
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.

jagreilly
User
User
Posts: 7
Joined: 09 Mar 2022, 17:33
Sage Version: Other

Re: Delivery Address

Post by jagreilly » 11 Mar 2022, 17:55

Hi Bruce,

Have double checked, this is definitely the one I'm running. See attached, the file name the PDF gets seems to comes from the description on this screen but as you can see this is definitely the one with BD in it.
You do not have the required permissions to view the files attached to this post.

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

Re: Delivery Address

Post by brucedenney » 14 Mar 2022, 10:57

Do the invoices exist in the invoicing module?

It works for me.
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.

jagreilly
User
User
Posts: 7
Joined: 09 Mar 2022, 17:33
Sage Version: Other

Re: Delivery Address

Post by jagreilly » 14 Mar 2022, 16:20

Hi Bruce

Yes see attached, they are definitely in there just still won't pull through.
You do not have the required permissions to view the files attached to this post.

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

Re: Delivery Address

Post by brucedenney » 14 Mar 2022, 16:33

Here is a screen shot of the report run against the demo data and it returns the account code and the address.

I can't really help any further unless you want to become a customer and then I can have a look in-situ on your PC.
You do not have the required permissions to view 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.

jagreilly
User
User
Posts: 7
Joined: 09 Mar 2022, 17:33
Sage Version: Other

Re: Delivery Address

Post by jagreilly » 14 Mar 2022, 16:38

Thanks Bruce, I've sent you a message with my details hopefully we can resolve.

Post Reply

Who is online

Users browsing this forum: Google [Bot] and 0 guests