Monday, May 30, 2022

How to Call Json Api with POST/GET through X++ using D365 Fin Ops

 Hi  folks, 


Developers most of the time get the requirement of executing POST or GET operations for the JSON API calls in Fin Ops.

A simple X++ code mentioned below can do the work for us : -




class CallJSONAPI
{
    public static str  post(str  _url,str  _jsonstr)
    {
        str     returnresponse;
        
        str							requestJSON, responseJSON, token, tokenJSON, byteStr;

        System.Net.HttpWebRequest		request, requestApi;

        System.Net.HttpWebResponse	response, responseApi;

        System.Byte[]					bytes, bytesApi;

        System.Text.Encoding			utf8, utf8Api;

        System.IO.Stream				requestStream, responseStream, requestStreamApi, responseStreamApi;

        System.IO.StreamReader		streamReader, streamReaderApi;

        System.IO.StreamWriter                 streamWriter;

        System.Exception				ex;

        System.Net.WebHeaderCollection	httpHeader, httpHeaderApi;

        System.IO.Stream				stream;

        new InteropPermission(InteropKind::ClrInterop).assert();

        requestJSON	=_jsonstr;            

        if(requestJSON == '')
        {
            return 'Input JSON is null' ;
        }

        System.Uri uri	                =   new System.Uri(strFmt('%1',_url));

        System.Net.ServicePointManager::Expect100Continue	=   true;

        System.Net.ServicePointManager::set_SecurityProtocol(System.Net.SecurityProtocolType::Tls12);


        httpHeaderApi	    =   new System.Net.WebHeaderCollection();

        requestApi		    =   System.Net.WebRequest::Create(uri);

        requestApi.set_Method("POST"); // You can POST/GET here

        requestApi.set_ContentType("application/json;charset='utf-8'");

        utf8Api		        =   System.Text.Encoding::get_UTF8();

        requestJSON         =   strRem(requestJSON,"'\'");

        bytesApi		    =   utf8Api.GetBytes(requestJSON);

        requestApi.set_Headers(httpHeaderApi);

        requestApi.set_ContentLength(bytesApi.get_Length());

        requestApi.set_ContentType("application/json");

        requestStreamApi	=   requestApi.GetRequestStream();

        requestStreamApi.Write(bytesApi, 0, bytesApi.get_Length());

        responseApi		    =   requestApi.GetResponse();

        responseStreamApi	=   responseApi.GetResponseStream();

        streamReaderApi	    =   new System.IO.StreamReader(responseStreamApi);

        responseJSON	    =   streamReaderApi.ReadToEnd();

        responseStreamApi.Close();

        streamReaderApi.Close();

        responseApi.Close();

        returnresponse  =   responseJSON;

        return  returnresponse;
    }
}


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

Thursday, September 2, 2021

How to enable Maintenance Mode in Tier 1 Environments / VMs in D365 FO


Maintenance mode is helpful for us in lot of scenarios where we need some environmental changes in our D365 Finance and Operations platform.

Any kind of change in Financial dimensions and configuration keys are only allowed if the environment is in Maintenance Mode.

In Tier2 or Sandbox environments we get this option very easily in the Maintain Menu in LCS

Environment Page but if the same activity needs to be done Tier 1 environments which we access through  RDP then there is a different approach altogether.


Let's take a look step by step : - 


1) Stop the following services : - 


    a) Microsoft Dynamics Batch Management Service


    b) Microsoft Dynamics Data Import and Export Service


    c) World Wide Web Service


2) In the environment open Sql Server Management Studio and sign in to Sql Server using axdbdmin login

3) Click on New Query option

4) Run the below SQL script : -


    USE AXDB;


    update SQLSYSTEMVARIABLES SET VALUE = 1 where PARM = 'CONFIGURATIONMODE'


5) Once done turn on the services mentioned in Step 1


6) Now the environment should be in Maintenance Mode and you can perform various actions such as

       activate Financial Dimensions and Enable/Disable Configuration keys.


Once the work is done in the SQL statement in step 4 instead of SET VALUE = 1 make it 0 which will turn off the maintenance mode.


Note : - You might need to restart the environment if after the above steps the maintenance mode is not enabling or disabling.



********* Please mention your queries if any in the comments area ***************


How to get the details of the selected records through x++ in D365 FO

 

Sometimes we come across a scenario where we need to get the data from the selected records in a form contained in a grid.

For these kind of scenarios MultiSelectionHelper class has always been useful to achieve the same.


Let us look at an example mentioned below : -



MultiSelectionHelper          selectionHelper = MultiSelectionHelper::construct();
Set                           selectedRecords = new Set(Types::Record);
ABCTable                  abcTable;

selectionHelper.parmDataSource(ABCTable_DS); 

abcTable  = selectionHelper.getFirst();  

if (abcTable.RecId)
{
    while(abcTable)
    {
        selectedRecords.add(abcTable);
        
        info(strFmt('Selected record.. %1',abcTable.myField));
        
        abcTable = selectionHelper.getNext();
    }
}




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

How to export data from table to excel through x++ in D365 FO


 Exporting data to excel might be required in most of the scenarios where Data Integration is involved.

Excel can be proved as one of the most significant applications for Data Transformation and manipulations.

Let us see how can achieve the data export using x++ coding standards :-
u



DocuFileSaveResult           saveResult;

Table1                       table1details , table1datasource , table1update;

PurchAgreementHeader         purchAgreementHeader;

TransDate                    delDate;

FormDataSource               fdsTable1det = sender.formRun().dataSource('Table1');


table1datasource = fdsTable1det.cursor();

select firstonly purchAgreementHeader where purchAgreementHeader.RecId ==                                                                                                            table1datasource.AgreementHeaderRefRecId;

while select forupdate table1update where                                                                                                                         table1update.AgreementHeaderRefRecId ==                                                                                                                table1datasource.AgreementHeaderRefRecId
{
     if(table1update)
     {
           ttsbegin;

           if(InventItemPurchSetup::findDefault(table1update.ItemId).LeadTime != 0)
           {
                delDate =  DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone()) +     InventItemPurchSetup::findDefault(table1update.ItemId).LeadTime;
                
                table1update.RequestedDeliveryDate = delDate;
           }

          table1update.PurchQty = 0;

          table1update.RemarksComments = "Enter your comments here";

          table1update.update();

          ttscommit;

     }

}

saveResult = DocuFileSave::promptForSaveLocation("Table1", "xlsx", null, "Table1 Details");

if (saveResult && saveResult.parmAction() != DocuFileSaveAction::Cancel)
{
    saveResult.parmOpenParameters('web=1');

    saveResult.parmOpenInNewWindow(false);

    System.IO.Stream workbookStream = new System.IO.MemoryStream();

    System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();

    using (var package = new ExcelPackage(memoryStream))
    {
        var currentRow = 1;
        
        var worksheets = package.get_Workbook().get_Worksheets();

        var Table1Worksheet = worksheets.Add("Export");

        var cells = Table1Worksheet.get_Cells();

        OfficeOpenXml.ExcelRange cell = cells.get_Item(currentRow, 1);

        System.String value = "Item Number";

        cell.set_Value(value);

        cell = null;

        value = "Unit of Measure";

        cell = cells.get_Item(currentRow, 2);

        cell.set_Value(value);

        cell = null;

        value = "Requested Delivery Date";

        cell = cells.get_Item(currentRow, 3);

        cell.set_Value(value);

        cell = null;

        value = "Remarks and Comments";

        cell = cells.get_Item(currentRow, 4);

        cell.set_Value(value);

        cell = null;

        value = "Purchase Quantity";

        cell = cells.get_Item(currentRow, 5);

        cell.set_Value(value);

        cell = null;

        value = "Agreement Quantity";

        cell = cells.get_Item(currentRow, 6);

        cell.set_Value(value);

        cell = null;

        value = "Remaining Quantity";

        cell = cells.get_Item(currentRow, 7);

        cell.set_Value(value);

        cell = null;

        value = "Released Quantity";

        cell = cells.get_Item(currentRow, 8);

        cell.set_Value(value);

        cell = null;

        value = "Received Quantity";

        cell = cells.get_Item(currentRow, 9);

        cell.set_Value(value);

        cell = null;

        value = "Invoiced Quantity";

        cell = cells.get_Item(currentRow, 10);

        cell.set_Value(value);

        cell = null;            

        value = "Agreement Line Record Id";

        cell = cells.get_Item(currentRow, 11);

        cell.set_Value(value);

        cell = null;

        value = "Agreement Header Record Id";

        cell = cells.get_Item(currentRow, 12);

        cell.set_Value(value);



        while select table1details where table1details.AgreementHeaderRefRecId ==                                                                       purchAgreementHeader.RecId
        {

              currentRow ++;

              cell = null;

              cell = cells.get_Item(currentRow, 1);

              cell.set_Value(table1.ItemId);

              
              cell = null;

              cell = cells.get_Item(currentRow, 2);

              cell.set_Value(table1.ProductUnitOfMeasure);

              
              cell = null;
              
              cell = cells.get_Item(currentRow, 3);

              cell.set_Value(any2Str(table1.RequestedDeliveryDate));


              cell = null;

              cell = cells.get_Item(currentRow, 4);

              cell.set_Value(any2Str(table1.RemarksComments));

              cell = null;


               cell = cells.get_Item(currentRow, 5);

               cell.set_Value(any2Str(table1.PurchQty));


               cell = null;

               cell = cells.get_Item(currentRow, 6);

               cell.set_Value(any2Str(table1.AgreementQty));

               
               cell = null;

               cell = cells.get_Item(currentRow, 7);

               cell.set_Value(any2Str(table1.RemainingQty));

           
               cell = null;

               cell = cells.get_Item(currentRow, 8);

               cell.set_Value(any2Str(table1.ReleasedQty));

               
               cell = null;

               cell = cells.get_Item(currentRow, 9);

               cell.set_Value(any2Str(table1.ReceivedQty));

               
               cell = null;

               cell = cells.get_Item(currentRow, 10);

               cell.set_Value(any2Str(table1.InvoicedQty));


               cell = null;          

               cell = cells.get_Item(currentRow, 11);

               cell.set_Value(any2Str(table1.AgreementLineRefRecId));

               
               cell = null;

               cell = cells.get_Item(currentRow, 12);

               cell.set_Value(any2Str(table1.AgreementHeaderRefRecId));
               
       }
       
       package.Save();

  }
       memoryStream.Seek(0, System.IO.SeekOrigin::Begin);

       DocuFileSave::processSaveResult(memoryStream, saveResult);

}


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

Wednesday, August 18, 2021

How to get display value from LedgerDimension field through x++ in D365 FO


While working with LedgerJournalTrans table most of the times we get the requirement for extracting the display value from LedgerDimension field in order to use it somewhere else.


There are two ways through which this can be achieved through below two classes : - 

1) LedgerDimensionFacade 

2) LedgerDynamicAccountHelper


Let's take a look at the below code snippet where we want to extract the display value from the LedgerDimension field : -


1) LedgerDimensionFacade


 str                     dispVal;

 LedgerJournalTrans      jourTrans;

 ledgerJournalTrans.LedgerDimension = dimensionAttributeValueCombination.RecId;        

 dispVal = LedgerDimensionFacade::getDisplayValueForLedgerDimension(jourTrans.LedgerDimension)

 info(dispVal);


2) LedgerDynamicAccountHelper


 str                     val;

 LedgerJournalTrans      jourTrans;

 ledgerJournalTrans.LedgerDimension = dimensionAttributeValueCombination.RecId;        

 val = LedgerDynamicAccountHelper::getAccountNumberFromDynamicAccount(jourTrans.LedgerDimension);

 info(dispVal);


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

Monday, January 25, 2021

How to import WBS(Work breakdown structure) Quotation data from MS Excel in Dynamics 365 FO

 

Just like I mentioned in my previous post regarding the upload of WBS Data and the challenges developers face similarly we sometimes get  the requirement of writing the code for uploading the WBS Quotation Data from MS Excel in to D365 Fin Ops Masters.


The Entire Code Construct is same for this as it was there in my last post except the Data entity name which will be used in this scenario will be different.


Data Entity which we have to use is "ProjQuotationWBSEntity".


Let's see how its done in the code given below : - 


using System.IO;

using OfficeOpenXml;

using OfficeOpenXml.ExcelPackage;

using OfficeOpenXml.ExcelRange;

using OfficeOpenXml.Style;

using OfficeOpenXml.Table;


class WBSProjQuotationExcelUpload

{

    /// <summary>

    /// Runs the class with the specified arguments.

    /// </summary>

    /// <param name = "_args">The specified arguments.</param>

    public static void main(Args _args)

    {

        MemoryStream                              memoryStream    = new MemoryStream();

        WBSProjQuotationExcelUpload   importWBS       = WBSProjQuotationExcelUpload::construct();

        ProjProjectWBSDraftEntity           projectwbsentity;

        ProjWBSActivityEstimatesEntity      activityEstimateEntity;

        ProjPlanVersion                               projPlanVersion,projPlanVersionProcess,projPlanVersioncount;

        FormRun                                          formRun;

        projPlanVersion                                =   _args.record();

        formRun                                           =   _args.caller();


        if (Box::okCancel("Do you want to Upload Excel ?", DialogButton::Cancel) == DialogButton::Ok)

        {

            select count(RecId) from projPlanVersioncount

                where projPlanVersioncount.HierarchyId == projPlanVersion.HierarchyId;


            if (projPlanVersioncount.RecId != 1)

            {

                formRun.deleteRecords();

            }


            ttsbegin;

            update_recordset  projPlanVersionProcess

                setting ProcessedEstimation = NoYes::No

                where projPlanVersionProcess.HierarchyId   ==    projPlanVersion.HierarchyId;

            ttscommit;


            importWBS.import();

        }

    }


    public static WBSProjQuotationExcelUpload construct()

    {

        return new WBSProjQuotationExcelUpload();

    }


    public void import()

    {

        System.IO.Stream                    stream;

        ExcelSpreadsheetName            sheet;

        FileUploadBuild                       fileUpload;

        DialogGroup                             dlgUploadGroup;

        FileUploadBuild                       fileUploadBuild;

        FormBuildControl                    formBuildControl;

        ProjQuotationWbsEntity          wbsEntity;


        Dialog                          dialog = new Dialog('Import Project Quotation WBS from excel');


        dlgUploadGroup                  = dialog.addGroup("Upload WBS Quotation Group");

        formBuildControl                = dialog.formBuildDesign().control(dlgUploadGroup.name());

       fileUploadBuild = formBuildControl.addControlEx(classStr(FileUpload), 'UploadWBSQuotation');


        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);

        fileUploadBuild.fileTypesAccepted('.xlsx');


        if(dialog.run() && dialog.closedOk())

        {

            FileUpload                          fileUploadControl   = dialog.formRun().control(dialog.formRun().controlId('UploadWBSQuotation'));

            FileUploadTemporaryStorageResult    fileUploadResult    = fileUploadControl.getFileUploadResult();


            if (fileUploadResult != null && fileUploadResult.getUploadStatus())

            {

                stream = fileUploadResult.openResult();

                using (ExcelPackage Package = new ExcelPackage(stream))

                {

                    int rowCount, i;

                    Package.Load(stream);

                    ExcelWorksheet worksheet        = package.get_Workbook().get_Worksheets().get_Item(1);

                    OfficeOpenXml.ExcelRange range  = worksheet.Cells;

                    rowCount = worksheet.Dimension.End.Row  - worksheet.Dimension.Start.Row + 1;


                    ttsbegin;

                    for (i = 2; i<= rowCount; i++)

                    {

                        wbsEntity.QuotationId           =  range.get_Item(i, 1).Value;

                        wbsEntity.WBSId                 =  range.get_Item(i, 2).Value;

                        wbsEntity.Reference         =  range.get_Item(i, 3).Value;

                        wbsEntity.TaskID            =  range.get_Item(i, 4).Value;

                        wbsEntity.Task                  =  range.get_Item(i, 5).Value;

                        wbsEntity.Note                  =  range.get_Item(i, 6).Value;

                        wbsEntity.Category              =  ProjParameters::find().EmplCategory;

                        wbsEntity.Quantity          =  range.get_Item(i, 7).Value;

                        wbsEntity.ExpensePercent    =  range.get_Item(i, 8).Value;

                        wbsEntity.MarkupPercent     =  range.get_Item(i, 9).Value;

                        wbsEntity.StartDate             =  str2DateDMY(range.get_Item(i, 10).Value);

                        wbsEntity.EndDate               =  str2DateDMY(range.get_Item(i, 11).Value);

                        wbsEntity.insert();

                    }

                    ttscommit;

                    

                    info(strFmt('%1 records processed', rowCount-1));

                }

            }

        }

    }

}


Happy Coding.


Please type in the comments section if you have any queries.



How to import WBS (Work breakdown structure) data from MS Excel in Dynamics 365 FO

 

In most of the scenarios where developers get a requirement to import data from Microsoft Excel into the WBS Masters and Transaction tables they get stuck as WBS Paradigm includes lot of tables and classes to execute a proper WBS Header and Line structure.


Now with Data Entities in picture the task has become much more simpler to achieve.


Let's take a look at the code mentioned below in a class and see how its done : - 


Note : - The Below code has used the initialization of some custom fields


using System.IO;

using OfficeOpenXml;

using OfficeOpenXml.ExcelPackage;

using OfficeOpenXml.ExcelRange;

using OfficeOpenXml.Style;

using OfficeOpenXml.Table;


class WBSExcelUpload

{

    public static void main(Args    _args)

    {

        MemoryStream                                  memoryStream    = new MemoryStream();

        WBSExcelUpload                              importWBS       = WBSExcelUpload::construct();

        ProjProjectWBSDraftEntity               projectwbsentity;

        ProjWBSActivityEstimatesEntity      activityEstimateEntity;

        ProjPlanVersion                               projPlanVersion,projPlanVersionProcess,projPlanVersioncount;

        FormRun                                            formRun;


        projPlanVersion =   _args.record();

        formRun =   _args.caller();

        if (Box::okCancel("Do you want to Upload Excel WBS Data?"

                                        , DialogButton::Cancel) == DialogButton::Ok)

        {

            select count(RecId) from projPlanVersioncount

                where projPlanVersioncount.HierarchyId == projPlanVersion.HierarchyId;

            if (projPlanVersioncount.RecId != 1)

            {

                formRun.deleteRecords();

            }

            ttsbegin;

                update_recordset  projPlanVersionProcess

                setting ProcessedEstimation = NoYes::No

                where projPlanVersionProcess.HierarchyId   ==    projPlanVersion.HierarchyId;

            ttscommit;

            importWBS.import();

        }

        

    }


    public static WBSExcelUpload construct()

    {

        return new WBSExcelUpload();

    }


    public void import()

    {

        System.IO.Stream                stream;

        ExcelSpreadsheetName            sheet;

        FileUploadBuild                 fileUpload;

        DialogGroup                     dlgUploadGroup;

        FileUploadBuild                 fileUploadBuild;

        FormBuildControl                formBuildControl;

        ProjProjectWBSDraftEntity       wbsEntity;


        Dialog                          dialog = new Dialog('Import WBS Data from excel');


        dlgUploadGroup                  = dialog.addGroup("WBS Data Upload Group");

        formBuildControl                = dialog.formBuildDesign().control(dlgUploadGroup.name());

        fileUploadBuild       = formBuildControl.addControlEx(classStr(FileUpload), 'UploadExcelWBS');


        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);

        fileUploadBuild.fileTypesAccepted('.xlsx');


        if(dialog.run() && dialog.closedOk())

        {

            FileUpload                          fileUploadControl   = dialog.formRun().control(dialog.formRun().controlId('UploadExcelWBS'));

            FileUploadTemporaryStorageResult    fileUploadResult    = fileUploadControl.getFileUploadResult();


            if (fileUploadResult != null && fileUploadResult.getUploadStatus())

            {

                stream = fileUploadResult.openResult();

                using (ExcelPackage Package = new ExcelPackage(stream))

                {

                    int rowCount, i;

                    Package.Load(stream);

                    ExcelWorksheet worksheet  = package.get_Workbook().get_Worksheets().get_Item(1);

                    OfficeOpenXml.ExcelRange range  = worksheet.Cells;

                    rowCount = worksheet.Dimension.End.Row  - worksheet.Dimension.Start.Row + 1;

                    

                     ttsbegin;

                    for (i = 2; i<= rowCount; i++)

                    {

                        wbsEntity.ProjectId             =  range.get_Item(i, 1).Value;

                        wbsEntity.WBSId                 =  range.get_Item(i, 2).Value;

                        wbsEntity.Reference         =  range.get_Item(i, 3).Value;

                        wbsEntity.TaskID            =  range.get_Item(i, 4).Value;

                        wbsEntity.Task                  =  range.get_Item(i, 5).Value;

                        wbsEntity.Note                  =  range.get_Item(i, 6).Value;

                        wbsEntity.Category              =  ProjParameters::find().EmplCategory;

                        wbsEntity.Quantity          =  range.get_Item(i, 7).Value;

                        wbsEntity.ExpensePercent    =  range.get_Item(i, 8).Value;

                        wbsEntity.MarkupPercent     =  range.get_Item(i, 9).Value;

                        wbsEntity.StartDate             =  str2DateDMY(range.get_Item(i, 10).Value);

                        wbsEntity.EndDate               =  str2DateDMY(range.get_Item(i, 11).Value);

                        wbsEntity.insert();

                    }

                    ttscommit;

                    

                    info(strFmt('%1 records processed', rowCount-1));

                }

            }

        }

    }

}


Please type in the comments section of this post if you have any queries.


Happy Coding !!!

Importing Excel Dates in D365 F&O through X++ without the Apostrophe Trick

  We often get a requirement to create excel upload custom functionality in x++ . In this post we will see how to handle Excel OLE Automatio...