Dynamics AX 2012
Ilegal Reference error while creating sales order lines / Purchase order lines / Transfer order lines is due to Number Sequences of InventTrans Table –> InventTransId field.
Go to Number seq – select the InventTransId sequence number , clear all numbers that are on hold. “use manual clean up button”
Before starting few configuration need to set :- 1. Create DSN : To create a Data Source Name (DSN) go to Administrative Tools > Data Sources (ODBC). click add select Sql server native client – provide server name -Next- click on change default Database and select the database “MyExternalDB”. 2. CODE
// X++, Main method in a class.
static public void Main(Args _args)
{
LoginProperty loginProperty;
OdbcConnection odbcConnection;
Statement statement;
ResultSet resultSet;
str sql, criteria;
SqlStatementExecutePermission perm;
;
// Set the information on the ODBC.
loginProperty = new LoginProperty();
loginProperty.setDSN("dsnName");
loginProperty.setDatabase("MyExternalDB");
//Create a connection to external database.
odbcConnection = new OdbcConnection(loginProperty);
if (odbcConnection)
{
sql = "SELECT * FROM MYTABLE WHERE FIELD = "
+ criteria
+ " ORDER BY FIELD1, FIELD2 ASC ;";
//Assert permission for executing the sql string.
perm = new SqlStatementExecutePermission(sql);
perm.assert();
//Prepare the sql statement.
statement = odbcConnection.createStatement();
resultSet = statement.executeQuery(sql);
//Cause the sql statement to run,
//then loop through each row in the result.
while (resultSet.next())
{
//It is not possible to get field 3 and then 1.
//Always get fields in numerical order, such as 1 then 2 the 3 etc.
print resultSet.getString(1);
print resultSet.getString(3);
}
//Close the connection.
resultSet.close();
statement.close();
}
else
{
error("Failed to log on to the database through ODBC.");
}
}
Errors :-
1. Unable to open the file
2.Target file must be in UNC format.
Since the reports runs on client . in order to generate the report via batch job .
first create a new batch job , assign the server name to the batch job.
goto Basic –>Periodic–>Batch–>Processing –> add the batch job and click ok.
In Runbase batch –> in method runsImpersonated change the return : as “false”.
Error : One or more accounting distribution is missing a ledger account or contains a ledger account that is not valid. Use the Accounting distribution form or the Posting profile to update the ledger account.
This error was occurred when we add financial dimensions to main account .
Resolve:
We have missed the setup to check the box.
General Ledger –> setup –>chart of account –> configure chart of account –> edit –> click on the dimension (filter button) ;
If u have specified the info then no need of check box , other wise click on check box ‘Allow Blank’
Shrinking the Database Log File:
1.Take complete database backup before performing any operation
First you need to take the Backup of .mdf and .ldf files, follow the below steps to complete process
2. Stop the Dynamics Ax Services:-
Start -> Run-> Type Services.msc -> Locate the Dynamics Ax Object Server -> Stop
3. Take the Database Offline Mode :
Open the SQL Server Management Studio and connect to appropriate Database Server
Right click on the database -> Tasks -> Take Offline
It will release all the resources gracefully.
4. Copy the .mdf and .ldf files :
Generally these file exist on the path C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
If you are not sure about the database location Right Click on the Database -> Properties ->Select File (Located on Left Side Corner)
Than you can find the path for both .mdf and .ldf files
5. Bring the database online Mode:
After taking the backup, we need to make the database online mode
Right Click on the Database -> Task -> Bring Online
6. Query to Shrink log database file :
Run the following Query to shrink the log database file
Note: Do not run this query until you have proper backup
USE DynamicsAXLive (.mdf file name)
GO
ALTER DATABASE DynamicsAXLive (.mdf file name)
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE(DynamicsAX_log, 5000); (log database file Name & minimum size to shrink)
GO
ALTER DATABASE DynamicsAXLive (.mdf file name)
SET RECOVERY FULL;
GO
Getting SID’s in Domain:-
type in cmd
–> wmic useraccount get name,sid /format:csv > c:\output.csv
Updating the SID’s from csv file :-
UserInfo usrInfo;
string50 userId;
Dialog dialog;
DialogField dialogFileName;
SysOperationProgress simpleProgress;
Filename filename;
CommaIo csvFile;
container readCon;
Container filterCriteria;
int numLines;
int cnt;
FileIOPermission permission;
TextIO textIO;
NoYesId Data;
textBuffer tb = new textBuffer();
#File
#avifiles
;
dialog = new Dialog(‘Select CSV File’);
dialogFileName = dialog.addField(typeid(Filenameopen), “File Name”);
filterCriteria = [‘*.csv’];
filterCriteria = dialog.filenameLookupFilter(filterCriteria);
dialog.run();
if (dialog.run())
{
filename = dialogFileName.value();
if(!filename)
info(“Filename must be filled”);
permission = new fileIOpermission(filename,#io_read);
permission.assert();
textIO = new TextIO(filename,#io_read);
if (!textIO)
throw error(“Error reading file”);
tb.fromFile(filename); //File name with Path …
numLines = tb.numLines();
csvFile = new CommaIo(filename, ‘r’);
csvFile.inFieldDelimiter(” “); // Delimiter…
simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, ‘Importing data’,1000);
try
{
if (csvFile) //Checking for csv file.
{
readCon = csvFile.read();//reading the file.
ttsbegin;
for(cnt=1;cnt<=numLines;cnt++)
{
readCon = str2Con(tb.nextToken(true));
userId = Conpeek(readCon,2); // username
select forupdate usrInfo where usrInfo.Id == userId;
if(usrInfo)
{
usrInfo.sid = Conpeek(readCon,3); // sid
usrInfo.networkDomain = Conpeek(readCon,1) ; // network domain
usrInfo.doUpdate();
info(strfmt(“the user updated are :%1”,usrInfo.id));
}
else
{
info(strfmt(“the users not updated are :%1”,Conpeek(readCon,2)));
}
}
ttscommit;
}
}
catch
{
throw error(“”);
}
}
}
SSRS REPORT : Getting multiple select lookup of company(Legal Entity) in ssrs report
Contract class :
Class declaration
[
DataContractAttribute,
SysOperationContractProcessingAttribute(classStr(MZK_DemoUIBuilder))
]
public class MZK_DemoContract implements SysOperationInitializable, SysOperationValidatable
{
FromDate dateFrom;
ToDate dateTo;
List entity;
}
Parameters :
[
DataMemberAttribute(‘FromDate’),
SysOperationLabelAttribute(literalstr(“@SYS328562”)),
SysOperationHelpTextAttribute(literalstr(“@SYS328563”)),
SysOperationGroupMemberAttribute(‘Period’),
SysOperationDisplayOrderAttribute(‘1’)
]
public FromDate parmDateFrom(fromdate _dateFrom = dateFrom)
{
dateFrom = _dateFrom;
return dateFrom;
}
[
DataMemberAttribute(‘ToDate’),
SysOperationLabelAttribute(literalstr(“@SYS328564”)),
SysOperationHelpTextAttribute(literalstr(“@SYS328565”)),
SysOperationGroupMemberAttribute(‘Period’),
SysOperationDisplayOrderAttribute(‘2’)
]
public ToDate parmDateTo(ToDate _dateTo = dateTo)
{
dateTo = _dateTo;
return dateTo;
}
[
DataMemberAttribute(‘Entity’),
AifCollectionTypeAttribute(‘ Entity ‘, Types::String),
SysOperationLabelAttribute(literalstr(“Entity “)),
SysOperationHelpTextAttribute(literalstr(“Entity”))
]
public List parmEntity(List _ entity = entity)
{
entity = _ entity;
return entity;
}
public boolean validate()
{
boolean isValid = true;
return isValid;
}
UI BUILDER CLASS :
public class MZK_DemoUIBuilder extends SysOperationAutomaticUIBuilder
{
}
public void postRun()
{
MZK_DemoContract contract = this.dataContractObject();
}
public void postBuild()
{
DialogField dialogDateFrom;
DialogField dialogDateTo;
DialogField dialogEntity;
super();
// get dialog fields
dialogDateFrom = this.bindInfo().getDialogField(this.dataContractObject(),
methodStr(MZK_DemoContract, parmDateFrom));
dialogDateTo = this.bindInfo().getDialogField(this.dataContractObject(),
methodStr(MZK_DemoContract, parmDateTo));
dialogEntity= this.bindInfo().getDialogField(this.dataContractObject(),
methodStr(MZK_DemoContract, parmEntity));
dialogEntity.registerOverrideMethod(
methodStr(FormStringControl, lookup),
methodStr(MZK_DemoUIBuilder,lookup),
this);
if (dialogEntity)
{
dialogEntity.lookupButton(2);
}
}
public void initializeFields()
{
MZK_DemoContract contract = this.dataContractObject();
}
public void getFromDialog()
{
MZK_DemoContract contract = this.dataContractObject();
super();
}
public void build()
{
Dialog dialogLocal = this.dialog();
MZK_DemoContract contract = this.dataContractObject();
dialogLocal.addGroup(“@SYS40”);
this.addDialogField(methodStr(MZK_DemoContract,parmDateFrom), contract);
this.addDialogField(methodStr(MZK_DemoContract,parmDateTo), contract);
dialogLocal.addGroup(“Entity”);
this.addDialogField(methodStr(MZK_DemoContract,parmEntity), contract);
}
public void lookupEntity(FormStringControl _formStringControl)
{
Query query = new Query();
container cnt;
query.addDataSource(tableNum(CompanyInfo)).fields().dynamic(NoYes::No);
query.dataSourceTable(tableNum(CompanyInfo)).fields().addField(fieldNum(CompanyInfo,Name));
query.dataSourceTable(tableNum(CompanyInfo)).fields().addField(fieldNum(CompanyInfo,DataArea));
SysLookupMultiSelectGrid::lookup(query,_formStringControl,_formStringControl,cnt);
}
static void CSVRead(Args _args)
{
Dialog dialog;
DialogField dialogFileName;
SysOperationProgress simpleProgress;
Filename filename;
CommaIo csvFile;
container readCon;
Container filterCriteria;
int numLines;
int cnt;
FileIOPermission permission;
TextIO textIO;
NoYesId Data;
CSVReadTable csvRead; // sample Table
textBuffer tb = new textBuffer();
#File
#avifiles
;
dialog = new Dialog(‘Select CSV File’);
dialogFileName = dialog.addField(“Filenameopen”, “File Name”);
filterCriteria = [‘*.csv’];
filterCriteria = dialog.filenameLookupFilter(filterCriteria);
dialog.run();
if (dialog.run())
{
filename = dialogFileName.value();
if(!filename)
info(“Filename must be filled”);
permission = new fileIOpermission(filename,#io_read);
permission.assert();
textIO = new TextIO(filename,#io_read);
if (!textIO)
throw error(“Error reading file”);
tb.fromFile(filename); //File name with Path …
numLines = tb.numLines();
csvFile = new CommaIo(filename, ‘r’);
csvFile.inFieldDelimiter(” “); // Delimiter…
simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, ‘Importing data’,1000);
try
{
if (csvFile) //Checking for csv file.
{
readCon = csvFile.read();//reading the file.
ttsbegin;
for(cnt=1;cnt<=numLines;cnt++)
{
readCon = str2Con(tb.nextToken(true));
csvRead.Id = Conpeek(readCon,1); //first column
csvRead.Name = Conpeek(readCon,2); //secon column
csvRead.Amount = Conpeek(readCon,3); //third column
csvRead.insert();
}
ttscommit;
info(” Records inserted”);
}
}
catch
{
throw error(“”);
}
}
}
type in cmd
–> wmic useraccount get name,sid /format:csv > c:\output.csv
this.query().dataSourceTable(tableNum(LedgerTrans)).addGroupByField(fieldid2ext(fieldNum(LedgerTrans, Dimension), 4));
Microsoft Dynamics Ax Technical Blog 