How to create a Query(dynamically)

How to create a Query(dynamically) and add a link in Axapta

Hi friends,

     Today we are trying to join two tables dynamically and adding a  link type between the two tables. Open the Aot and in jobs write the following code

static void CustTableSales1(Args _args)
{
    Query       query;
    QueryRun    queryrun;
    QueryBuildDataSource    qbds1;
    QueryBuildDataSource    qbds2;
    QueryBuildRange         qbr1;
    QueryBuildRange         qbr2;
    CustTable               custTable;
    ;
    query   = new query();
    qbds1   =   query.addDataSource(tablenum(CustTable));
    qbds1.addSortField(fieldnum(custTable,AccountNum),Sortorder::Descending);
    qbr1    = qbds1.addRange(fieldnum(custTable,custGroup));
    qbr1.value(queryvalue('10'));
    qbr2    =  qbds1.addRange(fieldnum(custTable,Blocked));
    qbr2.value(queryvalue(CustVendorBlocked::No));
    qbds2   = qbds1.addDataSource(tablenum(SalesTable));
    qbds2.relations(false);
    qbds2.joinMode(joinmode::ExistsJoin);
    qbds2.addLink(fieldnum(CustTable,AccountNum),fieldnum(SalesTable,CustAccount));
    queryrun    = new queryrun(query);
    while(queryrun.next())
    {
    custTable   = queryrun.get(tablenum(custTable));
    info(strfmt("%1 - %2",custtable.AccountNum,custTable.Name)); // to check your result
    }
}

Sample union query from AX 2009

Sample union query from AX 2009

Hi friends;

Queries build with the Query classes now supports unions, meaning that you can combine the result from several tables into one result set. The results you want to combine from the different tables must be structured the same way for all tables.

You could for example create a query combining CustTable and VendTable. This would be particularly useful if you need to present for example a lookup form showing both customers and vendors in the same grid. In earlier version you’d have to push customer and vendor data to a temporary table before being able to present the combined data in one grid.

Here is an example on how to build and use a union query from X++:

static void union(Args _args)
{
    Query                query;
    QueryBuildDataSource qbdsCustTable;
    QueryBuildDataSource qbdsVendTable;
    QueryRun             queryRun;
    CustTable            custVendTable;
    Map                  mapTableBranches = new Map(types::Integer, typeId2Type(typeId(TableId)));
    SysDictTable         dictTable;
    ;



    // The map is used to match the UnionBranchID with a table id
    mapTableBranches.insert(1, tableNum(CustTable));
    mapTableBranches.insert(2, tableNum(VendTable));


    query = new Query();
    query.queryType(QueryType::Union);


    qbdsCustTable = query.addDataSource(tableNum(CustTable));
    qbdsCustTable.unionType(UnionType::UnionAll); // Include duplicate records
    qbdsCustTable.fields().dynamic(false);
    qbdsCustTable.fields().clearFieldList();
    qbdsCustTable.fields().addField(fieldNum(CustTable, AccountNum));
    qbdsCustTable.fields().addField(fieldNum(CustTable, Name));


    qbdsVendTable = query.addDataSource(tableNum(Vendtable));
    qbdsVendTable.unionType(UnionType::UnionAll); // Include duplicate records
    qbdsVendTable.fields().dynamic(false);
    qbdsVendTable.fields().clearFieldList();
    qbdsVendTable.fields().addField(fieldNum(VendTable, AccountNum));
    qbdsVendTable.fields().addField(fieldNum(VendTable, Name));


    queryRun = new QueryRun(query);
    queryRun.prompt();

    while (queryRun.next()) 
    {
        custVendTable = queryRun.getNo(1);
        dictTable = SysDictTable::newTableId(mapTableBranches.lookup(custVendTable.unionAllBranchId)); 

        info (strFmt("%1 %2 (%3)", custVendTable.AccountNum,
                                   custVendTable.Name,
                                   dictTable.name()));
    }
}

- Harry

Comments in Code Editor - EditorScripts class

Comments in Code Editor (Edit  EditorScripts class)

Hi Friends,

try the following code to add your own function in Code editor.
Class Name:- EditorScripts
Method 1- To Insert Header and Footer
Add a new method and copy and paste following code, 

public void theaxapta_insert_HeaderFooter(Editor e)
{
    e.unmark();
  //  e.gotoLine(1);
   // e.gotoCol(1);

    e.insertLines('// Added or Modified on ' + date2str(today(),123,2,1,3,1,4, DateFlags::FormatAll ) + ' at ' + time2str(timenow(), 1, 1) + ' by theaxapta(' + curuserid() +') Begin:' + '\n');
    e.insertLines('// Added or Modified on ' + date2str(today(),123,2,1,3,1,4, DateFlags::FormatAll ) + ' at ' + time2str(timenow(), 1, 1) + ' by WCL(' + curuserid() +') End:' + '\n');
}

Method 2- Insert Comment

public void theaxapta_comment(Editor _editor)
{
    int startLine = _editor.selectionStartLine();
    int endLine   = _editor.selectionEndLine();
    int i;
    #define.comment('// added or modified by WCL')

    _editor.unmark();
    for (i = startLine; i <= endLine; i++)
    {
        _editor.gotoLine(i);
        _editor.gotoCol(1);
        _editor.insertString(#comment);
    }
}

Above code will available in code editor -> when u open a code editor  -> right click-> scripts-> here u find 

1. Theaxapta - Insert Header
2. Theaxapta - Comment

 Make some changes as per your requirement.
Enjoyyyyyyyyy

-Harry

Document Handling In Axapta

Document Handling In Axapta

When we create a new word document through document handling , we can transfer data from tables in Microsoft Dynamics AX to bookmarks in the new document. This is done by creating a template with some standard text and some bookmarks where the data from tables are to be inserted. For example we can transfer the address and contact information for a customer to the letter head in a Microsoft Word document when creating a new letter for that customer.
Prepare a Word template :We need to create a Word template with bookmarks where the data should be inserted. Then we set up the document type by linking the document type to the template and by adding the data fields to be transferred to the individual bookmarks.
Step I : Create a Word template
1.            Open Microsoft Office Word.
2.            In the new template, add the text and graphics we want to appear in all new documents that we base on the template.
3.            Place the cursor where we want to insert data from Microsoft Dynamics AX and then click Insert > Bookmark.
4.            Type a meaningful name for the bookmark and click Add.
5.            By Repeating step 3 & 4 you can add more bookmarks as much as you require
6.            Save this template by extension .dotx

Note : It is important to use a table where more than one row of data is needed. e.g. if we are expecting to insert more than one invoices for a vendor as shown in the above screen shot. This will help maintain the format when the data is populated by the system. Otherwise the data will be distorted.
Step B : Link the template to a document type
1.            Click Basic > Setup > Document management > Document types.
2.            Select the document type that we want to link to a template.
3.            Click Options.
4.            In the Table list, select the table to associate with the template.
5.            In the Template file list, type the path to the template file or use the browse button to locate the file on the computer or network.
Class description:             Create Word document via COM
Group:                                  Document          

Step C : Add bookmark information to table fields
1.            Click the button “Option”.. In the Options form, On overview tab give the name of the table and path of the template file
    select the table that is linked to the template that we want to use.
2.            Select the Fields tab.
3.            Press CTRL+N to add a field to be transferred.
4.            In the Data table list, select a table to get data from.
5.            In the Data field list, select the data field to get data from.
6.            In Bookmark, type the name of the bookmark we have inserted in the document where the data will be inserted.
7.            Select Hard return to insert a line break after the data inserted (optional).
8.            Repeat step 3 through 7 to add more data fields to the list.
Note : The table we get the data from does not have to match the table selected on the Overview tab, but the two tables have to be related.
                The Document handling feature can only create one letter per invoice.
On Field tab select the AX table and fields as shown below in the screen shot . For each field give the Bookmark from the template.

Step D : Test the report
Go to vendor master and select a vendor whose invoices have been  pending / on hold.
Click document handling button. Indocument handling click the Button New and select the document type created in previous Step B.

A word document will be created with data for the vendor

Verify the data on the document. Pls note that the invoice number date and amount fields are nicely aligned . If the bookmarks had not been in a table in the template then the invoice details would have been distorted. ( try using  a template with bookmarks without table and see yourself )

These small steps can reduce your new report requirement.