lowercase to UPPERCASE in CODE Fields

sudoku · 2006-11-09

Hi

I have to create a report to convert data in the CODE Field from Lower case to UPPERCASE as I have some bad data . This has occured as the data was entered from SQL not from the Navision Client.

Can anybody help me out how to access the Fields in a Table with Data type "CODE" and to loop through all the tables as I have multiple tables with bad data.

Thx in Advance.

ara3n · 2006-11-09

First you need to find those tables.

Next you need to find the fields that are of type code to do this create a new tabular form. set the source table to field. select all the fields from the virtual table fields. Run the form. Filter on table ID and filter on Type = Code.

Then open enterprise manager.

and write the following sql statement

update [NavisionSP2$Customer]
set test = upper(test), secondtestfield = upper(secondtestfield)

in the above sql statment
NavisionSP2 is the company name and table name
test and secondtestfield are code fields.

Good luck.

sudoku · 2006-11-09

Thx for your help Rashed.

It worked for one table.thx a lot.

I have a problem sorting the tables as I have more than 200 tables to sort based on CODE Field.
How can I implement the above for all the Tables containing CODE Field in SQL?

Thx in Advance.

ara3n · 2006-11-09

I will have to write some code. I'll see if I can or somebody can do it in their free them.

Who ever did this, inserting data like that should taken out to the woods and hmm should be left in there.

sudoku · 2006-11-10

Thx rashed. Appreciate your time.

I tried creating a report in Navision client which was of not much help.
Is it the same to run a report in Navision Client to Change the Code Field to UpperCase and doing the same by writing a query in SQL? I need some help with the query.

thx in advance.

ara3n · 2006-11-10

in navision if you try to access the record, Navision will error and say the record is corrupted. You can only fix this from sql directly.

Here is an idea. Write a report in navision that will create a text file that will consist of sql statements for all the tables and fields?

sudoku · 2006-11-10

I tried to write a query in SQL to convert lowercase to uppercase but could not.
I could not sort the fields by datatype as the datatype for code,text is varchar in SQL. I am a bit confused how to sort.

Can anybody help me out with the SQL query to sort the code fields and change them to uppercase. ](*,)

thx in advance

ara3n · 2006-11-10

I wrote the sql statement above what is that you don't understand about the sql statement?

update [NavisionSP2$Customer]
set test = upper(test)

This statement changes the field test to uppercase.

sudoku · 2006-11-10

sudoku wrote:

Thx for your help Rashed.

It worked for one table.thx a lot.

I have a problem sorting the tables as I have more than 200 tables to sort based on CODE Field.
How can I implement the above for all the Tables containing CODE Field in SQL?

Thx in Advance.

You got me wrong Rashed.
I guess u missed my response for the code u sent to me.It worked for one table.I am just wondering how to ,as I have more than 200 tables and each table consisting of multiple Fields with the datatype CODE.
I need to write a query to loop around all the tables and fields with Datatype=CODE and convert them to uppercase.

Thx in Advance

ara3n · 2006-11-10

Oh sorry.

I will create a table and a form, that will generate the sql statement. Once I get free time.

ara3n · 2006-11-12

Hello sudoku

here is the report. This report will dynamically create the sql statements for all navision tables that contain code fields and will change them to uppercase. Simply import it and compile it.

OBJECT Report 50100 sql fix
{
  OBJECT-PROPERTIES
  {
    Date=11/12/06;
    Time=12:34:18 PM;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    ProcessingOnly=Yes;
    OnInitReport=BEGIN
                   FileName := 'C:\sqlfix.sql';
                   CompName := COMPANYNAME;
                 END;

    OnPreReport=BEGIN
                  MyFile.CREATE(FileName);
                  MyFile.WRITEMODE(TRUE);
                  MyFile.TEXTMODE(TRUE);
                END;

    OnPostReport=BEGIN
                   MyFile.CLOSE;
                 END;

  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table2000000001;
        DataItemTableView=SORTING(Type,Company Name,ID)
                          WHERE(Type=CONST(Table));
        OnPreDataItem=BEGIN
                        FILTERGROUP(10);
                        SETFILTER(ID,'<>385&<>1550020');    //add any table that is company independent here
                        FILTERGROUP(11);
                        SETRANGE(ID,0,99008535);  // filter out million range tables.cause they are company independent
                      END;

        OnAfterGetRecord=BEGIN
                           tabref.OPEN(ID);
                           IF tabref.FINDFIRST THEN BEGIN
                             fields.SETRANGE(TableNo,ID);
                             fields.SETRANGE(Type,fields.Type::Code);
                             fields.SETRANGE(Class,fields.Class::Normal);
                             I := 0;
                             IF fields.FIND('-') THEN BEGIN
                               MyFile.WRITE('update [' + CompName + '$' + FixName(Name) + ']');
                               MyFile.WRITE('SET');
                               REPEAT
                                 IF I MOD 10 = 0 THEN BEGIN
                                   setstring := '[' + FixName(fields.FieldName) + '] = upper([' + FixName(fields.FieldName) + '])';
                                 END ELSE BEGIN
                                 setstring := setstring + ', ' +
                                                '[' + FixName(fields.FieldName) + '] = upper([' + FixName(fields.FieldName) + '])';
                                 END;
                                 I += 1;
                                 IF I MOD 10 = 0 THEN BEGIN
                                   IF I = fields.COUNT THEN
                                     MyFile.WRITE(setstring)
                                   ELSE
                                     MyFile.WRITE(setstring + ',');
                                   setstring := '';
                                 END;
                               UNTIL fields.NEXT = 0;
                               MyFile.WRITE(setstring);
                               MyFile.WRITE('');
                             END;
                           END;
                         END;

        ReqFilterFields=ID,Name;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=12000;
            SectionHeight=846;
          }
          CONTROLS
          {
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
      { 1000000000;TextBox;2970 ;440  ;5060 ;440  ;SourceExpr=FileName }
      { 1000000001;Label  ;0    ;440  ;2530 ;440  ;CaptionML=ENU=File Name }
      { 1000000002;TextBox;2970 ;990  ;5060 ;440  ;SourceExpr=CompName }
      { 1000000003;Label  ;0    ;990  ;2530 ;440  ;CaptionML=ENU=Company Name }
    }
  }
  CODE
  {
    VAR
      fields@1000000000 : Record 2000000041;
      tabref@1000000001 : RecordRef;
      FileName@1000000002 : Text[250];
      MyFile@1000000003 : File;
      CompName@1000000004 : Text[250];
      setstring@1000000005 : Text[1024];
      I@1000000006 : Integer;
      quote@1000000007 : TextConst 'ENU=''';

    PROCEDURE FixName@1000000041(fieldname@1000000000 : Text[100]) : Text[100];
    BEGIN
      fieldname := CONVERTSTR(fieldname,'.','_');
      fieldname := CONVERTSTR(fieldname,'/','_');
      fieldname := CONVERTSTR(fieldname,'\','_');
      fieldname := CONVERTSTR(fieldname,quote,'_');
      EXIT(fieldname);
    END;

    BEGIN
    {
      //directions import the report. run it. it will create sqlfix.sql file. Open enterprise manager and paste the file and run it on the
        navision database
    }
    END.
  }
}

ara3n · 2006-11-12

btw sudoku is one of first things I do when flying to client sites. They have them on airplane magazines. It makes flights go a lot faster.

sudoku · 2006-11-13

Thx rashed.Appreciate ur time for helping me out. \:D/
I owe u 1.
I love playing the game sudoku so I named my ID after it.

will get back to you soon with some new issues.

=D>

Thx a lot.
sudoku

Savatage · 2006-11-13

sudoku wrote:

Thx rashed.Appreciate ur time for helping me out. \:D/
I owe u 1.
I love playing the game sudoku so I named my ID after it.

will get back to you soon with some new issues.

=D>

Thx a lot.
sudoku

http://www.mibuso.com/dlinfo.asp?FileID=538

Howdy, Stranger!

Quick Links

Categories

lowercase to UPPERCASE in CODE Fields

Comments