Importing data through Data entities using X++

This post shows code to import data in D365 F&O through Data entities using X++ code. One of the best part of this approach is that no more complex joins and relevant tables need to be populate, it just utilizes the power of Data entity and get the data in the system.

To begin with, we will first need to define a data project in Data management workspace as shown in the pictures below.

Step1:

Define a function that will find and return DMFDefinitionGroup.

    private DMFDefinitionGroup findDMFDefinitionGroup(Filename   _fileName)

    {

        DMFDefinitionGroup definitionGroup;

        

        if (strStartsWith(_fileName, 'WorkerEnrolledBenefitDetail'))

        {

            select firstonly definitionGroup

                where definitionGroup.DefinitionGroupName == 'Pledge-GLImport'; //DMF import data project

        }        

        return definitionGroup;

    }

Reading files from Azure Blob container

This post shows X++ code used to list and read all files in Azure Blob container. Please refer to the following code.

    /// <summary>
    /// Start reading files from Blob
    /// </summary>
    public void startReadingFiles()
    {
        CloudBlobClient                 cloudBlobClient;
        CloudBlobContainer              cloudBlobInputContainer;
        CloudStorageAccount             cloudStorageAccount;
        CloudBlockBlob                  iterationBlob, cloudBlockReadBlob;
        Filename                        file, fileName;
        System.Collections.IEnumerable  list;
        System.Collections.IEnumerator  listEnumerator;
        if (!payrollParameters.CUSBlobAccount || !payrollParameters.CUSBlobKey || !payrollParameters.CUSBlobgContainerInput)
        {
            throw error("Missing Parameters");
        }
        var storageCredentials = new Microsoft.WindowsAzure.Storage.Auth.StorageCredentials(payrollParameters.CUSBlobAccount, payrollParameters.CUSBlobKey);
        try
        {
            cloudStorageAccount = new Microsoft.WindowsAzure.Storage.CloudStorageAccount(storageCredentials, true);
        
            if (cloudStorageAccount)
            {
                cloudBlobClient = cloudStorageAccount.CreateCloudBlobClient();
                cloudBlobInputContainer = cloudBlobClient.GetContainerReference(payrollParameters.CUSBlobgContainerInput);
                
                list = new System.Collections.Generic.List<str>();
                list = cloudBlobInputContainer.ListBlobs(null, false, 0, null, null);
                listEnumerator = list.getEnumerator();
                while  (listEnumerator.moveNext())
                {
                    iterationBlob = listEnumerator.get_Current();
                    if (iterationBlob.Exists(null, null))
                    {
                        file = iterationBlob.StorageUri.PrimaryUri.AbsoluteUri;
                        fileName = iterationBlob.Name;
                        cloudBlockReadBlob = cloudBlobInputContainer.GetBlockBlobReference(fileName);
                        System.IO.Strean readStream = cloudBlockReadBlob.OpenRead(null, null, null);
                    }
                }
                info("Read from Blob Successful");
            }
        }
        catch
        {
            error("Unable to connect to the Blob");
        }
    }

Sample Positive Pay XSLT

This post contains sample XSLT's for both Payroll and Bank positive pay. 

Bank Positive Pay XSLT

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
exclude-result-prefixes="msxsl xslthelper" 
xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.02" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xslthelper="http://schemas.microsoft.com/BizTalk/2003/xslthelper">
  <xsl:output method="text" omit-xml-declaration="yes" version="1.0" encoding="utf-8"/>
  <xsl:template match="/">
    <Document>
      <xsl:for-each select="Document/BANKPOSITIVEPAYEXPORTENTITY">
        <!--Cheque Detail begin-->
<xsl:variable name="terminator">"</xsl:variable>
<xsl:choose>
          <xsl:when test='CHEQUESTATUS/text()=normalize-space("Void") or CHEQUESTATUS/text()=normalize-space("Rejected") or CHEQUESTATUS/text()=normalize-space("Cancelled")'>
            <xsl:value-of select="'C'"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:value-of select="'I'"/>
          </xsl:otherwise>
        </xsl:choose>
<xsl:value-of select="''" />
<xsl:value-of select="substring(concat(ACCOUNTNUM/text(), '          '), 0, 11)"/>
<xsl:value-of select="''" />
<xsl:value-of select="format-number(CHEQUENUM, '0000000000')"/>
<xsl:value-of select="''" />
<xsl:value-of select="translate(format-number(AMOUNTCUR, '00000000.00'), '.', '')"/>
<xsl:value-of select="''" />
<xsl:value-of select="msxsl:format-date(TRANSDATE/text(), 'yyMMdd')"/>
<xsl:value-of select="''" />
<xsl:value-of select="BANKNEGINSTRECIPIENTNAME/text()"/>  
<xsl:text>
</xsl:text>
      </xsl:for-each>
    </Document>
  </xsl:template>
</xsl:stylesheet>

Payroll Positive Pay XSLT

<xsl:stylesheet version="1.0" 

xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 

xmlns:msxsl="urn:schemas-microsoft-com:xslt" 

exclude-result-prefixes="msxsl xslthelper" 

xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.02" 

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 

xmlns:xslthelper="http://schemas.microsoft.com/BizTalk/2003/xslthelper">

  <xsl:output method="text" omit-xml-declaration="yes" version="1.0" encoding="utf-8"/>

  <xsl:template match="/">

    <Document>

      <xsl:for-each select="Document/PAYROLLPOSITIVEPAYEXPORTENTITY">

        <!--Cheque Detail begin-->

<xsl:variable name="terminator">"</xsl:variable>

<xsl:value-of select="substring(concat(ACCOUNTNUM/text(), '          '), 0, 11)"/>

<xsl:value-of select="''" />

<xsl:value-of select="format-number(CHEQUENUM, '0000000000')"/>

<xsl:value-of select="''" />

<xsl:value-of select="msxsl:format-date(TRANSDATE/text(), 'MMddyyyy')"/>

<xsl:value-of select="''" />

<xsl:value-of select="translate(format-number(AMOUNTCUR, '0000000000'), '.', '')"/>

<xsl:value-of select="''" />

<xsl:value-of select="substring(BANKNEGINSTRECIPIENTNAME/text(), 0, 30)"/>  

<xsl:value-of select="''" />

<xsl:choose>

          <xsl:when test='CHEQUESTATUS/text()=normalize-space("Void")'>

            <xsl:value-of select="'V'"/>

          </xsl:when>

          <xsl:otherwise>

            <xsl:value-of select="''"/>

          </xsl:otherwise>

        </xsl:choose>

<xsl:choose>

          <xsl:when test='CHEQUESTATUS/text()=normalize-space("Void")'>

            <xsl:value-of select="msxsl:format-date(VOIDEDDATE/text(), 'MMddyyyy')"/>

          </xsl:when>

          <xsl:otherwise>

            <xsl:value-of select="''"/>

          </xsl:otherwise>

        </xsl:choose>

<xsl:text>

</xsl:text>

      </xsl:for-each>

    </Document>

  </xsl:template>

</xsl:stylesheet>

Upload file in Azure Blob using X++

This post shows how to write a code in X++ to upload file in Azure Blob. Please refer to the following X++ method to understand how it works.

    /// <summary>

/// Uploads file to Azure Blob /// </summary> /// <param name = "_csvFileContent">CSV file contents</param> private void uploadToBlob(str _csvFileContent) { CloudBlobClient cloudBlobClient; CloudBlobContainer cloudBlobContainer; CloudStorageAccount cloudStorageAccount; if (!payrollParameters.CUSBlobAccount || !payrollParameters.CUSBlobKey || !payrollParameters.CUSBlobContainer) { throw error("@CMCTS:DonationReportBlobMissingParam"); } System.Byte[] reportBytes = new System.Byte[0](); System.Text.ASCIIEncoding enc = new System.Text.ASCIIEncoding(); reportBytes = enc.GetBytes(_csvFileContent); System.IO.MemoryStream memoryStream = new System.IO.MemoryStream(reportBytes); var storageCredentials = new Microsoft.WindowsAzure.Storage.Auth.StorageCredentials(payrollParameters.CUSBlobAccount, payrollParameters.CUSBlobKey); try { cloudStorageAccount = new Microsoft.WindowsAzure.Storage.CloudStorageAccount(storageCredentials, true); if (cloudStorageAccount) { cloudBlobClient = cloudStorageAccount.CreateCloudBlobClient(); cloudBlobContainer = cloudBlobClient.GetContainerReference(payrollParameters.CUSBlobContainer); CloudBlockBlob CloudBlockBlob = cloudBlobContainer.GetBlockBlobReference(exportFileName); CloudBlockBlob.UploadFromStream(memoryStream, null, null, null); Info("@CMCTS:UploadToBlobSuccessful"); } } catch { error("@CMCTS:DonationReportAzureBlobConnectError"); error("@CMCTS:UploadToBlobUnSuccessful"); } }