| Subject | Re: String question |
| From | Mervyn Bick <invalid@invalid.invalid> |
| Date | Tue, 14 Nov 2023 12:19:55 +0200 |
| Newsgroups | dbase.getting-started |
| Attachment(s) | transform_string.wfm, transform_string1.wfm, INSERT_K_MARKER.prg |
On 2023/11/12 16:00, Charlie wrote: > Say you have a string such as a = "1000" > I am using numbers in string form for a specific reason. > > I would like to add a comma like this... "1,000" > > The data is in a form grid. I was thinking I could use the function or picture properties of the column to do this but haven't been able to figure that out. Is that possible or is there some other way to do this? If one assigns columns in a grid's columns property one can access the function and picture properties of the columnentryfield objects in the grid. Unfortunately this doesn't help in this case as the functions available won't do what you need to the existing "numeric" string. 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. :-) The easiest option is to add a new field to the table and populate it with strings formatted with the comma thousands markers inserted. Rather than add a permanent field, which would need to be edited if the main field is ever changed, I would create a temporary field for the form at runtime and drop the temporary field when the form is closed. Characters are normally saved without leading spaces i.e left justified and numbers are normally right justified. The little example program attached will deal with your "numeric" strings whether they have been saved with leading or trailing spaces. I've assumed that you have only used integers but the little program to insert commas will happily deal with strings that include a decimal point. The ideal place to add and populate the new field is in a form's header section. Unfortunately dBASE has problems with RETURN commands in the header so the function to add the commas needs to be moved out into a separate program. Numeric values are normally right justified. In one example form I've used leading spaces to do this. While it means that one doesn't have to specify columns in the grid's columns property, it does mean that one needs to use a monospaced font. In the second form, although the function property of the columnentryfield objects in the grid can't be used to insert the commas, once a field has been created that includes the commas in the string the J function can be used to right justify the string. In this case a proportional font can be used. Mervyn | |
clear if file('transform_string.dbf') drop table transform_string endif if not file('transform_string.dbf') create table transform_string (numStr character(15),numStr1 character(15),nNum int) use transform_string generate 30 replace all numStr with str(nNum,10,0) //Saves value with leading spaces use delete from transform_string where nNum = 0 or nNum > 900000 update transform_string set numStr1 = cast(nNum as char(10)) //Saves values without leading spaces alter table transform_string drop nNum //Code above is used to create a test table with a field containing //integers as strings in two fields. One field with leading spaces and //the other with trailing spaces endif //The code below adds two new character fields to the table and then //populates the new fields from the original two fields with commas //inserted to mark thousands. set procedure to insert_k_marker.prg //Only necessary if .prg is not in the same folder as the .wfm file //Add a path or sourcecode alias try alter table transform_string drop NewNumStr alter table transform_string drop NewNumStr1 catch( exception e ) endtry alter table transform_string add NewNumStr char(12) //extra spaces to allow for comas alter table transform_string add NewNumStr1 char(12) use transform_string scan cStr = insert_k_marker(numStr) //Process field with leading spaces replace NewNumStr with right(space(12)+cStr,12) cStr = insert_k_marker(numStr1) //Process field with trailing spaces replace NewNumStr1 with right(space(12)+cStr,12) endscan use close procedure insert_k_marker.prg ** END HEADER -- do not remove this line // // Generated on 2023-11-14 // parameter bModal local f f = new transform_stringForm() if (bModal) f.mdi = false // ensure not MDI f.readModal() else f.open() endif class transform_stringForm of FORM with (this) onClose = class::FORM_ONCLOSE height = 22.8636 left = 0.1429 top = 0.0455 width = 95.0 text = "" endwith this.TRANSFORM_STRING1 = new QUERY(this) with (this.TRANSFORM_STRING1) left = 33.0 width = 14.0 height = 1.0 sql = 'select * from "transform_string.DBF"' active = true endwith this.GRID1 = new GRID(this) with (this.GRID1) fontName = "Consolas" dataLink = form.transform_string1.rowset height = 16.0455 left = 6.8571 top = 3.5 width = 81.1429 endwith this.TEXT1 = new TEXT(this) with (this.TEXT1) height = 2.2727 left = 54.0 top = 0.6364 width = 34.0 text = "Uses leading spaces to display the string right justified. A monospaced font must be used." endwith this.rowset = this.transform_string1.rowset function form_onClose() form.transform_string1.active = false alter table transform_string drop NewNumStr //Remove temp field alter table transform_string drop NewNumStr1 //Remove temp field return endclass | |
clear if file('transform_string1.dbf') drop table transform_string1 endif if not file('transform_string1.dbf') create table transform_string1 (numStr character(15),nNum int) use transform_string1 generate 30 use delete from transform_string1 where nNum = 0 or nNum > 900000 update transform_string1 set numStr = cast(nNum as char(10)) //Saves values without leading spaces alter table transform_string1 drop nNum //Code above is used to create a test table with a field containing //integers as strings withut leading spaces. endif //The code below adds a new character fields to the table and then //populates the new field from the original field with commas //inserted to mark thousands. set procedure to insert_k_marker.prg //Only necessary if .prg is not in the same folder as the .wfm file //Add a path or sourcecode alias try alter table transform_string1 drop NewNumStr catch( exception e ) endtry alter table transform_string1 add NewNumStr char(12) //extra spaces to allow for comas use transform_string1 scan cStr = insert_k_marker(numStr) //Process field with leading spaces replace NewNumStr with cStr endscan use close procedure insert_k_marker.prg ** END HEADER -- do not remove this line // // Generated on 2023-11-14 // parameter bModal local f f = new transform_string1Form() if (bModal) f.mdi = false // ensure not MDI f.readModal() else f.open() endif class transform_string1Form of FORM with (this) onClose = class::FORM_ONCLOSE height = 21.5455 left =10.1429 top = 0.0455 width = 54.2857 text = "" endwith this.TRANSFORM_STRING11 = new QUERY(this) with (this.TRANSFORM_STRING11) width = 14.0 height = 1.0 sql = 'select * from "transform_string1.DBF"' active = true endwith this.GRID1 = new GRID(this) with (this.GRID1) dataLink = form.transform_string11.rowset columns["COLUMN1"] = new GRIDCOLUMN(form.GRID1) with (columns["COLUMN1"]) dataLink = form.transform_string11.rowset.fields["numstr"] editorType = 1 // EntryField width = 21.4286 endwith columns["COLUMN2"] = new GRIDCOLUMN(form.GRID1) with (columns["COLUMN2"]) dataLink = form.transform_string11.rowset.fields["newnumstr"] editorType = 1 // EntryField width = 17.1429 endwith with (columns["COLUMN1"].headingControl) value = "numStr" endwith with (columns["COLUMN2"].editorControl) function = "J" endwith with (columns["COLUMN2"].headingControl) value = "NewNumStr" endwith height = 14.6818 left = 4.0 top = 5.1818 width = 44.7143 endwith this.TEXT1 = new TEXT(this) with (this.TEXT1) height = 3.7273 left = 27.2857 top = 0.4091 width = 23.7143 text = "Uses fuction property of columnentryfield object in grid to right justify the entry. A proporionally spaced font can be used." endwith this.rowset = this.transform_string11.rowset function form_onClose() form.transform_string11.active = false alter table transform_string1 drop NewNumStr //Remove temp field return endclass | |
function insert_k_marker(numStr) local cStr if argcount() = 0 msgbox('A string must be passed as a parameter') return endif cStr = ltrim(trim(numStr)) //Remove leading and trailing spaces p = 0 if at('.',cStr) > 0 x = at('.',cStr)-1 else x = len(cStr) endif for n = x to 1 step -1 p ++ if mod(p,3) = 0 and asc(substr(cStr,n,1))<> 32 and n<>1 cStr = stuff(cStr,n,0,',') endif next return cStr | |