Processing an Excel File in Batch Using the FileUpload Functionality Together with SysOperationFramework
Sometimes in D365 things happen which are either human or system error, which will leave you with faulty data in your system.
That kind of thing happened some time ago for one of our clients, when a daily batch job removed all the loads for the purchase orders which had lines who have ‘Registered’ quantities, and left them with more than 3000 purchase orders for which they couldn’t create and post a product receipt journal, because they didn’t have the load associated with them, and going with the logic of creating a new batch job for posting product receipts for quantity = ‘Registered’ would’ve broke their workflow.
So, they exported the purchase orders that they wanted to update, and after seeing that the update should be done for more than 3000 orders, I knew that I should extend the SysOperationFramework, so the update could be done in batch processing on the server, and the users wouldn’t experience any performance issues on the interface itself.
When talking about extending the SysOperationFramework parts, you probably already know that we will have the well-known structure of classes in order to design our batch process: the controller class, responsible for running the operation and modifying the contract, the contract class, responsible for passing parameters and values from the user to the logic that processes operation, the service class, responsible for providing the logic which is being executed (in our example, iterating through the Excel file, getting the purchase orders IDs and posting a product receipt for them), and the User Interface builder (UIBuilder) class, responsible for the actual looks and event handlers of the dialog that is presented to the user.
Let’s have a look at what our code changes will look like in the D365 user interface, before scheduling the actual batch job:
Analyzing this dialog, we will see that it looks like every other batch dialog, with only one parameter: a file.
Below, we will see the code which is taking care of the design and functionality for this batch process.
The Controller Class:
/// <summary>
/// Controller class for creating & posting PO packing slips
/// </summary>
class AXMPostPOReceiptsController extends SysOperationServiceController
{
/// <summary>
/// Creates new instance of <c>AXMPostPOReceiptsController</c>
/// </summary>
public void new()
{
super();
this.parmClassName(classStr(AXMPostPOReceiptsService));
this.parmMethodName(methodStr(AXMPostPOReceiptsService, processOperation));
this.parmDialogCaption("Create packing slips");
}
/// <summary>
/// Sets the caption of the job
/// </summary>
/// <returns>Caption of the job</returns>
public ClassDescription caption()
{
return "Create packing slips";
}
/// <summary>
/// Method which is run while calling the corresponding menu item
/// </summary>
/// <param name = "args">Arguments passed from the menu item</param>
public static void main(Args args)
{
AXMPostPOReceiptsController controller;
controller = new AXMPostPOReceiptsController();
controller.startOperation();
}
}
Looking at the code for the AXMPostPOReceiptsController, we see that it has 3 methods, of which 2 are overridden: new and caption. Above every method there is a summary explanation of what that method is doing. We can see that in the new method, we are initializing a new instance of the controller class and are defining the processing class and method (the service class), in our case, AXMPostPOReceiptsService class.
Please don’t be confused about the caption and the dialog caption for the batch job, ‘Create packing slips’, we know that sometimes we also refer to the product receipts as packing slips.
The Data Contract Class:
/// <summary>
/// Data contract class for creating & posting PO packing slips
/// </summary>
[ DataContract,
SysOperationContractProcessing(classStr(AXMPostPOReceiptsUIBuilder))
]
class AXMPostPOReceiptsDataContract
{
container storageResult;
/// <summary>
/// Parameter method which holds values of the packed variables from <c>FileUploadTemporaryStorageResult</c> class
/// </summary>
/// <param name = "_storageResult">Packed instance of <c>FileUploadTemporaryStorageResult</c> class</param>
/// <returns>Container with packed values</returns>
[DataMemberAttribute('StorageResult')]
public container parmStorageResult(container _storageResult = storageResult)
{
storageResult = _storageResult;
return storageResult;
}
}
As said, the data contract class holds the parameters that user wants to pass to the logic which is being executed. In our case, we have one parameter that we’re passing, storageResult of type container. As explained in the summary, this variable holds the values of the packed variables of the FileUploadTemporaryStorageResult class instance, which in fact holds the upload result of the FileUpload control.
As for now, the only thing that you need to know is that FileUploadTemporaryStorageResult implements SysPackable, which means that we can do serialization on it, like pack and unpack the variables defined in the #CurrentList for the #CurrentVersion macro.
Note: Please visit the File upload control page, where you can find documentation on this control.
The UI Builder Class:
/// <summary>
/// UI Builder class for posting purchase orders packing slips
/// </summary>
class AXMPostPOReceiptsUIBuilder extends SysOperationUIBuilder
{
private str availableTypes = ".csv, .xlsx";
private const str OkButtonName = 'CommandButton';
private const str FileUploadName = 'FileUpload';
AXMPostPOReceiptsDataContract contract;
/// <summary>
/// Overriden the <c>postBuild</c> method to add a <c>FileUpload</c> control
/// </summary>
public void postBuild()
{
DialogGroup dialogGroup;
FormBuildControl formBuildControl;
FileUploadBuild dialogFileUpload;
super();
contract = this.dataContractObject();
dialogGroup = dialog.addGroup("File path");
formBuildControl = dialog.formBuildDesign().control(dialogGroup.name());
dialogFileUpload = formBuildControl.addControlEx(classstr(FileUpload), FileUploadName);
dialogFileUpload.style(FileUploadStyle::MinimalWithFilename);
dialogFileUpload.baseFileUploadStrategyClassName(classstr(FileUploadTemporaryStorageStrategy));
dialogFileUpload.fileTypesAccepted(availableTypes);
dialogFileUpload.fileNameLabel("@SYS308842");
}
/// <summary>
/// Subscribes events to the dialog form
/// </summary>
/// <param name = "_formRun">The instance of the dialog form</param>
private void dialogEventsSubscribe(FormRun _formRun)
{
FileUpload fileUpload = _formRun.control(_formRun.controlId(FileUploadName));
fileUpload.notifyUploadCompleted += eventhandler(this.uploadCompleted);
fileUpload.notifyUploadAttemptStarted += eventhandler(this.uploadStarted);
_formRun.onClosing += eventhandler(this.dialogClosing);
}
/// <summary>
/// Executes logic for unsubscribing the registered events on the form
/// </summary>
/// <param name = "sender"></param>
/// <param name = "e"></param>
[SuppressBPWarningAttribute('BPParameterNotUsed', 'This is event parameter not required to use')]
private void dialogClosing(xFormRun sender, FormEventArgs e)
{
this.dialogEventsUnsubscribe(sender as FormRun);
}
/// <summary>
/// Unsubscribes events from the dialog form
/// </summary>
/// <param name = "_formRun">The instance of the dialog form</param>
private void dialogEventsUnsubscribe(FormRun _formRun)
{
FileUpload fileUpload = _formRun.control(_formRun.controlId(FileUploadName));
fileUpload.notifyUploadCompleted -= eventhandler(this.uploadCompleted);
fileUpload.notifyUploadAttemptStarted -= eventhandler(this.uploadStarted);
_formRun.onClosing -= eventhandler(this.dialogClosing);
}
/// <summary>
/// Executes additional logic once the upload of the file is completed
/// </summary>
protected void uploadCompleted()
{
var formRun = this.dialog().dialogForm().formRun();
FileUpload fileUpload = formRun.control(formRun.controlId(FileUploadName));
FileUploadTemporaryStorageResult uploadResult = fileUpload.getFileUploadResult();
if (uploadResult != null && uploadResult.getUploadStatus())
{
contract.parmStorageResult(uploadResult.pack());
}
this.setDialogOkButtonEnabled(formRun, true);
}
/// <summary>
/// Additional logic which is executed once the upload of the file has started
/// </summary>
private void uploadStarted()
{
var formRun = this.dialog().dialogForm().formRun();
this.setDialogOkButtonEnabled(formRun, false);
}
/// <summary>
/// Enables/Disables the OK button of the dialog
/// </summary>
/// <param name = "_formRun">The instance of the dialog form</param>
/// <param name = "_isEnabled">Should the OK button be enabled?</param>
protected void setDialogOkButtonEnabled(FormRun _formRun, boolean _isEnabled)
{
FormControl okButtonControl = _formRun.control(_formRun.controlId(OkButtonName));
if (okButtonControl)
{
okButtonControl.enabled(_isEnabled);
}
}
/// <summary>
/// Override of the <c>postRun</c> method in order to add events subscriptions
/// </summary>
public void postRun()
{
super();
FormRun formRun = this.dialog().dialogForm().formRun();
this.dialogEventsSubscribe(formRun);
this.setDialogOkButtonEnabled(formRun, false);
}
}
We can see that in this class we have more methods and more logic. You can also notice that we have event handlers, which should run the specified code once the event is started or completed. Analyzing the code, first we can see that the method postBuild has been overridden. Here, we’re constructing the actual look of the dialog. We can see that first we’re creating a group for the file path, and after that we’re adding the FileUpload control, we’re setting the style and the file upload strategy (you can see the documentation in the referenced documentation for File upload). We can also set the available types that user will be able to choose and upload. You can see that the availableTypes is actually string variable with file extensions defined.
After we’re done with adding controls to the dialog, it’s time to make the form responsive, and that’s when the event handlers come into action. As for every event, first we need to do a subscription to a certain event, and after we’re finished, we unsubscribe from that event. So, next we will have a look at method postRun which is called once the dialog is built and ran.
In this method, we see that we’re taking instance of the current dialog (FormRun), we’re making event subscriptions for that FormRun instance and we’re calling the method setDialogOkButtonEnabled which as the name suggests, it’s either enabling/disabling the ‘OK’ button, which is actually submitting the form displayed on the dialog. The solution here is only enabling the ‘OK’ button once we have successfully uploaded a file.
Now, let’s review the logic for subscribing and unsubscribing to an event. Looking at method dialogEventsSubscribe, we see that we’re retrieving the FileUpload control that we’ve created in postBuild method, and we’re adding (subscribing) to events for once the upload has attempted to start and once the upload is completed. The event handlers for these events are respectively methods uploadStarted and uploadCompleted. After that, we’re assigning an event handler for the onClosing event of the FormRun, which is basically calling another method, dialogEventsUnsubscribe, which is basically subtracting (unsubscribing) the event handlers from the events.
Looking at the event handlers, we can see that event handler uploadStarted doesn’t do anything special, but only disables the dialog ‘OK’ button. However, the other event handler, uploadCompleted, we’re once again retrieving the FileUpload, but this time, because the upload is completed, it means that we can take the upload result and store it somewhere (or do additional logic with it).
After taking the result of the file upload, as explained before, I am packing the result and sending it as a parameter to our contract class, so I can later use it in our service class, where all the logic for creating and posting the product receipts is located.
The Service Class
/// <summary>
/// Service class which is executing the logic for creating & posting PO packing slips
/// </summary>
class AXMPostPOReceiptsService extends SysOperationServiceBase
{
#File
container currentLine;
CommaTextStreamIo localStream;
PurchId currentPurchId;
PurchTable purchTable;
/// <summary>
/// The actual logic of the process
/// </summary>
/// <param name = "_contract">Instance of the <c>AXMPostPOReceiptsDataContract</c> class</param>
public void processOperation(AXMPostPOReceiptsDataContract _contract)
{
if (_contract.parmStorageResult() != conNull())
{
FileUploadTemporaryStorageResult fileUploadResult = new FileUploadTemporaryStorageResult();
fileUploadResult.unpack(_contract.parmStorageResult());
if (fileUploadResult != null)
{
try
{
localStream = CommaTextStreamIo::constructForRead(File::UseFileFromURL(fileUploadResult.getDownloadUrl()));
if (localStream.status() != IO_Status::Ok)
{
throw error(strfmt('Is not possible to open the file. Error %1',enum2str(localStream.status())));
}
localStream.inFieldDelimiter("\,");
localStream.inRecordDelimiter("\n");
currentLine = localStream.read();
while(currentLine)
{
currentPurchId = conPeek(currentLine, 1);
purchTable = PurchTable::find(currentPurchId);
if (purchTable)
{
Num psNumber = strFmt("%1_PS", currentPurchId);
PurchFormLetter purchFormLetter = PurchFormLetter::construct(DocumentStatus::PackingSlip);
purchFormLetter.update(purchTable, psNumber, systemDateGet(), PurchUpdate::Recorded, AccountOrder::None, NoYes::No, NoYes::No);
}
currentLine = localStream.read();
}
}
catch (Exception::Error)
{
error("@RET433");
}
}
}
}
}
Since we’re only doing a creation and posting of a product receipt for a certain purchase orders, the logic here will be pretty simple. We can see that we have once method, processOperation, which is taking an instance of the data contract class. With the help of the data contract, we can transfer the packed values of the file upload result from the UI builder to the service class.
Once we have unpacked the result, we’re constructing an IO stream, to which we’re using a file from URL, and that URL is generated with the help of FileUploadTemporaryStorageResult based on the unpacked values. I have wrapped all of the logic in try – catch block, and the code iterates trough the file that the user has uploaded previously, and it’s getting column values (in my case, it’s getting only the values from the first column, since I don’t have additional columns).
Once the value is taking, code is checking whether a purchase order exists for that purchase ID, and if it does, it proceeds with creating and posting the product receipt with the help of the PurchFormLetter class, and as you can see the parameters for the update are a buffer of the purchase table, a number for the packing slip (which in this case is generated manually, but that can be done via number sequences too), the date of the posting, the update parameter (in this case, only ‘Registered’ lines will be included), the account order set to ‘None’, and the settings for the proforma and whether the packing slip should be printed or not (both options set to ‘No’).
This was everything for processing a file in batch using the SysOperationFramework that you should know for starters. I really hope that I have well explained the bits of code which were presented.
Please note that there is also another way to solve this, and that would be to create a data entity and a table in which you will place your values (for this example, purchase order IDs), and also a batch classes and a query against the newly created table, so you can process the data that you have implemented. Both solutions work well, and although the data entity solution is more extendible, the one with the file upload is quicker and less painful.
