Upgrade - Strip All Customizations

matttrax

Have a database that has been customized and modified to shreds. The base data is still in good shape, though. Upgrading to 5.0 SP1 at the same time. Basically doing a reimplementation, but want an easy way to move the data, so going with the upgrade toolkit.

The first step is to convert or restore the database into a 5.0 client. Makes sense. But this of course brings all custom fields and data over.

I can't just replace the objects in Task 6 by importing the "customized" (which in my case would be all base 5.0 objects) because it will complain about removing a field that has data in it. At least that's what I think, haven't tried yet. And I really don't want to manually uninstall unused 3rd party add-ons and go through every table doing MODIFYALLs to blank things out.

Is there an easy way to only move base fields? Or any other ideas? Thanks for your help.

ara3n

IF you are doing a reeimplementation, you tried the old version like another ERP system. You create data Migration plan (dataports, RIM)

You identify the GAPS (modification they would like to have that are not in 5.0)

Implement the Mods in 5.0

Test the mods and data migration


Test some more.
Training

Test some more.

Golive.

DenSter

The first step is just to have the old database on the new exe, you still need to migrate data over. The assumption is always that all custom fields go over to the new version. Removing some of them will require you to make a decision about the data that the field stores. Do you simply remove it? Do you convert it to another field?

matttrax

Yeah, my only fear with doing it that way was that I heard there were a lot of new fields in 5.0 (I believe specifically with certain inventory tables, but I might not be remembering correctly) that I just wouldn't know to fill in. I figured the upgrade toolkit would take care of this for me and simplify things.

David_Cox

If you are returning to the standard database tables and functions, it is only the tables that matter in the old version, as all other objects get deleted in the upgrade.

So just backup and restore a copy of the live database in its existing version, then create a report that clears the values from the custom tables and fields only, Import all the objects from from a standard database, then you can follow the upgrade toolkit

David

matttrax

That's what I started doing, just to play around. Running into one problem, though, when deleting a field that is in a key.

Stripped down version of code:

Key.SETRANGE(TableNo, Object.ID);
MESSAGE('%1', Key.COUNT);

returns a count of 0.

IF Key.FINDSET THEN
  REPEAT
    IF Key.TableNo = Object.ID THEN
      MESSAGE('Found Key %1', Key.Key)
  UNTIL Key.NEXT = 0;

returns exactly the number of keys I expected.

I know I'm really hungry and possibly not thinking clearly, but am I missing something obvious here?

David_Cox

Don't understand what you mean here.

I would just create a report with all the tables that have custom fields, then for each data Item, say we had custom fields on the Customer.

Customer - OnAfterGetRecord()
MyTextField := '';
MyDecField :=0;
MyOptionfield := 0;
MyDateField := 0D;
MODIFY;

Then when I import the standard Customer table in the custom fields and keys will go.

The only way you will have a problem is if you have changed the primary key on a standard table.

David

matttrax

Oh, that still involves me going through table by table and manually adding fields to the report. That would be miserable. The people who worked on the system before never said No and they have over 1000 custom fields and no modified flags checked on anything.

Perhaps more complete code will help, using salesperson table as an example, has 5 custom fields and 1 key that contains one of those fields.

Object.SETRANGE(Object.Type, Object.Type::Table);
Object.SETFILTER(ID, '13');
IF Object.FINDSET THEN
  REPEAT
    Field.SETRANGE(TableNo, Object.ID);
    Field.SETFILTER("No.", '50000..100000');
    IF Field.FINDSET THEN BEGIN
      REPEAT
        //Key.SETFILTER(TableNo, '%1', Object.ID);
        //Key.SETFILTER(Key, '*%1*', Field.FieldName); 
        //If either of these filters is set, the count is 0
        IF Key.FINDSET THEN BEGIN
          REPEAT
            IF Key.TableNo = Object.ID THEN
              MESSAGE('Found Key:\Table: %1\Key: %2\', Key.TableNo, Key.Key);
          UNTIL Key.NEXT = 0;
        //Key.DELETEALL(TRUE);
        END;
        //Field.DELETEALL;
      UNTIL Field.NEXT = 0;
    END;
  UNTIL Object.NEXT = 0;

Looping through the object table, then through the field table for the custom fields on that table. Examine all keys to see if that field is contained in one of them. If so delete it. When you finish checking all keys for all custom fields in that table, delete the custom fields.

Problem is that when I do a setrange on the key table, it doesn't find anything. I know the value being set is correct. And since it is part of the primary key I know it's not a flowfield.

Thanks

jlandeen

Here's one way to at least clear all the custom field values - once those are cleared out you should be able to restore the data into a clean database.

Build a process only report that loops through all of the Table Objects (use the Table 'Object' filtered for type = Table as the main data item). You can then use the Field table (filtered for all fields between 50,000 and 99,9999) to find all of the fields that are in the custom range. Remember to only include Normal fields - flow fields and flow filters are not actually stored in the database.

Now you should have all the details you need to loop through a record reference to the table and then modify the data all of the custom fields (using a field ref data type) and set them to blank, zero or some other empty default and save the record.

This should allow you to write everything in a very generic fashion and should only need to write code to deal resetting each data type value specifically.

David_Cox

You will not be able to delete the fields until the values have been removed, as jlandeen said you could use RecRef and FieldRef and based on the field type clear the Custom field data.

The code you have written you do not need, all you need to do after clearing the values is to import the tables from a standard database the same version as your copy, this will remove the Custom Fields and reset the keys

I do not have an example to hand so have a search for RecRef, FieldRef and RecID for sample code, then use the Table and Field records to find the fields in the 50000..99999 range, and you will have to run this in each Company


David

matttrax

Decided to do a combination of the methods described. Had to remove all data from the fields. But also disabled the keys and the fields in order to have a double check that all data was deleted successfully.

If anyone wants to use it, don't forget to take out the SETRANGE for the Object table. I was only testing for Table 13 so it wouldn't take as long.

Maybe not the best code, and certainly not fully tested yet. But it's a start.

OBJECT Report 50099 Delete Customizations
{
  OBJECT-PROPERTIES
  {
    Date=06/11/08;
    Time=[ 4:31:33 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    ProcessingOnly=Yes;
    OnPreReport=BEGIN
                  StartTime := TIME;
                END;

    OnPostReport=BEGIN
                   EndTime := TIME;
                   MESSAGE('Started: %1\Finished: %2', StartTime, EndTime);
                 END;

  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table2000000001;
        DataItemTableView=SORTING(Type,Name)
                          WHERE(Type=CONST(Table));
        OnPreDataItem=BEGIN
                        SETRANGE(ID, 13);
                      END;

        OnAfterGetRecord=BEGIN
                           TestFieldRec.SETRANGE(TableNo, ID);
                           TestFieldRec.SETRANGE("No.", 50000, 100000);
                           IF NOT TestFieldRec.FINDFIRST THEN
                             CurrReport.SKIP;
                         END;

      }
      SECTIONS
      {
      }
       }
    { PROPERTIES
      {
        DataItemIndent=1;
        DataItemTable=Table2000000041;
        DataItemTableView=SORTING(TableNo,No.);
        OnPreDataItem=BEGIN
                        SETRANGE("No.", 50000, 100000);
                        SETRANGE(Enabled, TRUE);
                      END;

        OnAfterGetRecord=BEGIN
                           TableReference.OPEN(Object.ID);
                           IF TableReference.FINDSET THEN
                             REPEAT
                               FieldReference := TableReference.FIELD(Field."No.");
                               CASE FORMAT(FieldReference.TYPE) OF
                                 'Code', 'Text':
                                   FieldReference.VALUE := '';
                                 'Date':
                                   FieldReference.VALUE := 0D;
                                 'Time':
                                   FieldReference.VALUE := 0T;
                                 'Integer', 'Decimal':
                                   FieldReference.VALUE := 0;
                                 'Boolean':
                                   FieldReference.VALUE := FALSE;
                                 'Option':
                                   FieldReference.VALUE := 0;
                               END;
                               TableReference.MODIFY;
                             UNTIL TableReference.NEXT = 0;
                           TableReference.CLOSE;
                         END;

        DataItemLink=TableNo=FIELD(ID);
      }
      SECTIONS
      {
      }
       }
    { PROPERTIES
      {
        DataItemIndent=2;
        DataItemTable=Table2000000063;
        DataItemTableView=SORTING(TableNo,No.);
        OnPreDataItem=BEGIN
                        //For whatever reason you can't link on the TableNo field for the Key table, did it by name
                        //since that should also be unique
                        SETFILTER(Key, '@*' + Field.FieldName + '*');
                        SETRANGE(Enabled, TRUE);
                      END;

        OnAfterGetRecord=BEGIN
                           Enabled := FALSE;
                           MODIFY;
                         END;

        OnPostDataItem=BEGIN
                         Field.Enabled := FALSE;
                         Field.MODIFY;
                       END;

        DataItemLink=TableName=FIELD(TableName);
      }
      SECTIONS
      {
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      TestFieldRec@1000000000 : Record 2000000041;
      TableReference@1000000001 : RecordRef;
      FieldReference@1000000002 : FieldRef;
      StartTime@1000000003 : Time;
      EndTime@1000000004 : Time;

    BEGIN
    END.
  }
}

jlandeen

Yup that looks like a simple little report that would do the trick.

If there are multiple companies involved in it you may want to insert a root data item of company and call the change company on all of the reocrds. This would allow you to get all the data cleared out in 1 report....simple and easy

But otherwise a nice piece of code.

David_Cox

Field 100000 is outside the custom range, not sure if it is used anywhere but to be safe change, TestFieldRec.SETRANGE("No.", 50000, 100000); to TestFieldRec.SETRANGE("No.", 50000, 99999);

David