Noted: This content is based on Dynamics 365 for Finance and Operations version 8.0 Platform Update 15
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';
}
}
Noted: This content is based on Dynamics 365 for Finance and Operations version 8.0 Platform Update 15
Scenario:
Rename many customer names.
Solution:
If the table has many relations, it will take a long time to rename through the record information one by one. We can use the table method renamePrimaryKey() in a batch class and run in batch mode.
CustTable custTable;
ttsBegin;
select firstOnly custTable
where custTable.AccountNum == 'XXXX-WC-00002';
if (custTable)
{
custTable.AccountNum = 'XXXX-WC-00001';
custTable.renamePrimaryKey();
}
ttsCommit;
Noted: This content is based on Dynamics 365 for Operations Platform Update 5
In previous AX version, once we want to change the string field size, we just only edit its String Size easily. But it's funny that there are more steps in Dynamics 365.
Scenario:
Extend invoice field to 30 characters.
Overlaying or extension:
Not matter I try both overlaying and extension. The String Size properties is always disabled. Only using EDT overlaying, that allow to change the Extends properties.
EDT overlaying is available, only when the EDT isn't in Application Platform package. As below, the InvoiceId is in Application Suite, it's allowed to adjust Extends properties.
The reason why we can't edit EDT in Application Platform package, e.g. Num. Bececause we can't create the model under that package.
So, your project must be in the model that under ApplicationSuite package for overlaying the EDT.
Then, I create a new EDT with String Size 30 and replace it in InvoiceId's Extends properties. Now the String Size is changed to 30.
Later that, don't forget to do full synchronize database (Go to Dynamics 365 > Synchronized database) to check if there is an affect with some object. If there is an error, you must adjust that object too.
Noted: This content is based on Dynamics 365 for Operations Platform Update 5
Yesterday, I tried my first customization for existing Report. I can't find any training resource on Microsoft Learning Portal. So, this is the way I tried by myself.
Scenario:
Customize an existing report on Dynamics 365. For example, Dimension Statement in General Ledger module.
Create Model:
Once you know which report will be customized and search for all related object. Notice that those objects are under "Application Suite".
When create a model about report, we must select the option "Select existing package" as a part of "ApplicationSuite"
Add report the project:
Once create a new project after creating model. Go to the report in AOT > right click Customize. When finish to modify the report layout, right click at the report > Deploy Reports.
Modify Class:
If you have to modify the DataProvider class, right click > Customize.
Build & Deploy:
Once finish customization, we can build the project and deploy the report together. Select the project > right click > Deploy Reports.
Noted: This content is based on Dynamics 365 for Operations Platform Update 5
It's been really long time, right? I changed job 2 years ago and was too busy with projects. Since right now, we are moving to Dynamics 365. While writing this blog, I'm doing self-learning (started 3 days ago) and work with a project together. So, I want to make a note for later reference.
Scenario:
In FreeText invoice form, create a lookup invoice list of current customer in the freeText invoice line.
I'll skip how to create new field in Dynamics 365 since there are many blogs mentioned already. This will show the event handler class for the field in form only.
Referenced packages:
Because we are going to write code about Query and static class. We need to reference some packages. I prepared a model referencing below packages.
- ApplicationFoundation
- ApplicationPlatform
- ApplicatonSuite
- Directory
- SourceDocumentation
- SourceDocumentationTypes
How do you know which package we should reference? Just notice from the model of class we want to call. For example as below, I'm going to use SysQuery class belonging to "Application Foundation".
Or when I want to create a static class, there are 3 packages are needed; Directory, SourceDocumentation, SourceDocumentationTypes. Once the project needs more packages, it will show some errors when we build the project to inform you.
Copy Event Handler method:
I created a string control for the new field and want to override OnLookup event. Under the form control, go to Events > OnLookup > right click > Copy event handler method.
Event Handler Class:
Create a class to project and paste code. It will create a method with FormControlEventType::Loopup. This lookup will be used in another form. So, I create another extension class sending the FormControl, its EventAgrs and InvoiceAccount as parameters. Notice the red text blow to retrieve a field value (InvoiceId) from form datasource.
/// <summary>
/// Handles events raised by <c>CustFreeInvoice</c> form.
/// </summary>
[ExtensionOf(FormStr(CustFreeInvoice))]
final public class PKA_CustFreeInvoiceEventHandler_Extension
{
/// <summary>
/// Adds a lookup to the <c>RefInvId</c> control on <c>CustInvoiceLine</c> form.
/// </summary>
/// <param name="_sender">The source of the event.</param>
/// <param name="_e">Arguments of the OnLookup event.</param>
[FormControlEventHandler(formControlStr(CustFreeInvoice, CustInvoiceLine_RefInvId), FormControlEventType::Lookup)]
public static void CustInvoiceLine_PKA_RefInvId_OnLookup(FormControl _sender, FormControlEventArgs _e)
{
FormRun form = _sender.formRun();
FormDataSource custInvoiceTable_ds = form.dataSource(formDataSourceStr(CustFreeInvoice, CustInvoiceTable)) as FormDataSource;
CustInvoiceTable custInvoiceTable = custInvoiceTable_ds.cursor();
PKA_CustInvoiceTable_Extension::lookup_RefInvId(_sender, _e, custInvoiceTable.InvoiceAccount);
}
}
/// <summary>
/// Method extension for <c>CustInvoiceTable</c> table.
/// </summary>
public static class PKA_CustInvoiceTable_Extension
{
/// <summary>
/// Adds an InvoiceId lookup from CustInvoiceJour.
/// </summary>
/// <param name="_sender">The source of the event.</param>
/// <param name="_e">Arguments of the OnLookup event.</param>
[SysClientCacheDataMethodAttribute(true)]
public static void lookup_RefInvId(FormControl _sender, FormControlEventArgs _e, CustInvoiceAccount _invoiceAccount)
{
SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(CustInvoiceJour), _sender);
Query query = new Query();
sysTableLookup.addLookupfield(fieldnum(CustInvoiceJour, InvoiceId), true);
sysTableLookup.addLookupfield(fieldnum(CustInvoiceJour, InvoiceAccount));
sysTableLookup.addLookupfield(fieldnum(CustInvoiceJour, InvoiceDate));
QueryBuildDataSource qbds = query.addDataSource(tablenum(CustInvoiceJour));
QueryBuildRange qdr = qbds.addRange(fieldnum(CustInvoiceJour, InvoiceAccount));
qdr.value(queryValue(_invoiceAccount));
qdr = qbds.addRange(fieldnum(CustInvoiceJour, InvoiceId));
qdr.value(sysQuery::valueNotEmptyString());
sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();
FormControlCancelableSuperEventArgs ce = _e as FormControlCancelableSuperEventArgs;
//cancel super() to prevent error.
ce.CancelSuperCall();
}
}
