AX 2012 - How to add tables in shared/private projects through excel using X++

Hi Readers,

Today, in this post we are going to tell you a very important topic. With the help of this article you will get the knowledge, how to add tables in shared/private projects through excel using X++.

Let's start...

Requirement:

We need to create a shared/private project and add all the tables in this with the tables grouping object. Please see the below image for better understanding.

As you can see for the above requirement we need to do the following things:

Approach 1:

 

1) Create a shared/private project in the AOT and add a Tables group in it.

2) Add all the AOT tables in this one by one manually.

Here, we can get the solution but it's time consuming. So what to do?

Well, we can achieve this using X++.

Solution:

Approach 2:

1. Create a excel file for all the tables in the AOT, for this you can use below SQL Script to fetch all the tables name.

-- SQL Script written by Chirag Gupta

use [YourModelDBName] 

select  Name, AxId

from ModelElement (nolock)
where ElementType = 44
order by AxId

2. Paste the above query result in Excel to upload with the help of X++. Now our excel is ready to upload.

Note: Your excel format must be like following image:

3. Create a AX Job and copy paste the below code.

// This code is written by Chirag Gupta

static void AddTablesInSharedProject(Args _args)

{
    #Properties
    #Axd
    #DMF
    #AOT
    #DimensionsEDT
    #Excel

    ProjectGroupNode            groupNode;
    str                         properties;
    TreeNode                    projectNode;
    TreeNode                    tempNode;

    SysExcelApplication         application;
    SysExcelWorkbooks           workbooks;
    SysExcelWorkbook            workbook;
    SysExcelWorksheets          worksheets;
    SysExcelWorksheet           worksheet;
    SysExcelCells               cells;
    COMVariantType              type;
    int                         row;

    FilenameOpen                fileNameOpen;
    dialogField                 dialogFilename;
    Dialog                      dialog;
    Str1260                     tableName;
    ;

    dialog          =   new Dialog("Excel Upoad");
    dialogFilename  =   dialog.addField(identifierStr(FilenameOpen));
    dialog.filenameLookupFilter(["@SYS28576",#XLSX,"@SYS28576",#XLS]);
    dialog.filenameLookupTitle("Upload from Excel");
    dialog.caption("Excel Upload");
    dialogFilename.value(fileNameOpen);

    if(!dialog.run())
    {
        return;
    }

    fileNameOpen    = dialogFilename.value();
    application     = SysExcelApplication::construct();
    workbooks       = application.workbooks();

    try
    {
        workbooks.open(fileNameOpen);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }

    workbook    = workbooks.item(1);
    worksheets  = workbook.worksheets();
    worksheet   = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    row = 1;

    projectNode = SysTreeNode::createProject('Project1', ProjectSharedPrivate::ProjShared); // ProjShared for shared project
    // Note: Project1 is the Project name to be created, you can specify yours. It must be unique name.

    groupNode   = projectNode.AOTadd('Tables');
    properties  = groupNode.AOTgetProperties();
    properties  = setProperty(properties, #PropertyStructName, 'Tables');
    properties  = setProperty(properties, #ProjectGroupType, 'Tables');

    groupNode.AOTsetProperties(properties);

    //info(strFmt("Below objects are not added in the new Project: %1", projectNode));

    do
    {
        row++;

        tableName   = cells.item(row,1).value().bstr(); // To read tables name from excel one by one
        tempNode    = TreeNode::findNode(#TablesPath);
        tempNode    = tempNode.AOTfindChild(tableName);

        if (tempNode)
        {
            groupNode.addNode(tempNode);
        }
        else
        {
            info(tableName);
        }

        type = cells.item(row+1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);
    application.quit();

    projectNode.AOTsave();
    projectNode.AOTrefresh();
    projectNode.AOTcompile(1); // Compile using X++

    info("Done");
}

4. Run the AX Job and browse the excel file like below image.

 

5. Click on OK button and wait for sometime for infolog as Done.

6. Once AX Job runs successfully open AOT Projects or press CTRL + Shift + P.

7. A new project will be created as 'Project1' under shared project node.

8. Enjoy, we have done it.

Comment below if this article helped you to add tables in shared/private projects through excel using X++ ...

Author:
Chirag Gupta
Microsoft Dynamics 365 AX Technical Consultant at IBM Bangalore

Date:
28-Sep-2019

Happy Learning !!