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;

}

How to Create/Update/Find a phone number for a customer/vendor in X++ [AX 2012]

As a follow-up to my last post about finding phone numbers, here is sample code of how you can properly find/create/update a phone number for a customer/vendor.

Case- 1: Creating or updating phone number for a customer/vendor

static void CreatePhoneExample(Args _args)
{
    CustTable                           custTable = CustTable::find('100013'); // TODO - Change to your customer
    LogisticsElectronicAddress          logisticsElectronicAddress;
    container                           defaultRole = map2Con(LogisticsLocationEntity::getDefaultLocationRoleFromEntity(tableNum(DirPartyTable)));
    
    setPrefix(strFmt("Creating/Updating number for customer %1", custTable.AccountNum));
    
    // This will find/create a number for a customer
    ttsBegin;
    logisticsElectronicAddress.Type = LogisticsElectronicAddressMethodType::Phone;
    logisticsElectronicAddress.Locator = '555-555-5555';
    
    logisticsElectronicAddress.Location = DirPartyLocation::findOrCreate(custTable.Party, 0).Location;

    // This will find or create the new logisticsElectronicAddress
    // If it does not find it, it will do a .insert() which will only persist these fields (Location, Type, Locator, LocatorExtension)
    // so if you want to set the Description or if it's primary or not, you will need to update the record after this call
    logisticsElectronicAddress = LogisticsElectronicAddress::findOrCreate(logisticsElectronicAddress);
    
    // We re-select it for update in case this isn't a new number and it found an existing
    // because the "find" doesn't "select for update"
    logisticsElectronicAddress = LogisticsElectronicAddress::findRecId(logisticsElectronicAddress.RecId, true);
    
    logisticsElectronicAddress.Description = "New Primary Phone";
    
    // If you set the number to primary, during the insert/update it will handle unassigning previously
    // marked primary numbers if they exist
    logisticsElectronicAddress.IsPrimary = NoYes::Yes;
    
    logisticsElectronicAddress.update();
    
    // At this point, we need to mark the "purpose" of the number.  I'm just using the default role, which should be "Business"
    LogisticsEntityLocationRoleMap::createEntityLocationRoles(tableNum(LogisticsElectronicAddressRole), logisticsElectronicAddress.RecId, conPeek(defaultRole, 1), true);
    
    info(strFmt("Created/updated phone number [%1] for customer %2.", logisticsElectronicAddress.Locator, custTable.AccountNum));
    ttsCommit;
}

Case- 1: Finding phone number for a customer/vendor

Method-1:

Display LogisticsElectronicAddressLocator Phone()
{
    LogisticsElectronicAddress      logisticsElectronicAddress;

    //return LogisticsElectronicAddress::findByLocation(DirPartyLocation::findOrCreate(this.Party, 0).Location).Locator;

    select Location, Type, Locator from logisticsElectronicAddress
        where logisticsElectronicAddress.Location == DirPartyLocation::findOrCreate(this.Party, 0).Location
           && logisticsElectronicAddress.Type == LogisticsElectronicAddressMethodType::Phone;
    Return logisticsElectronicAddress.Locator;
}



Note: Here this.Party may refer to MSDirPartyPostalAddressView.Party or CustTable/VendTable.Party;



Method-2: 

Display Description PhoneDescription()
{
    LogisticsElectronicAddress      logisticsElectronicAddress;

    //return LogisticsElectronicAddress::findByLocation(DirPartyLocation::findOrCreate(this.Party, 0).Location).Locator;

    select Location, Type, Locator from logisticsElectronicAddress
        where logisticsElectronicAddress.Location == DirPartyLocation::findOrCreate(this.Party, 0).Location
           && logisticsElectronicAddress.Type == LogisticsElectronicAddressMethodType::Phone;
    Return logisticsElectronicAddress.Description;
}

Note: Here also this.Party may refer to MSDirPartyPostalAddressView.Party or CustTable/VendTable.Party;





Happy DAXing.....

The Table.Field does not have a corresponding parm-method on the AxBC-class

If you add new field in a table and getting "The Table.Field does not have a corresponding parm-method on the AxBC-class" error message, you can run following Job to fix this issue. This job will add corresponding parm-method for newly added field in a table.

static void CreateAxBCParmMethodJob(Args _args)

{ 
    axGenerateAxBCClass axGenerateAxBCClass; 

    axGenerateAxBCClass = AxGenerateAxBCClass::newTableId(tablenum(CustTable));

    axGenerateAxBCClass.run(); 
 

}

The Table.Field does not have a corresponding parm-method on the AxBC-class

If you add new field in a table and getting "The Table.Field does not have a corresponding parm-method on the AxBC-class" error message, you can run following Job to fix this issue. This job will add corresponding parm-method for newly added field in a table.

static void CreateAxBCParmMethodJob(Args _args)

{ 
    axGenerateAxBCClass axGenerateAxBCClass; 

    axGenerateAxBCClass = AxGenerateAxBCClass::newTableId(tablenum(CustTable));

    axGenerateAxBCClass.run();  

}

Happy DAXing....