Simple view with computed columns for date functions

In a recent development project we had to add query ranges and grouping on the week, month or year number dynamically. Initial thoughts were to use expressions in query ranges combined with date functions like wkOfYr. However after a few attempts it was clear that this was not the best solution to our problem. That is when we decided to use a view with computed columns.

We created a view and added the original table as a datasource. The idea was to add the week of year, month number and year number as computed columns and then use them in the query as normal fields.

The following are the steps you need to do to add a computed column for the week number:

  • First create a new integer computed column for the week number. Right-click on fields under the view and select new > integer computed column. Let’s call it MyComputedColumn for our example.
  • Then create a method on your view which will calculate the value for the computed column. The method must be set to private static of type string. For AX 2012 you should set the method to run on server. The method below is self-explanatory, it gets the SQL field name from the view and returns an SQL select statement to convert the date to week number using the SQL function DATEPART.
private static str myDateWeek()
{
        DictView dictView = new dictView(tableNum(MyView));

        str myTransDate = dictView.computedColumnString(
            identifierStr(DataSourceName),
            fieldStr(DSTableName, TransDate));

        return "SELECT DATEPART(WEEK," + myTransDate +")";
}
  • Next set the “View Method” property on the computed column you created earlier.
  • Finally build and db synch your code. Remember the view is created in the database under the views section. Here you can see the result of the computed column.
(CAST ((SELECT DATEPART(WEEK,T1.MyTransDate)) AS INT)) AS MyComputedColumn

For the month and year you can create additional computed columns with their respective methods.

"SELECT MONTH(" + myTransDate +")";

"SELECT YEAR(" + myTransDate +")";

Remember that a computed column works by adding SQL query syntax to the view. Therefore you need to use SQL functions like DATEPART(WEEK,%1) and not D365 FO/ AX functions like WkOfYr in the returned select statement. You can easily find SQL syntax functions on the web such as this one at w3schools.

Data Entity Export from code

This post  explains how to implement the same data entity export functionality provided in the Data Management workspace in a form or a menu item.

Below is form dialog that I created to research how to create data entities and run the export functionality directly from X++ code and using standard DMF classes.

Creating an export data project

The first code sample is how I created the export data project. The definition group name is anything that you want. For the source name (data format EXCEL, CSV etc) you can use the standard lookup method DMFQuickImportExportFormHelper.buildSourceNameControlLookup(). For the entity name there is another standard lookup DMFUtil::lookupEntity(this).

/// <summary>
/// Create DMF Processing group to export 'Customer Groups'
/// </summary>
/// <param name = "_groupName">Definition group name</param>
/// <param name = "_sourceName">Export file type e.g. CSV</param>
/// <param name = "_entityName">Entity Name</param>
public static void createNewDefinitionGroup(DMFDefinitionGroupName _groupName, DMFSourceName _sourceName, DMFEntityName _entityName)
{
    DMFEntity dmfEntity = DMFEntity::find(_entityName);
    DMFDataSource dmfDataSource = DMFDataSource::find(_sourceName);

    if (dmfEntity &amp;&amp; dmfDataSource)
    {
        DMFDefinitionGroupName dmfDefinitionGroupName = _groupName;
        Description description = _groupName;

        //Create data project
        DMFEntityBase::addEntityForProcessingV3(dmfDefinitionGroupName,
                                            description,
                                            dmfEntity.EntityName,
                                            dmfDataSource.SourceName,
                                            "", //Sample file
                                            "@SYS6047", //"All fields"
                                            NoYes::Yes, //Skip staging
                                            NoYes::Yes, //Export to File. Set Yes to create a definition group of operation type Export
                                            "", //Sheet lookup
                                            NoYes::No, //Truncate Entity Data
                                            DMFRefreshType::FullPush);
    }
}

Exporting the data definition group

To export the definition group we use the DMFPackageExporter class after creating a new DMF Definition Group Execution.

DMFDefinitionGroup definitionGroup = DMFDefinitionGroup::find("My demo export"); 

if (definitionGroup)
{
    DMFExecutionId dmfExecutionId = DMFUtil::setupNewExecution(definitionGroup.DefinitionGroupName);
    Description description = strFmt('Execution - %1 for Definition Group - %2', dmfExecutionId, definitionGroup.DefinitionGroupName);
            
    ttsbegin;

    //Create new dmf execution group for the new execution
    if (DMFDefinitionGroupExecution::insertOrDisplay(definitionGroup, dmfExecutionId, description, false))
    {                
        dmfDefinitionGroupExecution = DMFDefinitionGroupExecution::find(definitionGroup.DefinitionGroupName, dmfDefinitionGroupEntity.Entity, dmfExecutionId);
                
        if (dmfDefinitionGroupExecution)
        {
            DMFExportImportDataContract dmfDataContract = new DMFExportImportDataContract();
                    
            DMFPackageExporter packageExporter = new DMFPackageExporter();
            packageExporter.exportToPackageV2(definitionGroup.DefinitionGroupName,
                                '',
                                _dmfDefinitionGroupName,
                                dmfExecutionId,
                                dmfDataContract.parmReExecute(),
                                curext(),
                                dmfDataContract.parmGenerateDataPackage());

            //Download file for the newly created dmf execution group
            ETBDataEntityHelper::downloadFile(dmfDefinitionGroupExecution);
        }
    }

    ttscommit;
}

Download file

To send the file to the user from the DMF Definition Group Execution table we use DMFEntityExportDetails to get the download URL.

public static void downloadFile(DMFDefinitionGroupExecution dmfDefinitionGroupExecution)
{
    DMFEntityExportDetails  exportDetails;
    DMFEntityName           entityName;
    DMFEntity               dMFEntity = DMFEntity::find(dmfDefinitionGroupExecution.Entity);
            
    if (dMFEntity.ParentEntityName != '')
    {
        entityName = dMFEntity.ParentEntityName;
    }
    else
    {
        entityName = dmfDefinitionGroupExecution.Entity;
    }

    select firstonly1 SampleFilePath, DefinitionGroup from exportDetails where
                exportDetails.DefinitionGroup == dmfDefinitionGroupExecution.DefinitionGroup &amp;&amp;
                exportDetails.ExecutionId == dmfDefinitionGroupExecution.ExecutionId &amp;&amp;
                exportDetails.Entity == entityName;

    if (exportDetails.RecId &amp;&amp; exportDetails.SampleFilePath)
    {
        str downloadUrl = DMFDataPopulation::getAzureBlobReadUrl(str2Guid(exportDetails.SampleFilePath));
            System.IO.Stream stream = File::UseFileFromURL(downloadUrl);
            File::SendFileToUser(stream, strFmt('%1-%2.%3', exportDetails.DefinitionGroup, exportDetails.Entity, DMFDataPopulation::getFileExtensionFromURL(downloadUrl)));
    }
    else
    {
        warning(strFmt("@DMF:DMFNoExportFileFoundForEntity", dmfDefinitionGroupExecution.Entity, dmfDefinitionGroupExecution.ExecutionId));
    }
}

Changing the query on the export definition group

Another useful class is the DMFFormBrowser. This opens the entity query dialog and lets the user add filters. You can also code your own ranges on the query and then package it to attached to the DMF Group Execution. You would use this code before calling the exportToPackageV2 method shown previously.

container   queryData;

if (dmfDefinitionGroupEntity)
{
    queryData = DMFUtil::getDefaultQueryForEntityV3(dmfDefinitionGroupEntity.Entity, dmfDefinitionGroupEntity.DefinitionGroup);
    //Show query dialog
    queryData = DMFFormBrowser::showQuery(queryData);
}
            
//Update Query filter on DMF Definition Group
if(queryData != connull())
{
    dmfDefinitionGroupExecution.selectForUpdate(true);
    dmfDefinitionGroupExecution.QueryData = queryData;
    dmfDefinitionGroupExecution.write(); 
                       
    if (dmfDefinitionGroupEntity)
    {
        //Save updated query for next time
        dmfDefinitionGroupEntity.selectForUpdate(true);
        dmfDefinitionGroupEntity.QueryData = queryData;
        dmfDefinitionGroupEntity.write();
    }
}

File upload: CSV import

This blog post is a code dump of a file upload dialog for D365 FO. There are various around, but I could not find a complete example so I decided to share my own. This is a single class extending the RunBase class, which has a file upload control and then parses the CSV file returned from the IO stream. No data entities are used although you can modify the code and use them.

D365 FO File Upload
public class ImportSalesOrderRunbase extends RunBase
{
    DialogRunbase dialog;
    private str availableTypes = ".csv";
    private const str OkButtonName = 'OkButton';
    private const str FileUploadName = 'FileUpload';
    private str textFile;

    public Object dialog()
    {
        DialogGroup      dialogGroup;
        FormBuildControl formBuildControl;
        FileUploadBuild  dialogFileUpload;
        Set              enumSet = new Set(Types::Enum);

        
        dialog = super();
        dialogGroup = dialog.addGroup("Import sales orders");
        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");

        dialog.addText("CSV Columns \n1. External Sales Id \n2. Cust Account \n3. Item Id \n4. Unit Price \n5. Quantity \n6. Line Discount \n7. Line Amount");
    
        return dialog;
    }

    protected void setDialogOkButtonEnabled(DialogRunbase _dialog, boolean _isEnabled)
    {
        FormControl okButtonControl = this.getFormControl(_dialog, OkButtonName);

        if (okButtonControl)
        {
            okButtonControl.enabled(_isEnabled);
        }
    }

    protected FormControl getFormControl(DialogRunbase _dialog, str _controlName)
    {
        return _dialog.formRun().control(_dialog.formRun().controlId( _controlName));
    }

    protected void uploadCompleted()
    {
        FileUpload fileUpload = this.getFormControl(dialog, FileUploadName);
        fileUpload.notifyUploadCompleted -= eventhandler(this.UploadCompleted);
                
        textFile = fileUpload.fileName();

        this.setDialogOkButtonEnabled(dialog, true);
    }

    /// Disable the dialog Ok button until the file upload is complete.
    public void dialogPostRun(DialogRunbase _dialog)
    {
        FileUpload fileUpload = this.getFormControl(_dialog, FileUploadName);
        fileUpload.notifyUploadCompleted += eventhandler(this.uploadCompleted);
        this.setDialogOkButtonEnabled(_dialog, false);
    }

    public static void main(Args _args)
    {
        ImportSalesOrderRunbase importSO = new ImportSalesOrderRunbase();

        if (importSO.prompt())
        {
            importSO.run();
        }
    }

    public void run()
    {
        #File
        container               currentLine;
        int                     totalOfLines;
        CommaTextStreamIo       localStream;
        Num                     number;
            
       
        FileUpload fileUploadControl = this.getFormControl(dialog, FileUploadName);
        FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();
        
        if (fileUploadResult != null &amp;&amp; fileUploadResult.getUploadStatus())
        {
            textFile = fileUploadResult.getDownloadUrl();
        }
        
        localStream = CommaTextStreamIo::constructForRead(File::UseFileFromURL(textFile));
                 
        if (localStream.status() != IO_Status::Ok)
        {
            throw error('File cannot be opened');
        }
    
        localStream.inFieldDelimiter("\,");
        localStream.inRecordDelimiter("\n");
    
        currentLine = localStream.read();
        
        while(currentLine)
        {
            this.parseCSV(currentLine);

            currentLine = localStream.read();
        }
    }

    private void parseCSV(container _record)
    {
        str         externalSalesId = conPeek(_record, 1);
        str         custAccount = conPeek(_record, 2);
        str         itemId = conPeek(_record, 3);
        str         salesPrice = conPeek(_record, 4);
        str         salesQty = conPeek(_record, 5);
        str         lineDisc = conPeek(_record, 6);        
        str         lineAmount = conPeek(_record, 7);
        
        //Write your code to create Sales Orders and Lines
    }
}