lowercase to UPPERCASE in CODE Fields

sudokusudoku Member Posts: 50
edited 2006-11-13 in Navision Attain
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.

Comments

  • ara3nara3n Member Posts: 9,256
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • sudokusudoku Member Posts: 50
    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.
  • ara3nara3n Member Posts: 9,256
    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. :D
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • sudokusudoku Member Posts: 50
    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.
  • ara3nara3n Member Posts: 9,256
    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?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • sudokusudoku Member Posts: 50
    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
  • ara3nara3n Member Posts: 9,256
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • sudokusudoku Member Posts: 50
    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
  • ara3nara3n Member Posts: 9,256
    Oh sorry.

    I will create a table and a form, that will generate the sql statement. Once I get free time.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ara3nara3n Member Posts: 9,256
    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.
      }
    }
    
    
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ara3nara3n Member Posts: 9,256
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • sudokusudoku Member Posts: 50
    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
  • SavatageSavatage Member Posts: 7,142
    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
Sign In or Register to comment.