Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
-
jalz
- Livecode Opensource Backer
- Posts: 340
- Joined: Fri Sep 12, 2008 11:04 pm
Post
by jalz » Sun Jan 04, 2015 10:18 pm
Hi Guys,
I've got a sql query which populates an array with data. There are certain fields that I want to display as a currency format "0.00" - would be great to include a currency sign, but thats not necessary I just need to have 2 decimal places. Through the dictionary I have found number format command and understand that it can only work if I implement it with a calculation (even a fake one will do). Im trying it out, and I can't seem to get it to work in my array. Anyone tell me what Im missing - Im trying to get the data in tDataA[tCounter]["Label 2"] displaying in a currency format. I need to divide the item 4 by 10000 before displaying it.
Thanks as always
Code: Select all
set the numberformat to "0.00"
put item 2 of tLine into tDataA[tCounter]["Label 22"]
put item 3 of tLine into tDataA[tCounter]["Label 3"]
put item 4 of tLine into tTotal
--put tTotal/10000 into tTotal
divide tTotal by 10000
put tTotal into tDataA[tCounter]["Label 2"]
put item 4 of tLine into tDataA[tCounter]["Label 2"]
put item 5 of tLine into tDataA[tCounter]["Label 4"]
put item 6 of tLine into tDataA[tCounter]["Label 5"]
put item 7 of tLine into tDataA[tCounter]["Label 6"]
put item 8 of tLine into tDataA[tCounter]["Label 23"]
put item 9 of tLine into tDataA[tCounter]["Label 24"]
put item 10 of tLine into tDataA[tCounter]["Label 25"]
put item 11 of tLine into tDataA[tCounter]["Label 7"]
put item 12 of tLine into tDataA[tCounter]["Label 8"]
put item 13 of tLine into tDataA[tCounter]["Label 9"]
put item 14 of tLine into tDataA[tCounter]["Label 13"]
put item 15 of tLine into tDataA[tCounter]["Label 14"]
put item 16 of tLine into tDataA[tCounter]["Label 15"]
put item 17 of tLine into tDataA[tCounter]["Label 16"]
put item 18 of tLine into tDataA[tCounter]["Label 17"]
put item 19 of tLine into tDataA[tCounter]["Label 18"]
put item 20 of tLine into tDataA[tCounter]["Label 19"]
put item 21 of tLine into tDataA[tCounter]["Label 20"]
put item 22 of tLine into tDataA[tCounter]["Label 21"]
-- add the currency format
--set the numberformat to "0.00"
--Divide tDataA[tCounter]["Label 2"] by 10000
-
Klaus
- Posts: 13824
- Joined: Sat Apr 08, 2006 8:41 am
- Location: Germany
-
Contact:
Post
by Klaus » Mon Jan 05, 2015 12:16 am
Hi Jalz,
the FORMAT function should work here:
...
## Will force a floating number with 2 decimal places:
put format("%.2f",item 4 of tLine) into tDataA[tCounter]["Label 2"]
...
Or add a currency symbol right here:
...
put "$" && format("%.2f",item 4 of tLine) into tDataA[tCounter]["Label 2"]
## You get the picture
...
Best
Klaus
-
jalz
- Livecode Opensource Backer
- Posts: 340
- Joined: Fri Sep 12, 2008 11:04 pm
Post
by jalz » Mon Jan 05, 2015 12:27 am
Thanks Klaus as always,
Your line of code formatted my results with with the two decimal places.
Jalz
-
phaworth
- Posts: 592
- Joined: Thu Jun 11, 2009 9:51 pm
Post
by phaworth » Mon Jan 05, 2015 8:13 pm
You could also have the number formatted for you in your SQL SELECT statement:
SELECT '$' || printf("%.2f",cast(numbercolumn as "REAL")/10000)
printf is the way you do it SQLite, probably different in other SQL implementations. Also assumes numbercolumn is not already in REAL format, if so just leave out the cast function.
I'm a strong believer in making your database do as much work as possible
Pete
-
tellboy
- Livecode Opensource Backer
- Posts: 50
- Joined: Wed Mar 06, 2013 1:31 pm
Post
by tellboy » Mon Jan 05, 2015 8:40 pm
Hi,
I am interested in an easy way to format currency.
Klaus wrote:put "$" && format("%.2f",item 4 of tLine) into tDataA[tCounter]["Label 2"]
The normal way of displaying currency here in the UK is:
£100.00
-£100.00
How can you format currency with the format command for say:
-£10,000.00 picking up the correct position for the negative sign and also incorporating a comma separating thousands.
I've looked at the dictionary entry for format and there does not appear to be a reference to currency.
Thanks
Terry
-
MaxV
- Posts: 1579
- Joined: Tue May 28, 2013 2:20 pm
- Location: Italy
-
Contact:
Post
by MaxV » Wed Jan 07, 2015 12:00 pm
I use this code (for euro currency):
########CODE#######
function europenum mynum
put round(mynum,2) into mynum
set itemdelimiter to "."
put the first item of mynum into integ
put the second item of mynum into decimal
if decimal is not empty then
#this is optiona for money values
if length(decimal) = 1 then
put 0 after decimal
end if
##
put "," before decimal
else
put ",00" into decimal #money values
end if
put 1 into n
put reversed(integ) into integ
repeat for each char numb in integ
if n = 4 then
put "'" before decimal
put 1 into n
end if
put numb before decimal
add 1 to n
end repeat
#check negative values
if (char 1 of decimal is "-" ) and (char 2 of decimal is "'" ) then
delete char 2 of decimal
end if
return decimal
end europenum
#####END OF CODE#####
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
-
magice
- Posts: 457
- Joined: Wed Mar 18, 2009 12:57 am
Post
by magice » Wed Jan 07, 2015 5:40 pm
This is what I used in a point of sale system I wrote for work. I think it is similar to what MaxV posted. It might help or at least give inspiration.
Code: Select all
function formatMoney tAmount
put format("%.2f",tAmount) into tAmount
split tAmount by "."
put the number of characters in tAmount[1] into tDigits
if tDigits >3
then
put (tDigits mod 3) into tStart
put 0 into tCount
put "," after character tStart of tAmount[1]
repeat with i = (tStart+1) to tDigits
if tCount is 3
then
put "," after character i of tAmount[1]
put 0 into tCount
else
add 1 to tCount
end if
end repeat
end if
combine tAmount using "."
if the first character of tAmount is "," then delete the first character of tAmount
return tAmount
end formatMoney
-
magice
- Posts: 457
- Joined: Wed Mar 18, 2009 12:57 am
Post
by magice » Wed Jan 07, 2015 6:19 pm
actually I just noticed a way to shorten it.
Code: Select all
function formatMoney tAmount
put format("%.2f",tAmount) into tAmount
split tAmount by "."
put the number of characters in tAmount[1] into tDigits
if tDigits >3
then
put (tDigits mod 3) into tStart
put "," after character tStart of tAmount[1]
repeat with i = (tStart+1) to tDigits
if ((i-tStart) mod 4) is 0 then put "," after character i of tAmount[1]
end repeat
end if
combine tAmount using "."
if the first character of tAmount is "," then delete the first character of tAmount
return tAmount
end formatMoney
Thank you for making me look at it again.
-
tellboy
- Livecode Opensource Backer
- Posts: 50
- Joined: Wed Mar 06, 2013 1:31 pm
Post
by tellboy » Wed Jan 07, 2015 8:19 pm
magice wrote:Thank you for making me look at it again.
This does not work correctly for negative numbers for example:
-116.79 returns -,116.79
All the best
Terry
-
magice
- Posts: 457
- Joined: Wed Mar 18, 2009 12:57 am
Post
by magice » Wed Jan 07, 2015 8:36 pm
tellboy wrote:magice wrote:Thank you for making me look at it again.
This does not work correctly for negative numbers for example:
-116.79 returns -,116.79
All the best
Terry
no it won't. It was never written to do that. however conversion should be simple just put in an if statement at the beginning that removes the first character if it is > 0. Then if it is n0t > 0 put "-£" into a variable if it is not put just "£" into that variable then append the return something like this
Code: Select all
function formatMoney tAmount
if tAmount <0
then
delete the first character of tAmount
put "-£" into tPre
else
put "£" into tPre
end if
put format("%.2f",tAmount) into tAmount
split tAmount by "."
put the number of characters in tAmount[1] into tDigits
if tDigits >3
then
put (tDigits mod 3) into tStart
put "," after character tStart of tAmount[1]
repeat with i = (tStart+1) to tDigits
if ((i-tStart) mod 4) is 0 then put "," after character i of tAmount[1]
end repeat
end if
combine tAmount using "."
if the first character of tAmount is "," then delete the first character of tAmount
return (tPre&tAmount)
end formatMoney
I didn't test it, but you should get the idea of what I am trying to do and fix the syntax if I made a typo.
Last edited by
magice on Wed Jan 07, 2015 8:48 pm, edited 1 time in total.
-
tellboy
- Livecode Opensource Backer
- Posts: 50
- Joined: Wed Mar 06, 2013 1:31 pm
Post
by tellboy » Wed Jan 07, 2015 8:47 pm
magice wrote:no it won't. It was never written to do that. however conversion should be simple....
As you suggested I modified your handler and it works, thanks for your assistance, it is greatly appreciated.
Here is the modified handler for others who may come across this thread.
Code: Select all
function formatCurrency tAmount
put format("%.2f",tAmount) into tAmount
if tAmount < 0 then
delete the first character of tAmount
put "-£" into tCurrencyPrefix
else
put "£" into tCurrencyPrefix
end if
split tAmount by "."
put the number of characters in tAmount[1] into tDigits
if tDigits > 3 then
put (tDigits mod 3) into tStart
put "," after character tStart of tAmount[1]
repeat with i = (tStart+1) to tDigits
if ((i-tStart) mod 4) is 0 then
put "," after character i of tAmount[1]
end if
end repeat
if character 2 of tAmount[1] = "," and tAmount[1] < 0 then
delete character 2 of tAmount[1]
end if
end if
combine tAmount using "."
if the first character of tAmount is "," then
delete the first character of tAmount
end if
return tCurrencyPrefix & tAmount
end formatCurrency
I suppose that for those that need it you could modify the function to pass in the currency type as "£" or "$" for example.
All the best and thanks again.
Terry
-
magice
- Posts: 457
- Joined: Wed Mar 18, 2009 12:57 am
Post
by magice » Wed Jan 07, 2015 9:11 pm
Good now your homework assignment is to read through that until you understand exactly what it's doing. Look extra hard at the mod operator. With it, you can create a closed loop of numbers. This can be invaluable when making decisions based on < or > of more than 2 values, when you want the lowest value to circle back and beat the highest. Think about the rock, paper, scissors game. With "mod" you can create a numerical equivalent.
-
Klaus
- Posts: 13824
- Joined: Sat Apr 08, 2006 8:41 am
- Location: Germany
-
Contact:
Post
by Klaus » Wed Jan 07, 2015 10:23 pm
Hi Terry,
a little timesaving hint:
You can abbreviate character to char and also use numbers as a counter like this:
...
delete char 1 of tAmount
## And we can also count backwards!
## tooo loooong:
## delete the last char of tAmount
## nicely short:
delete char -1 of tAmount
...
## And the plural
put the num of chars of tAmount[1] into tDigits
...
Also:
fld = field
btn = button
cd = card
grc = graphic
rect = rectangle
Lazy moi
But:
player = player
stack = stack
Best
Klaus