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 !!!!

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 !!!

How to display / delete VSTS Workspace through Visual Studio Command Prompt - Dynamics 365 FO

 

Since VSTS (Visual Studio Team Services) has become an integral part for any kind of development and customization in D365 Fin Ops.

The Basic steps include configuring of Workspaces which requires mapping of the Metadata and Projects.

Sometimes through the UI of Visual Studio we face difficulties in VSTS workspace related activities such as display of all workspaces or deleting any workspace.

In most of the cases any user who tries creating a workspace with a specific naming convention gets an error "Workspace with the name "ABC" already exist" but if we check we cannot see the workspace because it might have been created by a different user

If we have proper administrative privileges we can display and remove it from Visual Studio Command Line Interface known as Visual Studio Command Prompt.

Just follow these simple steps : - 

1) Open Visual Studio Command Prompt as Administrator

2) To display the workspaces type the below command : - 

     TF workspaces /collection:"https://abc-dynamics.visualstudio.com" /owner : *

                                           or

     To delete a workspace type the below command : - 

     TF workspace /delete /collection:https://abc-dynamics.visualstudio.com dev-vm-dev0;abcuser@xyz.com

   For anymore further queries please mention in the comments.

How to access data via SQL Commands in Production Environment in D365 FO

Accessing the data for Production Environment is one of the major challenges which consultants face especially when there is a data discrepancy issue and because of this issue major financial statements are getting impacted.


Microsoft has given us very limited privileges when it comes to Sandbox environments and that too can be done only in UAT Environments. For Production all the control is there with Microsoft Product Team only. So the Programmers have somehow found a way to run basic SQL scripts in Production Environment and perform some data manipulations.

Note : - Data Manipulation can always be proved risky so before deploying this customization to Production Environment we have to proceed with caution and always keep the Production DB copy in our LCS Asset Library

Here is how we can do it in few simple steps : - 

1) Open Visual Studio as Administrator in your Tier 1 or Tier 2 DEV Environments :

2) Create New Project and associate it with your own custom model

3) We need to create 2 classes : - SqlBrowser and SqlHelper

4) First let's begin with the class creation for Sql Browser

5) Right click on Project -- >  Add New Item --> Class --> SQLHelper

6) Use the code mentioned below : - 

class SQLHelper

{

       

        public static Container getExpectedResultFromQuery(str queryStmt)

        {

            int i;

            int colType;

            container expectedSubResult, expectedResult;

            int counter;

            ResultSet resultSet;

            ;

            try

            {

                // Executes the Query statement on the back end database.

                resultSet = SQLHelper::resultSetExecuteQuery(queryStmt);

                while(resultSet.next())

                {

                    expectedSubResult = connull();

                    for ( i = 1; i <= resultSet.getMetaData().getColumnCount(); i++)

                    {

                        colType = resultSet.getMetaData().getColumnType(i);

                        switch (colType)

                        {

                            case 0: // String

                            case 8: // Memo

                                expectedSubResult += resultSet.getString(i);

                                break;

                            case 1: // Interger

                                expectedSubResult += resultSet.getInt(i);

                                break;

                            case 2: // Real

                                expectedSubResult += resultSet.getReal(i);

                                break;

                            case 3: // Date

                                expectedSubResult += resultSet.getDate(i);

                                break;

                            case 4: // Enum

                                expectedSubResult += resultSet.getBoolean(i);

                                break;

                            case 6: // UtcDateTime

                                expectedSubResult += dateTime2str(resultSet.getDateTime(i));

                                break;

                            case 45: // Guid

                                expectedSubResult += guid2str(resultSet.getGuid(i));

                                break;

                            case 49: // Int64

                                expectedSubResult += resultSet.getInt64(i);

                                break;

                            default:

                                break;

                        }

                        // End of Switch

                    }

                    // End of for

                    expectedResult += [expectedSubResult];

                }

            info(con2Str(expectedSubResult));

                // End of while

            }

            catch

            {

                Error('error');

            }

            return expectedResult;

        }

       

 

        private server static ResultSet resultSetExecuteQuery(str strQuery)

        {

            Connection  connection = new Connection();

            Statement   statement = connection.createStatement();

            SqlStatementExecutePermission permission;

            ResultSet resultSet;

           

            // Get the formatted Query statement.

            strQuery = strfmt(strQuery,SRSStatementQuery::getDbSchemaPrefix());

            

            permission = new SqlStatementExecutePermission(strQuery);

            permission.assert();

            resultSet = statement.executeQuery(strQuery);

            return resultSet;

        }

 7)  Now Create SQLBrowser class which will call the functionality of SQLHelper

class SqlBrowser

{

    public static void Main(Args _args)

    {

        Dialog dialog= new Dialog("SQL");

        DialogField queryInput = Dialog.addField(extendedTypeStr(MyExtendedDataType),"SQL query");

        dialog.run();

        if(dialog.closedOk() && queryInput.value())

        {

            str query = queryInput.value();

            container con = SQLHelper::getExpectedResultFromQuery(query);

            info(con2Str(con));

            File::SendStringAsFileToUser(con2Str(con),'sqlresult.txt');

            //exporting result of select statement to txt file

        }

    }

8) Now create Action Menu Item for the class Sql Browser

9) If you do not want to create a menu item then you will have to access it through the browser url as mentioned below : - 

www.abc.cloud.dynamics.com/?mi=sysclassrunner&cls=SqlBrowser

10) Once your menu item or class is called it will show you the screen like below : - 

 

11) That's it enter your query and you will get your desired result.

How to Export and Import Models in D365 FO

 Once of the most common requirements before moving the code between multiple environments is to take the model backup from the source environment and restore the same in the destination environment.

Here's how we can achieve that in few simple steps mentioned below : - 

1) Open Command Prompt as Administrator

2) Go to the path C:\AOSService\PackagesLocalDirectory\bin

3) For Exporting the model type the below command : - 

ModelUtil.exe -export -metadatastorepath=k:\aosservice\packageslocaldirectory -modelname="MyCustomModel" -outputpath=c:\NewFolder

Note : - Output path is the desired path of the user where the file has to be exported

The Above command will generate .axmodel file which can be used later to import the model in the D365 Model Metadata repository

For Importing the already exported model through .axmodel file use the below command in the same location C:\AOSService\PackagesLocalDirectory\bin  : -

ModelUtil.exe -import -metadatastorepath=k:\aosservice\packageslocaldirectory -file=c:\NewFolder\MyCustomModel.axmodel

4) Once the import is complete Open Visual Studio as Administrator

5) Go to Dynamics 365 Menu --> Model Management -->Refresh Models

That's it you will be able to access all the Model Elements in your future customizations

As a best practice its always recommended to do Full Build of the models after the import is completed.

How to use the method parameter in Pre or Post Event Handler of an existing method of a table/class using X++ in D365 FO

Most of the times we have seen such scenarios where the requirement is to call or assign a different value to an existing method parameter for a table or a class in pre or post event handlers.

In case of such predicaments there is an approach which comes handy and that is to simply use the getArg( ) method of the args class variable.


Let's take a look at the following example.

There is a table PurchLine which contains populate( ) method.

class PurchLine
{
     public static void populate(ItemId _invid)
    {
         // Method definition goes here.
     }
}


Now we need to assign a value to the invid parameter in Pre or Post event handler for this method.


This is how we can achieve our objective :-


public static void populate_Post(XppPrePostArgs _args)
{
    PurchLine purchLine = _args.getArg(identifierStr(_purchLine)) as PurchLine;

    str  a         = _args.getArg(identifierStr(_invid));

    a = "1167"; // We have assigned a new value
 }

In the above example getArg( ) method has helped us to achieve our goal very easily.


There might be hundreds of other approaches as well but this one might come handy for most of us when we need to work with method parameters in Pre or Post Event Handlers.