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

expresion in recuring invoices

Report design issues and solutions.
Post Reply
Piston Broke
User
User
Posts: 19
Joined: 05 Oct 2011, 12:02
Sage Version: v2009 15 UK/Europe/Africa

expresion in recuring invoices

Post by Piston Broke » 06 Jan 2012, 10:29

Don't know if this is possible but here goes

I have to send several service invoices once a month with the same line on each "services for Jan 2012", now if I use recurring invoices it will always say Jan 2012 Can I add an expression so the month is always correct as the invoice is always done the month before it would be( current month +1). I have thought of using report designer so the actual service line is just "services for" and report designer puts in the month and year but can't work out how to make sage print the next month so when I do the invoices at the end of Jan the invoice comes out with Feb 2012

Thanks

Alain

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

Re: expresion in recuring invoices

Post by brucedenney » 10 Jan 2012, 19:23

I think this will work


I get the invoice date and extract the year

YearFromDate(INVOICE.INVOICE_DATE)

and the month

MonthFromDate(INVOICE.INVOICE_DATE)

make a date the first day of the month / year of the invoice.

CreateDateTime(YearFromDate(INVOICE.INVOICE_DATE),MonthFromDate(INVOICE.INVOICE_DATE),1)

I then add 31 days making it one of the first few days of the next month

CreateDateTime(YearFromDate(INVOICE.INVOICE_DATE),MonthFromDate(INVOICE.INVOICE_DATE),1)+31

format to just give me month and year

DateTimeToFormattedString(CreateDateTime(YearFromDate(INVOICE.INVOICE_DATE),MonthFromDate(INVOICE.INVOICE_DATE),1)+31
, "MMM yyyy")


Append that to the fixed text.

"services for "+DateTimeToFormattedString(CreateDateTime(YearFromDate(INVOICE.INVOICE_DATE),MonthFromDate(INVOICE.INVOICE_DATE),1)+31
, "MMM yyyy")
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.

Piston Broke
User
User
Posts: 19
Joined: 05 Oct 2011, 12:02
Sage Version: v2009 15 UK/Europe/Africa

Re: expresion in recuring invoices

Post by Piston Broke » 17 Jan 2012, 12:58

Sorry it took so long to get back to you but thanks

I had also asked the same question on SageForum.co.uk and some guy came up with this

Code: Select all

INVOICE.DESCRIPTION + " " + DateTimeToFormattedString(CreateDateTime(YearFromDate(Now()), MonthFromDate(Now()), 1) + DaysInMonth(YearFromDate(Now()), MonthFromDate(Now())), "MMM yyyy")
And it works exactly how I wanted it to

Thanks again for the help

Alain

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

Re: expresion in recuring invoices

Post by brucedenney » 17 Jan 2012, 14:34

They are subtly different.

The one you suggested uses todays date to work out the text, so if you reprint and old invoice it will have the wrong period on it.

My version uses the invoice date so even when re-printing an old invoice will put the right text on it.
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.

Piston Broke
User
User
Posts: 19
Joined: 05 Oct 2011, 12:02
Sage Version: v2009 15 UK/Europe/Africa

Re: expresion in recuring invoices

Post by Piston Broke » 18 Jan 2012, 11:50

Thanks

Never thought of the reprint issue so if I use

Code: Select all

services for "+DateTimeToFormattedString(CreateDateTime(YearFromDate(INVOICE.INVOICE_DATE),MonthFromDate(INVOICE.INVOICE_DATE),1)+31
, "MMM yyyy")
But then remove the "services for" and replace it with INVOICE.DESCRIPTION + " " so I can alter the preceding string using the description I type on the invoice screen as mostly it's "services for" but some times it's "rent for"

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

Re: expresion in recuring invoices

Post by brucedenney » 18 Jan 2012, 13:54

This is what you want

Code: Select all

INVOICE.DESCRIPTION + " " + DateTimeToFormattedString(CreateDateTime(YearFromDate(INVOICE.INVOICE_DATE),MonthFromDate(INVOICE.INVOICE_DATE),1)+31
, "MMM yyyy")
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.

Piston Broke
User
User
Posts: 19
Joined: 05 Oct 2011, 12:02
Sage Version: v2009 15 UK/Europe/Africa

Re: expresion in recuring invoices

Post by Piston Broke » 18 Jan 2012, 14:19

Cheers Bruce

You're a star

Is there any way of getting the full invoice text to print on the statement, now it just prints good/services using "AUDIT_SPLIT.STMT_TEXT" I would like it to print the line of text on invoice including the date

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

Re: expresion in recuring invoices

Post by brucedenney » 19 Jan 2012, 19:21

Full text, would mean showing each item and splitting each invoice into all its parts, I can't think of a reason why it could not be done.
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.

Piston Broke
User
User
Posts: 19
Joined: 05 Oct 2011, 12:02
Sage Version: v2009 15 UK/Europe/Africa

Re: expresion in recuring invoices

Post by Piston Broke » 20 Jan 2012, 09:41

Sorry didn't explain properly

At the moment every statement just puts goods/services in details I would like it to put in details the actual invoice line like " rent for Jan 2012 "

I'm using a standard sage statement report and in report designer under details the expression is "AUDIT_SPLIT.STMT_TEXT" but I can't find another option I can use

The statement list is all Service invoices using V2009

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

Re: expresion in recuring invoices

Post by brucedenney » 20 Jan 2012, 14:17

AUDIT_HEADER.DESCRIPTION perhaps?
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.

Piston Broke
User
User
Posts: 19
Joined: 05 Oct 2011, 12:02
Sage Version: v2009 15 UK/Europe/Africa

Re: expresion in recuring invoices

Post by Piston Broke » 27 Jan 2012, 10:03

Thanks Bruce you've done it again

That's me sorted for now

Really appreciate your help

Post Reply

Who is online

Users browsing this forum: No registered users and 15 guests