Monday, July 8, 2019

How to add document note in D365


class VendAccountDocument_JKS

{



    static void main(Args _args)

    {

        VendTable vendTable;

        DocuType docuType;

        DocuRef docuRef;

        vendTable = VendTable::find('BRMF-000001');

        docuType = DocuType::find('Note');

        if (!docuType ||

            docuType.TypeGroup != DocuTypeGroup::Note)

        {

            throw error("Invalid document type");

        }

        docuRef.RefCompanyId = vendTable.dataAreaId;

        docuRef.RefTableId = vendTable.TableId;

        docuRef.RefRecId = vendTable.RecId;

        docuRef.TypeId = docuType.TypeId;

        docuRef.Name = 'Automatic note';

        docuRef.Notes = 'Added from X++';

        docuRef.insert();

        info("Document note has been added successfully");

    }



}

How to read excel and update record in AX2012 :

static void POCUpdatePurchaseDetails_JS(Args _args)
{
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    COMVariantType          type;
    Dialog dialog = new     Dialog("Excel upload utility");
    dialogField             dialogFilename;
    int row;
    int                     totalUpdatedRecord = 0;
    FileName                filename;
    InventTable             inventTable;
    ItemId                  itemId;
    ItemGroup               itemGroup;
    InventTableModule       inventTableModule;

    ;

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen),"@SYS53125");

    dialog.filenameLookupTitle("Update data from excel file");
    dialog.filenameLookupFilter(["@SYS28576",'*XLSX', "@SYS28576",'*XLS']);
    if (!dialog.run())
        return;
    filename = dialogFilename.value();
    try
    {
        workbooks.open(filename);
    }
    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;
    do
    {
        row++;
        itemId = cells.item(row, 1).value().bStr();

        ttsbegin;

        inventTable = InventTable::find(itemId,true);
        inventTableModule=InventTableModule::find(inventTable.ItemId,ModuleInventPurchSales::Purch,true);
        if (inventTable)
        {
            inventTableModule.Price=str2num(cells.item(row, 2).value().bStr());
            inventTableModule.PriceUnit=str2num(cells.item(row, 3).value().bStr());
            inventTableModule.update();
            totalUpdatedRecord++;
        }

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

    info(strFmt('Data updated, total record=%1 record',totalUpdatedRecord));

}

How to find SQL query in D365:


How to find SQL query out of the AOT query:


static void AOTQuery(Args _args)
{

    QueryRun queryRun;

    ;

    queryRun = new QueryRun(queryStr(QueryName));//add any name of query here in place of QueryName

    info(queryRun.query().toString());

}

Create Query dynamically in AOT through code:

Create Query dynamically in AOT through code:

static void CreateQueryInAOT(Args _args)
    {
        TreeNode                treeNodeObj;
        Query                   queryObj; // Extends TreeNode class.
        QueryBuildDataSource    qbds;
        QueryBuildRange         qbr;
        QueryRun                qr;
        CustTable               xrecCustTable;
        str                     queryName = "TestQuery";
        // Macro.
        #AOT
        // Delete the query from the AOT, if the query exists.
        treeNodeObj = TreeNode::findNode(#QueriesPath);
        treeNodeObj = treeNodeObj.AOTfindChild(queryName);
        if (treeNodeObj) { treeNodeObj.AOTdelete(); }
        // Add the query to the AOT.
        treeNodeObj = TreeNode::findNode(#QueriesPath);
        treeNodeObj.AOTadd(queryName);
        queryObj = treeNodeObj.AOTfindChild(queryName);
        // Further define the query.
        qbds  = queryObj.addDataSource(tablenum(CustTable));
        qbr   = qbds.addRange(fieldnum(CustTable, CustGroup));
        qbr.value("Inter");
        // Compile the query.
        queryObj.AOTcompile(1);
        queryObj.AOTsave();
        // Run the query.
        qr = new QueryRun("TestQuery");
        while ( qr.next() )
        {
            xrecCustTable = qr.GetNo(1); // 1 means first data source.
            Global::info(strFmt("%1 , %2",
                xrecCustTable.AccountNum, xrecCustTable.CustGroup));
        }