Scenario:
Sometime consultants don't want to use the Data Management in D365 due to lack of data structure knowledge, short timeline or many table relations. They want to import from an Excel to all related tables in one shot.Solution:
Prepare a batch class for importing master data. I normally use this template and just change the part of create() method for each data.using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class AeyImportTemplate extends RunBaseBatch
{
Filename ItemFileName;
Filename filename;
DialogField dialogFilename;
System.IO.Stream stream;
FileUploadTemporaryStorageResult fileUploadResult;
OfficeOpenXml.ExcelRange range;
CommaTextStreamIo commaIo;
RecordInsertList rsl;
boolean newIteration;
int line,
lineimported,lineerror, lineskipped;
Counter loop;
FileUploadBuild dialogFileUpload;
FileUpload fileUpload;
DialogRunbase dialog;
#define.CurrentVersion(1)
#define.Version1(1)
#localmacro.CurrentList
fileName
#endmacro
void create(int row)
{
str itemId;
price inventPrice;
InventTableModule inventTableModule;
itemId = range.get_Item(row, 1).Value;
inventPrice = range.get_Item(row, 2).Value;
try
{
ttsbegin;
select forUpdate inventTableModule
where inventTableModule.ItemId == ItemId
&& inventTableModule.ModuleType == ModuleInventPurchSales::Invent;
if (!inventTableModule)
{
inventTableModule.clear();
}
inventTableModule.Price = inventPrice;
if (!inventTableModule)
{
inventTableModule.insert();
}
else
{
inventTableModule.update();
}
lineimported ++;
ttscommit;
}
catch
{
lineerror ++;
ttsAbort;
}
}
void import()
{
Container con;
InventTable inventTable;
;
setPrefix("Import master data");
if (this.openFile())
{
using (ExcelPackage Package = new ExcelPackage(stream))
{
int rowCount, i, endRow, startRow;
Package.Load(stream);
ExcelWorksheet worksheet = package.get_Workbook().get_Worksheets().get_Item(1);
range = worksheet.Cells;
endRow = worksheet.Dimension.End.Row;
startRow = worksheet.Dimension.Start.Row;
rowCount = endRow - startRow + 1;
try
{
for (i = 2; i<= rowCount; i++)
{
setPrefix(strFmt("Line %1", i));
this.create(i);
}
}
catch
{
throw error("Error - Import template");
}
}
}
info(strfmt("%1 record(s) imported", lineimported));
info(strfmt("%1 record(s) fail", lineerror));
}
boolean openFile()
{
boolean ret = false;
if (fileUploadResult != null && fileUploadResult.getUploadStatus())
{
stream = fileUploadResult.openResult();
ret = true;
}
else
{
throw error(strfmt("@SYS62207",filename));
}
return ret;
}
public Object dialog()
{
dialog = super();
DialogGroup dialogGroup = dialog.addGroup('Upload file');
FormBuildControl formBuildControl = dialog.formBuildDesign().control(dialogGroup.name());
dialogFileUpload = formBuildControl.addControlEx(classstr(FileUpload), 'FileUpload');
dialogFileUpload.style(FileUploadStyle::MinimalWithFilename);
dialogFileUpload.fileNameLabel("@SYS308842");
dialogFileUpload.fileTypesAccepted(".xlsx");
return dialog;
}
/// <summary>
/// Disables the dialog Ok button until the file upload is complete.
/// </summary>
/// <param name="_dialog">The <c>Runbase</c> dialog object.</param>
public void dialogPostRun(DialogRunbase _dialog)
{
fileUpload = _dialog.formRun().control(_dialog.formRun().controlId('FileUpload'));
fileUpload.notifyUploadCompleted += eventhandler(this.uploadCompleted);
this.setDialogOkButtonEnabled(_dialog, false);
}
/// <summary>
/// After the file has been uploaded, the Ok button is enabled.
/// </summary>
public void uploadCompleted()
{
filename = fileUpload.fileName();
this.setDialogOkButtonEnabled(dialog, true);
fileUploadResult = fileUpload.getFileUploadResult();
fileUpload.notifyUploadCompleted -= eventhandler(this.UploadCompleted);
}
/// <summary>
/// Enables or disables the dialog Ok button.
/// </summary>
/// <param name = "_dialog">The <c>Runbase</c> dialog object.</param>
/// <param name = "_isEnabled">Indicates to enable or disable the Ok button.</param>
protected void setDialogOkButtonEnabled(DialogRunbase _dialog, boolean _isEnabled)
{
FormControl okButtonControl = _dialog.formRun().control(_dialog.formRun().controlId('OkButton'));
if (okButtonControl)
{
okButtonControl.enabled(_isEnabled);
}
}
public container pack()
{
return [#CurrentVersion,#CurrentList];
}
public boolean unpack(container packedClass)
{
Version version = runbase::getVersion(packedClass);
;
switch (version)
{
case #CurrentVersion:
[version,#CurrentList] = packedClass;
break;
default:
return false;
}
return true;
}
public void run()
{
this.import();
super();
}
static void main(Args _args)
{
AeyImportTemplate import;
FormRun formRun;
Args args;
;
import = new AeyImportTemplate();
if (import.prompt())
{
import.run();
}
}
public ClassDescription caption()
{
return 'Import template';
}
}
