numberformat in a sql result

[jalz]

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]

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]

Thanks Klaus as always,

Your line of code formatted my results with with the two decimal places.

Jalz

[phaworth]

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]

Hi,

I am interested in an easy way to format currency.

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]

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#####

[magice]

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]

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]

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]

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.

[tellboy]

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]

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]

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