Upload financial dimensions through Excel.

Finance and Operations/AX Developer Forum
1

Hi all,

I have new form, in that form i have a Upload button when i click the Upload button data should be fill in below fields.

fields are MainAccountId,Division,Department,Section,AmountMST.

Those fields are coming from GL–Common–MainAccounts and DIVISION,Department and Sections fields are coming from General ledger/Setup/Financial Dimensions.

Please suggest me any one have code for that.

2

So you already have these setups in AX? Does division and department are separate fields or its just the single dimension field?

You need to be providing all the necessary information before expecting a proper answer.

3

Hi kranthi,

division and department are separate fields and when click the Upload button excel data should be filled in below form.

pastedimage1525342373763v1.png

thanks for reply

4

Then you can write your code to read the data from excel and insert into table/tables used in that form. Do you see any issue here?

Search on web, you can find number of code examples on how to read the data from excel. Try and let us know if you see any issue.

5

Hi kranthi,

the below code i was used in excel import. its working fine for me.

public void DataImport(LTCostValue _ltCostValue)
{
int insertcount= 0;
int row = 1;
Name Division,Department,Section;
MainAccountNum mainAccountId;
real Amount;

str COMVariant2Str(COMVariant _cv, int _decimals = 0,int _characters = 0,int _separator1 = 0,int _separator2 = 0)
{
switch(_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();
case (COMVariantType::VT_R4):
return num2str(_cv.float(),_characters,_decimals, _separator1,_separator2);
case (COMVariantType::VT_R8):
return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),_characters,_decimals, _separator1, _separator2);
case (COMVariantType::VT_DATE):
return date2str(_cv.date(),123,2,1,2, 1,4);
case (COMVariantType::VT_EMPTY):
return “”;
default:
throw error(strfmt("@SYS26908",_cv.variantType()));
}
return “”;
}

dialog = new Dialog();
dialog.caption(“Select File”);
fileName = dialog.addField(extendedTypeStr(FilenameOpen));
dialog.run();

application = SysExcelApplication::construct();
workbooks = application.workbooks();

try
{
workbooks.open(fileName.value());
}
catch
{
throw error(“File cannot be opened”);
}

workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();

do
{
row++;

mainAccountId = COMVariant2Str(cells.item(row,1).value());
division = COMVariant2Str(cells.item(row,2).value());
Department = COMVariant2Str(cells.item(row,3).value());
Section = COMVariant2Str(cells.item(row,4).value());
Amount = any2real(COMVariant2Str(cells.item(row,5).value()));

ttsBegin;
costAllocationUploadTrans.clear();
costAllocationUploadTrans.LTCostValue = _ltCostValue;
costAllocationUploadTrans.MainAccountId = mainAccountId;
costAllocationUploadTrans.Division = Division;
costAllocationUploadTrans.Department = Department;
costAllocationUploadTrans.Section = Section;
costAllocationUploadTrans.Amount = Amount;
costAllocationUploadTrans.insert();
ttsCommit;
comtype = cells.item(row+1, 1).value().variantType();
}
while (comtype != COMVariantType::VT_EMPTY);
application.quit();

}

Main() code:-

public static void main(Args _args)
{
FormRun caller;
FormStringControl formcontrolCostValue;
LTCostValue ltcostValue;
FormDataSource formDataSource;
LTCostValueUpload ltCostValueUpload;

LT_CostValueUpload load = new LT_CostValueUpload();
ltCostValueUpload = _args.record();
load.DataImport(ltCostValueUpload.AllocationId);
formDataSource = _args.record().dataSource();
formDataSource.research(true);
}

Thanks for suggest to me!!!