Monday, January 14, 2013

Set not empty / not blank record in query (AX 2009)

Hi,

If we want to set not empty condition at query then following code can be used in value

SysQuery::valueNotEmptyString()

Example:

this.query().dataSourceTable(tablenum(LedgerTrans)).addRange(fieldId2Ext(fieldnum(LedgerTrans, Dimension),5)).value(SysQuery::valueNotEmptyString());

Thursday, January 10, 2013

Most commonly used Format and code while creating reports X++ report


Most commonly used  Format and code while creating reports.



public class ReportRun extends ObjectRun
{
    TransDate               fromDate;
    TransDate               toDate;
    DialogField             dialogFromDate, dialogToDate;
    DialogField             dialogLedgerAccount;
    DialogField             dialogPrintExcelReport;
    LedgerAccount           ledgerAccount;

    LedgerTrans             ledgerTrans1, ledgerTransOpenBal;
    str 50                  dimension;
    AmountMST               amount;
    AmountCur               creditAmt,debitAmt;
    AmountCur               openingBalAmt,closingBalAmt;

    #define.CurrentVersion(4)
    #localmacro.CurrentList
        FromDate,
        ToDate,
        ledgerAccount
    #endmacro
}

==================================================


public container pack()
{
    return [#CurrentVersion,#CurrentList];
}

==================================================


public boolean unpack(container _packedClass)
{
    boolean     ret;
    Version     version = RunBase::getVersion(_packedClass);
    ;

    switch (version)
    {
        case #CurrentVersion:
            [version, #CurrentList] = _packedClass;
            ret = true;
            break;

        default :
            ret = false;
    }
    return ret;
}





==================================================


public Object dialog(Object _dialog)
{
    DialogRunbase dialog = _dialog;
    ;

    dialog.addGroup("Date Range");
    dialogFromDate = dialog.addFieldValue(typeid(TransDate), fromDate,"From Date");
    dialogToDate = dialog.addFieldValue(typeid(TransDate), toDate,"To Date");
    dialogLedgerAccount = dialog.addFieldValue(typeid(AccCategory), ledgerAccount,"Ledger Account");

    return dialog;
}


==================================================


public boolean getFromDialog()
{
    ;
    fromDate = dialogFromDate.value();
    toDate   = dialogToDate.value();
    ledgerAccount = dialogLedgerAccount.value();

    return true;
}



==================================================
Fetch method --------

queryRun = new QueryRun(this);
    this.query().dataSourceTable(tablenum(LedgerTrans)).addRange(fieldnum(LedgerTrans,TransDate)).value(queryrange(fromdate,todate));

this.query().dataSourceTable(tablenum(LedgerTrans)).addRange(fieldnum(LedgerTrans,AccountNum)).value(ledgerAccount);
    this.query().dataSourceTable(tablenum(LedgerTrans)).addOrderByField(fieldId2Ext(fieldnum(LedgerTrans, Dimension),5));

//this.query().dataSourceTable(tablenum(LedgerTrans)).orderMode(OrderMode::GroupBy);
    //this.query().dataSourceTable(tablenum(LedgerTrans)).addGroupByField(fieldId2Ext(fieldnum(LedgerTrans, Dimension),5));

//this.query().dataSourceTable(tablenum(LedgerTrans)).fields().addField(fieldnum(LedgerTrans, AmountCur), SelectionField::Sum);

//query.dataSourceNo(1).fields().addField(fieldnum(CustSettlement, SettleAmountMST), SelectionField::Sum);
    //this.query().dataSourceTable(tablenum(LedgerTrans)).addRange(fieldnum(LedgerTrans,Dimension[5])).value(dimension);

//this.query().dataSourceTable(tablenum(LedgerTrans)).addRange(fieldId2Ext(fieldnum(LedgerTrans, Dimension),5)).value("Anil P Talla");

//query.dataSourceNo(2).addGroupByField(fieldId2Ext(fieldnum(CustTrans, Dimension),1));



while (queryRun.next())
    {

        ledgerTrans = queryRun.get(tablenum(LedgerTrans));
        // Add code here
    }