Format Arrays

[Bob Sneidar]

Hi all. 

Ever queried or updated an SQL database with float column types, and noted to your dismay that float values truncate leading AND trailing values? So now when you want to put that data into a datagrid, 800.00 becomes 800, 495.60 becomes 495.6. Wouldn't it be great if there was a command that went through all those arrays in the datagrid data and formatted the float values the way you want? Well now you can! 

Right now it only calls formatMoney (which I have included and because that is what I needed it for) but add your own formatting in the switch statement and call your own functions. A great example would be retrieving sql datetime formats and converting them to another livecode date format before populating your datagrid (or anything else really). 

aArray is obviously an array, but can be a single array or an array of arrays (but not mixed). pFormat is how you want to format the values, pKeyList is which array keys you want to format, and pType is the kind of array you are passing (record or data). 

Anyone who wants to tack on their own formatting can chime in. Actually, this would be a great open source github project! 

on formatArray @aArray, pFormat, pKeyList, pType
   if pType is "record" then 
      put aArray into aArrayRecords [1]
   else -- pType is "data" (array of arrays)
      put aArray into aArrayRecords
   end if
   
   put the keys of aArrayRecords into tArrayKeys
   sort lines of tArrayKeys numeric ascending
   
   repeat for each line tRecord in tArrayKeys
      put aArrayRecords [tRecord] into aRecord
      put  the keys of aRecord into tRecordKeys
      sort lines of tRecordKeys numeric ascending
      put (char 1 of tRecordKeys is "@") into IsRecord
      
      repeat for each item tKey in pKeyList
         put aRecord [tKey] into tValue
         
         switch pFormat
            case "money"
               put formatMoney(tValue, false) into tValue
               break
         end switch
         
         if isRecord then
            sqlrecord_set aRecord, tKey, tValue
         else
            put tValue into aRecord [tKey]
         end if
      end repeat
      
      put aRecord into aArrayRecords [tRecord]
   end repeat   
   
   
   -- convert back to passed array
   if pType is "record" then 
      put aArrayRecords [1] into aArray 
   else
      put aArrayRecords into aArray
   end if
end formatArray

function formatMoney theValue, tUseSymbols
   -- strip out monetary symbols
   replace "$" with empty in theValue
   replace "¢" with empty in theValue
   
   switch 
      case theValue = 0 or theValue >= 1
         put "$%1.2f" into tFormatString
         if tUseSymbols is false then replace "$" with empty in tFormatString
         put format(tFormatString, theValue) into theValue
         break
      case theValue >= 0
         put "%#.2f¢" into tFormatString
         if tUseSymbols is false then replace "¢" with empty in tFormatString
         put format(tFormatString, theValue) into theValue
         break
      case theValue <= -1 -- why am I doing this??
         replace "-" WITH empty in theValue
         put format("($%1.2f)", theValue) into theValue
         break
      case theValue < 0 -- why am I doing this??
         replace "-" WITH empty in theValue
         put format("(%#.2f¢)", theValue) into theValue
         break
   end switch
   
   return theValue
end formatMoney