How to enlarge Version List of objects

pnv

Hi, all

I have the following problem whith Version List field of Object table limited by 80 symbols. In my company every client's demand is registered and new unique number is assigned to it. If NAV object in database is modified for a demand it must be marked by this number in Version List. Since some objects (e.g. tables 81, 36, 37 or codeunits 12, 22, 80) are actively changed, so very soon there is not enough room to add new number. Does somebody have an idear how to solve it

Many thanks

Savatage

Are you using the Documentation() section?

matttrax

The Documentation trigger is a good start, but if you need a searchable way to find what objects were modified during what project try using source control software. There are plenty of them out there, even a couple built just for Navision.

Take a look at Object Manager Light in the download section.

rdebath

We've always done most of our development using the SQL version of Navision so a simple "Audit" trigger on the object table gives a very good start to keeping track of what's happening.

Obviously, you need the documentation trigger for the why of the change, but this can be used to answer the who and the when parts and make sure that the why is updated!

The SQL trigger is in the documentation trigger, you need to run it in SQL Management studio.

Once you've got the data the first thing you'll want to do is delete most of it, so I've added "Patch No" and "Version No" fields so you can tag the ones you want to keep. And finally the restore function lets you bring the audit record back into the live object table.

Requirements are Navision 3.60 or later running on SQL2000 or later.

OBJECT Table 99999 Object History
{
  OBJECT-PROPERTIES
  {
    Date=10/04/09;
    Time=06:24:45;
    Version List=;
  }
  PROPERTIES
  {
    DataPerCompany=No;
  }
  FIELDS
  {
    { 1   ;   ;Type                ;Option        ;OptionString=No,Table,Form,Report,Dataport,Codeunit,XMLport,MenuSuite }
    { 2   ;No ;Company Name        ;Text30        ;TableRelation=Company;
                                                   Description=Tabledata Only }
    { 3   ;   ;ID                  ;Integer        }
    { 4   ;   ;Name                ;Text30         }
    { 5   ;   ;Modified            ;Boolean        }
    { 6   ;   ;Compiled            ;Boolean        }
    { 7   ;   ;BLOB Reference      ;BLOB           }
    { 8   ;   ;BLOB Size           ;Integer       ;BlankZero=Yes }
    { 9   ;No ;DBM Table No.       ;Integer       ;BlankZero=Yes;
                                                   Description=Native Tabledata Only }
    { 10  ;   ;Date                ;Date           }
    { 11  ;   ;Time                ;Time           }
    { 12  ;   ;Version List        ;Text80         }
    { 50  ;   ;Old Modified        ;Boolean        }
    { 51  ;   ;Old Date            ;Date           }
    { 52  ;   ;Old Time            ;Time           }
    { 53  ;   ;Old Version List    ;Text80         }
    { 54  ;   ;BLOB Updated        ;Boolean        }
    { 55  ;   ;Before              ;Option        ;OptionString=Insert,Delete }
    { 101 ;   ;Server Time         ;DateTime       }
    { 102 ;   ;Patch No            ;Code20         }
    { 103 ;   ;Entry No            ;BigInteger    ;AutoIncrement=Yes }
    { 104 ;   ;User ID             ;Text30         }
    { 107 ;   ;Version Code        ;Code20         }
  }
  KEYS
  {
    {    ;Entry No                                 }
    {    ;Type,ID,Entry No                         }
    {    ;Type,ID,Date,Time                        }
    {    ;Patch No                                 }
  }
  CODE
  {

    PROCEDURE Restore@1000();
    VAR
      Object@1000 : Record 2000000001;
    BEGIN
      TESTFIELD("BLOB Size");
      //TESTFIELD(Date);
      CALCFIELDS("BLOB Reference");
      IF NOT "BLOB Reference".HASVALUE THEN FIELDERROR("BLOB Reference");

      IF NOT Object.GET(Type, '', ID) THEN BEGIN
        CLEAR(Object);
        Object.Type := Type;
        Object."Company Name" := '';
        Object.ID := ID;
        Object.TRANSFERFIELDS(Rec);
        Object.INSERT;
      END ELSE BEGIN
        Object.TRANSFERFIELDS(Rec);
        Object.MODIFY;
      END;
    END;

    BEGIN
    {
      /****** Object:  Trigger [dbo].[Trig_Object_Upd]    Script Date: 04/10/2009 06:14:41 ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE TRIGGER [dbo].[Trig_Object_Upd]
         ON  [dbo].[Object]
         AFTER INSERT,UPDATE,DELETE
      AS
      declare
        @type Integer,
        @oldid Integer,
        @newid Integer,
        @olddate Datetime,
        @oldtime Datetime,
        @oldsize Integer,
        @oldmodified Integer,
        @newmodified Integer,
        @oldversion Varchar(80),
        @olduser Varchar(30),
        @newuser Varchar(30),
        @blobupdated Integer,
        @insRowCount Integer,
        @delRowCount Integer,
        @errmsg Varchar(80)
      SET NOCOUNT ON

      SELECT @olddate = '1753-01-01 00:00:00'
      SELECT @oldtime = '1753-01-01 00:00:00'
      SELECT @oldsize = 0
      SELECT @oldmodified = 0
      SELECT @oldversion = ''
      SELECT @newuser = SYSTEM_USER
      SELECT @insRowCount = count(*) FROM inserted
      SELECT @delRowCount = count(*) FROM deleted WHERE [ID] < 1000000000 OR [ID] > 2000000000
      SELECT @blobupdated = 0
      IF update([BLOB Reference])
        SELECT @blobupdated = 1

      IF @insRowCount > 1 or @delRowCount > 1 BEGIN
        RAISERROR ('Please Update only one object at a time for the version tracking',16,1)
        ROLLBACK TRANSACTION
        RETURN
      END

      IF @insRowCount = 0 AND @delRowCount = 0 RETURN

      IF @insRowCount > 0 BEGIN
        SELECT @Type = [Type], @newid = [ID], @newmodified = [Modified] FROM inserted
        If @Type = 0 return -- Dont log TableData Records
        If @newid > 999999999 and @newid < 2000000000 return -- Dont log restores
      END

      IF @delRowCount > 0 BEGIN
        SELECT @Type = [Type], @oldid = [ID], @oldmodified = [Modified], @olddate = [Date], @oldtime = [Time],
               @oldversion = [Version List]
          FROM deleted
        If @Type = 0 return -- Dont log TableData Records
      END

      IF (@delRowCount > 0 and @insRowCount = 0) or update([ID]) BEGIN
        INSERT INTO [Object History] (
          [Type], [ID], [Name], [Modified], [Compiled], [BLOB Reference], [BLOB Size],
          [Date], [Time], [Version List],
          [Old Modified], [Old Date], [Old Time], [Old Version List], [BLOB Updated],
          [User ID],[Version Code],[Patch No],[Server Time],[Before])
          SELECT
          o.[Type], o.[ID], o.[Name], o.[Modified], o.[Compiled], NULL, 0,
          '1753-01-01 00:00:00', '1753-01-01 00:00:00', '',
          @oldmodified, @olddate, @oldtime, @oldversion, @blobupdated,
          @newuser, '', '', CURRENT_TIMESTAMP, 1
          FROM deleted o
      END

      IF @insRowCount > 0 BEGIN
        INSERT INTO [Object History] (
          [Type], [ID], [Name], [Modified], [Compiled], [BLOB Reference], [BLOB Size],
          [Date], [Time], [Version List],
          [Old Modified], [Old Date], [Old Time], [Old Version List],[BLOB Updated],
          [User ID],[Version Code],[Patch No],[Server Time],[Before])
        SELECT
          o.[Type], o.[ID], o.[Name], o.[Modified], o.[Compiled], o.[BLOB Reference], o.[BLOB Size],
          o.[Date], o.[Time], o.[Version List],
          @oldmodified, @olddate, @oldtime, @oldversion, @blobupdated,
          @newuser, '', '', CURRENT_TIMESTAMP, 0
          FROM dbo.[Object] o INNER JOIN inserted i
            ON i.[Type] = o.[Type] AND i.[ID] = o.[ID]
      END
    }
    END.
  }
}

pnv

Savatage wrote:

Are you using the Documentation() section?

Certainly we use Ducementation trigger. Unfortunally this way of ducumenting doesn't make it possible to filter objects in Object Designer by modification number

pnv

rdebath wrote:

We've always done most of our development using the SQL version of Navision so a simple "Audit" trigger on the object table gives a very good start to keeping track of what's happening.

Thanks a lot !! I almost know how to figure out problem
It's a good idea to create "Audit" trigger for the SQL version of Navision. We have the strict rules of Documentation trigger filling in. Combining this two premises we can suppliment the "Audit" table with long Version List field and generate it automatically. If we want not only to see the objects but also to export them we can (with use of Automated Keystokes) filter (Ctrl+F1) records in Object Designer according to filters set in "Audit" table and then call File->Export.
Open question is how to return control to "Audit" form :?:

rdebath

Minimise the object designer
Shift-F12
Alt+-
n

This should switch you back to the form you were on. Then you just use a hidden menubutton to capture something like Ctrl+Shift+F10

But: if you want to export/import objects as text I suggest you look at http://www.mibuso.com/dlinfo.asp?FileID=826

It's got a repackage of a DLL from codegenius that does just that.