Page 1 of 1

Delivery Address

Posted: 09 Mar 2022, 17:48
by jagreilly
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.

Re: Delivery Address

Posted: 10 Mar 2022, 10:50
by brucedenney
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.

Re: Delivery Address

Posted: 10 Mar 2022, 11:15
by jagreilly
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!

Re: Delivery Address

Posted: 10 Mar 2022, 12:14
by brucedenney
Try this.

Re: Delivery Address

Posted: 10 Mar 2022, 12:32
by jagreilly
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 :?

Re: Delivery Address

Posted: 11 Mar 2022, 10:51
by brucedenney
The account ref should show at the very least, are you using the one I posted with BD on the end of the name?

Re: Delivery Address

Posted: 11 Mar 2022, 11:32
by jagreilly
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.

Re: Delivery Address

Posted: 11 Mar 2022, 14:26
by brucedenney
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.

Re: Delivery Address

Posted: 11 Mar 2022, 17:55
by jagreilly
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.

Re: Delivery Address

Posted: 14 Mar 2022, 10:57
by brucedenney
Do the invoices exist in the invoicing module?

It works for me.

Re: Delivery Address

Posted: 14 Mar 2022, 16:20
by jagreilly
Hi Bruce

Yes see attached, they are definitely in there just still won't pull through.

Re: Delivery Address

Posted: 14 Mar 2022, 16:33
by brucedenney
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.

Re: Delivery Address

Posted: 14 Mar 2022, 16:38
by jagreilly
Thanks Bruce, I've sent you a message with my details hopefully we can resolve.