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"); } }

Authorization token decode

I came across a very useful website for decoding authorization token while connecting D365 F&O using .net applications, Postman or any other tool. 

The site is called https://jwt.io/ where you can put authorization token value to decode it.

This site is really helpful in verifying audience value, incorrect audience value can lead to 401 (Unauthorized) error.

Reference Group Control lookup in D365

This post shows how to create lookup for ReferenceGroupControl using extensions.

All we need to do is to register event handler for lookup function and make a lookup little bit differently as shown below.

After handler is registered use the following code.

[FormControlEventHandler(formControlStr(MyWorker, MyWorker_HcmWorkerRecId), FormControlEventType::Lookup)]
public static void MyWorker_HcmWorkerRecId_OnLookup(FormControl sender, FormControlEventArgs e)
{
    MyClass::createLookup(sender);

    FormControlCancelableSuperEventArgs cancelableArgs = e as FormControlCancelableSuperEventArgs;
    cancelableArgs.CancelSuperCall();
}

private static void createLookup(FormReferenceGroupControl _ctrl)
{
    SysReferenceTableLookup sysTableLookup = SysReferenceTableLookup::newParameters(tablenum(HcmWorker), _ctrl);
    Query                   query = new Query();

    sysTableLookup.addLookupfield(fieldNum(HcmWorker, PersonnelNumber));
   
    query.addDataSource(tablenum(HcmWorker));
    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}

Changin NewRecordAction through extensions

In this post we will discuss how to change NewRecordAction property using extensions in D365 for Finance and Operations.

As you know NewRecordAction property is not enabled when being used in extensions as shown in the picture below.

Resolution: 

This can be achieved by capturing onInitialized form handler event as shown below.