How to add/ set Multiple Enum values in a query Range in X++ in AX 2012.?

 Here, we will discuss that how to setup range for multiple Enum values/ elements. for this we need to call query datasource first then pass the query range. follow as mentioned below:

Method-01: Datasource init()

public void init()
{

Query query;
super();

query = ProdTable_ds.query();
qbds = query.dataSourceTable(tableNum(ProdTable));

qbr = qbds.addRange(fieldNum(ProdTable, ProdStatus));

}

Method-02: Datasource executeQuery()

public void executeQuery()

{
//qbr.value(SysQuery::valueNot(ProdStatus::ReportedFinished));

qbr.value(strFmt('%1,%2,%3,%4,%5', enum2str(ProdStatus::Created),

enum2str(ProdStatus::CostEstimated),

enum2str(ProdStatus::Scheduled),

enum2str(ProdStatus::Released),

enum2str(ProdStatus::StartedUp))); super();

} 

Note:

qbr.value(strfmt("%1,%2", Date2StrXpp(fromDate), Date2StrXpp(todate)));

 

OR

qbds.addRange(fieldNum(EIRTenderRecordTable, Status))

        .value(queryValue(EIRStatus::Active));

qbds.addRange(fieldNum(EIRTenderRecordTable, StartDate))

        .value(SysQuery::range(_requisitionDate, dateNull()));

qbds.addRange(fieldNum(EIRTenderRecordTable, EndDate))

        .value(SysQuery::range(dateNull(), _requisitionDate)); 

Not empty records:

qbds.addRange(fieldnum(custtable,name)).value(sysquery::valueNotEmptyString()) ;

Empty records:

qbds.addRange(fieldnum(custtable,name)).value(sysquery::valueEmptyString()) ;

Records are not equal 10000:

qbds.addRange(fieldnum(custtable,accountnum)).value(sysquery::valueNot(“10000”) ) ;

Ranges:

qbds.addRange(fieldnum(custtable,accountnum)).value(queryrange(“1″,”10000”) ) ;

Enum:

qbds.addRange(fieldnum(B_RMARequestTable, RefType)).value(queryvalue(B_RMAReqRefType::RepairJournal));

Happy DAXing.....


  



 

Import/ Update Financial dimension through Excel or Table's data

Import/Update Financial dimension through excel data

 

 

 

 

Job: static void importFinancialDimension(Args _args)
{
SysExcelApplication application;

SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;

SysExcelWorksheets worksheets;

SysExcelWorksheet worksheet;

SysExcelCells cells;

COMVariantType type;

Name name;

FileName filename;

CustAccount custAccount;

CustTable custTable;

int row;


AxeDataRecord _dataRecord;

Description busStreem,CostCenter,Country,Department,Importer,IntOrder,Region,SubPrg,TradePartner;

    DimensionAttributeValue             dimAttrBusStreemVal,dimAttrCostcenterVal,dimAttrCountryVal,dimAttrDepartmentVal,dimAttrImporterVal,

dimAttrIntOrderVal,dimAttrRegionVal,dimAttrSubPrgVal,dimAttrTradePartnerVal;

    DimensionAttribute                  dimAttrBusStreem,dimAttrCostcenter,dimAttrCountry,dimAttrDepartment,dimAttrImporter,

dimAttrIntOrder,dimAttrRegion,dimAttrSubPrg,dimAttrTradePartner;

    DimensionAttributeValueSetStorage  davss;

    boolean                             isValidDimension = true;



    DimensionDefault                    DefaultDimension;

    davss = DimensionAttributeValueSetStorage::find(DefaultDimension);

    dimAttrBusStreem        =   DimensionAttribute::findByName('Business_Stream');

dimAttrCostcenter       =   DimensionAttribute::findByName('CostCenter');

    dimAttrCountry          =   DimensionAttribute::findByName('Country');

dimAttrDepartment       =   DimensionAttribute::findByName('Department');

dimAttrImporter         =   DimensionAttribute::findByName('Importer');

dimAttrIntOrder         =   DimensionAttribute::findByName('InternalOrder');

dimAttrRegion           =   DimensionAttribute::findByName('Region');

dimAttrSubPrg           =   DimensionAttribute::findByName('SubProgram');

dimAttrTradePartner  =   DimensionAttribute::findByName('TradingPartners');



application = SysExcelApplication::construct();

workbooks = application.workbooks();

//specify the file path that you want to read

//filename = "C:\\Users\\v-kunaya\\Documents\\Vimal\\DIXF\\Final data imported into MST\\Customer_PrimaryAddress_FinDim1.xlsx";

filename = "C:\\Users\\v-kunaya\\Documents\\Vimal\\DIXF\\Final data imported into MST\\Customer_PrimaryAddressAX2009_FinDim1.xlsx";

//filename = "C:\\DMF\\DIXF_vimsin\\Ready to Import\\Imported on 26102015\\Customer_PrimaryAddress_FinDim7.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++;
  DefaultDimension = 0;

custAccount = cells.item(row, 1).value().bStr();

if (custAccount =="")

custAccount = any2str(cells.item(row, 1).value().double());

busStreem = cells.item(row, 2).value().bStr();

if (busStreem == "")

busStreem = any2str(cells.item(row, 2).value().double());

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

if (CostCenter == "")

CostCenter = any2str(cells.item(row, 3).value().double());

Country = cells.item(row, 4).value().bStr();

if (Country == "")

Country = any2str(cells.item(row, 4).value().double());

Department = cells.item(row, 5).value().bStr();

if (Department == "")

Department = any2str(cells.item(row, 5).value().double());

Importer = cells.item(row, 6).value().bStr();

if (Importer == "")

Importer = any2str(cells.item(row, 6).value().double());

IntOrder = cells.item(row, 7).value().bStr();

if (IntOrder == "")

IntOrder = any2str(cells.item(row, 7).value().double());

Region = cells.item(row, 8).value().bStr();

if (Region == "")

Region = any2str(cells.item(row, 8).value().double());

SubPrg = cells.item(row, 9).value().bStr();

if (SubPrg == "")

SubPrg = any2str(cells.item(row, 9).value().double());

TradePartner = cells.item(row, 10).value().bStr();

if (TradePartner == "")

TradePartner = any2str(cells.item(row, 10).value().double());

dimAttrBusStreemVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrBusStreem, busStreem, false, true);

dimAttrCostcenterVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrCostcenter, CostCenter, false , true);

    dimAttrCountryVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrCountry, Country, false, true);

dimAttrDepartmentVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrDepartment, Department, false, true);

dimAttrImporterVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrImporter, Importer, false, true);

dimAttrIntOrderVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrIntOrder, IntOrder, false, true);

dimAttrRegionVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrRegion, Region, false, true);

dimAttrSubPrgVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrSubPrg, SubPrg, false, true);

dimAttrTradePartnerVal = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrTradePartner, TradePartner, false, true);

    if (dimAttrBusStreemVal)
    {
       davss.addItem(dimAttrBusStreemVal);
    }     if (dimAttrCostcenterVal)


    {

        davss.addItem(dimAttrCostcenterVal);

    }

if (dimAttrCountryVal)


    {

        davss.addItem(dimAttrCountryVal);

    }

     if (dimAttrDepartmentVal)


    {

        davss.addItem(dimAttrDepartmentVal);

    }

if (dimAttrImporterVal)


    {

        davss.addItem(dimAttrImporterVal);

    }

     if (dimAttrIntOrderVal)


    {

        davss.addItem(dimAttrIntOrderVal);

    }

if (dimAttrRegionVal)


    {

        davss.addItem(dimAttrRegionVal);

    }

     if (dimAttrSubPrgVal)


    {

        davss.addItem(dimAttrSubPrgVal);

    }

if (dimAttrTradePartnerVal)


    {

        davss.addItem(dimAttrTradePartnerVal);

    }

if (dimAttrBusStreemVal || dimAttrCostcenterVal || dimAttrCountryVal || dimAttrDepartmentVal || dimAttrImporterVal


|| dimAttrIntOrderVal || dimAttrRegionVal || dimAttrSubPrgVal || dimAttrTradePartnerVal)

    {

        DefaultDimension =   davss.save();

    }
     else

         DefaultDimension = 0;

   ttsBegin;

select forupdate custTable

where custTable.AccountNum == custAccount;

if(custTable && custTable.DefaultDimension == 0)


{

custTable.DefaultDimension = DefaultDimension;

custTable.update();

info(strFmt("%1 is updated",custAccount));


}
else

info(strFmt("%1 is not updated",custAccount));

ttsCommit;

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



}
while (type != COMVariantType::VT_EMPTY);



application.quit();

}

Import/Update Financial dimension through available table's data

Job: public DimensionDefault findDefaultDimId(AxeDataRecord _dataRecord)

{

    DimensionAttributeValue             dimCostcenterVal,dimDepartmentVal;

    DimensionAttribute                  dimAttrCostcenter,dimAttrDepartment;

    DimensionAttributeValueSetStorage   davss;

    boolean                             isValidDimension = true;

    DimensionDefault                    DefaultDimension; 

    davss = DimensionAttributeValueSetStorage::find(DefaultDimension);

    dimAttrCostcenter           =   DimensionAttribute::findByName('CostCenter');

    dimAttrDepartment           =   DimensionAttribute::findByName('Department'); 

   dimCostcenterVal      =   DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrCostcenter, _dataRecord.value('Dimension2_'), false , true);

    dimDepartmentVal      =   DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrDepartment, _dataRecord.value('Dimension') , false, true);

    if(dimCostcenterVal )

    {

        davss.addItem(dimCostcenterVal);

    }

    if (dimDepartmentVal)

    {

        davss.addItem(dimDepartmentVal);

    }

    if(dimCostcenterVal || dimDepartmentVal)

    {

        DefaultDimension =   davss.save();

 

        return DefaultDimension;

    }

    else

        return 0;

}