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.