Generate Token once and use it to authenticate all APIs in Postman

Purpose of this short tutorial is to explain how to generate token once and use the generated token to authenticate apis in Postman without manually copy pasting it each time.

Steps : 

1. Create Environment : 

        First create new environment or you can use existing environment.

2. Create token variable :

        Create a variable named token in the environment created in step 1. Here I have created Test environment  and created token variable.

3. Create Collection :

        Then create a collection. I have created a collection with Test name here. In the Authorization tab of collection set it to Bearer token and in token tab enter the variable created in step 2. 

4. Add token request :

        Now add the token request in the collection created. Enter the token url, credentials and all the details required.

5. Code to set token variable :

        Once all details are entered, add the following code to the Tests segments of the request.

if(pm.response.code === 200)

{

pm.environment.set('token',pm.response.json().access_token)

}

6. Use generated token in apis :

        Step generates the token and sets the variable (token). Now we can use that variables in all apis for authentication. For all apis that we need to run using this token, select environment as test.

Thank you for reading..!!!

 

X++ code to apply GST tax adjustment

                                          

While importing invoices from an external system, we may find penny difference between how tax is calculated between these two systems. But this penny difference turns into big amount when we integrate/import thousands of invoices from an external system. This could be real pain in reconciliation. So we would need to match the taxes from both the systems before posting.

There is manual way to apply adjustment on tax document generated in D365 finance and operations, but user can not manually adjust those thousands of invoices.

This can be taken care through x++ code. Please find below the sample code to adjust CGST amount on Free text invoice before posting. This code updates CGST amount to 212 on free text invoice line.

class Custom_ApplyTaxAdjustment

{        

     public static void main(Args _args)

    {        

        RefRecId                        invRec = 68719504909;//hardcoded for testing purpose      

        CustInvoiceTable                invoiceTable;

        CustInvoiceLine                 custLine;

        TaxDocumentRowMeasureAdjustment taxAdj;

        

        ITaxableDocument            taxableDocumentObject;

        ITaxDocument                    taxDoc;

        

        ttsbegin;

        invoiceTable    =   CustInvoiceTable::findRecId(invRec);

        select custLine

                where custLine.ParentRecId    ==  invoiceTable.RecId;

        taxDoc=TaxBusinessService::getTaxDocumentBySource(invoiceTable.TableId, invoiceTable.RecId);

        

        taxableDocumentObject = TaxableDocumentObject::constructServer(TaxableDocumentDescriptorFactory::getTaxableDocumentDescriptor(invoiceTable));

        taxableDocumentObject.parmTaxDocument(taxDoc);

        

        TaxDocumentRowMeasureAdjustment::createAndUpdate(custLine.TableId, custLine.RecId,'Header/Lines/GST/CGST/Tax Amount',212,212,0,invoiceTable.TableId, invoiceTable.RecId); //hardcoded for testing purpose      

              

        TaxBusinessService::recalculateTax(taxableDocumentObject);

        ttscommit; 

    }

}

Please do comment if this code helps you and also let me know if you need any specific code. 

Thank you for reading...!! 

Run SQL query and get data in excel from D365 finance and operations

Run SQL query and export data in excel from D365 finance and operations

As we know, in case 0f cloud implementation of d365 finance and operations we do not have access to SQL server on production environment. Hence, whenever we need to get data from multiple tables as and when required, we have two options:

1. Develop a new report.

2. Create a request to Microsoft

Any of the above option may take 1-2 days’ time. Sometimes we can’t afford to lose this much time. Also, whenever we need the different data, we need to follow any one of the above option.

To solve this problem, I have developed a class which takes the SQL query as input and the result is given in an excel file.

When you run the class you will get the below dialog box. Once you enter the query and click OK, it exports the result of query into excel.

This is only to get the data from database. Only select queries are run through this class, update/insert is not supported. One should not update any data through SQL, it is not the best practice.

Thank you for reading..!!!

Please find the code below: 

using System.IO;

using OfficeOpenXml;

using OfficeOpenXml.Style;

using OfficeOpenXml.Table;

using OfficeOpenXml.ExcelPackage;

using OfficeOpenXml.ExcelRange;

class Custom_ExportExcel_SSMS extends RunBase

{

   Dialog                          dialog;

   Filename                        FileUploadName, textFile;

   int                             fileLineNumber;

   NoYes                           generateTemplate;

   DialogField                     df_qry;     

   FileName                        filename;

   str                             sqlQry;

   /// <summary>

   ///

   /// </summary>

   /// <returns></returns>

   public ClassDescription caption()

   {

       

       return ret = "SSMS result export";

   }

   public Object dialog()

   {

       DialogGroup         dialogGroup;

       FormBuildControl    formBuildControl;

       FileUploadBuild     dialogFileUpload;

       FilenameSave        fileSave;

       Set                 enumSet = new Set(Types::Enum);

       ;

       

       dialog = super();

       

       df_qry = dialog.addField(extendedTypeStr(Notes), 'Query');                

       return dialog;

   }

   public boolean getFromDialog()

   {

       sqlQry    =   df_qry.value();

       return true;

   }

   public static Custom_ExportExcel_SSMS construct()

   {

       return new Custom_ExportExcel_SSMS ();

   }

   public static void  main(Args _args)

   {

       Custom_ExportExcel_SSMS   exportData = Custom_ExportExcel_SSMS::construct();

       if(exportData.prompt())

       {

           exportData.generateFile();

       }

   }

   private Void generateFile()

   {

       int                             column = 1;

       str                             fileContent;

       int             colCount, colNumber, rowNumber;

       Connection Con = new Connection();

       Statement Stmt = Con.createStatement();

       ResultSet R;

       ResultSetMetaData   rMetadata;

       MemoryStream memoryStream = new MemoryStream();

       fileName = 'SSMS query result.xlsx';

       R =Stmt.executeQuery(sqlQry);

           

       rowNumber   =   1;

       using (var package = new ExcelPackage(memoryStream))

       {

           var currentRow              = 1;

           var worksheets              = package.get_Workbook().get_Worksheets();

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

           var cells                   = exportWorksheet.get_Cells();

           OfficeOpenXml.ExcelRange    cell;

           System.String               value;

                                   

           while ( R.next() )

           {

               if(currentRow == 1)

               {

                   rMetadata = R.getMetaData();

                   colCount = rMetadata.getColumnCount();

                           

                   for(colNumber = 1; colNumber <= colCount; colNumber++)

                   {

                       cell = null; cell = cells.get_Item(currentRow, column); column++; value = rMetadata.getColumnName(colNumber); cell.set_Value(value);

                   }

                   currentRow++;

               }

               column = 1;

               for(colNumber = 1; colNumber <= colCount; colNumber++)

               {

                   switch(rMetadata.getColumnType(colNumber))

                   {

                       case Types::String :

                       case Types::RString :

                       case Types::VarString :

                           cell = null; cell = cells.get_Item(currentRow, column); column++; value = R.getString(colNumber); cell.set_Value(value);

                           break;

                       case Types::Int64 :

                           cell = null; cell = cells.get_Item(currentRow, column); column++; cell.set_Value(R.getInt64(colNumber));

                           break;

                       case Types::Enum :

                       case Types::Integer :

                           cell = null; cell = cells.get_Item(currentRow, column); column++; cell.set_Value(R.getInt(colNumber));

                           break;

                       case Types::Real :

                           cell = null; cell = cells.get_Item(currentRow, column); column++; cell.set_Value(R.getReal(colNumber));

                           break;

                       case Types::Date :

                           cell = null; cell = cells.get_Item(currentRow, column); column++; cell.set_Value(R.getDate(colNumber));

                           break;

                       case Types::UtcDateTime :

                           cell = null; cell = cells.get_Item(currentRow, column); column++; cell.set_Value(R.getDateTime(colNumber));

                           break;

                       case Types::Guid :

                           cell = null; cell = cells.get_Item(currentRow, column); column++; cell.set_Value(R.getGuid(colNumber));

                           break;

                       default:

                           cell = null; cell = cells.get_Item(currentRow, column); column++; cell.set_Value(R.getInt64(colNumber));

                           break;

                   }

                   

               }

               currentRow++;

           }

           

           package.Save();

           file::SendFileToUser(memoryStream, fileName);

       }

   }

}