07-03-2012 10:19 AM
I've created a report which calculate VAT (Tax) and the total cost of the product(s) including VAT. The problem I have is that if the VAT comes out to say £11.906 I then formats the results to two decimal places the result becomes £11.91. I want it to show £11.90.
I use the following expressions in the stages of the calculations / report
format (Product1Price1* (Product1VAT1/100),"currency") This converts it to currency
formatnumber(Product1Price1* (Product1VAT1/100),2) This gives me the VAT PRICE
format(sum(Product1Price1+ (Product1Price1* (Product1VAT1/100))),"currency") This gives me the total price inc VAT as currency
formatnumber(sum(Product1Price1+ (Product1Price1* (Product1VAT1/100))),2) This gives me the total price inc VAT
format(sum( Custom10 + Custom11 + Custom31 + Custom32 + Custom33 + Custom34 + Custom35 + Custom36),"currency")
This gives me the grand total of all the products (there can be up to eight)
The reports works, but as I stated at the top I want the VAT to round down in all cases.
I've come across a VB command called Math.floor but I can't get it to work
Help !! Does anyone have any thoughts on how to do this? What is the correct syntax to round down in all cases?
Thanks in advance for any help / support
07-03-2012 10:24 AM
Are you doing this as expression statements in the field or as a VB script?
07-03-2012 10:47 AM
Wow. I am afraid I would suggest you buy Sage Accounts and link the products through Opportunities (then you also won't be limited to 8) and then convert an Opp to an Invoice? (Assuming you are running ACT! Premium 2012?)
07-03-2012 11:58 AM
All I need to do is find out the syntax for rounding down to two decimal places – everything else is fine
Below is the tab from the contact screen that is used to input the prices as the customer did not want to use the opportunity screens
Invoice below (created by report)
Note the rounding £238.10 x 5% = £11.905 which comes out as £11.91. I want to always round down, so that it comes out as £11.90
07-03-2012 12:28 PM
What you want can be done but not the way you're using. The field expressions are very limited and you need a more complex script. Right click on the section or the report and choose Edit Program Scripts to open the script editor.
There is a third party book with comprehensive training information on the ACT! reports, here is the link.
Full disclusure I wrote the reports chapters of the book.
07-04-2012 09:43 AM
Hi Roy and thanks for the info.. I suspect I will end up buying the book so that I have more info for future projects as the expression commands are very limited.
I have however managed to resolve the rounding problem. I went back to basics and instead of doing all the calculations in the reports section, I just pick up the totals from the add on facility called “automatic Field calculations” by exponenciel. This add-on had already been installed to do various field calculations, so it was easy to pick up the correct data.
I’ll order the book when I get my next ACT! Project.