A good post by Vanya Kashperuk regarding difference between refresh, reread and research... Thanks Vanya !!!

http://kashperuk.blogspot.com/2010/03/tutorial-reread-refresh-research.html

Edit Methods in AXAPTA

Requirement: We need to write the edit method on the places where we need to select the records and pass it to the business logic for further processing. Also, we need edit methods to display the values of the related record from other table when the related table is not a part of the form datasource. The same purpose can be handled using display method as well but edit method allows us to modify the values in the display method. So, we can say that:

Edit Method = Display Method + Editing capability

How to write an Edit Method: We can write an edit method using the code shown below. Before getting in to the code, we must understand the requirement of the below code:

·         We have a form which is listing the Project Invoices in a grid

·         We need to have a checkbox next to all the records to select/deselect the records

·         We want to keep the Record Ids of the selected records in to a container so that we can use it for processing.

·         When a record is checked, we must check in the container if the record already exists in the container or not. If it exist, we must return true so that the form show the value as checked.

Else, we can create or delete the entry in the container basis the user action like if user has checked the checkbox, we will make an entry in to the container or if user has unchecked the checkbox, we will delete the entry from the container.

Calculating Available Physical for an Item

Write this method on the InventSum datasource on the form Ref form: InventOnHandMovement

display InventQtyAvailPhysical availPhysicalUnit(InventSum _inventSum)
{
    return inventItemUnitConvert.qty(_inventSum.availPhysical(), unitIdInvent, unitIdSecondary, _inventSum.itemId);
}

Open Internet explorer Thru Axapta

static void openInternetExplorer(Args _args)
{
    ;
    WINAPI::shellExecute("iexplore.exe",'Google.com');
}

Accessing External Database from X++

1.       Create an ODBC connection through Administrative tools >> Data Sources (ODBC)
>> Create a new ‘SQL Server’ connection
>> Test the connection
2.       Use the following code to access the external database:
server static void GetShippingTrans(Args    _args)
{
    LoginProperty                   loginProperty;
    OdbcConnection                  connection;
    Statement                       statement;
    ResultSet                       results;
    Str                             sqlStatement;
    str                             dataSourceName = "TestExternalDB_XXX";
    str                             databaseName = "payroll_XXX_dev";
    SqlStatementExecutePermission   sqlPermission;
    ;
   
    loginProperty = new LoginProperty();
    loginProperty.setDSN(dataSourceName);
    LoginProperty.setDatabase(databaseName);

    try
    {

        connection = new OdbcConnection(loginProperty);
        statement = connection.createStatement();
        sqlStatement = StrFmt("SELECT * FROM EmployeeCTCDetails");
        sqlPermission = new SQLStatementExecutePermission(sqlStatement);
        sqlPermission.assert();
        results = statement.executeQuery(sQLStatement);
        while (results.next())
        {
            setprefix(strfmt("Accessing the external database"));
            print results.getInt(1),'-',results.getString(2),'-',results.getString(3),'-',results.getReal(4);
        }
        CodeAccessPermission::revertAssert();
    }
    catch (Exception::Error)
    {
        error(strFmt("Error accessing external database."));
    }
    pause;
}

Getting list of all associated Goups with user in AX

static void WI_GetUserGroups(Args _args)
{
    UserGroupList  lUserGroupList;
     ;

    WHILE SELECT lUserGroupList WHERE lUserGroupList.userId == curuserid()
    {
         info(lUserGroupList.GroupId);
    }
}

To get current date and time using DateTimeUtil

To get Current date with minimum time and maximum time:

Current date 12:00:00 AM - DateTimeUtil::newDateTime(SystemDateGet(),0);
Current date 11:59:59 PM - DateTimeUtil::newDateTime(SystemDateGet(),Global::timeMax());

Create Ledger Journal Table and Trans Records

----------- Journal Table ------------------------

LedgerJournalTable createLedgerJournalTable()
{
    LedgerJournalTable journalTable;
    ;
    journalTable.JournalNum    = JournalTableData::newTable(journalTable).nextJournalId();
    journalTable.journalType   = LedgerJournalType::Daily;
    journalTable.JournalName   = PSAParameters::find().ExpenseJournalNameId;//Customized field
    journalTable.initFromLedgerJournalName(journalTable.JournalName);
    cnrAccount = journalTable.ledgerJournalName().OffsetAccount;
    if(!cnrAccount)
        throw error("Account not defined in PSA Parameters.");
    origLedgerTrans.JournalNum);
    journalTable.insert();
    return journalTable;
}
----------- Journal Trans ------------------------
void createLedgerJournalTrans(LedgerJournalTable        _ledgerJournalTable,
                                   tmpProjectOpenPOs    _tmpProjectOpenPOs //Customized table)
{
    LedgerJournalTrans  journalTrans;
    PurchLine           lPurchLine = PurchLine::find(_tmpProjectOpenPOs.PurchId, _tmpProjectOpenPOs.LineNum, true);
    ProjTable           lProjtable = ProjTable::find(_tmpProjectOpenPOs.ProjId);
    ;
    journalTrans.clear();
    journalTrans.initValue();
    journalTrans.Txt = StrFmt("Invoice for Purchase order %1 line number %2",_tmpProjectOpenPOs.PurchId, _tmpProjectOpenPOs.LineNum);
    journalTrans.Voucher = this.newVoucher(_ledgerJournalTable);
    journalTrans.JournalNum = _ledgerJournalTable.JournalNum;
    journalTrans.LineNum = LedgerJournalTrans::lastLineNum(journalTrans.JournalNum)+1;
    journalTrans.transDate = systemDateGet();
    journalTrans.AccountType = LedgerJournalACType::Project;
    journalTrans.AccountNum = _tmpProjectOpenPOs.ProjId;
    journalTrans.AmountCurDebit = _tmpProjectOpenPOs.NetAmount;
    journalTrans.OffsetAccountType = LedgerJournalACType::Ledger;
    journalTrans.OffsetAccount = NRAccount; //Customized field
    journalTrans.Dimension = lPurchLine.Dimension;
    journalTrans.CurrencyCode = lPurchLine.CurrencyCode;
    journalTrans.ProjCategoryId = lPurchLine.ProjCategoryId;
    journalTrans.ProjLinePropertyId = lPurchLine.ProjLinePropertyId;
    journalTrans.ProjQty = 1;
    journalTrans.modifiedField(FieldNum(LedgerJournalTrans,Projqty));
    journalTrans.SalesPrice = lPurchLine.ProjSalesPrice;
    journalTrans.ProjSalesCurrencyId = lPurchline.ProjSalesCurrencyId;
   
    journalTrans.insert();
}

Posting a Ledger Journal through Code

void postJournal(LedgerJournalTable _ledgerJournalTable)
{
    Args                    lArgs = new Args();
    ;
    lArgs.record(_ledgerJournalTable);
    LedgerJournalPost::main(lArgs);
}

To get new Voucher Number

Voucher newVoucher(LedgerJournalTable  _ledgerJournalTable)
{
    Voucher     lVoucher;
    ;
    lVoucher = new JournalVoucherNum(JournalTableData::newTable(_ledgerJournalTable)).getNew(false);
    return lVoucher;
}

Export Data to Excel

Below two methods would:

• Create an excel file
• Export the data from AX table to Excel file
• Save the excel file

void export2Excel()

{

    boolean                 excelInit;

    str                     lFilepath,lFilename,lFileExt;

    tmpProjectOpenPOs    tmpProjectOpenPOs = this.parmTmpProjectOpenPOs();

    #define.csv(".csv")

    ;
    progressTotal = 100;//TODO: SysQuery::countLoops(queryRun);

    [lFilepath, lFilename, lFileExt]   = Global::fileNameSplit(filename);

    WHILE SELECT tmpProjectOpenPOs 

    {

        if (!excelInit)

        {

            row = 1;

            this.initExcel(); // Intiate the headings

            excelInit = true;

        }

        i++;

        Cell = cells.item(i,1);

        Cell.value(tmpProjectOpenPOs.ProjId);

        Cell = Cells.item(i,2);

        Cell.value(tmpProjectOpenPOs.PurchId);

        Cell = cells.item(i,3);

        Cell.value(tmpProjectOpenPOs.LineNum);

        Cell = cells.item(i,4);

        Cell.value(enum2str(tmpProjectOpenPOs.LineStatus));//Enum field

        Cell = cells.item(i,5);

        Cell.value(tmpProjectOpenPOs.NetAmount);
         Cell = cells.item(i,6);

        Cell.value(Date2Str(tmpProjectOpenPOs.DeliveryDate,213,2,4,2,4,2)); // Date field

    }

    if (!excelInit)

    {

        info(strFmt("No records available"));

    }

    else

    {

       excel.visible(false);

        excel.displayAlerts(false);

        If (lFileExt == #csv)

        {

            book.saveAs(fileName,6);

        }

        Else

        {

             book.saveAs(fileName);

        }

        book.saved(true);

        books.close();

        excel.quit();

        info("Records exported successfully");

    }

    books = null;

    book = null;

    sheet = null;

    cell = null;

    excel = null;

}

----- Intiate the heading labels (called in the method above) ---------


void initExcel()

{

    int locRow = 1;

    ;

    i=1;

    excel   = SysExcelApplication::construct();

    books   = excel.workbooks();

    excel.visible(false);

    excel.displayAlerts(false);

    books.close();

    books.add();

    book    = books.item(1);

    sheets  = excel.worksheets();

    sheet   = sheets.itemFromNum(1);

    sheet.name(strFmt("Purchase orders"));

    cells   = sheet.cells();
    Cell = cells.item(i,1);

    Cell.value("Project");

    Cell = cells.item(i,2);

    Cell.value("Purchase order");

    Cell = cells.item(i,3);

    Cell.value("Line no");

    Cell = cells.item(i,4);

    Cell.value("Line status");

    Cell = cells.item(i,5);

    Cell.value("Net amount");

}