Dynamics AX tips, tricks and everything

Error Installing Management Reporter 2012

2012-12-20T13:54:00.000+08:00

I've been experiencing installation issue of Management Reporter 2012 for MS Dynamics AX 2012 for quite few weeks. I've goggled every MSDN forum I can find but this dynamics community forum helps me to resolve this issue in a quick easy way.

Upon configuring the Management Reporter 2012, it keeps on saying that the user doesn't have credentials and is not a valid user. See below resolution coming from the dynamics community forum.

A few things for you to check.

1. Verify if you are deploying using the domain\administrator that the "Display Name" in active directory is set, and not blank.

2. Try deploying an app and process service first. If it fails, please close the config console and reopen. Then click on Deployment logs and attach the log or copy/paste into your thread so we can see the error you are hitting.

Management Reporter 2012 does not use IIS, so no website will be created. You should see Windows Services and a SQL Server database.

For more AX tips and tricks, feel free to browse some of my articles.

Link for Settled Invoices in AX 2009

2012-09-23T08:23:00.002+08:00

It is easy to create a report of Vendor Invoices with particular Payments done if this transaction is posted. You'll gonna use LastSettlementVoucher in the VendTrans to pair an invoice to its payment.

What if create a report which invoices has been marked for payment but not yet posted? Since the payment is not posted, the VendTrans has an empty LastSettlementVoucher field.

In order to do this, there is a table, VendTransOpen, that stores information which invoices has been marked for settlement.

Here is the code:

Select the reference id of VendTransOpen

SELECT VendTransOpen

JOIN SpecTrans

WHERE SpecTrans.RefCompany == VendTransOpen.DataAreaId

&& SpecTrans.RefTableId == VendTransOpen.TableId

&& SpecTrans.RefRecId == VensTransOpen.RecId;

Select the correspoding invoice marked

SELECT VendTrans

WHERE VendTrans.RecId == VendTransOpen.RefRecId;

For more AX tips and tricks, feel free to browse some of my articles.

Field That Could Link Purchase Order (PO) and Request For Quote (RFQ)

2012-09-23T07:27:00.002+08:00

If you are creating a report and couldn't find a way to connect this two processes, Purchase Order (PO) and Request For Quote (RFQ), then you are in the right blog. Here it is!

In the PurchTable table where the header of purchase order resides, there is a field 'PurchId' which you can link to PurchRFQLine that has a field of 'PurchId' also. It generates purchase order number once the item requested is awarded to the vendor.

PurchTable.PurchId == PurchRFQLine.PurchId

For more AX tips and tricks, feel free to browse some of my articles.

Read/Write to Excel using X++

2012-07-01T21:25:00.002+08:00

Here is the simplest way to read/write in excel using X++. You can also view my previous blog about creating an excel file using X++.

//Write to Excel File using X++
static void Write2ExcelFile(Args _args)
{
InventTable inventTable;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
;

application = SysExcelApplication::construct();
workbooks = application.workbooks();
workbook = workbooks.add();
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
cells.range('A:A').numberFormat('@');

cell = cells.item(1,1);
cell.value("Item");
cell = cells.item(1,2);
cell.value("Name");
row = 1;
while select inventTable
{
row++;
cell = cells.item(row, 1);
cell.value(inventTable.ItemId);
cell = cells.item(row, 2);
cell.value(inventTable.ItemName);
}
application.visible(true);
}

//Read an Excel File using X++
static void ReadExcel(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row;
ItemId itemid;
Name name;
FileName filename;

;

application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
filename = "C:\\item.xls";
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}

workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
do
{
row++;
itemId = cells.item(row, 1).value().bStr();
name = cells.item(row, 2).value().bStr();
info(strfmt('%1 - %2', itemId, name));
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}

For more AX tips and tricks, feel free to browse some of my articles.

Create an Excel File using X++

2012-07-01T21:05:00.000+08:00

Creating an excel file is essential mostly in data import or export. Microsoft Dynamics AX has a feature to import and export through excel template. But in some cases, you need to create an excel to integrate with other application. Below is an example of creating an excel file in X++ using an existing table in AX.

static void ExportToExcel(Args _args)
{
#AviFiles
SysOperationProgress progress = new SysOperationProgress();
SysExcelApplication sysExcelApplication;
SysExcelWorkbooks sysExcelWorkBooks;
// Filename to which you will be writing your data
FileName fileName = "C:\\Windows\\Temp\\ExportToExcel.xlsx";
SysExcelWorkbook sysExcelWorkBook;
SysExcelWorkSheets sysExcelWorkSheets;
SysExcelWorkSheet sysExcelWorkSheet;
SysExcelWorkSheet sysExcelWorksheetBackOrder;
SysExcelWorksheet sysExcelWorkSheetToBeDeleted;
SysExcelStyles styles;
SysExcelStyle style;
SysExcelFont font;
SysExcelCells cells;
int row = 1;
int rowBackOrder;
CustTable custTable;
SalesTable salesTable;
SalesLine salesLine;
boolean workSheetAdded = false;
int nbrOfCustomers;
;

// Initialising progress bar
progress.setCaption("Export To Excel in progress...");
progress.setAnimation(#AviTransfer);
// Initialisation of some objects
sysExcelApplication = SysExcelApplication::construct();
// Create new workbook
sysExcelWorkBooks = sysExcelApplication.workbooks();
sysExcelWorkBook = sysExcelWorkBooks.add();
// Create new style
styles = sysExcelWorkBook.styles();
style = styles.add("Header");
// Set font for this style to bold and color to red
font = style.font();
font.bold(true);
font.color(255);
// Get worksheets collection
sysExcelWorkSheets = sysExcelWorkbook.worksheets();
// Excel visible on desktop running the job or not?
sysExcelApplication.visible(false);
// Newly created Excel files have by default some worksheets
// Delete those worksheets created by default
while(sysExcelWorkSheets.count() > 1)
{
sysExcelWorkSheetToBeDeleted = sysExcelWorkSheets.itemFromNum(2);
sysExcelWorkSheetToBeDeleted.delete();
}
// Add as many worksheets as there are customers
select count(RecId) from CustTable;
sysExcelWorkSheet = sysExcelWorkSheets.add(null,null,CustTable.RecId);
// Add another worksheet
sysExcelWorkSheet = sysExcelWorkSheets.add();
//Rename the first worksheet
sysExcelWorkSheet.name("Customers");
// Make a title row
// set a value in cell on row 1 column 1
sysExcelWorkSheet.cells().item(1,1).value("Customer account");
// set a value in cell on row 1 column 2
sysExcelWorksheet.cells().item(1,2).value("Name");
// set the title row in the Header style
sysExcelWorksheet.rows().item(1).style("Header");
while select custTable
{
progress.setText(strfmt("Customer %1", custTable.Name));
row++;
rowBackOrder = 1;
sysExcelWorksheet.cells().item(row,1).value(custTable.AccountNum);
sysExcelWorksheet.cells().item(row,2).value(custTable.Name);
while select salesLine
where salesLine.SalesStatus == salesStatus::Backorder
&& salesLine.ConfirmedDlv < Today()
&& salesLine.RemainSalesPhysical > 0
join salesTable
where salesTable.SalesId == salesLine.SalesId &&
salesTable.CustAccount == custTable.AccountNum
{
if(!workSheetAdded)
{
// Use the next Excel worksheet and rename it
sysExcelWorksheetBackOrder = sysExcelWorkSheets.itemFromNum(
row);
//Name of worksheet can have maximum 31 characters
sysExcelWorksheetBackOrder.name(substr(custTable.Name,1,31));
workSheetAdded = true;
// Make a title row
// set a value in cell on row 1 column 1
sysExcelWorksheetBackOrder.cells().item(1,1).value(
"Ship Date");
// set a value in cell on row 1 column 2
sysExcelWorksheetBackOrder.cells().item(1,2).value(
"Item Number");
// set a value in cell on row 1 column 3
sysExcelWorksheetBackOrder.cells().item(1,3).value(
"Item Name");
// set a value in cell on row 1 column 4
sysExcelWorksheetBackOrder.cells().item(1,4).value(
"Deliver Remainder");
// set the title row in the Header style
sysExcelWorksheetBackOrder.rows().item(1).style("Header");
}
rowBackOrder++;
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,1).value(
salesLine.ConfirmedDlv);
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,2).value(
salesLine.ItemId);
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,3).value(
InventTable::find(salesLine.ItemId).ItemName);
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,4).value(
salesLine.RemainSalesPhysical);
}
// Set the column width to autofit
sysExcelWorksheetBackOrder.columns().autoFit();
// Format the 4th column as Number with 2 decimal places
cells = sysExcelWorksheetBackOrder.cells();
cells.range("D2:D99").numberFormat("0,00");
workSheetAdded = false;
}
// Set the column width to autoFit
sysExcelWorksheet.columns().autoFit();
// Suppress the pop-up window:
// A file named foo already exists in this location. Do you want to replace it?
sysExcelApplication.displayAlerts(false);
// Save the Excel file
sysExcelWorkbook.saveAs(fileName);
sysExcelWorkBook.comObject().save();
sysExcelWorkBook.saved(true);
// Make sure you close the Excel application
// Especially if you run the job without showing Excel on the desktop
// (sysExcelApplication.visible(false))
sysExcelApplication.quit();

}

For more AX tips and tricks, feel free to browse some of my articles.

How to Transfer Fixed Asset and its Transactions To a New Value Model

2012-06-09T01:26:00.000+08:00

This contains process on how to transfer fixed asset and its transactions to a new value model. This AX blog will give step by step procedure on how to do it.

Below are the steps:

1. Create a manual depreciation profile in General Ledger > Setup > Fixed Asset > Depreciation Profile. This is a sample depreciation in this process.

2. Attached the depreciation profile to a value model

3. In the General tab, ensure that the "Allow net book value higher than acquisition cost" and "Allow negative net book value".

4. Create an asset group that will carry the depreciation profile in General Ledger > Setup > Fixed Asset > Fixed Asset Group.

5. Click the Value Model button and attached the following value model created above.

6. Proceed with FA transactions such as Acquisition and depreciation involving the created Value Model

When an asset is subject for transfer to other company in the middle of the depreciation, the asset must have a different depreciation profile.

These are the things to be setup when an asset is transferred:

1. Select the fixed asset created and click the Value Model button.

2. Add the Value Model that carries the remaining life of the asset. For example, if the asset has a six years of life and transferred in the third year, the depreciation profile of the asset must be three years only.

3. Use the Split Fixed Asset function

4. Click Functions button > Split Fixed Asset and fill in the following:

To fixed asset : USE THE SAME FIXED ASSET NUMBER
To value model : USE THE NEW VALUE MODEL WHICH HAS REMAINING LIFE
Percent : INDICATE 100
Name : USE THE FA TRANSFER JOURNAL

5. An infolog would appear stating that the transactions has been transferred.

6. Go to General Ledger > Journals > Fixed Assets to retrieve the created journals.

7. The Journals that were created are the Acquisition and the accumulated depreciation that will be transferred to the new value model

8. Proceed on Posting the Journal

9. General Ledger > Common Forms > Fixed Asset Details

10. Select the Fixed Asset

11. Click the Value Model button

12. Select the original value model

13. Click the Inquiry button > Balances. Note that the Net Book Value is now Zero.

14. Close the form.

15. Select the newly created value model then click Inquiry button > Balances.

16. Note that the selected value model has a netbook value.

17. Close the form then select the old value model

18. Set the value model status to "Closed".

19. Click Save

20. The next time you do a transaction on the fixed asset, it will use the new value model.

For more AX tips and tricks, feel free to browse some of my articles.

Get Current Company in AX 2009

2012-06-09T00:10:00.000+08:00

Below is the example on how to get current company in AX 2009.

In AOT > Jobs, paste the following code:

static void curExtExample(Args _arg)

{

str curCompany;

;

// Sets curCompany to the extension of the current company.

curCompany= curExt();

print "Current extension is " + curCompany;

pause;

}

For more AX tips and tricks, feel free to browse some of my articles.

Hide Transaction Using Posting Layer

2012-05-28T01:50:00.000+08:00

POSTING LAYER

This AX blog consist of procedure and sample on how to hide transactions using posting layer in AX 2009. Not really hiding but for internal purposes use for detailed reports and so on.

Here is the guidelines.

1. Go to General Ledger > Setup > Journals > Journal Names

2. In the General Tab > Posting Section, select Posting Layer as Operations

You can now generate trial balance by indicating only transactions posted in Operation Layer in General Ledger > Reports > Transactions > Periodic > Trial Balance.

For more AX tips and tricks, feel free to browse some of my articles.

Related Field in RFQ and PO in AX 2009

2012-05-28T01:48:00.000+08:00

I've been given a task to create a report listing all purchase order created in purchase requisition module of AX 2009. Thank God I've found an AX blog that points a field that can a link between the two tables. For inventory items, it is so hard to find relating tables and field.

In the PurchRFQLine Table, there is a field PurchId that you can use in joining with PurchTable with the same field as PurchId.

For more AX tips and tricks, feel free to browse some of my articles.

Error in Intercompany Bank Transaction

2012-05-28T01:47:00.000+08:00

We have a problem in one of our client in terms of intercompany bank transactions. This is so particular that we've been crazy for weeks solving this problem. The error is this, "Account number for transaction type Bank does not exist." We thought this is a license issue but it is not! I created this AX blog because of this. This was very helpful.

When we check the Bank details, the ledger account is already setup but the error still exist.

This is how our Microsoft partner did. They have identified that the Extended Data Type for Ledger Account is about 20 characters and for the Bank Account Id is 10 characters. The application cannot read the ledger account of the Bank Account that is why AX 2009 is throwing an error. To resolve this, they adjust the character of Bank Account Id same as to the Ledger Account. After this, the transaction can be now posted.

For more AX tips and tricks, feel free to browse some of my articles.