Many times we have to generate and assign sequence number in X++. And usually we find attached sequence number form Parameter tables.  We can get customer through following X++ code

 

NumberSeq     _sequence;

ttsBegin;

 

_sequence = NumberSeq::newGetNum( CustParameters::numRefCustAccount());

    info((_sequence.num()));

info(_sequence.Num());

    info((_sequence.num()));

 

ttsCommit;

 

But sometimes these reference methods did not available in these Parameters table. For these fields you can get sequence number with reference to extended data type. For example if you have to find next value for CustInvoiceId of Free text Invoice of customer you have check the extended data type of field. And then get the next number with reference to Extended Data Type

 

NumberSeq     _sequence;

ttsBegin;

_sequence = NumberSeq::newGetNum( NumberSeqReference::findReference(extendedTypeNum(CustInvoiceId)));

info((_sequence.num()));

ttsCommit;

 

{ 0 comments }

During creating and assigning Sequence number in X++ code, I found this error continuously. Later i found that getting and assigning next sequence number should be  written inside transaction block. for example

ttsbegin;
itemId = NumberSeq::newGetNum(InventParameters::numRefItemId()).num();
ttscommit;

{ 0 comments }

When we are working with Document Aif services, any exception is logged in SysExceptionTable. You can excess these details from following link.

Exceptions

There is client requirement that all exception must be log in SysExceptiontable in custom service. I did this with the help of following code snippets which just log the division by Zero exception in SysExceptionTable.

 

 

static void Job19(Args _args)

{

 

InfologData         infoData;

AifInfoLog          aifInfoLog;

container           infologData;

SysInfoLogEnumerator            infoLogEnum;

SysInfologMessageStruct         infoMessageStruct;

 

SysExceptiontable _Excep;

str  ExceptionMessage;

int ab=0;

int test=10;

int result;

 

try

{

aifInfoLog = new AifInfoLog();

result = test / ab;

 

 

}

catch

{

infologData = aifInfoLog.getInfoLogData();

infoLogEnum = SysInfoLogEnumerator::newData(infologData);

while(infoLogEnum.moveNext())

{

//Extract the message from the string

infoMessageStruct = SysInfologMessageStruct::construct(infoLogEnum.currentMessage());

ExceptionMessage +=”\n” + infoMessageStruct.message();

 

 

}

_Excep.Exception =Exception::Error;

_Excep.Description = ExceptionMessage;

_Excep.Module =”Mamoo’s Test”;

_Excep.insert();

 

 

}

 

}

 

Sysexceptiontable

 

 

{ 0 comments }

 

In X++ we can use System.Data.SqlClient.SqlReader or System.Data.DataSet, to Query on external database. But unfortunately there is No isDBNull function available for checking the null and same time system.string().isnullorempty and CLRInterop::Null is not work.

 

If you are using dataset or datatable then use datarow’s is null method

For example

 

If (!DataRow.isNull(“FirstName”)

{

Info(DataRow.get_Item(“FirstName”);

}

 

Second way to use the index of column according to field name in query, It is zero based index

If (!DataRow.isNull(0)

{

Info(DataRow.get_Item(“FirstName”);

}

 

If use data reader then use data reader is dbnull method, this method takes the index of field, there is no overload method for field Name,

 

int columnNum = datareader.GetOrdinal(“FirstName”);
if (datareader.IsDBNull(columnNum))
{
Info(datareader.get_item(“FirstName”);

}

{ 0 comments }

execute stored procedure with parameters dynamics ax 2012

by alirazazaidi on October 20, 2014

During in one of my assignment, I have to call sql server stored procedure from X++ code. There is little tricky to call stored procedure with parameters. Following code will help me to call stored procedure with Parameters. Consider sp_StudentCreates a stored procedure with three parameters, first two string and last one is date.

private void InsertStudent( StudentDC  _dc)
{
    LoginProperty loginProperty;

OdbcConnection odbcConnection;

Statement statement;

ResultSet resultSet;

LoginProperty myLoginProperty;

str sql, criteria;

int output;

SqlStatementExecutePermission perm;

str myUserName="dynamicworld\\aliraza.zaidi";

str myPassword="xyz";

str myConnectionString;

myConnectionString=strfmt("UID=%1;PWD=%2",myUserName,myPassword);

myLoginProperty = new LoginProperty();

myLoginProperty.setServer("dynamicworld.com");

myLoginProperty.setDatabase("studentDb");

myLoginProperty.setOther(myConnectionString);



//Create a connection to external database.

odbcConnection = new OdbcConnection(myLoginProperty);



if (odbcConnection)

{



    sql = "Execute sp_StudentCreates   '"+_dc.parmFirstName())+"','"+_dc.parmLastMame())+"','"+date2str(_dc.parmDate(),321,DateDay::Digits2,DateSeparator::Hyphen,DateMonth::Digits2,DateSeparator::Hyphen,DateYear::Digits4)+"'";
 info(sql);

//Assert permission for executing the sql string.

perm = new SqlStatementExecutePermission(sql);

perm.assert();



//Prepare the sql statement.

statement = odbcConnection.createStatement();

output = statement.executeUpdate(sql);






statement.close();

}

else

{

error("Failed to log on to the database through ODBC.");
}

}

{ 0 comments }

Connect to an external Database from Dynamics Ax 2012

by alirazazaidi on October 18, 2014

Sometimes we have to communicate with outside of Dynamics Ax with in boundaries of Dynamics ax.

Consider following scenario where I have to communicate with StudentDb in Sql server and insert rows in Student Info. We can do this with odbc connection.

 

 

Now create New AX job and paste following code there

 

 

LoginProperty loginProperty;

OdbcConnection odbcConnection;

Statement statement;

ResultSet resultSet;

LoginProperty myLoginProperty;

str sql, criteria;

int output;

SqlStatementExecutePermission perm;

str myUserName="dynamicworld\\aliraza.zaidi";

str myPassword="abcd";

str myConnectionString;

 

;

 

 

 

 

myConnectionString=strfmt("UID=%1;PWD=%2",myUserName,myPassword);

myLoginProperty = new LoginProperty();

myLoginProperty.setServer("WIN-IKPOSIU2SGD");

myLoginProperty.setDatabase("studentdb");

myLoginProperty.setOther(myConnectionString);

 

//Create a connection to external database.

odbcConnection = new OdbcConnection(myLoginProperty);

 

if (odbcConnection)

{

 

sql ="INSERT INTO ..[StudentInfo]([FirstName],[LastName]) VALUES ('aliraza','zaidi')";

//Assert permission for executing the sql string.

perm = new SqlStatementExecutePermission(sql);

perm.assert();

 

//Prepare the sql statement.

statement = odbcConnection.createStatement();

output = statement.executeUpdate(sql);

 

 

statement.close();

}

else

{

error("Failed to log on to the database through ODBC.");
}




{ 0 comments }

During exploring the question posted on Microsoft community AX fourm, I found about a global method, which converters numeric values into words.

10-15-2014 12-05-28 AM
static void Job_AmountInWords(Args _args)

{

info(numeralsToTxt(420.420));

}

 

{ 0 comments }

During SSRS report for Dynamics Ax 2012, I found that when report goes on multiple pages, column header did not appear all page except the first one.

I fix this problem by setting some properties to true.

 

Select the tablix and below right side of columns group header and click there on small arrow head and select the advance Mode.

10-13-2014 11-03-17 AM

 

This will open the row group  header and column groups like below picture.

10-13-2014 11-06-34 AM

 

Now right click on top static in row groups, or Press F4 to open the properties window.

 

 

From property window, set propertyRepeatOnNewPage to True. Also set KeepWithGroup property to After

 

10-13-2014 11-06-24 AM

 

. When you deploy the report. Column header will appear on each page.

{ 0 comments }

Recently installed the SSRS report extension on my VM Dynamics Ax 2012 R2 CU7. During installation, I continuously found following error message In log files.

 

“Unable to find appropriate service endpoint information in the configuration object”

Later I understand that SSRS reports communicate with AOS through WCF services and WCF service for Dynamics Ax was not running on AOS.

When I opened the Application Integration Framework è Inbound port, I found that BI service was deactivated.

10-12-2014 5-58-16 PM

 

I activate the service, and retry SSRS reporting extension installation, which installed successfully.

 

10-12-2014 11-13-28 PM

{ 0 comments }

Vendor Reasons Lookup

by alirazazaidi on October 12, 2014

In Dynamics Ax 2012, you can create Vendor Reasons, These reasons helps to describe codes used to against the vendor, vendor transaction and vendor task. A reason code is used to justify the why change or transaction occurs.

You can find Vendor reasons in Account Reasonable Area Page.

10-12-2014 11-10-47 AM

 

 

You can add vendor reason from Vendor Reason Form

 

VendorReason

 

If you go AOT you find that Data store in ReasonTable

{ 0 comments }