export field Captions and Option strings

milo · 2010-04-01

I need to export the default field Captions and Option strings for language NL, version 2009 SP1.
How can I export them without manually table-per-table copy/paste to Excel?

We have no developer license or tools. (I tried the MergeTool but I have no permission to run the objects.)

matttrax · 2010-04-01

I don't think you can do this without the developer tools. Why do you need it? What's the end goal?

milo · 2010-04-01

I make reports with SQL Server Reportings Services outside of NAV.
I prefer to link an "Option field" table rather than "hard code" the captions in every single query.

Furthermore I'm creating a "business rules" database for checking if all fields on an certain table are filled in correctly for the company I work for. The users only now the dutch field names, so I need the caption of the fields so I can link them to the "business rules".

For Option fields: table name; field name; option no. (0,1,2,ect) ; caption ENU ; caption NLD
For table fields: table name; field name; field no. ; caption ENU ; caption NLD

Maybe someone with developer tools can create a simple CSV for me? [-o<

milo · 2010-04-01

just discovered that I can make a dataport with DataItemTable "Field" to export the field data
still need Option fields values...

einsTeIn.NET · 2010-04-01

Take a look at FieldRef.OPTIONSTRING and FieldRef.OPTIONCAPTION. But it's not that easy for someone only with low level programming skills. I don't know if you could pass this challenge.

Duikmeester · 2010-04-01

Following report will create an c:\temp\export.txt with the following specifications.

Line 1 (2 fields): TableID;TableName
Line 2n (3 fields): TableID;LanguageCode,TableCaption
Line 3n (4 fields): TableID;FieldID;FieldName;FieldOptionString
Line 4n (5 fields): TableID;FieldID;LanguageCode;FieldCaption;FieldOptionCaption

OBJECT Report 50000 Export Captions
{
  OBJECT-PROPERTIES
  {
    Date=01-04-10;
    Time=15:14:51;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table2000000058;
        DataItemTableView=SORTING(Object Type,Object ID)
                          ORDER(Ascending)
                          WHERE(Object Type=CONST(Table));
        DataItemVarName=Tables;
        ReqFilterHeadingML=[ENU=Table;
                            NLD=Tabel];
        OnPreDataItem=BEGIN
                        gFile.TEXTMODE := TRUE;
                        gFile.WRITEMODE := TRUE;
                        gFile.CREATE('c:\temp\export.txt');
                      END;

        OnAfterGetRecord=BEGIN
                           gRecordRef.OPEN("Object ID",TRUE);

                           gFile.WRITE(
                             STRSUBSTNO('%1;%2',
                               gRecordRef.NUMBER,
                               gRecordRef.NAME
                             )
                           );
                         END;

        OnPostDataItem=BEGIN
                         gFile.CLOSE;
                       END;

        ReqFilterFields=Object ID;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            SectionWidth=20250;
            SectionHeight=846;
          }
          CONTROLS
          {
            { 1210030007;Label  ;0    ;0    ;1050 ;846  ;ParentControl=1210030006;
                                                         HorzAlign=Right;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1210030009;Label  ;1200 ;0    ;6150 ;846  ;ParentControl=1210030008;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=20250;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1210030006;TextBox;0    ;0    ;1050 ;423  ;CaptionML=[ENU=ID;
                                                                    NLD=ID];
                                                         SourceExpr=gRecordRef.NUMBER }
            { 1210030008;TextBox;1200 ;0    ;6150 ;423  ;CaptionML=[ENU=Name;
                                                                    NLD=Naam];
                                                         SourceExpr=gRecordRef.NAME }
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemIndent=1;
        DataItemTable=Table2000000045;
        DataItemTableView=SORTING(Abbreviated Name)
                          ORDER(Ascending)
                          WHERE(Globally Enabled=CONST(Yes),
                                STX File Exist=CONST(Yes));
        DataItemVarName=TableCaptions;
        OnPreDataItem=BEGIN
                        giLanguage := CurrReport.LANGUAGE;
                      END;

        OnAfterGetRecord=BEGIN
                           CurrReport.LANGUAGE := "Language ID";

                           gFile.WRITE(
                             STRSUBSTNO('%1;%2;%3',
                               gRecordRef.NUMBER,
                               "Abbreviated Name",
                               gRecordRef.CAPTION
                             )
                           );
                         END;

        OnPostDataItem=BEGIN
                         CurrReport.LANGUAGE := giLanguage;
                       END;

      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=20250;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1210030010;TextBox;2850 ;0    ;4500 ;423  ;CaptionML=[ENU=Caption;
                                                                    NLD=Label];
                                                         SourceExpr=gRecordRef.CAPTION }
            { 1210030000;TextBox;1200 ;0    ;1500 ;423  ;CaptionML=[ENU=Language;
                                                                    NLD=Taal];
                                                         SourceExpr="Abbreviated Name" }
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemIndent=1;
        DataItemTable=Table2000000041;
        DataItemTableView=SORTING(TableNo,No.)
                          ORDER(Ascending);
        DataItemVarName=Fields;
        ReqFilterHeadingML=[ENU=Field;
                            NLD=Veld];
        OnAfterGetRecord=BEGIN
                           gFieldRef := gRecordRef.FIELD("No.");

                           gFile.WRITE(
                             STRSUBSTNO('%1;%2;%3;%4',
                               gRecordRef.NUMBER,
                               gFieldRef.NUMBER,
                               gFieldRef.NAME,
                               gFieldRef.OPTIONSTRING
                             )
                           );
                         END;

        ReqFilterFields=No.;
        DataItemLink=TableNo=FIELD(Object ID);
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            SectionWidth=20250;
            SectionHeight=846;
          }
          CONTROLS
          {
            { 1210030003;Label  ;2850 ;0    ;1050 ;846  ;ParentControl=1210030002;
                                                         HorzAlign=Right;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1210030005;Label  ;4050 ;0    ;6150 ;846  ;ParentControl=1210030004;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1210030011;Label  ;10350;0    ;9000 ;846  ;ParentControl=1210030001;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=20250;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1210030002;TextBox;2850 ;0    ;1050 ;423  ;CaptionML=[ENU=ID;
                                                                    NLD=ID];
                                                         SourceExpr=gFieldRef.NUMBER }
            { 1210030004;TextBox;4050 ;0    ;6150 ;423  ;CaptionML=[ENU=Name;
                                                                    NLD=Naam];
                                                         SourceExpr=gFieldRef.NAME }
            { 1210030001;TextBox;10350;0    ;9000 ;423  ;CaptionML=[ENU=OptionString;
                                                                    NLD=OptieText];
                                                         SourceExpr=gFieldRef.OPTIONSTRING }
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemIndent=2;
        DataItemTable=Table2000000045;
        DataItemTableView=SORTING(Abbreviated Name)
                          ORDER(Ascending)
                          WHERE(Globally Enabled=CONST(Yes),
                                STX File Exist=CONST(Yes));
        DataItemVarName=FieldCaptions;
        OnPreDataItem=BEGIN
                        giLanguage := CurrReport.LANGUAGE;
                      END;

        OnAfterGetRecord=BEGIN
                           CurrReport.LANGUAGE := "Language ID";

                           gFile.WRITE(
                             STRSUBSTNO('%1;%2;%3;%4;%5',
                               gRecordRef.NUMBER,
                               gFieldRef.NUMBER,
                               "Abbreviated Name",
                               gFieldRef.CAPTION,
                               gFieldRef.OPTIONCAPTION
                             )
                           );
                         END;

        OnPostDataItem=BEGIN
                         CurrReport.LANGUAGE := giLanguage;
                       END;

      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=20250;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1210030014;TextBox;4050 ;0    ;1500 ;423  ;CaptionML=[ENU=Language;
                                                                    NLD=Taal];
                                                         SourceExpr="Abbreviated Name" }
            { 1210030012;TextBox;5700 ;0    ;4500 ;423  ;CaptionML=[ENU=Caption;
                                                                    NLD=Label];
                                                         SourceExpr=gFieldRef.CAPTION }
            { 1210030013;TextBox;10350;0    ;9000 ;423  ;SourceExpr=gFieldRef.OPTIONCAPTION }
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemIndent=1;
        DataItemTable=Table2000000026;
        DataItemTableView=SORTING(Number)
                          ORDER(Ascending)
                          WHERE(Number=CONST(1));
        MaxIteration=1;
        OnAfterGetRecord=BEGIN
                           gRecordRef.CLOSE;
                         END;

      }
      SECTIONS
      {
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  REQUESTPAGE
  {
    PROPERTIES
    {
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      giLanguage@1210030002 : Integer;
      gRecordRef@1210030000 : RecordRef;
      gFieldRef@1210030001 : FieldRef;
      gFile@1210030003 : File;

    BEGIN
    END.
  }
  RDLDATA
  {
  }
}

milo · 2010-04-01

thanks Duikmeester!

rdebath · 2010-04-04

Duikmeester wrote:
gRecordRef.NUMBER,

Hmm, that's odd, it returns a number over 2000100000 for temp tables.
It seems to work for recref.GETTABLE too, and in 3.60.

Is this how you detect a TEMP table ?

Howdy, Stranger!

Quick Links

Categories

export field Captions and Option strings

Comments