2017-03-31

[AX2012] Minimum On-hand on On-hand Form

Put display method in InventSum datasource:
\Forms\InventOnhandItem\Data Sources\InventSum\Methods\inventOnHandMin

display public InventQtyMinOnhand inventOnHandMin(InventSum _inventSum)
{
    InventDim       joinDim, dimValues;
    InventDimParm   dimParm;
    ReqItemTable    reqItemTable;

    dimValues.data(_inventSum.joinChild());
    dimParm.initFromInventDim(dimValues);

    select sum(MinInventOnhand) from reqItemTable where reqItemTable.ItemId == _inventSum.ItemId
        #InventDimExistsJoin(reqItemTable.CovInventDimId, joinDim, dimValues, dimParm);

    return reqItemTable.MinInventOnhand;
}

Then put this method in overview grid.

2017-03-09

[AX 365] Upload and Read Excel File

This is a sample to upload and read Excel file.

class RunnableClass1UploadExcel
{       
    public static void main(Args _args)
    {       
        FileUploadTemporaryStorageResult result;
        str                 fileUrl;
        System.IO.Stream    stream;

        OfficeOpenXml.ExcelWorksheet    workSheet;
        OfficeOpenXml.ExcelPackage      package;
        OfficeOpenXml.ExcelRange        cells;

        int         rowCount, colCount;
        anytype     anyValue;
        container   conRow;
        ;
        result = File::GetFileFromUser() as FileUploadTemporaryStorageResult;
       
        if (result && result.getUploadStatus())
        {
            fileUrl = result.getDownloadUrl();
            try
            {
                stream = File::UseFileFromURL(fileUrl);
                package = new OfficeOpenXml.ExcelPackage(stream);

                if(package)
                {
                    worksheet   = package.get_Workbook().get_Worksheets().Copy("Sheet1","Data");
                    cells       = worksheet.get_Cells();
                    rowCount    = worksheet.get_Dimension().get_End().get_Row();
                    colCount   = worksheet.get_Dimension().get_End().get_Column();

                    for (int i=2;i<=rowCount;i++)//row
                    {
                        conRow = conNull();
                        for (int j=1;j<=colCount;j++)//column
                        {
                            anyValue= cells.get_Item(i, j).get_Value();
                            conRow += anyValue;
                        }

                        if(conRow)
                        {
                            info(
                                strFmt("%1 - %2",
                                    any2Str(conPeek(conRow, 1)),
                                    any2Str(conPeek(conRow, 2))
                                    )
                                );
                        }
                    }
                }
            }
            catch(Exception::Error)
            {
                info(strFmt("%1 - %2",Exception::Error, fileUrl));
            }
            result.deleteResult();
        }
    }

}

2017-03-08

[AX 365] Create CSV File

This is a sample code to create csv file. It use CommaTextStreamIo class.

class RunnableClassExport2CSV
{       
    public static void main(Args _args)
    {   
        ATSGroupTable     groupTable;
        CommaTextStreamIo comma = CommaTextStreamIo::constructForWrite();
        ;
        while select groupTable
        {
            comma.writeExp([groupTable.GroupId, groupTable.Name]);
        }
        File::SendFileToUser(comma.getStream(), "atus.csv");
    }

}

2017-02-22

Convert/Clone VHDX to VHD

Virtualbox cannot run VHDX file currently. So we have to convert them to VHD or VDI.
Here is a command to convert/clone from VHDX to VHD
VBoxManage.exe clonehd "PATH_TO_YOUR_FILE.VHDX" --format VHD "PATH_TO_CONVERTED_FILE.vhd"
Sample command:
C:\Program Files\Oracle\VirtualBox>VBoxManage.exe clonehd c:\MyData\VM\VBox\TOCR3CU9\StandardCU9.vhdx --format VHD c:\MyData\VM\VBox\TOCR3CU9\StandardCU9.vhd

2016-11-16

[AX2012] Unit Conversion Sample Code

Sample:

UnitOfMeasureConverter::convert(
           this.PurchReceivedNow,            UnitOfMeasure::unitOfMeasureIdBySymbol(this.PurchUnit),
UnitOfMeasure::unitOfMeasureIdBySymbol(this.inventTable().inventUnitId()),
NoYes::Yes,
InventTable::itemProduct(this.ItemId) or InventTable.product)

2016-11-14

[AX2012] Error: Logon failed for the unattended execution account

Error while setting server report parameters. Error message: The report server has encountered a configuration error. Logon failed for the unattended execution account. (rsServerConfigurationError)


Solution:
  1. Check your Service Account user & password in Reporting Service Configuration Manager.
  2. Check your Execution Account user & password in Reporting Service Configuration Manager.

2016-11-02

[AX2009] Merge Query Range from Two Different Queries

In my case, I want to merge query ranges from Query object to Form Data source. The data source will join to CustTable. The CustTable will be filtered by Query Object TIDCustTable.

public class FormRun extends ObjectRun
{
    QueryBuildDatasource    qbds3;
    Query                   q3;
    QueryRun                qRunCustTable;
}
 public void init()
{
    QueryBuildDatasource    qbds1, qbds4;
    ;
    qRunCustTable = new QueryRun(QueryStr(TIDCustTable));
    super();
    qbds1 = TIDIRTable_ds.query().dataSourceTable(tablenum(TIDIRTable));
    qbds4 = SalesTableDS_ds.query().dataSourceTable(tablenum(SalesTable));
    qbds4.addLink(fieldnum(TIDIRTable, SalesId), fieldnum(SalesTable, SalesId));

    qbds3 = qbds1.addDataSource(tablenum(CustTable));
    qbds3.addLink(fieldnum(TIDIRTable, InvoiceAccount), fieldnum(CustTable, AccountNum));

}

When click Select button:
void btnSelect()
{
    ;
    if(qRunCustTable.prompt())
    {
        q3  = new Query(qRunCustTable.query());
    }
}
Process button:
public void btnProcess()
{
    QueryBuildDatasource    qbds1 = TIDIRTable_ds.query().dataSourceTable(tablenum(TIDIRTable));
    QueryBuildDatasource    qbds4 = SalesTableDS_ds.query().dataSourceTable(tablenum(SalesTable));
    ;

    if(element.validateProcess())
    {
        qbds3.clearRanges();
        if(qRunCustTable.query().dataSourceTable(tablenum(CustTable)).rangeCount() >0)
            element.mergeRanges(TIDIRTable_ds.query(), qRunCustTable.query(), 4);//4 is table number of CustTable in TIDIRTable query


        SysQuery::findOrCreateRange(qbds1, fieldnum(TIDIRTable, InvoiceDate)).value(SysQuery::range(dtFromDate.dateValue(), dtToDate.dateValue()));
        if(txBranch.valueStr())
        {
            SysQuery::findOrCreateRange(qbds1, fieldnum(TIDIRTable, CountryRegionId)).value(SysQuery::value(txCountryRegionId.valueStr()));
            SysQuery::findOrCreateRange(qbds1, fieldnum(TIDIRTable, Branch)).value(SysQuery::value(txBranch.valueStr()));
        }
        else
        {
            if(qbds1.findRange(fieldnum(TIDIRTable, CountryRegionId)))
                qbds1.clearRange(fieldnum(TIDIRTable, CountryRegionId));

            if(qbds1.findRange(fieldnum(TIDIRTable, Branch)))
                qbds1.clearRange(fieldnum(TIDIRTable, Branch));
        }

        if(cbSalesStatus.selection() == SalesStatus::None)
        {
            qbds4.joinMode(JoinMode::OuterJoin);
            if(qbds4.findRange(fieldnum(SalesTable, SalesStatus)))
            {
                qbds4.clearRange(fieldnum(SalesTable, SalesStatus));
            }
        }
        else
        {
            qbds4.joinMode(JoinMode::InnerJoin);
            SysQuery::findOrCreateRange(qbds4, fieldnum(SalesTable, SalesStatus)).value(SysQuery::value(cbSalesStatus.selection()));
        }
        TIDIRTable_ds.executeQuery();
    }
}

Merge method:
public void mergeRanges(Query       q,
                               Query       initQ,
                               int         dsNo,
                               int         initQdsNo = 1,
                               boolean     alwaysAddDatasource     = false,
                               boolean     addSameFieldRange       = true)
{
    QueryBuildRange     fromQbr;
    QueryBuildRange     toQbr;
    QueryBuildDynalink  dyna;
    Counter             fromI = 1;
    //int                 qdsNo = initQdsNo;
    int                 qdsNo = dsNo;
    Counter             toI;
    tableId             tableId;
    DictField           dictField;
    int                 dictIndex;
    int                 originalId;

    void add()
    {
        q.addDataSource(initQ.dataSourceNo(initQdsNo).file());
        qdsNo = q.dataSourceCount();
    }

    void findToQbr(fieldId id)
    {
        originalId  = id;
        id          = fieldExt2Id(id);
        toQbr       = null;

        // get table id & dictField
        tableId = q.dataSourceNo(qdsNo).table();
        dictField = new DictField(tableId,id);
        dictIndex = originalId >> 16;

        // If this is a dimension field, get range using the array index, otherwise get range with id
        if ( ((dictField.arraySize() > 1) && q.dataSourceNo(qdsNo).findRange(fieldId2Ext(id, dictIndex))) ||
            ((dictField.arraySize() <= 1) && q.dataSourceNo(qdsNo).findRange(id)) )
        {
            // If same range is found, then next range
            if (! addSameFieldRange)
                continue;

            toI = 1;
            while (toI)
            {
                // is this a Dimension field?
                if (dictField.arraySize() > 1)
                    toQbr  = q.dataSourceNo(qdsNo).findRange(fieldId2Ext(id, dictIndex),toI);
                else
                    toQbr  = q.dataSourceNo(qdsNo).findRange(id,toI);

                if (! toQbr)
                    break;

                // If To value is empty, use it later
                if (! toQbr.value())
                    break;

                // If To and From values are equal then add new range later
                if (toQbr.value() == fromQbr.value())
                    break;

                toI++;
            }
        }
        if (! toQbr             ||
            (addSameFieldRange  &&
             toQbr              &&
             toQbr.value()      &&
             fromQbr.value()    &&
             toQbr.value()      != fromQbr.value()
            )
           )
        {

            if (dictField.arraySize() > 1)
            {
                // yes, working with Dimension
                toQbr  = q.dataSourceNo(qdsNo).addRange(fieldId2Ext(id, dictIndex));
            }
            else
            {
                toQbr  = q.dataSourceNo(qdsNo).addRange(id);
            }
        }
    }


    // Start

    if (! initQ)
        throw error(strfmt("@SYS23669",funcname()));

    if (! initQ.dataSourceNo(initQdsNo))
        throw error(strfmt("@SYS23667",funcname()));

    if (! q)
        throw error(strfmt("@SYS23669",funcname()));
/*
    if (q.dataSourceCount() >= initQdsNo)
    {
        if (q.dataSourceNo(initQdsNo).file() != initQ.dataSourceNo(initQdsNo).file())
        {
            if (alwaysAddDatasource)
                add();
            else
               throw error(strfmt("@SYS23668",funcname()));
        }
    }
    else
        add();
*/
    if (! initQ)
        return;

    for (fromI=1;fromI<=initQ.dataSourceNo(initQdsNo).rangeCount();fromI++)
    {
        fromQbr = initQ.dataSourceNo(initQdsNo).range(fromI);

        findToQbr(fromQbr.field());

        if (toQbr)
        {
            // Only set if From has a value
            if (fromQbr.value())
                toQbr.value(fromQbr.value());

            switch (fromQbr.status())
            {
                case RangeStatus::Locked:
                    if (toQbr.status() == RangeStatus::Open)
                        toQbr.status(RangeStatus::Locked);
                    break;
                case RangeStatus::Hidden:
                    switch (toQbr.status())
                    {
                        case RangeStatus::Open,RangeStatus::Locked:
                            toQbr.status(RangeStatus::Hidden);
                            break;
                    }
                    break;
            }
        }

    }

    fromI = 1;
    while (fromI <= initQ.dataSourceNo(initQdsNo).dynalinkCount())
    {
        dyna =  initQ.dataSourceNo(initQdsNo).dynalink(fromI);

        findToQbr(dyna.field());

        if (toQbr)
            toQbr.value(queryValue(dyna.cursor().(dyna.dynamicField())));

        fromI++;
    }
}