Subject Re: Change Upper Case From roy price <royprice@royprice.name> Date Sat, 01 May 2021 16:57:07 -0400 Newsgroups dbase.getting-started Gaetano D. Wrote: > On 1/05/2021 10:38, roy price wrote: > > I am using the gridcolumnindexchange.cc on my form, which seems to work well. > > However the field containing text descriptions sorts words starting with upper case letter as different to those starting with a lower case letter. > > The first words all start with an upper case letter, but subsequent words in the field may have either. (poor input standard) > > Is there any program that would go through the records and change all words to always start with an upper case letter. > > I hope to avoid the laborious editing that would make for consistency. > > (obviously I would prefer not to try to modify the gridcolumnindexchange.cc program to use the UPPER() function in the index.) But that may be the way to go. > > Regards > > Roy Price > > I had a quick look at the code and it seems gridcolumnindexchange.cc > does not create indexes, it just checks if an index exists for that > column heading (the index name must be the field name) and activates > that index: > > cField = aColumns[ nColumn, 2 ] // fieldName > >   try >           oRowset.indexName := cField > > If you want the lowest effort solution, just change the index to using > an expression e.g. upper(<fieldName>) in the table itself. (it sounds > like you know how to do this, but just in case: open the table in design > mode, then open the menu "Structure-Manage Indexes - New - specify with > expression, and enter "upper(fieldName)" as the expression and make sure > you set the name of the index to the same name as the field you are > indexing on else gridcolumnindexchange.cc won't find it) > > If you want to standardize the data and it only involves changing the > case to Uppercase for the first character of a character field, it's > quite easy to do (of course, take a backup copy of the table first...). > You would also need to take care of the data entry side else you will > need to run this script before any data is read into the grid, which is > not ideal. I have included a try/catch in case there rowset is read-only > or in case there is a primary index that could cause primary key > violations - the code would exit in that event. > > q=new query() > > q.sql = "select myField from myTable" > > q.active=true > > q.rowset.first() > > do while not q.rowset.endofset > >      q.rowset.fields["myField"].value = > upper(substr(q.rowset.fields["myField"].value,1,1))+substr(q.rowset.fields["myField"].value,2,len(q.rowset.fields["myField"].value)-1) > >      try > >          q.rowset.next() //the navigation triggers an implicit save, > hence the try/catch, just in case... > >      catch(exception e) > >          ?e.message > >          ?"the code caused this error at value: > "+q.rowset.fields["myfield"].value > >          release object q > >          q=null > >          return > >      endtry > > > enddo > > release object q > > q=null > Thanks for this information, I have successfully used both suggestions, which go a long way towards viewing the indexed field correctly in the Form, and changing the first character of the first word of the character field to upper case. However my major problem was with the second word of the character field, which sometimes is upper case and sometimes lower. A clue to the solution might be that there is a space before the word to be changed. (In Botany, the convention is for the (First letter) Species to be upper case, and the Variety in lower case.) Regards Roy price