Table metadata extract to Excel file using X++ code

Hello all,

Today I want to show you how to extract Table metadata properties i.e Field Name, label, size, EDT

Here is the snippet of code.

 

static void ExtractMetaData(Args _args)
{
    // Metadata related variables
    DictTable                   table;
    DictIndex                   dictIndex;
    DictField                   field;
    DictEnum                    enum;
    DictType                    type;
    DictRelation                relation;
    int                         i = 0, j,k;
    SQLDictionary               sqlDictionary;
    Tablename                   _tablename;
    str                         enumDetails;
    #DictField

    // Excel related variables
    int                         rowNum = 1,rowNum2;
    Filename                    _filename;
    SysExcelApplication_2007    excel;
    SysExcelWorkBook_2007       workBook;
    SysExcelWorkSheet           workSheet;
    SysExcelCells               cells;
    #Excel

    // User interaction related variables
    Dialog                      dialog;
    DialogField                 dialogField_Table, dialogField_FilePath;

    ;

    // Get Table name and file save path from user
    /*dialog = new Dialog();
    dialog.caption('Metadata Extractor');
    dialogField_Table = dialog.addField(typeid(TableName));
    dialogField_Table.label('Table name');
    dialogField_FilePath = dialog.addField(typeid(FilePath));
    dialogField_FilePath.label('Directory to create output file');

    if(dialog.run())
    {
        _tablename = dialogField_Table.value();
        _filename = dialogField_FilePath.value() + '\\'+ _tablename + '_Metadata.xlsx';
    }
    else
        throw error::missingParameter(dialog);*/

    _tablename = "DMFSubledgerEntityOCP";
    _filename = "D:\\DMF\\SubledgerBalances.xlsx";

    // Start execution
    try
    {
        // Create instance of the excel application
        excel = SysExcelApplication_2007::construct(new COM(#excel));

        // Create a excel workbook
        workBook = excel.workbooks().add();

        // Name the 1st worksheet in the workbook
        workBook.worksheets().itemFromNum(1).name(_tableName);//_tableName

        // Delete the other worksheets from the workbook
       /*worksheet = workBook.worksheets().itemFromNum(2);
        worksheet.delete();
        workSheet = workBook.worksheets().itemFromNum(2);
        workSheet.delete();*/

        // Access the cells of the 1st worksheet in the workbook and add the title row
        cells = workBook.worksheets().itemFromNum(1).cells();
        cells.item(1,rowNum).value('Field Name');
        cells.item(2,rowNum).value('Field Label');
        cells.item(3,rowNum).value('Data Type');
        cells.item(4,rowNum).value('Length');
        cells.item(5,rowNum).value('Unique Field');
        cells.item(6,rowNum).value('Mandatory Field');
        cells.item(7,rowNum).value('Reference Field');
        cells.item(8,rowNum).value('Reference Table');
        cells.item(9,rowNum).value('Other constraints');

        // Iterate through all the fields of the table and add the metadata to the excel worksheet
        while select sqlDictionary
       //order by fieldId
        where
        sqlDictionary.fieldId != 0 &&
        sqlDictionary.tabId == tableName2Id(_tableName)
        {
            // Increment the working row
            rowNum++;

            // Retrieve the "DictField" object for reference
            field = new DictField(sqlDictionary.tabId,sqlDictionary.fieldId);

            // Populate the excel with metadata
            cells.item(1,rowNum).value(field.name()); //Field Name
            cells.item(2,rowNum).value(field.label()); //Data Type
            cells.item(3,rowNum).value(enum2str(sqlDictionary.fieldType)); //Data Type

            // Length of a field is only populated if it is exists
            if(sqlDictionary.strsize != 0)
                cells.item(4,rowNum).value(sqlDictionary.strSize); //Length

            // Retrieve the "DictType" object for reference
            type = new DictType(field.typeId());

            // Populate the excel with metadata
            if(bittest(field.flags(),#DBF_MANDATORY))
                cells.item(6,rowNum).value('Yes'); //Mandatory field

            // Check if the field has a EDT attached to it
            if(type)
            {
                // Extract the relationship defined on the EDT
                relation = type.relationObject();

                // Proceed if a relationship exists
                if(relation)
                {
                    // Populate the excel with metadata
                    cells.item(7,rowNum).value(fieldid2name(relation.table(),relation.lineExternTableValue(1))); //Reference Field
                    cells.item(8,rowNum).value(tableid2name(relation.table())); //Reference Table
                }
            }

            // Check if the field has a Enum attached to it
            if(field.enumId())
            {
                // Retrieve the "DictEnum" object for reference
                enum = new DictEnum(field.enumId());

                // Initialize variable to temporarily store enum values
                enumDetails = 'Possible values = ';

                // Extract the enum values into the temporary variable
                for(i=0;i<enum.values();i++)
                {
                    enumDetails = enumDetails +'\n'+ int2str(enum.index2Value(i)) + ' - ' + enum.index2Label(i);
                }

                // Populate the excel with metadata
                cells.item(9,rowNum).value(enumDetails); //Other constraints
            }
        }

        // Retrieve the "DictTable" object for reference
        table = new DictTable(tableName2Id(_tableName));
  /*
        // Loop through all the indices of the table
        for (j=0;j<table.indexCnt();j++)
        {
            // Retrieve the "DictIndex" object for reference
            dictIndex = new DictIndex(tableName2Id(_tableName),table.indexCnt2Id(j));

            // Check if the index is a unique index
            if(dictIndex.allowDuplicates() == NoYes::No)

                for(k=1;k<=dictIndex.numberOfFields();k++) // Iterate through all the fields of the index
                {
                    // Find the position of the field in the worksheet and set the Unique Index property to Yes
                    cells.item(4,(cells.range('A:A').find(fieldid2name(table.id(),dictIndex.field(k))).column())).value('Yes');
                }

        }
   */
        // Format the worksheet - Make the header row grey and bold, and autofit the columns
        workBook.styles().add('1').interior().color(WinApi::RGB2int(190, 190, 190));
        workBook.styles().item(1).font().bold(true);
        workBook.worksheets().itemFromNum(1).range('A1:H1').style('1');
        workBook.worksheets().itemFromNum(1).columns().autoFit();

        // Save the workbook and present it to the user
        workBook.saved(true);
        //workBook.saveAs(_filename);
        excel.visible(true);
    }
    catch(Exception::Error)
    {
        throw error("error");
    }

}

D365 F&O Standard

Table - DataFeedsTableCatalog

/// <summary>

/// This table store a list of tables that can be used to configure a data feed

/// </summary> 

 

/// <summary>

    /// resets the contents of the table catalog

    /// </summary>

    internal static void resetTableCatalog()

    {

        DataFeedsTableCatalog tableCatalog;

        delete_from tableCatalog;

        ttsbegin;

        System.Collections.IEnumerator tableCollectionEnumerator = DataFeedsTableCatalog::getTableQueryEnumerator();

        while (tableCollectionEnumerator.MoveNext())

        {

            DataFeedsTableCatalog::createTableRefRecord(tableCollectionEnumerator.Current as Microsoft.Dynamics.AX.Metadata.NodeLib.Node);

        }

 

        ttscommit;

    }

 

    /// <summary>

    /// Gets the table query enumerator.

    /// </summary>

    /// <returns>The instance of the <c>System.Collections.IEnumerator</c> class that can iterate table nodes.</returns>

    internal static System.Collections.IEnumerator getTableQueryEnumerator()

    {

        System.Collections.IEnumerator tableCollectionEnumerator;

 

        var query = Microsoft.Dynamics.AX.Metadata.NodeLib.Specialized.TableQuery::Construct();

        query.AllowTemporary = false;

        query.AllowSystem    = true;

        tableCollectionEnumerator = query.GetEnumerator();

       

        return tableCollectionEnumerator;

    }

 

    internal static void createTableRefRecord(Microsoft.Dynamics.AX.Metadata.NodeLib.Node _node)

    {

        DataFeedsTableCatalog tableCatalog;

        SysDictTable    dictTable = new SysDictTable(tableName2Id(_node.Name));

        tableId         tableId   = dictTable.id();

       

        if (dictTable && dictTable.enabled())

        {

            // don't include views, temp tables, in memory tables, dixf staging tables

            if ( DictTable.isView() || dictTable.tableType() != TableType::Regular || dictTable.tableGroup() == TableGroup::Staging)

            {

                return;

            }

 

            tableCatalog.TableIdValue = tableId;

            tableCatalog.TableObjectName = dictTable.name();

            tableCatalog.TableLabel = dictTable.label();

            tableCatalog.TableConfigurationKey = dictTable.getRootConfigKeyLabel();

            tableCatalog.SavePerCompany = dictTable.dataPrCompany();

            tableCatalog.TableGroup = dictTable.tableGroup();

            var tableMetadata = Microsoft.Dynamics.Ax.Xpp.MetadataSupport::GetTable(tableCatalog.TableObjectName);

            tableCatalog.Tags = tableMetadata.Tags;

            tableCatalog.insert();

        }

    }