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!
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
- - - - - - - - - - - - - - - - - - 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
Customer statements
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Customer statements
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.
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.
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.
Re: Customer statements
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?
How do you create joins between those expressions please?
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Customer statements
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.
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.
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.
Re: Customer statements
You are a star thank you!!
Who is online
Users browsing this forum: No registered users and 2 guests