Hi all,
Hoping for some guidance and any guidance would be much appreciated.
I need to add transaction number to a new invoice template as we tend to use this more than the invoice number, but i am struggling to do so. Having found transaction number in a report i can see it is labelled audit_header.tran_number, however i don't seem to be able to pick this from within my invoice template.
I am assuming this is due their being no join to the audit_header table when i click on 'joins', having played about trying to add one using the graphical editor and the raw editor i seem to be struggling.
Is there an easy way of adding this join to let me add the transaction number?
Thanks!
Adding Transaction Number to Invoice Template
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Adding Transaction Number to Invoice Template
At the point when the invoice is printed it may not have been updated to the ledger, so as a result it would not be possible to print the audit trail transaction number as the audit trail transaction might not exist.
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: Adding Transaction Number to Invoice Template
Thanks for taking the time to respond.
So do you think if you made sure you posted to ledgers first their may be a way of adding transaction number to an invoice?
So do you think if you made sure you posted to ledgers first their may be a way of adding transaction number to an invoice?
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Adding Transaction Number to Invoice Template
You can!
You need to join the Invoice Table to the Audit_Header table
So the INVOICE.INVOICE_NUMBER need to match the AUDIT_HEADER.INV_REF
The issue is that one is text and the other is a number so they are incompatible for joining.
Sage have provided a way around this there is a field called INV_REF_NUMERIC which you can join on.
You also want to make the join a Parent Outer so that should the matching transaction not exist in the Audit Trail it will still return data.
There is still a minor flaw, what if there is another audit trail with that same number, a purchase invoice for example?
You could make the join more complicated, being both the account reference and the invoice reference, but this still has flaws a supplier account with the same account reference and same invoice reference would match. Also a sales ledger credit note on the same account with the same ref would match.
So I added a filter to limit it to SI transactions (or blank transactions should the invoice not have been posted to the ledger when printed)
I attach a sample of the above.
Have a look at each part and implement it in your own layout.
I hope this helps you.
You need to join the Invoice Table to the Audit_Header table
So the INVOICE.INVOICE_NUMBER need to match the AUDIT_HEADER.INV_REF
The issue is that one is text and the other is a number so they are incompatible for joining.
Sage have provided a way around this there is a field called INV_REF_NUMERIC which you can join on.
You also want to make the join a Parent Outer so that should the matching transaction not exist in the Audit Trail it will still return data.
There is still a minor flaw, what if there is another audit trail with that same number, a purchase invoice for example?
You could make the join more complicated, being both the account reference and the invoice reference, but this still has flaws a supplier account with the same account reference and same invoice reference would match. Also a sales ledger credit note on the same account with the same ref would match.
So I added a filter to limit it to SI transactions (or blank transactions should the invoice not have been posted to the ledger when printed)
I attach a sample of the above.
Have a look at each part and implement it in your own layout.
I hope this helps you.
Login to access 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.
Re: Adding Transaction Number to Invoice Template
Thanks fr this - i will give it a go!
Who is online
Users browsing this forum: Google [Bot] and 5 guests