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

..........