update_recordset [AX 2012]

Example 1: Assigning from a Math Expression

MyTable myTableBuffer;
;
update_recordset myTableBuffer
setting field1 = field1 * 1.10;

Example 2: Using a Where Clause
MyTable myTableBuffer;
;
update_recordset myTableBuffer
setting
    field1 = 1,
    field2 = fieldX + fieldY
where field1 == 0;
Example 3: Joining Tables in an Update
static void Join22aJob(Args _args)
{
    TableEmployee tabEmpl;
    TableDepartment tabDept;
    TableProject tabProj;
    ;
    update_recordset tabEmpl
    setting
        currentStatusDescription = tabDept .DeptName
            + ", " + tabProj .ProjName
    join tabDept
        where tabDept .DeptId == tabEmpl .DeptId
    join tabProj
        where tabProj .ProjId == tabEmpl .ProjId;

    info(strFmt("Number of records updated is %1."
        ,tabEmpl .rowCount()));
}

Happy DAXing
.........

Get User's selected records and update values

Hi,
Here we will see that how to get (fetch) user's selected records only and apply update method on selected records only (i.e. there are thousands records and user wants to update value only in 10 then user will select 10- records and click on the action button then action should be performed).
Scenario- 1: 

• Create "Button", add it into related form then expand that Button, add Clicked() method and add logic.
• Here CustPackingSlipTrans is the data source of that form where this clicked method is mapped

// <purpose> to update "boolean" value on the basis of user's selected records
void clicked()
{
    CustPackingSlipTrans CustPackingSlipTransLoc,CustPackingSlipTransLocal;
    super();
    //recordcount = ReservationCancellation_ds.recordsMarked().lastIndex();
    CustPackingSlipTransLoc = CustPackingSlipTrans_ds.getFirst(1);
    while(CustPackingSlipTransLoc)
    {
        ttsBegin;
        //CustPackingSlipTransLoc.MSASNExport = NoYes::No;
        //CustPackingSlipTransLoc.update();
        update_recordSet CustPackingSlipTransLocal
            setting MSASNExport = NoYes::No
            where CustPackingSlipTransLocal.RecId == CustPackingSlipTransLoc.RecId;
        ttsCommit;
        CustPackingSlipTransLoc = CustPackingSlipTrans_ds.getNext();
    }
    //info(CustPackingSlipTransLoc.SalesId);
    CustPackingSlipTrans_DS.refresh();
    CustPackingSlipTrans_DS.research();
}

Happy DAXing
...................

Export Region- wise Data from AX to csv with seperate file name

how to export transactions with default dimensions to Excel

We recently had a support request where customer wanted to see all fixed asset transactions with financial dimensions in an Excel file. The code below solved the problem. I hope this code example can help people who are thinking of creating a simple xls report or want to see all default dimension values with transactions in one table.
Problem: On the fixed asset transaction form (Fixed assets -> Inquiries -> Fixed asset transactions) you will see the tab 'Financial dimensions' but it is not possible to personalize it so that the dimensions will appear on the Overview tab so that we could export/copy them to excel together with the rest of the data from the Overview tab. So, in short, we need to see fixed asset transactions together with the dimensions either in a report (that we can then export to excel) or on the fixed asset transactions form (Fixed assets -> Inquiries -> Fixed asset transactions) so that we can export this data to excel.
Solution: The following code was proposed that exports all fixed asset transactions to an Excel file together with all dimensions. Because the set of dimensions can be different on different transactions, we're adding dimension columns dynamically in the loop.

---

public static void main(Args _args)
{
    AssetTrans assetTrans;  
    SysExcelApplication application;
    SysExcelWorkBooks workbooks;
    SysExcelWorkBook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    SysExcelCell cell;
    int row;
    DimensionAttributeValueSetItemView dimAttrSet;
    DimensionAttribute dimAttr;
    str dimAttrStr;
    Map dims;
    int dimNum;
    ;
    application = sysExcelApplication::construct();
    workbooks = application.workbooks();
    workbook = workbooks.add();  
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    cells.range('A:A').numberFormat('@');

    dims = new Map(Types::String, Types::Integer);

    //generate header    row++;
    cell = cells.item(row, 1);  
    cell.value("Voucher");
    cell = cells.item(row, 2);  
    cell.value("Transaction date");
    cell = cells.item(row, 3);  
    cell.value("Fixed asset number");
    cell = cells.item(row, 4);  
    cell.value("Transaction type");
    cell = cells.item(row, 5);
    cell.value("Amount");
    cell = cells.item(row, 6);  
    cell.value("Fixed asset group");

    //generate lines    while select assetTrans
    //The following loop will provide the data to be populated in each column    {
        row++;
        //add fixed asset trans data        cell = cells.item(row,1);      
        cell.value(assetTrans.Voucher);
        cell = cells.item(row,2);
        cell.value(assetTrans.TransDate);
        cell = cells.item(row,3);
        cell.value(assetTrans.AssetId);
        cell = cells.item(row,4);
        cell.value(enum2str(assetTrans.TransType));
        cell = cells.item(row,5);
        cell.value(assetTrans.AmountCur);
        cell = cells.item(row,6);
        cell.value(assetTrans.AssetGroup);

        // add dimensions        while select dimAttrSet
            where dimAttrSet.DimensionAttributeValueSet == assetTrans.DefaultDimension
        join Name from dimAttr
            where dimattr.RecId == dimAttrSet.DimensionAttribute
        {
            if (!dims.exists(dimAttr.Name)) // if dim column does not exists            {
               //add dimension column               dims.insert(dimAttr.Name, dimNum + 7);
               dimNum++;
               cell = cells.item(1, dims.lookup(dimAttr.Name));
               cell.value(dimAttr.Name);
            }
           //add dimension value           cell = cells.item(row, dims.lookup(dimAttr.Name));
           cell.value(dimAttrSet.DisplayValue);
        }
    }
    application.visible(true); // opens the excel worksheet}

Happy DAXing

................

How to Import/ Export Data from Microsoft Dynamics AX to CSV file

Exporting the Data from AX to CSV file

Hi....
here are the examples of how to deal import/ export data functionality in AX 2012

Import data from csv file into AX 2012

Here is an example of importing SubProgram financial dimension from CSV file into AX 2012...

static void ReadCsvFile(Args _args)

{

    #File

    IO  iO;

    DimensionFinancialTag   dimFinTag, dimFinTagOrig;

    FilenameOpen        filename = "C:\\Users\\v-vimsin\\Documents\\Vimal\\DIXF\\SubProgram.csv";//To assign file name

    Container           record;

    boolean first = true;

    

    iO = new CommaTextIo(filename,#IO_Read);

    if (! iO || iO.status() != IO_Status::Ok)

    {

        throw error("@SYS19358");

    }

    while (iO.status() == IO_Status::Ok)

    {

        record = iO.read();// To read file

        if (record)

        {

            if (first)  //To skip header

            {

                first = false;

            }

            else

            {              

                dimFinTag.Value                 = conpeek(record, 3);//To peek record

                dimFinTag.Description           = conpeek(record, 1); 

                dimFinTag.FinancialTagCategory  = conpeek(record, 2);

                

                select dimFinTagOrig 

                    where dimFinTagOrig.FinancialTagCategory == dimFinTag.FinancialTagCategory

                       && dimFinTagOrig.Value == dimFinTag.Value;

                if (!dimFinTagOrig)

                    dimFinTag.insert();

                

                info(strfmt('%1--%2',dimFinTag.Value,dimFinTag.Description));

            }

        }

    }

}

Export data from AX 2012 into csv file

Here is an example of exporting the AX data to a CSV file ...

Example- 1: 
static void ExportDataToCSV(Args _args)
{
    Query                             q;
    QueryBuildDataSource    qbds;
    QueryBuildRange            qbr;
    QueryRun                       qr;
    CommaIO                       commaIO;
    FileName                        fileName;
    InventTable                     inventTable;
    ;
   
    fileName       = WINAPI::getTempPath() + "ItemDetails" + ".csv";
    commaIO      = new CommaIO(fileName,'W');
   
    q                  = new Query();
    qbds             = q.addDataSource(tablenum(InventTable));
    qbr               = qbds.addRange(fieldnum(InventTable,ItemId));
   
    qr                = new QueryRun(q);
   
    commaIO.write("ItemId","Item Name","Item Type","Item GroupId","Dimension GroupId","Model GroupId");
    while( qr.next() )
    {
        inventTable = qr.get(tablenum(InventTable));
       
        commaIO.write(inventTable.ItemId,inventTable.ItemName,enum2str(inventTable.ItemType),inventTable.ItemGroupId,
                      inventTable.DimGroupId,inventTable.ModelGroupId);
    }
   
    WINAPI::shellExecute(fileName);
}

Example- 2: 
public static void main(Args _args)
{
    Commaio file;
    container line;
    InventTable inventTable;
    #define.filename("C:\\dpk_Items.csv")
    #File

    ;
    file = new Commaio(#filename , #io_write); or  file = new Commaio(#filename , 'W');
    //file.outFieldDelimiter(';');
    if( !file || file.status() != IO_Status::Ok)
    {
        throw error("File Cannot be opened");
    }
    while select inventTable
    {
        line = [inventTable.ItemId,inventTable.ItemName];
        file.writeExp(line);
    }
}

Note: As per this you can save the document in any format such as doc,txt,xls only.

Happy DAXing.....

Create/ Export Excel BOM Report Template from X++ in AX 2012

BOM Excel Template report:

Path: Product information management/common/release products

Select each grid record whose Production type is BOM or Formula as shown in below screen and click Lines in BOM Engineer action pane or Lines in Formula Engineer action pane

Add Export to excel button on version Group for exporting excel report template

Override Export to Excel click button which will call a new class through action menu item

void clicked()
{
     BOMVersion      BOMVersionlocal;
    str             menuItemStr;
    MenuFunction    menuFunction;
    Args            args = new args();
 
    super();
    //getFirst method gets all the selected records in the grid
    BOMVersionlocal = BOMVersion_ds.getFirst(1,true);
    args.record(BOMVersionlocal);
 
    menuItemStr = menuitemActionStr(SL_BOMExport);   
    menuFunction = new MenuFunction(menuItemStr, MenuItemType::Action);
    if (BOMVersionlocal.RecId != 0)
    {
       menuFunction.run(args);
    }
    else
    {
        warning('No record selected');
    }
 
}

Create a new Class which exporting BOM header and Lines into excel

public static void Main(Args _args)
{
    ItemId          itemid;
    BOMVersion      BOMVersion;
 
    BOMVersion = _args.record();
 
    itemid = BOMVersion.ItemId; 
 
    SL_BOMExport::exportBOM(itemid,BOMVersion.BOMId);
}

public static void exportBOM(ItemId _itemId, BOMId _bomId)
{ 
    BOMVersion               BOMVersion;
    BOM                      BOM;
    Description              personalNumbervalue;
 
    SysExcelApplication     application;
    SysExcelWorkBooks       workbooks;
    SysExcelWorkBook        workbook;
    SysExcelWorksheets      worksheets;
    sysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    SysExcelCell            cell;
    SysExcelFont            SysExcelFont;
 
    int                     row = 1;
 
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();            //gets the workbook object
    workbook = workbooks.add();                     // creates a new workbook
    worksheets = workbook.worksheets();             //gets the worksheets object
    worksheet = worksheets.itemFromNum(1);          //Selects the first worksheet in the workbook to insert data
    cells = worksheet.cells();
    cells.range('A:A').numberFormat('@'); // numberFormat ‘@’ is to insert data as Text 
 
    //header
    worksheet.cells().item(1,1).value(CompanyInfo::Find().Name);
    worksheet.cells().item(1,1).font().bold(true);
 
    worksheet.cells().item(2,1).value("List of item formula / BOM");
    worksheet.cells().item(2,1).font().bold(true); 
 
    // bom header
    worksheet.cells().item(4,1).value("BOM");
    worksheet.cells().item(4,1).font().bold(true);
 
    worksheet.cells().item(5,1).value("Name");
    worksheet.cells().item(5,1).font().bold(true);
 
    worksheet.cells().item(6,1).value("Site");
    worksheet.cells().item(6,1).font().bold(true);
 
    worksheet.cells().item(4,4).value("From date");
    worksheet.cells().item(4,4).font().bold(true);
 
    worksheet.cells().item(5,4).value("To date");
    worksheet.cells().item(5,4).font().bold(true);
 
    worksheet.cells().item(6,4).value("From qty");
    worksheet.cells().item(6,4).font().bold(true);
 
    worksheet.cells().item(4,7).value("Active");
    worksheet.cells().item(4,7).font().bold(true);
 
    worksheet.cells().item(5,7).value("Approved by");
    worksheet.cells().item(5,7).font().bold(true);
 
    worksheet.cells().item(6,7).value("Approved");
    worksheet.cells().item(6,7).font().bold(true);
  
     select BOMVersion
           where BOMVersion.ItemId == _itemId
           && BOMVersion.BOMId     == _bomId;
 
        cell = cells.item(4,2);
        cell.value(BOMVersion.BOMId);
 
        cell = cells.item(5,2);
        cell.value(BOMVersion.Name);
 
        cell = cells.item(6,2);
        cell.value(InventDim::find(BOMVersion.InventDimId).InventSiteId);
 
        cell = cells.item(4,5);
        if (BOMVersion.FromDate != dateNull())
        {
            cell.value(BOMVersion.FromDate);
        }
        else
        {
            cell.value("");
        }
 
        cell = cells.item(5,5);
        if (BOMVersion.ToDate != dateNull())
        {
             cell.value(BOMVersion.ToDate);
        }
        else
        {
             cell.value("");
        }
        cell = cells.item(6,5);
        cell.value(BOMVersion.FromQty);
 
        cell = cells.item(4,8);
        cell.value(enum2Value(BOMVersion.Active));
 
        cell = cells.item(5,8);
        personalNumbervalue = HcmWorker::find(BOMVersion.Approver).name();
        cell.value(personalNumbervalue);
 
        cell = cells.item(6,8);
        cell.value(enum2Value(BOMVersion.Approved));
 
    row = 6 ;
    row+=2; 
 
     // header lines
 
       worksheet.cells().item(row,1).value("Item number");
       worksheet.cells().item(row,1).font().bold(true);
 
       worksheet.cells().item(row,2).value("Product name");
       worksheet.cells().item(row,2).font().bold(true);
 
       worksheet.cells().item(row,3).value("Configuration");
       worksheet.cells().item(row,3).font().bold(true);
 
       worksheet.cells().item(row,4).value("Quantity");
       worksheet.cells().item(row,4).font().bold(true);
 
       worksheet.cells().item(row,5).value("Unit");
       worksheet.cells().item(row,5).font().bold(true);
 
       worksheet.cells().item(row,6).value("Per series");
       worksheet.cells().item(row,6).font().bold(true);
 
       worksheet.cells().item(row,7).value("Size");
       worksheet.cells().item(row,7).font().bold(true);
 
       worksheet.cells().item(row,8).value("Color");
       worksheet.cells().item(row,8).font().bold(true);
 
       worksheet.cells().item(row,9).value("Style");
       worksheet.cells().item(row,9).font().bold(true);
  
     while select BOMVersion
           where BOMVersion.ItemId == _itemId
            &&   BOMVersion.BOMId  == _bomId
         join BOM
            where BOMVersion.BOMId == BOM.BOMId
 
    { 
            row++;
 
            cell = cells.item(row,1);
            cell.value(BOM.ItemId);
 
            cell = cells.item(row,2);
            cell.value(BOM.itemNameGrid());
 
            cell = cells.item(row,3);
            cell.value(InventDim::find(BOM.InventDimId).configId);
 
            cell = cells.item(row,4);
            cell.value(BOM.BOMQty);
 
            cell = cells.item(row,5);
            cell.value(BOM.UnitId);
 
            cell = cells.item(row,6);
            cell.value(BOM.BOMQtySerie);
 
            cell = cells.item(row,7);
            cell.value(InventDim::find(BOM.InventDimId).InventSizeId);
 
            cell = cells.item(row,8);
            cell.value(InventDim::find(BOM.InventDimId).InventColorId);
 
            cell = cells.item(row,9);
            cell.value(InventDim::find(BOM.InventDimId).InventStyleId); 
    }
 
    application.visible(true);  // opens the excel worksheet 
}

Happy DAXing

..........

Get values of base enums using code in x++

Question: How to get  values of base enums using code in x++

Scenario- 1: static void getEnumValues(Args _args)

{

    EnumId   enumId   = enumNum(LedgerDimensionType);

    DictEnum dictEnum = new DictEnum(enumId);

    int      count  = dictEnum.values();

    int      counter 

    for(counter = 0; counter < count; counter ++)

    {

        // You can use the number of method exposed by DictEnum class

// dictEnum.name(counter)

// dictEnum.index2Value(counter)

// dictEnum.index2Symbol(counter)             

// dictEnum.index2Label(counter)

    }

}

Scenario- 2: Retrieve Label from Enum Value

The follow codes is useful due to retrieve the label of TransType field related table InventTrans :

1. SysDictEnum dictEnum = new SysDictEnum( enumnum(InventTransType) );


2. dictEnum.value2Label(InventTrans.TransType)

Scenario- 3: How can I make this comparison language independent?

I'm not sure of getting list of all languages. But you can try below code, it may help in your case.

static void Enum2Label(Args _args)
{
    DictEnum                dictEnum;
    int                     valueIndex;
    LanguageId              languageId='fr';

    int                     enumId=enumNum(ItemType);
    str                     labelId;
    ;

    dictEnum = new DictEnum(enumId);
    if (dictEnum)
    {
        for (valueIndex = 0 ; valueIndex < dictEnum.values(); valueIndex++)
        {
            labelId = dictEnum.index2LabelId(valueIndex);
            info(SysLabel::labelId2String2(labelId, languageId));
        }
    }
}

Happy DAXing.....

How to Import/ Export data from AX 2012 into Excel through code

How to write data in Excel from AX through X++ code

Hi..........

here are examples of how to deal import/ export data through excel in AX 2012......

Import data from Excel into AX 2012........

Example-1: Here is an example of importing SubProgram financial dimension from excel file into AX 2012...

static void importFinancialDimension(Args _args)

{

    SysExcelApplication     application;

    SysExcelWorkbooks       workbooks;

    SysExcelWorkbook        workbook;

    SysExcelWorksheets      worksheets;

    SysExcelWorksheet       worksheet;

    SysExcelCells           cells;

    COMVariantType          type;

    Name                    name;

    FileName                filename;

    DimensionFinancialTag   dimFinTag, dimFinTagOrig;

    int row;

    

    application = SysExcelApplication::construct();

    workbooks   = application.workbooks();

    

    //specify the file path that you want to read

    filename = "C:\\Users\\v-vimsin\\Documents\\Vimal\\DIXF\\SubProgram.xlsx";

    try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

        throw error("File cannot be opened.");

    }

    workbook    = workbooks.item(1);

    worksheets  = workbook.worksheets();

    worksheet   = worksheets.itemFromNum(1); //Here 1 is the worksheet Number which you can see like Sheet1

    cells       = worksheet.cells();

    

    do

    {

        row++;

        dimFinTag.Value                 = cells.item(row, 3).value().bStr();

        dimFinTag.Description           = cells.item(row, 1).value().toString();

        dimFinTag.FinancialTagCategory  = any2int64(cells.item(row, 2).value().bStr());

        

        select dimFinTagOrig 

            where dimFinTagOrig.FinancialTagCategory == dimFinTag.FinancialTagCategory

                && dimFinTagOrig.Value == dimFinTag.Value;

        if (!dimFinTagOrig)

            dimFinTag.insert();

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

    }

    while (type != COMVariantType::VT_EMPTY);

    application.quit();

}

Example-2: In order to import data into AX from Excel through X++..
Excel file import using X++ code:



static void Salestargetdataimport(Args _args) { #AviFiles SysOperationProgress progress = new SysOperationProgress(); SysExcelApplication application; SysExcelWorkbooks workbooks; SysExcelWorkbook workbook; SysExcelWorksheets worksheets; SysExcelWorksheet worksheet; SysExcelWorkSheet workSheetInventTableModule; SysExcelCells cellsInventTableModule; SysExcelCells cells; COMVariantType type; COMVariantType typeModule; DataEntryForm DataEntryForm1; int row = 0; FileIoPermission perm; dipl_monthsofyear months; year1 year1; Country Country2; #define.Filename('D:\Manish_Sales_to_Ax.xlsx') #define.FileMode('R') ;
perm.assert();
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(#Filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);//.itemFromName("Sales target");
cells = worksheet.cells();
progress.setCaption("Sales target data import...");
progress.setAnimation(#AviTransfer);
try
{
ttsbegin;
do
{
row++;
if (row > 1)
{
select firstonly DataEntryForm1
where DataEntryForm1.SalesPersonName == any2str(cells.item(row, 1).value().bStr())
&& DataEntryForm1.Year1 == str2enum(Year1, any2str(cells.item(row, 4).value().bStr()))
&& DataEntryForm1.MonthsOfYear == str2enum(Months, any2str(cells.item(row, 3).value().bStr()));
if (!DataEntryForm1)
{
DataEntryForm1.SalesPersonName = any2str(cells.item(row, 1).value().bStr());
DataEntryForm1.Country = str2enum(Country2, any2str(cells.item(row, 2).value().bStr()));
DataEntryForm1.MonthsOfYear = str2enum(Months, any2str(cells.item(row, 3).value().bStr()));
DataEntryForm1.Year1 = str2enum(Year1, any2str(cells.item(row, 4).value().bStr()));
if (DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::April || DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::May
|| DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::June)
{
DataEntryForm1.DIPL_QuarterOfYear = DIPL_QuarterOfYear::Q1;
}
else if (DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::July || DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::August
|| DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::September)
{
DataEntryForm1.DIPL_QuarterOfYear = DIPL_QuarterOfYear::Q2;
}
else if (DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::October || DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::November
|| DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::December)
{
DataEntryForm1.DIPL_QuarterOfYear = DIPL_QuarterOfYear::Q3;
}
else if (DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::January || DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::February
|| DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::March)
{
DataEntryForm1.DIPL_QuarterOfYear = DIPL_QuarterOfYear::Q4;
}
DataEntryForm1.OrderBookClosuresPlanned = cells.item(row, 5).value().double();
DataEntryForm1.OrderBookClosuresActual = cells.item(row, 6).value().double();
if (DataEntryForm1.OrderBookClosuresPlanned || DataEntryForm1.OrderBookClosuresActual)
{
DataEntryForm1.AcheivedPercentOrderBookClosure = (DataEntryForm1.OrderBookClosuresActual/DataEntryForm1.OrderBookClosuresPlanned) * 100;
}
DataEntryForm1.SalesPipeLinePlanned = cells.item(row, 7).value().double();
DataEntryForm1.SalesPipeLineActual = cells.item(row, 8).value().double();
if (DataEntryForm1.SalesPipeLinePlanned || DataEntryForm1.SalesPipeLineActual)
{
DataEntryForm1.AcheivedPercentSalesPipeline = (DataEntryForm1.SalesPipeLineActual/DataEntryForm1.SalesPipeLinePlanned) * 100;
}
DataEntryForm1.NumOfProposalsPlanned = cells.item(row, 9).value().double();
DataEntryForm1.NumOfProposalSubmitted = cells.item(row, 10).value().double();
if (DataEntryForm1.NumOfProposalsPlanned || DataEntryForm1.NumOfProposalSubmitted)
{
DataEntryForm1.AcheivedPercentNoOfProposals = (DataEntryForm1.NumOfProposalSubmitted/DataEntryForm1.NumOfProposalsPlanned) * 100;
}
DataEntryForm1.ValueOfProposalsPlanned = cells.item(row, 11).value().double();
DataEntryForm1.ValueOfProposalsSubmitted = cells.item(row, 12).value().double();
if (DataEntryForm1.ValueOfProposalsPlanned || DataEntryForm1.ValueOfProposalsSubmitted)
{
DataEntryForm1.AcheivedPercentValuOfProposals = (DataEntryForm1.ValueOfProposalsSubmitted/DataEntryForm1.ValueOfProposalsPlanned) * 100;
}
DataEntryForm1.insert();
}
}
type = cells.item(row+1, 1).value().variantType();
} while (type != COMVariantType::VT_EMPTY);
ttscommit;
}
catch (Exception::Error)
{
workbooks.close();
CodeAccessPermission::revertAssert();
application.quit();
ttsabort;
}
workbooks.close();
CodeAccessPermission::revertAssert();
application.quit();
}

perm = new FileIOPermission(#FileName, #FileMode);

Export data from AX 2012 into Excel file.......

Here is an example of exporting sales data and delivery address from AX 2012 into excel file...

static void SalesTableExportIntoExcel(Args _args)
{
    SalesTable              salesTable;
    LogisticsPostalAddress  postalAddress;
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    SysExcelCell            cell;
    SysExcelFont            font;
    int                     row;
    str                     city, state, country, zipCode;
    SysDictEnum             dictEnumStatus, dictEnumSalesType;
 
    dictEnumStatus    = new SysDictEnum(Enumnum(SalesStatus));  
    dictEnumSalesType = new SysDictEnum(Enumnum(SalesType));

    application     = SysExcelApplication::construct();
    workbooks       = application.workbooks();
    workbook        = workbooks.add();
    worksheets      = workbook.worksheets();
    worksheet       = worksheets.itemFromNum(1);
    cells           = worksheet.cells();
    cells.range('A:A').numberFormat('@');
 
    // Setting Header values
    cell = cells.item(1, 1);
    cell.value("Sales Order");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 2);
    cell.value("Supplemental part");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 3);
    cell.value("Customer account");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 4);
    cell.value("Name");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 5);
    cell.value("Created date and time");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 6);
    cell.value("Customer requisition");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 7);
    cell.value("ZIP/Postal Code");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 8);
    cell.value("Status");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 9);
    cell.value("Order type");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 10);
    cell.value("Country/region");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 11);
    cell.value("Reason for export");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 12);
    cell.value("State");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 13);
    cell.value("City");
    font = cell.font();
    font.bold(true);
    row = 1;
 
    while select salesTable
    {
        postalAddress   = salesTable.deliveryAddress();
        city            = postalAddress.City;
        state           = postalAddress.State;
        //country         = postalAddress.CountryRegionId;
        country         = LogisticsAddressCountryRegion::find(postalAddress.CountryRegionId).displayName();
        zipCode         = postalAddress.ZipCode;
     
        row++;
        cell = cells.item(row, 1);
        cell.value(salesTable.SalesId);
        cell = cells.item(row, 2);
        cell.value(" ");
        cell = cells.item(row, 3);
        cell.value(salesTable.InvoiceAccount);
        cell = cells.item(row, 4);
        cell.value(salesTable.invoiceName());//CustTable::find(salesTable.InvoiceAccount).name());
        cell = cells.item(row, 5);
        cell.value(datetime2str(salesTable.createdDateTime));
        cell = cells.item(row, 6);
        cell.value(salesTable.PurchOrderFormNum);
        cell = cells.item(row, 7);
        cell.value(zipCode);
        cell = cells.item(row, 8);
        cell.value(dictEnumStatus.value2Label(salesTable.SalesStatus));
        cell = cells.item(row, 9);
        cell.value(dictEnumSalesType.value2Label(salesTable.SalesType));
        cell = cells.item(row, 10);
        cell.value(country);
        cell = cells.item(row, 11);
        cell.value(salesTable.ExportReason);
        cell = cells.item(row, 12);
        cell.value(state);
        cell = cells.item(row, 13);
        cell.value(city);
    }
 
    application.visible(true);
}

Happy DAXing

How to filter records in a form by code in AX 2012

How to set range in a form by code in AX 2012

The standard filter functionality in Ax forms is a neat and powerful feature.
Using this filter functionality in your code is something you'll definitely use at some point in time as a programmer.

Although it's possible to do it in a single line of code, I prefer a 3 step solution. That way it's more flexible.
Let me show you by example. We'll filter the customers records in form CustTable, only showing customers with currency USD.

Step 1: Declare a class variable
In the ClassDeclaration method of the form, define a range.

QueryBuildRange CurrencyQBR;

Step 2: Instantiate the new range.
In the init method on the datasource of the form, you assign the range to a specific field (after the super call).

public void init()
{
super();

CurrencyQBR = this.query().dataSourceName('CustTable').addRange(fieldnum(CustTable,Currency));
}
Step 3: In the last step, you assign a value to the range.
This is done in the executeQuery method on the same datasource of the form. Before the super call. Like this:

public void executeQuery()
{ ;

CurrencyQBR.value(queryvalue('USD'));

super();
}
You're done! When you open the form, your customer records are filtered, you only get the customers with currencycode USD set up.

Like I said in the intro of this post, this can be done in one line of code as well.
In the init method of the form datasource, after the super call, place this code:

this.query().dataSourceName('CustTable').addRange(fieldnum(CustTable,Currency)).value(queryvalue('USD'));
But this way, it's fixed. If you choose the 3 step method, you could for example use a variable in the range value. The way to go would be to place an input field on your form, get the value from it and supply it in the executeQuery method.
For example like this:
public void executeQuery()
{ ;

CurrencyQBR.value(queryvalue(MyInputField.text()));

super();
}
Just make sure the executeQuery method is executed, thus applying the desired filter (maybe be using a button on your form to activate it).
Of course it's possible to combine multiple querybuildranges.

Example: 

Let me show you by another example: We'll filter the Sales PackingSlip records in form MSASNShipmentExport (new developed), only showing PackingSlip records on the basis of Financial Dimension "Region" as per mentioned screen shots.

Step-1:

declare the class variables in the ClassDeclaration method of the form, define datasource and range.

    //<declared> by Vimal ...

    DimensionAttributevaluesetitem  DimensionAttributevaluesetitemLoc;    

    QueryBuildDataSource                 qbds, qbdsdimensionValue;

    QueryBuildRange                         qbrIsExport, qbrdimValue;

    //<end> by Vimal

Step 2: Instantiate the new range.

In the init method on the datasource of the form, you assign the range to a specific field (after the super call).

public void init()

{

    Query       query; //<declared> by Vimal

    super();

    //<start> declared by Vimal on April 23, 2015

    // to initialise range for filtering records as per Financial dimension "Region"

    query = CustPackingSlipTrans_ds.query();

    qbds = query.dataSourceTable(tablenum(CustPackingSlipTrans));

    qbds.clearRanges();

    qbrIsExport = qbds.addRange(fieldnum(CustPackingSlipTrans,MSASNExport)); //.value(IsExport.valueStr());

    qbdsdimensionValue = qbds.addDataSource(tableNum(DimensionAttributevaluesetitem));

    qbdsdimensionValue.addLink(fieldNum(DimensionAttributevaluesetitem, DimensionAttributeValueSet), fieldNum(CustPackingSlipTrans, DefaultDimension));

    qbdsdimensionValue.joinMode(JoinMode::ExistsJoin);

    qbrdimValue = qbdsdimensionValue.addRange(fieldNum(DimensionAttributevaluesetitem, DisplayValue));

    //<end> by Vimal

}

Step 3: In the last step, you assign a value to the range.

This is done in the executeQuery method on the same datasource of the form. Before the super call. Like this:

//<created> by Vimal .....

public void executeQuery()

{   

        

    

    qbrdimValue.value(FilterBy.valueStr()); //here, FilterBy is the StringEdit control name

    qbrIsExport.value(IsExport.valueStr()); //here, IsExport is the StringEdit control name

    //<end> by Vimal

    

    super();

.......................

}

You're done! When you open the form, your packingslip records are filtered, you only get the records with Financial Dimension "Region" set up.

Like this:

Happy DAXing

Default dimension storage in Ax 2012 : to fetch one dimension from defauly dimension group

 Hi,

In this post we will see, how to get one dimension value from default dimension master RecId.
Method:

DimensionValue getDimensionValue(RefRecID dimensionSetRecID, Name attributeName)

{

    DimensionAttributeValueSet      dimAttrValueSet;

    DimensionAttributeValueSetItem  dimAttrValueSetItem;

    DimensionAttributeValue         dimAttrValue;

    DimensionAttribute              dimAttribute; 

    dimAttrValueSet = DimensionAttributeValueSet::find(dimensionSetRecID); 

    select dimAttrValueSetItem

        where dimAttrValueSetItem.DimensionAttributeValueSet == dimAttrValueSet.RecId

    join dimAttrValue

        where dimAttrValue.RecId == dimAttrValueSetItem.DimensionAttributeValue

    join dimAttribute

        where dimAttribute.RecId == dimAttrValue.DimensionAttribute

        &&    dimAttribute.Name  == attributeName; 

    return dimAttrValue.getValue();

}

 

e.g.: Our task is to get "Importer"only through default dimension (here Importer is CustAccount) then we need to call this method like that:

 

importerDimension = CustTable::find(this.getDimensionValue(SalesTable.DefaultDimension, "Importer"));

    if (importerDimension)

    {

        msCommercialPackingSlipTmp.msImporterName = CustTable::find(importerDimension).name();

        msCommercialPackingSlipTmp.msImporterAddress = CustTable::find(importerDimension).address();

    }

    else

    {

        msCommercialPackingSlipTmp.msImporterName = msCommercialPackingSlipTmp.DeliveryName;

        msCommercialPackingSlipTmp.msImporterAddress = msCommercialPackingSlipTmp.DeliveryAddress;

    }

 

Happy DAXing....

 

Mark Compile application step complete in checklist "Mark as Complete"

Since AX2012 R2 CU7, most of us have started to use axbuild for a complete application compilation. While this is fine and dandy, there are still checklists within AX that demands this step to be run in the client itself. Until we get an option to “Mark as complete”, you can mark the step yourself using the following job:

static void CompleteCompile(Args _args)
{
    SysCheckList::finished(classnum(SysCheckListItem_Compile));
    SysCheckList::finished(classnum(SysCheckListItem_CompileUpgrade));
    SysCheckList::finished(className2Id(classStr(SysCheckListItem_CompileServ)));
    SysCheckList::finished(classnum(SysCheckListItem_SysUpdateCodeCompilInit));
}

Run the job and observe the step is marked as completed. No sweat!

Happy DAXing.....