Hello Community,
I want to import budget register entries.
I'have tried two methods :
1-EXCEL AD-IN
2-Data export /import Framework > common > Processing group > Budget
But both none of these works for me :
1-EXCEL AD-IN error message:
( I can't find no field called "Reference").
2-
*This post is locked for comments
The problem has been resolved.
small tip for BudgetTransactionLine:
make sure that you don't put entry number field as RANDOM but rather put the entry number of the header that you create manually. This will allow you to imports lines on the wished header.
Hi Ludwig,
I have the same issue as Imane Iam, I tried both links methodologies.
Following step by step the first link gave me a positive result message saying the record was published by it actually imported only the header and not the line.
Following step by step the second link, I have exactly the same error message as initially.
" BudgetTransactionLine.createList Line=4, Pos=4, Xpath=/BudgetTransactionLine/BudgetTransactionLine[1] The value in field BudgetTransactionHeader is invalid.
Field 'Reference' must be filled in.
Error found when validating record.
Field 'Reference' must be filled in.
No dimensions are defined for budgeting for the ledger. Define dimensions for budgeting to continue processing the entry
Error found when validating record.
Creation has been canceled."
Same result as with the first method, the header is imported but not the line.
I checked and Dimensions ARE defined for budgeting for the ledger.
Do you have an idea on what field is actually responsible to link BudgetTransactionHeader and BudgetTransactionLine and could be the "Reference" field the error is mentioning?
Sincerely,
M. Knobel
Hello,
It's already solved . Thank you
Hello,
It's already solved . I've used the second link .
Thank you
Hello Imane,
Have you setup the budgeting module and defined the dimensions for budgeting, the budget models, codes, etc.?
The reference is most likely the linkage between the header and the lines.
To see what needs to be entered, you can manually register a budget record and then export it with the help of DIXF.
In the output Excel file you should see what you need to fill.
Best regards,
Ludwig
Hello,
Yes i am trying the upload in AX2012, i've tried the method in the link but i get this error message:
"BudgetTransactionLine.createList Line=4, Pos=4, Xpath=/BudgetTransactionLine/BudgetTransactionLine[1] The value in field BudgetTransactionHeader is invalid.
Field 'Reference' must be filled in.
Error found when validating record.
Field 'Reference' must be filled in.
No dimensions are defined for budgeting for the ledger. Define dimensions for budgeting to continue processing the entry
Error found when validating record.
Creation has been canceled"
Share
Report
All responses (
Answers (
static void KTI_fixedAsset2(Args _args)
{
Dialog dialog;
Dialogfield dialogfield;
SysExcelApplication application;
SysExcelWorkbooks workBooks;
SysExcelWorkbook workBook;
SysExcelWorksheets workSheets;
SysExcelWorksheet workSheet;
SysExcelCells cells;
AssetTable assettable;
AssetBook assetbook;
AssetType assettype;
AssetPropertyType assetPropertyType;
NoYes noYes;
AssetDepreciationConvention assetDepreciationConvention;
Filename fileName;
Container filterCriteria;
COMVariantType type;
int row = 1 ;
int recordcnt ;
str 70 assetId,bookid;
str assetGroup,Name, Namealias,model,modelyear,serialnum, location, postingprofile,despreciation,techinfo1,lifetime;
real quantity,unitcost,acquistionprice,lifetimerest;
date deprectionstartdate,lasttdepreciationdate,acquistindate,usedfromdate;
anytype propertytype,servicelife;
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 "";
}
application = SysExcelApplication::construct();
workBooks = application.workbooks();
dialog = new Dialog("FileOpen");
dialogfield = dialog.addField(extendedTypeStr(Filenameopen), "File Name");
filterCriteria = ['*.xlsx'];
filterCriteria = dialog.filenameLookupFilter(filterCriteria);
dialog.run();
if (dialog.run())
{
filename = (dialogfield.value());
}
try
{
workBooks.open(fileName);
}
catch (Exception::Error)
{
throw error("File Cannot be opened");
}
workBook = workBooks.item(1);
workSheets = workBook.worksheets();
workSheet = workSheets.itemFromNum(1);
cells = workSheet.cells();
do
{
try
{
row++;
assetid = COMVariant2Str(cells.item(row, 1).value());
assetGroup = cells.item(row, 2).value().bStr();
Name = cells.item(row, 3).value().bStr();
Namealias = cells.item(row, 4).value().bStr();
assettype = str2enum(AssetType,cells.item(row, 5).value().bStr());
propertytype = COMVariant2str(cells.item(row,6).value());
quantity = cells.item(row, 7).value().double();
unitcost = cells.item(row, 8).value().double();
model = cells.item(row, 9).value().bstr();
modelyear = cells.item(row, 10).value().bstr();
serialnum = COMVariant2str(cells.item(row,11).value());
location = cells.item(row, 12).value().bStr();
postingprofile = cells.item(row, 15).value().bStr();
bookid = cells.item(row, 16).value().bStr();
acquistindate = cells.item(row, 17).value().date();
usedfromdate = cells.item(row, 18).value().date();
acquistionprice = cells.item(row, 19).value().double();
deprectionstartdate = cells.item(row, 20).value().date();
lasttdepreciationdate = cells.item(row, 21).value().date();
servicelife = COMVariant2str(cells.item(row,22).value());
lifetime = COMVariant2str(cells.item(row,23).value());
lifetimerest = cells.item(row, 24).value().double();
noYes = str2enum(NoYes,cells.item(row, 25).value().bStr());
techinfo1 = cells.item(row, 26).value().bstr();
assetDepreciationConvention = str2enum(AssetDepreciationConvention,cells.item(row, 27).value().bStr());
breakpoint;
ttsBegin;
if(assettable.AssetId == assetId)
{
select forUpdate assettable where assettable.AssetId == assetid;
// && assettable.dataAreaId == "RURT";
ttsBegin;
assetTable.initValue();
assettable.AssetId= assetid;
assettable.AssetGroup=assetGroup;
assettable.Name=Name;
assettable.NameAlias=Namealias;
assettable.AssetType=assettype;
assettable.propertytype= assetPropertyType;
assettable.quantity= quantity;
assettable.UnitCost= unitcost;
assettable.model= model;
assettable.modelyear= modelyear;
assettable.serialnum= serialnum;
assettable.location= location;
assettable.Techinfo1= techinfo1;
assettable.update();
ttsCommit;
}
else
{
assetTable.clear();
select * from assettable
where assettable.AssetId != assetId;
assetTable.initValue();
assettable.AssetId= assetid;
assettable.AssetGroup=assetGroup;
assettable.Name=Name;
assettable.NameAlias=Namealias;
assettable.AssetType=assettype;
assettable.propertytype= assetPropertyType;
assettable.quantity= quantity;
assettable.unitcost= unitcost;
assettable.model= model;
assettable.modelyear= modelyear;
assettable.serialnum= serialnum;
assettable.location= location;
assettable.Techinfo1= techinfo1;
assetTable.insert();
}
select forUpdate assetbook
where assetbook.AssetId != assettable.AssetId
&& assetbook.AssetId != assetId
&& assetbook.BookId != bookid;
if(!assetbook)
{
ttsBegin;
assetbook.BookId = bookid;
assetBook.selectForUpdate(true);
assetbook.AcquisitionDate = acquistindate;
assetbook.AcquisitionPrice = acquistionprice;
assetbook.usedfromdate= usedfromdate;
assetbook.Depreciationstartdate =deprectionstartdate;
assetbook.lastdepreciationdate= lasttdepreciationdate;
assetbook.ServiceLife =( servicelife);
assetbook.PostingProfile = postingprofile;
assetbook.lifetime = str2int(lifetime);
assetbook.lifetimerest= lifetimerest;
assetbook.DepreciationConvention= assetDepreciationConvention;
assetbook.Depreciation = noYes;
assetBook.AssetId = assetTable.AssetId;
assetBook.update();
ttsCommit;
}
else
{
assetBook.clear();
assetBook.initValue();
assetbook.selectForUpdate(true);
assetbook.BookId = bookid;
assetbook.AcquisitionDate = acquistindate;
assetbook.AcquisitionPrice = acquistionprice;
assetbook.usedfromdate= usedfromdate;
assetbook.Depreciationstartdate =deprectionstartdate;
assetbook.lastdepreciationdate= lasttdepreciationdate;
assetbook.ServiceLife = (servicelife);
assetbook.PostingProfile = postingprofile;
assetbook.lifetime=str2int( lifetime);
assetbook.lifetimerest= lifetimerest;
assetbook.DepreciationConvention= assetDepreciationConvention;
assetbook.Depreciation = noYes;
assetBook.AssetId = assetTable.AssetId;
assetBook.insert();
}
ttsCommit;
}
catch(Exception::Error)
{
throw error("The missing asset id %1 :",assettable.AssetId );
}
//type = cells.item(row+1, 1).value().variantType();
info(strFmt("Records %1-%2 inserted",assetTable.AssetId,assetTable.Location));
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
workbooks.close();
info("Done");
}
Try the above code according to your requirements,
Since dont know the language that you have attached to the excel.I am giving you the code.
Please try and let me know.
Regards,
AXTechie210
Hello Imane,
You try to do the upload in AX2012, right?
Have you already checked the available blogposts for this upload.
There are really a lot of them available that demonstrate the upload and that might help you.
See e.g.
blogs.msdn.microsoft.com/.../budget-entries-import-in-ax2012
axfinance.blogspot.com/.../budget-register-entry-upload-excel-add.html
Also check out the youtube channels and videos that are available there.
Best regards,
Ludwig
you can just write a job to export CSV or excel file to AX.
Just sent the File bharathkaruna2120@gmail.com
I can help you with the job.
i can't attach the file
I know it's fields error but i don't understand why i get some fields to fill while trying to import withe EXCEL ADD-IN that are different to the fields needed in import through Data import export framework.
What method should i adopt.
Thank you,
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156