Source language: Translate to:

Decimal places in a sum result

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Decimal places in a sum result

Postby stagmannz » Thu Sep 06, 2012 1:41 pm

Ok, im probably going to look stupid here but Im struggling to get a calculated field to add 2 decimal places...

So, here's the situation...
A basic orders table with a linked table holding the order details
A user can add items to the order by clicking 'add to order' this adds a record to the order_details table stamping the Order ID so its linked... anyway that all works.

The issue now is when I exec a dbpsum on the totals field i get a result minus the decimal places or if there is a decimal it only goes 1 place
i.e

'$123' or '$123.4'

I need:

$123.00 or $123.40

Obviously this will not work when sending out invoices with a number like that.

Now... I have tried to change the field type to 'Currency', String', 'integer', 'bigint' but seems to either not write to field (integer) or do the same thing on the other fields.

Anyone have any thought on this... my next cunning plan was to change it to a string and fieldtovar and calculate an array... but that's untidy and dont really want to do that.

Cheers
stagmannz
 
Posts: 15
Joined: Tue Feb 22, 2011 11:34 pm

Postby smokinbanger » Thu Sep 06, 2012 2:29 pm

There's two options you cam check out depending in the situation. Proving more information could help.

Option 1:
Code: Select all
DefineVar "[VariableName]" "Currency" "Global" "Optional Value To Store In Variable"


Option 2: (works well with grid views)
Code: Select all
dbpSetFieldProperties "DatabaseID" "TableName" "FieldName" "Options"
Read the help file for this one. There's LOTS of options!
smokinbanger
 
Posts: 198
Joined: Mon Jan 16, 2012 9:53 am
Location: United States

Postby BRobinsonS » Thu Sep 06, 2012 4:07 pm

The issue now is when I exec a dbpsum on the totals field i get a result minus the decimal places or if there is a decimal it only goes 1 place
i.e

'$123' or '$123.4'

I need:

$123.00 or $123.40


Struggled with this myself. If you look at the Report file (Invoice) for Order Entry (demo program), you will see the formatting used to show 2 decimal places.
[Invoices.SubTotal|[Config.CurrencySymbol]#,#.00]

Not sure if it works on Calculated fields but works for field on Invoice i.e. subtotal.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Postby smokinbanger » Thu Sep 06, 2012 7:44 pm

BRobinsonS wrote:If you look at the Report file (Invoice) for Order Entry (demo program), you will see the formatting used to show 2 decimal places.
[Invoices.SubTotal|[Config.CurrencySymbol]#,#.00]

Not sure if it works on Calculated fields but works for field on Invoice i.e. subtotal.
Unfortunately, that only works in forms created with the Report Designer.
smokinbanger
 
Posts: 198
Joined: Mon Jan 16, 2012 9:53 am
Location: United States

Postby BRobinsonS » Thu Sep 06, 2012 8:19 pm

Unfortunately, that only works in forms created with the Report Designer.

Message earlier
Obviously this will not work when sending out invoices with a number like that.


How are you sending out Invoices? Are they not created using Report Designer?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Postby smokinbanger » Thu Sep 06, 2012 8:30 pm

BRobinsonS wrote:
Unfortunately, that only works in forms created with the Report Designer.

Message earlier
Obviously this will not work when sending out invoices with a number like that.


How are you sending out Invoices? Are they not created using Report Designer?
Good Point. However...
stagmannz wrote:The issue now is when I exec a dbpsum on the totals field i get a result minus the decimal places or if there is a decimal it only goes 1 place
If using the Report Designer for invoices there is the Formula Tool that can do the job rather than dbpSum:
Code: Select all
SUM( [Orders.TotalPrice] )
smokinbanger
 
Posts: 198
Joined: Mon Jan 16, 2012 9:53 am
Location: United States

Postby stagmannz » Thu Sep 06, 2012 10:02 pm

How are you sending out Invoices? Are they not created using Report Designer?


haha, down here in NZ we still sometimes send physical invoices via mail! Kinda meant 'actually sending out an invoice to a customer' with no cents on it.

Anyway, will give some of these ideas a go soon, been busy on other things today.

The report will be the decider at the end of the day so i suppose as long as the report can display it then thats a win... Still... frustrating
stagmannz
 
Posts: 15
Joined: Tue Feb 22, 2011 11:34 pm

Postby Gaev » Fri Sep 07, 2012 4:01 am

stagmannz:

How about ...
Code: Select all
dbpSum "yourDB" "yourTable" "yourField" "[TotalValue]"
Math "[TotalValue]*1" "2" "[TotalValue]"
... and if a dollar sign is needed ...
SetVar "[TotalValue]" "!$[TotalValue]"
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Wrangler » Fri Sep 07, 2012 7:46 am

Wouldn't dbpsetfieldproperties work here?
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby stagmannz » Sun Sep 09, 2012 12:17 am

Thanks guys, I tried the field mask
Code: Select all
 dbpSetFieldProperties "DATABASE" "ORDERS" "TOTAL" "DisplayFormat=.00"
and that worked a treat!
I havent designed the invoice yet (hmm, anyone got a good invoice report template??) but will try the ideas posted by smokinbanger and BRobinsonS.

Thanks guys, got that one sorted!
stagmannz
 
Posts: 15
Joined: Tue Feb 22, 2011 11:34 pm


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest

cron