How to create a computed column in a view through x++ in D365 F&O ?

Computed columns in D365 Finance and Operations (D365 F&O) are extremely useful when you want to add dynamic values in views, which are calculated at runtime using SQL. Instead of storing values in tables, computed columns help improve performance and reduce storage. 

When you have an AOT query where sometimes adding additional data source causes returning of duplicate records and then if this query is used in a view which is then being used in an inquiry form will result in displaying wrong data. In such scenarios Computed Columns in a view help us a lot. 

In this post, I will show you step-by-step how to create a computed column using X++ that gets column value from a table in D365 FO based on a value returned by a data source in an AOT query.

Below mentioned is a step by step process to achieve the same : - 

Step 1: Create a New View

1. Go to AOT → Data Model → Views

2. Right-click → New View

3. Rename it: LedgerVoucherComputedView

Step 2: Add Data Sources

Add the desired table (e.g., VendPackingSlipJour) to the view.

Step 3: Add Fields

Add basic fields you want in the view like PackingSlipId, DataAreaId, DeliveryDate, etc.

Step 4: Add Computed Column

1. Right-click View → New → Computed Column

2. Rename it: VoucherNo

Step 5: Write X++ Logic for the Computed Column

Now we define the computation using X++ static method.

Create a static method for computed column in the View

public static str computePhysicalVoucher()
{
    DictView  myView = new DictView(tableNum(LedgerVoucherComputedView));
    str primaryDatasourcename = myView.query().dataSourceTable(tablenum(VendPackingSlipJour)).name();

    str physicalvoucher = strFmt(@"SELECT TOP 1 SubledgerVoucher FROM GeneralJournalEntry WHERE GeneralJournalEntry.DocumentNumber = %1 
                               and GeneralJournalEntry.SubledgerVoucherDataAreaId = %2
                               and GeneralJournalEntry.AccountingDate = %3",
                               SysComputedColumn::returnField(tableStr(LedgerVoucherComputedView),primaryDatasourcename,fieldStr(VendPackingSlipJour,PackingSlipId)),
                               SysComputedColumn::returnField(tableStr(LedgerVoucherComputedView),primaryDatasourcename,fieldStr(VendPackingSlipJour,DataAreaId)),
        SysComputedColumn::returnField(tableStr(LedgerVoucherComputedView),primaryDatasourcename,fieldStr(VendPackingSlipJour,DeliveryDate)));
   
    return physicalvoucher;

}

Step 6: Attach the method to the computed column

• Select your computed column (VoucherNo)
• In Properties → View Method → set value to : computePhysicalVoucher

Conclusion

Computed columns in D365 F&O are powerful for real-time calculations without storing unnecessary data. Using X++, we can dynamically build SQL expressions and boost performance.

That's all for now. Please let us know your questions or feedback in comments section !!!!

How to upload a file in SFTP server through x++ in D365 F&O ?

 

When working with Dynamics 365 Finance and Operations (D365 F&O), integrations often require us to send or receive files from external systems. One of the most common ways to do this securely is through SFTP (Secure File Transfer Protocol).

Unfortunately, D365 F&O doesn’t provide a native SFTP client out of the box. Instead, we have a few options to achieve this:

• Use Azure Blob Storage + Logic Apps / Azure Functions for file movement (Microsoft recommended approach).

• Use third-party libraries like Renci.SshNet with .NET interop inside X++.

In this post, we’ll focus on the direct SFTP approach using X++ and .NET interop.

Prerequisites: -

Before jumping into code, make sure you have the following in place:

1. Access to the SFTP server (host, port, username, password, and destination path).

2. The Renci.SshNet.Async library deployed to your D365 F&O environment (uploaded via Visual Studio project reference).

3. Proper permissions in your D365 F&O environment to run external .NET assemblies.

Now let us see step by step how to achieve this SFTP File upload functionality : - 

Step 1 - Create a C# Project for Class Library targeting to .Net Framework 4.7.2 (Very Important as D365 F&O also targets the same framework

Step 2 - Right click on the project and select Manage Nuget Packages. Go to Browse and install Renci.SshNet.Async package library

Step 3 - Create a class in C# Project and give it any name such as this case we have named it SFTPTransfer

Step 4 - Use the below code to create the functionality to upload your desired file to SFTP server : - 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using Renci.SshNet;

namespace SFTPFileTransfer
{
    public class SFTPTransfer
    {
        public void TransferSFTPFile(string _host, string _username, string _password, System.IO.Stream _sourceFile,
        string _destinationPath, int _port, string _fileName)
        {
            List <AuthenticationMethod> methods;

            methods = new List<AuthenticationMethod>
            {
                new PasswordAuthenticationMethod(_username, _password)
            };

            try
            {
                var connectionInfo = new ConnectionInfo(_host, _port, _username, methods.ToArray());

                using (SftpClient sftpclient = new SftpClient(connectionInfo))
                {
                    sftpclient.Connect();

                    sftpclient.ChangeDirectory(_destinationPath.Trim());

                    _sourceFile.Position = 0;

                    sftpclient.BufferSize = 8 * 1024;

                    sftpclient.UploadFile(_sourceFile, _fileName);
                }
            }
            catch (WebException ex)
            {
            }
        }
    }
}

Step 5 - Build the class library project which should generate the DLL file. In our case DLL file name is SFTPFileTransfer.dll

Step 6 - Copy and paste the dll into the path - \AosService\PackagesLocalDirectory\yourmodelname\bin

 

Step 7 - Create a new Finance Operations project. In our case we have named it FinalSFTPExportTest and created two classes - SFTPUploadTest and VendAgingReportController_Extension (as we are testing SFTP file transfer for Vendor Aging Report in excel format) 

Step 8 - Use the below mentioned code in SFTPUploadTest class : - 

 

using SFTPFileTransfer;

public class SFTPUploadTest
{
    public void executeuploadtoSFTP(SrsReportRunController _controller)
    {
        str sftpServer   = "abc.sftp.com";
        int sftpPort     = 22;
        str sftpUser     = "xyz";
        str sftpPassword = "abcd12344";
        str sftpPath     = "/AB/Report/"; // remote directory

        str fileName = 'VendorAgingReport.xlsx';
        // Be careful with the fileName as few SFTP servers reject the file due to invalid characters in the name

        // --- Generate Report as Excel file ---
    
        SRSPrintDestinationSettings printSettings;
        SRSReportRunService         srsReportRunService;
        Map                         reportParametersMap;
        Microsoft.Dynamics.AX.Framework.Reporting.Shared.ReportingService.ParameterValue[] parameterValueArray;
        SRSProxy                    srsProxy;
        System.Byte[]               reportBytes;

        // Setup report
        _controller.parmReportName(ssrsReportStr(VendAgingReport, DesignWithNoDetailAndNoTransactionCur)); // Vendor Aging Report
        _controller.parmShowDialog(false);
        _controller.parmExecutionMode(SysOperationExecutionMode::Synchronous);

        // Print settings
        printSettings = _controller.parmReportContract().parmPrintSettings();
        printSettings.printMediumType(SRSPrintMediumType::File);
        printSettings.fileFormat(SRSReportFileFormat::Excel);

        // Run report service
        srsReportRunService = new SRSReportRunService();
        srsReportRunService.getReportDataContract(_controller.parmReportContract().parmReportName());
        srsReportRunService.preRunReport(_controller.parmReportContract());

        reportParametersMap = srsReportRunService.createParamMapFromContract(_controller.parmReportContract());
        parameterValueArray = SrsReportRunUtil::getParameterValueArray(reportParametersMap);

        _controller.parmReportContract().parmReportExecutionInfo(new SRSReportExecutionInfo());
        _controller.parmReportContract().parmReportServerConfig(SRSConfiguration::getDefaultServerConfiguration());

        srsProxy = SRSProxy::constructWithConfiguration(_controller.parmReportContract().parmReportServerConfig());

        // Render report to byte array (Excel)
        reportBytes = srsProxy.renderReportToByteArray(_controller.parmReportContract().parmReportPath(),
                                                    parameterValueArray,
                                                    printSettings.fileFormat(),
                                                    printSettings.deviceinfo());

        if (!reportBytes || reportBytes.get_Length() == 0)
        {
            throw error("Vendor aging report rendering failed.");
        }
        System.IO.Stream objstream = new System.IO.MemoryStream(reportBytes);

        // --- Upload to SFTP ---
        SFTPTransfer transfer = new SFTPTransfer();

        transfer.TransferSFTPFile(sftpServer,sftpUser,sftpPassword,objstream,sftpPath,sftpPort,fileName);

    }

}

Step 9 - Use the below mentioned code to call  executeuploadtoSFTP method inside VendAgingReportController_Extension class so that every time the report runs the report output will be exported in excel format and will be sent to SFTP server : - 

[ExtensionOf(classstr(VendAgingReportController))]
final class VendAgingReportController_Extension
{
    protected void preRunModifyContract()
    {
        next preRunModifyContract();

        SFTPUploadTest objsftpupload = new SFTPUploadTest();

        objsftpupload.executeuploadtoSFTP(this);

    }

}

That's all for now. Please let us know your questions or feedback in comments section !!!!