SQLConnection program in AX

server static void main(Args args)
{
Connection con = new Connection();
Statement stmt = con.createStatement();
ResultSet r;
str sql;
SqlStatementExecutePermission perm;
;

sql = strfmt('SELECT VALUE FROM SQLSYSTEMVARIABLES');

// Set code access permission to help protect the use of
// Statement.executeUpdate.
perm = new SqlStatementExecutePermission(sql);
perm.assert();

try
{
r = stmt.executeQuery(sql);
while (r.next())
{
info(Strfmt("%1", r.getString(1)));
}
}
catch (exception::Error)
{
Error("An error occured in the query.");
}
// Code access permission scope ends here.
CodeAccessPermission::revertAssert();
}

Writing values to CSV in AX

static void testWriteToCSV(Args _args)
{
#File
CommaTextIo commaTextIo;
FileIOPermission permission;
SalesTable sales;
str fileName = @"C:\MyFileName.csv";
;
permission = new FileIOPermission(fileName,#io_write);
permission.assert();
commaTextIo = new CommaTextIo(fileName,#io_write);
while select sales
{
commaTextIo.writeExp([Sales.SalesId,Sales.CustAccount]);
}
CodeAccessPermission::revertAssert();

}

How to Write/ Read the Comma seperated values to/from a text file ?

The following sample code gives idea that how to write or read the values to a text file

static void testWriteAndRead(Args _args)
{
CommaIo io;
container con;
FileIoPermission perm;
int i;
;
#define.MyFile(@"c:\MyFileName.txt")
#define.MyFileWrite("w")
#define.MyFileRead("r")

perm = new FileIoPermission(#MyFile, #MyFileWrite);
if (perm == null)
{
return;
}
// Grants permission to execute the CommaIo.new method.
// CommaIo.new runs under code access security.
perm.assert();

// Write
io = new CommaIo(#MyFile, #MyFileWrite);

con = [1,"MyText",1.324,"Last field"]; // Assign the entries in the container according to record layout.
io.writeExp(con); // write this record according to file format (record/field delimiters).
// End Write

//Read
io = new CommaIo(#MyFile, #MyFileRead);
if (io != null)
{
con = io.read();
for(i=1;i<=conLen(con);i++)
{
info(strFmt("%1",conpeek(con,i)));
}
}
//End Read
// Close the code access permission scope.
CodeAccessPermission::revertAssert();

}

Array in AX

Arrays hold the values of any single type

static void testArray(Args _args)
{
Array ary = new Array (Types::String);
int i;
;
ary.value(1, "abc");
ary.value(2, "xyz");
ary.value(5, "ijk");
ary.value(3, "lmn");

info(strfmt("%1", ary.toString()));
info(strfmt("%1", ary.definitionString()));
info(strfmt("%1", ary.lastIndex())); // Return the last index value of the Array

for(i=1;i<=ary.lastIndex();i++)
{
info(strfmt("%1", ary.value(i)));
}

}

List in AX

List is the structure which contain values of any same type, where we can access sequentially.

static void testList(Args _args)
{
List li = new List(Types::Integer);
ListEnumerator enumerator;
ListIterator lItr;
;
// Add some elements to the list
li.addStart(3);
li.addEnd(1);
li.addEnd(2);

info(strfmt("%1", li.definitionString())); // returns the "Type" of the list
info(strfmt("%1", li.toString())); // output --> <3,1,2>

enumerator = li.getEnumerator();
while(enumerator.moveNext())
{
info(Strfmt("Enumerator %1 ",enumerator.current()));
}

lItr = new ListIterator (li);
while(lItr.more()) // checks whether there are more elements in the list
{
info(strFmt("Iterator %1",lItr.value()));
lItr.next(); // Skips to the next element
}

}

Map In AX

The "Map" allows to associate one value (the key) with another value. Both the key and value can be any valid type.

static void testMap(Args _args)
{
Map m = new Map(Types::STRING, Types::INTEGER);
MapIterator mi;
MapEnumerator me = m.getEnumerator();


int i,test;
str keyId = "abc";
;

m.insert("abc", 37);
m.insert("def", 102);

i = m.lookup("abc");
test = m.exists(keyId)? m.lookup(keyId): 0;
m.insert(keyid, test + 1); // updation

if (m.exists("abc"))
info(strFmt("%1",m.lookup("abc")));


mi = new MapIterator(m);
while (mi.more())
{
info(strFmt("%1 Iterator", mi.key()));
info(strFmt("%1", mi.value()));

mi.next();
}


while (me.moveNext())
{
info(strFmt("%1 Enumertor",me.currentKey()));
info(strFmt("%1",me.currentValue()));
}

}

Set In AX

Set contains values of the same type, where value is unique and always sorted on a value

static void testSet(Args _args)
{
Set set = new Set(Types::Integer);
SetIterator sItr;
SetEnumerator sEnum ;

;
set.add(100);
set.add(101);
set.add(102);


info(strFmt("%1",set.toString()));
info(strFmt("%1",set.elements())); //No of elements
info(strFmt("%1",set.in(100))); //To see if a value already is added, use the in method:

set.remove(100);

info(strFmt("%1",set.elements())); //No of elements

// Getting values by using SetEnumerator

sEnum = set.getEnumerator();

while (sEnum.moveNext())
{
info(strFmt("%1",sEnum.current()));
}

// Getting values by using SetIterator

sItr = new SetIterator(set); // initializing set to setIterator
while(sItr.more())
{

info(strFmt("%1",sItr.value()));
sItr.next();
}


}

QueryRangeFilter in AX

static void QueryRangeFilter(Args _args)
{
Query query;
QueryBuildDataSource datasource;
QueryBuildRange range;
QueryFilter filter;
QueryRun queryRun;
int countAfterChange = 0, countBeforeChange = 0;

query = new Query();
datasource = query.addDataSource(tableNum(CustTable));
datasource = datasource.addDataSource(tableNum(SalesTable));
datasource.joinMode(JoinMode::InnerJoin);
datasource.relations(true);

datasource.addLink(fieldNum(CustTable, AccountNum),
fieldNum(SalesTable, CustAccount));

filter = query.addQueryFilter(datasource,
fieldStr(SalesTable, CurrencyCode));

filter.value(SysQuery::value('USD'));

//range = datasource.addRange(fieldNum(SalesTable, CurrencyCode));
//range.value(SysQuery::value('EUR'));

queryRun = new QueryRun(query);
while (queryRun.next())
{
countBeforeChange ++;
if (queryRun.changed(tableNum(CustTable)))
countAfterChange ++;
}

info(strFmt("CountAfterChange : %1", countAfterChange ));
info(strFmt("CountBeforeChange : %1", countBeforeChange ));
}

Nice tips by Leo Babauta to eliminate the Stress.

http://zenhabits.net/20-ways-to-eliminate-stress-from-your-life/

Confirmation message through BOX

Dialogbutton    dialogBtn;
;
dialogBtn  =   Box::yesNo("Are you sure to continue ", dialogButton::Yes, "Choose your option");
if (dialogBtn == dialogButton::Yes)
{
     info( "Process will be continued ");
}
else
if (dialogBtn == dialogButton::No)
{
      info( "Process stopped");
}

How to create a view in AX

AOT --> Data Dictionary --> View --> New View

New view --> RightClick --> Properties  ---> Provide Name , Label

2 . Select the Metadata node in the View --> Datasource --> Add Required Datasources

3 . Go to fields node of the view and create new fields .

4 . Select one of the field --> Right click --> Properties --> Provide the DataSource and field name

     Repeat same for all the fields in the View

Note  :- We can create FieldGroups and Methods if required

5 . Save the created view and Open . we can view the data in table style.

Note :- We can also add the query(AOT --> Query --> Our Own Query) to our view by draging and droping the query on to the metadata node.

Next is how can we add a View to the form ?

1 . Create a new form in the AOT --> Forms Node

2 .  Go to  Views node and select the view which we have to add to form

3 . Drag and drop the view on the Datasource node of the form and save

4 . Expand New form --> Datasource --> New View --> fields . Here you can see the list of fields which we created in view's fields node.

5 . Select the required fields , drag and drop on the design node of the form.

6. Save the form and open .. you can view the required data from different tables(Those added in View) on the same grid.

How to set a startup message for AX

1) Start  ==> ControlPanel ==>Adiministrative tools ==> Microsoft dynamics AX configuration
2) General tab , in start up message box , enter the message which we want to display ==> Ok

Infolog Msgs

SysInfologMessageStruct msgStrct;
;
msgStrct = SysInfologMessageStruct::construct("test");
info(msgStrct.message());

How to get query through code ?

static  void  TestQuery(Args _args)
{

       AifInboundPort                 aifInboundPort;
       Query                               query;
       QueryBuildDataSource     queryBDS;
       QueryRun                         qr;
        ;
       query =  new Query(queryStr("AifInboundPortQuery"));
    // queryBDS = query.dataSourceTable(tablenum(AifInboundPort)); // Optional
       qr = new QueryRun(query);
       while (qr.next())
       {
          // aifInboundPort = qr.get(tableNum(AifInboundPort));
             aifInboundPort = qr.getNo(1);
             info(aifInboundPort.AosChannelId);
       }

}

How to get the address in single line

//BP Deviation documented
display Addressing CompanyAddress()
{
    str     singleLineAddr;
    ;

    for (j=0; j<10 j="j" p="p">    {
        if (j == 0 && strLine(CompanyInfo::find().Address, j))
        {
            singleLineAddr  +=  strLine(CompanyInfo::find().Address, j);
        }
        else if (strLine(CompanyInfo::find().Address, j))
        {
            singleLineAddr  +=  ', ' + strLine(CompanyInfo::find().Address, j);
        }
        else
        {
            break;
        }
    }

    return singleLineAddr;

}

Finding primary key in a table

The following job gives the name the primary field

DictTable dictTable;

DictField dictField;

;

dictTable = new SysDictTable(tableNum(TaxTrans));

dictField = new SysDictField(dictTable.id(), dictTable.primaryKeyField());

 info(dictField.name());

How to write the customized dialog lookup for SSRS report

Following Sample code is to create customized lookup for dialog field for SSRS report filter. In previous post we used contract class and RDP class , by using this ContractUIBuilder class we can overide the dialog lookup values of Contract class  parmMethods.

class ProfitabilityLookupsUIBuilder extends SrsReportDataContractUIBuilder

{

      DialogField             dialogCategory;

      DialogGroup           dialogGroup;

      boolean                  enable;

} 

//customized lookup method for a field(Category)

private void categoryLookup(FormStringControl _categoryLookup)

{

    Query                                      query = new Query();

    QueryBuildRange                   qbr;

    SysTableLookup                         sysTableLookup;
    // SysReferenceTableLookup     sysRefTableLookup; (For Reference field lookup in AX2012)

    QueryBuildDataSource              qbdsInventTable;

    ;

  

    sysTableLookup = SysTableLookup::newParameters(tablenum(InventTable), _categoryLookup);

   //sysRefTableLookup = SysReferenceTableLookup::newParameters(tablenum(InventTable), _categoryLookup);

  

    qbdsInventTable = query.addDataSource(tableNum(InventTAble)); 

    qbdsInventTable.addRange(fieldNum(InventTable, SupplierCategory).value("");    

    sysTableLookup.addLookupfield(fieldNum(InventTable,SupplierCategory));

    sysTableLookup.parmQuery(query);

    sysTableLookup.performFormLookup();

} 

public void build()

{

    UBMProductProfitabilityContract     rdpContract =  this.dataContractObject();

 

        dialogCategory = this.addDialogField(methodstr(UBMProductProfitabilityContract,parmcategory),rdpContract);

        dialogCategory.lookupButton(2);

} 

public void postRun()

{

    Dialog dialogLocal = this.dialog();

    DialogField dialogField,dialogField1;

    super(); 

    // This method should be called in order to handle events on dialogs.

    dialogLocal.dialogForm().formRun().controlMethodOverload(false); 

    // Override the methods of category  field. 

    dialogField1 = this.bindInfo().getDialogField(this.dataContractObject(), methodstr(UBMProductProfitabilityContract, parmcategory));

    dialogField1.registerOverrideMethod(methodstr(FormStringControl, lookup), methodstr(ProfitabilityLookupsUIBuilder,  categoryLookup), this);

}

The above class should be used in contract class as following

[DataContractAttribute,

SysOperationContractProcessingAttribute(classstr(ProfitabilityLookupsUIBuilder))

]

class ProfitabilityContract  

{

      SupplierCategory  category;                     

}

Add parmcategory method as shown below

 

[DataMemberAttribute('Category')

]

public AccountNum parmCategory(SupplierCategory _category = category)

{

       category = _ category;

       return category;

} 

------------- After this as usual need to write RDP class  --------------

 

Creating new SSRS report in AX2012

New SSRS report  in AX2012

1 . Create a temp table with the required fields

2. Create a query with required datasource

3.  Create a contract class (Report filters )

class UBMGrowthNetSalesContract

{

    FromDate            fromDate,fromDate2;

    ToDate              toDate,toDate2;

    InventSiteId        inventSite;

    InventLocationId    inventLocation;

    ItemGroupId         itemGroupId;

 

}

[DataMemberAttribute("FromDate1"),SysOperationDisplayOrderAttribute("1")]

public FromDate parmFromDate(FromDate    _fromDate = fromDate)

{

  fromDate = _fromDate;

  return fromDate;

}

[DataMemberAttribute("FromDate2"),SysOperationDisplayOrderAttribute("3")]

public FromDate parmFromDate2(FromDate    _fromDate2 = fromDate2)

{

  fromDate2 = _fromDate2;

  return fromDate2;

}

[DataMemberAttribute("ToDate1"),SysOperationDisplayOrderAttribute("2")]

public ToDate parmToDate(ToDate    _toDate = toDate)

{

  toDate = _toDate;

  return toDate;

}

 

[DataMemberAttribute("ToDate2"),SysOperationDisplayOrderAttribute("4")]

public ToDate parmToDate2(ToDate    _toDate2 = toDate2)

{

  toDate2 = _toDate2;

  return toDate2;

}

[DataMemberAttribute("Item Group"),SysOperationDisplayOrderAttribute("5")]

public ItemGroupId parmItemGroup(ItemGroupId    _itemGroupId = itemGroupId)

{

  ;

  itemGroupId = _itemGroupId;

  return itemGroupId;

}

[DataMemberAttribute("Warehouse"),SysOperationDisplayOrderAttribute("7")]

public InventLocationId parmLocationId(InventLocationId    _inventLocation = inventLocation)

{

  inventLocation = _inventLocation;

  return inventLocation;

}

[DataMemberAttribute("Site"),SysOperationDisplayOrderAttribute("6")]

public InventSiteId parmSiteId(InventSiteId    _inventSite = inventSite)

{

  inventSite = _inventSite;

  return inventSite;

}

 

4 . Create an RDP class with the required bussiness logic

 

[ SRSReportQueryAttribute (querystr(UBMGrowthNetSales)),

SRSReportParameterAttribute(classstr(UBMGrowthNetSalesContract))

]

class UBMGrowthNetSalesRDP extends SRSReportDataProviderBase

{

  UBMGrowthNetSales uBMGrowthNetSales;

}

 

 

 

[SRSReportDataSetAttribute("UBMGrowthNetSales")]

public ubmGrowthNetSales getUBMGrowthNetSales()

{

    select * from uBMGrowthNetSales;

    return uBMGrowthNetSales;

}

 

 

 

[SysEntryPointAttribute(false)]

public void processReport()

{

    QueryRun                                      queryRun1,queryRun2,queryRun3;

    Query                                             query1,query2,tableQuery,transQuery1,transQuery2;

    UBMGrowthNetSalesContract       ubmGrowthNetSalesContract;

    QueryBuildDataSource            queryBDSTable,qbdsInventTable,qbdsTable,qbdsTrans,qbdsTrans1,qbdsinventDim1,qbdsInventDim2;

    QueryBuildDataSource                  qbdsItemGroup;

    QueryBuildRange                          qbrItem;

    TransDate                                      _toDate,_fromDate,_toDate2,_fromDate2;

    ItemId                                             item,_itemId;

    InventSiteId                                    inventSite;

    InventLocationId                             inventLocationId;

    InventTable                                    inventTable;

    InventTrans                                   inventTrans1,inventTrans2;

    LineAmount                                   lineAmt,lineAmt1;

    InventDim                                      inventDim1,inventDim2;

    InventItemGroupItem                     itemGroupTable;

    ItemGroupId                                  itemGroup;

 

    ;

  //breakpoint;

    ubmGrowthNetSalesContract = this.parmDataContract() as UBMGrowthNetSalesContract;

 

    _toDate             = ubmGrowthNetSalesContract.parmToDate();

    _fromDate           = ubmGrowthNetSalesContract.parmFromDate();

    _toDate2            = ubmGrowthNetSalesContract.parmToDate2();

    _fromDate2          = ubmGrowthNetSalesContract.parmFromDate2();

    inventSite          = ubmGrowthNetSalesContract.parmSiteId();

    inventLocationId    = ubmGrowthNetSalesContract.parmLocationId();

    itemGroup           = ubmGrowthNetSalesContract.parmItemGroup();

 

    query1 = this.parmQuery();

    queryBDSTable = query1.dataSourceTable(tableNum(InventTable));

    qbrItem = queryBDSTable.findRange(fieldNum(InventTable,ItemId));

    item = qbrItem.value();

 

    tableQuery = new Query();

 

    qbdsInventTable = tableQuery.addDataSource(tableNum(Inventtable));

    qbdsInventTable.addRange(fieldNum(inventTable,ItemId)).value(item);

 

    qbdsItemGroup   = qbdsInventTable.addDataSource(tableNum(InventItemGroupItem));

    qbdsItemGroup.joinMode(JoinMode::ExistsJoin);

    qbdsItemGroup.addLink(fieldNum(InventTable,ItemId),fieldNum(InventItemGroupItem,ItemId));

       qbdsItemGroup.addRange(fieldNum(InventItemGroupItem,ItemGroupId)).value(queryValue(itemGroup));

 

    queryRun1 = new QueryRun(tableQuery);

    while(queryRun1.next())

    {

        inventTable = queryRun1.get(tableNum(inventTable));

 

        ubmGrowthNetSales.ItemId        = inventTable.ItemId;

        ubmGrowthNetSales.ItemName      = inventTable.itemName();

        ubmGrowthNetSales.FromDate      = _fromDate;

        ubmGrowthNetSales.FromDate2     = _fromDate2;

        ubmGrowthNetSales.ToDate        = _toDate;

        ubmGrowthNetSales.ToDate2       = _toDate2;

        //Lineamt value calculation

        transQuery1 = new Query();

        qbdsTrans = transQuery1.addDataSource(tableNum(inventTrans));

        qbdsTrans.addRange(fieldNum(Inventtrans,ItemId)).value(inventTable.ItemId);

        qbdsTrans.addRange(fieldNum(inventTrans,DatePhysical)).value(queryRange(_fromDate,_toDate));

 

        qbdsinventDim1 = qbdsTrans.addDataSource(tableNum(InventDim));

        qbdsinventDim1.joinMode(JoinMode::ExistsJoin);

        qbdsinventDim1.addLink(fieldNum(InventTrans,InventDimId),fieldNum(InventDim,inventDimId));

        qbdsinventDim1.addRange(fieldNum(InventDim,inventLocationId)).value(inventLocationId);

        qbdsinventDim1.addRange(fieldNum(InventDim,inventSiteId)).value(inventSite);

 

 

        queryRun2 = new QueryRun(transQuery1);

        while(queryRun2.next())

        {

            inventTrans1 = queryRun2.get(tableNum(InventTrans));

            lineAmt += inventTrans1.lineAmount();

        }

        ubmGrowthNetSales.NetSales1 =  lineAmt;

 

         // End of - Lineamt value calculation

 

        // LineAmt1 value calculation

        transQuery2 = new Query();

        qbdsTrans1 = transQuery2.addDataSource(tableNum(inventTrans));

        qbdsTrans1.addRange(fieldNum(Inventtrans,ItemId)).value(inventTable.ItemId);

        qbdsTrans1.addRange(fieldNum(inventTrans,DatePhysical)).value(queryRange(_fromDate2,_toDate2));

 

 

        qbdsinventDim2 = qbdsTrans1.addDataSource(tableNum(InventDim));

        qbdsinventDim2.joinMode(JoinMode::ExistsJoin);

        qbdsinventDim2.addLink(fieldNum(InventTrans,InventDimId),fieldNum(InventDim,inventDimId));

        qbdsinventDim2.addRange(fieldNum(InventDim,inventLocationId)).value(inventLocationId);

        qbdsinventDim2.addRange(fieldNum(InventDim,inventSiteId)).value(inventSite);

 

 

        queryRun3 = new QueryRun(transQuery2);

        while(queryRun3.next())

        {

            inventTrans2 = queryRun3.get(tableNum(InventTrans));

            lineAmt1 += inventTrans2.lineAmount();

        }

        ubmGrowthNetSales.NetSales2 =  lineAmt1;

 

        // End of - Lineamt1 value calculation

 

        if (lineAmt)

        {

            ubmGrowthNetSales.GrowthPercentage = ((lineAmt1 - lineAmt) / lineAmt) * 100;

 

        }

        lineAmt  =0;

        lineAmt1 =0;

        ubmGrowthNetSales.insert();

        ubmGrowthNetSales.clear();

 

    }

}

 

 

5.   Create required design format .

6.   Add to AOT

7 .  Build and deploy the report