Axapta/D365 Corner: AX 2012

How to read excel and update record in AX2012 :

static void POCUpdatePurchaseDetails_JS(Args _args)

{

SysExcelApplication application;

SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;

SysExcelWorksheets worksheets;

SysExcelWorksheet worksheet;

SysExcelCells cells;

COMVariantType type;

Dialog dialog = new Dialog("Excel upload utility");

dialogField dialogFilename;

int row;

int totalUpdatedRecord = 0;

FileName filename;

InventTable inventTable;

ItemId itemId;

ItemGroup itemGroup;

InventTableModule inventTableModule;

;

application = SysExcelApplication::construct();

workbooks = application.workbooks();

dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen),"@SYS53125");

dialog.filenameLookupTitle("Update data from excel file");

dialog.filenameLookupFilter(["@SYS28576",'*XLSX', "@SYS28576",'*XLS']);

if (!dialog.run())

return;

filename = dialogFilename.value();

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();

row=1;

{

row++;

itemId = cells.item(row, 1).value().bStr();

ttsbegin;

inventTable = InventTable::find(itemId,true);

inventTableModule=InventTableModule::find(inventTable.ItemId,ModuleInventPurchSales::Purch,true);

if (inventTable)

{

inventTableModule.Price=str2num(cells.item(row, 2).value().bStr());

inventTableModule.PriceUnit=str2num(cells.item(row, 3).value().bStr());

inventTableModule.update();

totalUpdatedRecord++;

}

ttscommit;

type = cells.item(row+1, 1).value().variantType();

}

while (type != COMVariantType::VT_EMPTY);

application.quit();

info(strFmt('Data updated, total record=%1 record',totalUpdatedRecord));

}

How to Export Data from tables or view for Users Roles in AX 2012

static void POCCreateExcelDocument_JS(Args _args)
{
SysExcelApplication             xlsApplication;
SysExcelWorkBooks               xlsWorkBookCollection;
SysExcelWorkBook                xlsWorkBook;
SysExcelWorkSheets              xlsWorkSheetCollection;
SysExcelWorkSheet               xlsWorkSheet;
SysExcelRange                   xlsRange;
CustTable                       custTable;
int                             row = 1;
str                             fileName;

SecurityUserRole                securityUserRole;
SecurityRoleTaskGrant           securityRoleTaskGrant;
SecurityTask                    securityTask;
SecurityRole                    securityRole;
UserInfo                        userInfo;
//UserPermission                userPermission;

;
fileName = "D:\\Jit.xlsx";
xlsApplication           = SysExcelApplication::construct();
xlsWorkBookCollection    = xlsApplication.workbooks();
xlsWorkBook              = xlsWorkBookCollection.add();
xlsWorkSheetCollection   = xlsWorkBook.worksheets();
xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);
xlsWorkSheet.cells().item(row,1).value("User Role");
xlsWorkSheet.cells().item(row,2).value("User Id");
xlsWorkSheet.cells().item(row,3).value("Legal Entity");
row++;
    while select userInfo
    {

        while select securityUserRole where securityUserRole.User==userInfo.id
        {
                while select securityRole where securityRole.RecId==securityUserRole.SecurityRole
                {

                xlsWorkSheet.cells().item(row,1).value(securityRole.Name);
                xlsWorkSheet.cells().item(row,2).value(userInfo.id);
                xlsWorkSheet.cells().item(row,3).value(userInfo.company);
                row++;
                }
        }

}

if(WinApi::fileExists(fileName))
WinApi::deleteFile(fileName);
xlsWorkbook.saveAs(fileName);
xlsApplication.visible(true);
}

Error: Cannot create a record in Ledger (Ledger). Ledger name: (Company Code),(Legal Entity Name) . The record already exists."

Solution:This Errror Happen when we open GL->Setup->Ledger form after Import ledger setup or move legal entity.i just have workarround for this u need to open the ledger table. find a record with your current compnay (table have a record for each company).select correct legal entity recid in PrimaryForLegalEntity field .now open the Ledger Form it will work fine.

Error: "The X++ debugger work only for users who are in the 'Microsoft Dynamics AX Debugging Users' local group of Windows. Get added in this group, then login to Windows"

Solution:

In the following sections, you determine which AOS service account to debug the RDP class in and add the account to the Microsoft Dynamics AX Debugging Users group. You create a server configuration with debug enabled and then you can add breakpoints in your code.

To determine the AOS service account

1.From the Start menu, point to All Programs, click Administrative Tools, and then click Services.

2. In Services, right-click the Microsoft Dynamics AX Object Server service and then click Properties.

3. In the Properties window, on the Log On tab, the AOS service account is specified in the This account field.

To add the AOS service account to the debug group

1. From the Start menu, point to All Programs, click Administrative Tools, click Computer Management, and then click Local Users and Groups.

2. In Local Users and Groups, double-click Groups, right-click Microsoft Dynamics AX Debugging Users and click Add to Group.

3. In the Properties window, click Add and add the AOS service account to the group.

4. Restart the machine.

To configure for debugging

1. From the Start menu, point to Administrative Tools and then click Microsoft Dynamics AX Server Configuration.

2. In Microsoft Dynamics AX Server Configuration Utility, click the Manage button, and then click Create configuration.

3. In the Create Configuration window, enter a Configuration name like Debug and then click OK.

4. On the Application Object Server tab, select Enable breakpoints to debug X++ code running on this server and Enable global breakpoints to debug X++ code running in batch jobs.

5. Click OK and when prompted to restart the AOS service click Yes.

6. From the Start menu, point to All Programs, click the Microsoft Dynamics AX folder, and then click the Microsoft Dynamics AX Debugger.

7. From the Microsoft Dynamics AX Development Workspace, from the Tools menu, click Options. On the Development tab, click the Debug mode dropdown, and then select When Breakpoint.

Pages

AX 2012