Subject Re: String question
From Mervyn Bick <invalid@invalid.invalid>
Date Thu, 16 Nov 2023 10:27:22 +0200
Newsgroups dbase.getting-started
Attachment(s) transform_string4.wfm

On 2023/11/14 12:19, Mervyn Bick wrote:

> With "proper" SQL it would be quite easy to create a calculated field
> with the comma markers. With .dbf files localSQL isn't up to the job and
> using the OODML method of adding a field to the rowset when the query
> opens requires the code to populate the field to be in a codeblock.  The
> codeblock in this case would be a monster if one could get it to work.
> It's definitely above my pay grade. :-)
Here we go again.  As soon as I say "can't be done" someone, Akshat in
this case, proves me wrong. :-)

I was fixated on inserting commas into the "numeric" strings Charlie is
using.  This is easily done in a function but, even if it is possible,
it would need a monster codeblock as the calculated field's
beforeGetValue event handler.

Akshat uses the dBASE transform() function to change a numeric value in
a field into a formatted string in a calculated field.  Charlie has his
numeric values saved as strings but localSQL certainly has no problem in
converting a string to a numeric value for a calculated field.

q.sql = 'select t.*,CAST(fieldname as INT) as newField from tablename t'

If the values in the field to be formatted contain decimal values then
replace the INT with NUMERIC(10,2)

The codeblock for the newField's beforeGetValue event handler, which
will work with both integers and values with decimals is

{||transform(this.value,'999,999,999.99')}


An example form is attached.

Mervyn.




if file('transform_string4.dbf')
drop table transform_string4
endif

if not file('transform_string4.dbf')
   create table transform_string4  (numStr character(15),nNum int)
   use transform_string4
   generate 30
//   replace all numStr with str(nNum,10,0)  //Saves values with leading spaces
   use
   delete from transform_string4 where nNum = 0  or nNum > 9000000
   update transform_string4 set numStr = cast(nNum as char(10)) //Saves values without leading spaces
   alter table transform_string4 drop nNum
   //Code above is used to create a test table with a field containing
   //integers as strings.  
endif  
** END HEADER -- do not remove this line
//
// Generated on 2023-11-16
//
parameter bModal
local f
f = new transform_string4Form()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class transform_string4Form of FORM
   with (this)
      height = 20.7727
      left = 34.1429
      top = 0.2273
      width = 51.7143
      text = ""
   endwith

   this.TRANSFORM_STRING41 = new QUERY(this)
   with (this.TRANSFORM_STRING41)
      left = 1.0
      top = 18.0
      width = 14.0
      height = 1.0
      sql = 'select t.*,CAST(numstr as INT) numstr1 from transform_string4 t '
      active = true
   endwith

   with (this.TRANSFORM_STRING41.rowset)
      with (fields["numstr1"])
         beforeGetValue = {||transform(this.value,'999,999,999.99')}
      endwith
   endwith

   this.GRID1 = new GRID(this)
   with (this.GRID1)
      dataLink = form.transform_string41.rowset
      columns["COLUMN1"] = new GRIDCOLUMN(form.GRID1)
      with (columns["COLUMN1"])
         dataLink = form.transform_string41.rowset.fields["numstr"]
         editorType = 1        // EntryField
         width = 21.4286
      endwith
      columns["COLUMN2"] = new GRIDCOLUMN(form.GRID1)
      with (columns["COLUMN2"])
         dataLink = form.transform_string41.rowset.fields["numstr1"]
         editorType = 1        // EntryField
         width = 15.7143
      endwith
      with (columns["COLUMN1"].headingControl)
         value = "numstr"
      endwith

      with (columns["COLUMN2"].editorControl)
         function = "J"
      endwith

      with (columns["COLUMN2"].headingControl)
         value = "numstr1"
      endwith

      height = 14.9091
      left = 3.5714
      top = 3.0455
      width = 44.8571
   endwith

   this.TEXTLABEL1 = new TEXTLABEL(this)
   with (this.TEXTLABEL1)
      height = 1.0
      left = 6.7143
      top = 0.6364
      width = 19.8571
      text = "data as saved in table"
   endwith

   this.TEXT1 = new TEXT(this)
   with (this.TEXT1)
      height = 1.5
      left = 28.8571
      top = 0.6364
      width = 18.7143
      text = "Calaculated field right justified in grid"
   endwith

   this.rowset = this.transform_string41.rowset

endclass