Page 1 of 1

Customer statements

Posted: 08 Apr 2021, 14:32
by Abbyy
Hello

I am trying to edit my customer statements so that it shows the first line of the delivery address, & PO no if there is one on the line next to the invoice number. I also want to add the due date, and change the text colour to red if the date has passed. Is there a way to do these things?

Most of our customers have multiple delivery addresses and each invoice on the statement could be for a different delivery address. I have tried SALES_DEL_ADDR.ADDRESS_1 but it shows line 1 of the Customer's registered address, and SALES_LEDGER.DEL_ADDRESS_1 comes up blank when you run the statement. I don't know if it's even possible, but guessing it needs to be linked to AUDIT_SPLIT.INV_REF to know which invoice to select the delivery address for. I haven't even played around with the PO number yet as know I will have the same problem.

Thanks! :)

Re: Customer statements

Posted: 08 Apr 2021, 15:01
by brucedenney
Yes, but no, but yes.

So the data you have is in the AUDIT_HEADER and AUDIT_SPLIT, the data you want is in the INVOICE table.

So you need to make a join between the INV_REF in the AUDIT_HEADER and the INVOICE_NUMBER, then you can reference the INVOICE.DEL_ADDRESS_1 and INVOCIE.CUST_ORDER_NUMBER

The SALES_DEL_ADDR table contains a list of delivery addresses for you to pick from, it is not needed for this.

The Due Date is available it is AUDIT_HEADER.DATE_DUE

You can even calculate the number of days past Due and put that on XXX Days overdue (I do this on my statements and supress the message when they are not overdue)

You can use conditional formatting to change the colour of objects based on the value of the number of days past DATE_DUE

I hope this helps.

Re: Customer statements

Posted: 08 Apr 2021, 15:22
by Abbyy
Thank you! I have managed to get the due date on and have managed to conditional format it so that it appears in red if overdue.

How do you create joins between those expressions please?

Re: Customer statements

Posted: 08 Apr 2021, 15:45
by brucedenney
Open the report in the report designer

go to the Join Editor which is in Report (wordy menu at the top)>Joins

Drag the table you want to add into the work area from the toolbox on the right. In you case INVOICE

Now drag the field you want to link and drop it onto the field you want it to link to. You will see a line linking the 2 fields on the work area.

In your case this will be the invoice number and the INV_REF_NUMERIC ... the invoice reference can contain letters and so it is text, computers do not let you join text to numbers, so Sage have added a number version of the invref and this is the one you need to use to achieve a join.

If you want to only see invoices that exist in the invoicing module then you are good to go, however if you want to see all the invoices (and receipts/creditnotes or anything else) in the audit trail even if they are not in the Invoice Table then you need to change the join to a "Parent Outer" join.

If you click on the line joining the tables, then on the "properties pane" which is normally pinned on the right, you will see the join type is Inner for "Conditional join type" and for "Join type", change them both to "Parent Outer" and now you will always get all the audit trail and only get the fields from the invoice table if the link can be established.

It is worth noting here that other transactions such as receipts that happen to contain a reference that is also a valid invoice number will also link to the invoice, so you may want to also do some extra suppression/hiding of those fields if the transaction type is not SI or SC.

Re: Customer statements

Posted: 08 Apr 2021, 16:04
by Abbyy
You are a star thank you!! :)