A practitioner’s musings on Dynamics 365 Finance and Operations
Today I was facing the same Issued, I added a few custom fields in AX table. After update data source and dropped custom field at required Place on form. When I run the form, I found that field showed as un-editable and unretrievable written there.
I found that it is cache problem. It solved by click on Refresh element cache. You can found the menu From Tool Menu, Caches and sub menu element Cache,
If problem not solved by this way then restart the Dynamics Ax 2012 service from services.
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;
When we are working with Document Aif services, any exception is logged in SysExceptionTable. You can excess these details from following link.
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();
}
}
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”);}
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.");
}
}
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.");
}
Consider a scenario where, I required a StringEditControl and Clicking on Control a lookup is open where the list of Customer of selected Legal Entity are available. And After Selecting required Customer, Customer Id Or AccountNum is return form Lookup form.
For This purpose we have to create form, which will used as look Up.
Create a New form. Name It “FormCustomerLookUp”
In Data Source Create and New dataSource Name It CustomerTable and Point to CustTable.
Expand the Design node and Right Click on It and Add Grid, Now from CustomerTable Data Source, Drag and drop “AccountNum” and “Party” on Grid. It will create two String Edit Control on Grid bound with CustomerTable.
Now Select on “StringEdit:CustomerTable_AccountNum” select Properties. Form Properties window Set Its Auto declaration property to True.
Now expend the Methods Node form and Right click and override the Init method from there and add following line of code there.
element.selectMode(CustomerTable_AccountNum);
Now right click on Design Node and Update following properties.
Width=360
Style=LookUP.
Now Create a form where this lookup will use. For this Article I have to create very simple form with only on stringEdit textbox.
On its Design Right click and create StringEditButton with Name “LookUpTest” and from Its property window Auto Declare Property is set to Yes and lookupButton property to “Always”.
Now Add two method, One used to Register control with Lookup, and Second method will use to call lookup form and gets its value.
protected void configureCustomerLookUp(FormStringControl stringControl)
{
if (stringControl)
{
stringControl.visible(true);
stringControl.registerOverrideMethod(
methodStr(FormStringControl, lookup),
identifierStr(CustomerLookUp), this);
}
}
Second method will be called on lookup button click
private void CustomerLookUp(FormStringControl stringControl)
{
Args args = new Args(identifierStr(FormCustomerLookUp));
FormRun lookupFormRun;
args.parm(“”);
args.caller(this);
lookupFormRun = ClassFactory::formRunClassOnClient(args);
lookupFormRun.init();
stringControl.performFormLookup(lookupFormRun);
lookupFormRun.wait();
}
If you have already some knowledge above code is easily Understanable, where Ax form Name set in args and instance of from is created at runtime and then attached the form to string controls performLookUp method.
Now run the form. And click on lookup button on textbox.
Click on LookUp Button
Selected Value will show on text box when you click on lookup grid
Nice Experiments, Now you can use This functionality as you want,
We can get standard sql from AOT Query object through X++ code.
Following X++ Code used to get Standard Sql out of EcoResProductListPage
static void Job2(Args _args)
{
Query query;
query = new Query(queryStr(EcoResProductListPage));
info(query.dataSourceNo(1).toString());
}
In Dynamics Ax List is used as collection, You can add , remove or update elements in it. Type of list is define at the time of its declaration and cannot be change after initialization.
We can iterate in List with the help of ListIterator object, which have several methods to process on list.
List intList = new List(Types::Integer);
List strList = new List(Types::String);
ListIterator literator
// add the element at the end of the list
intList.addEnd(123);
// add the element at the start of the list
intList.addStart(321);
intList.addEnd(300);
strList.addEnd ("Raza");
strList.addStart ("Ali");
strList.addStart ("Zaidi");
// If you want to insert the data at some specific index, then you need to make use of the listIterator class
// Iterator performs a midpoint
// insert at current position.
literator = new ListIterator(strList);
while (literator.more())
{
// can provide some condition, i.e. if check etc
if (literator.value() == "Ali")
{
listIterator.insert ("Between Raza and Zaidi");
}
}
Hi, You can get Count X++ select statement as
/// <summary>
/// Get resource poool count.
/// </summary>
/// <returns>
/// resource pool count.
/// </returns>
protected int getResourcePoolCount()
{
select count(Rank) from resourceTable
where resourceTable.UserSession == _userSession
&& resourceTable.ResourceSet == ProjResourceSet::Pool;
return resourceTable.Rank;
}