Wednesday, December 27, 2023

using SQL Query in X++

 If you need to execute sql query inside your X++ code then below example can help you. To pass value to SQL query we need to use strfmt() function. 


       str sql;

       Connection conn;

       SqlStatementExecutePermission permission;

       ;


       sql= strFmt("update ProdBOM set InventDimId = '%1' where RecId = %2",                                                                   inventDim.inventDimId,ProdBOM.RecId);

       permission = new SqlStatementExecutePermission(sql);

       conn = new Connection();

       permission = new SqlStatementExecutePermission(sql);

       permission.assert();

       conn.createStatement().executeUpdate(sql);

      

       CodeAccessPermission::revertAssert();

Tuesday, December 5, 2023

Get Product Master Size,Color,Style, ConfigID in D365FO using SQL

If you want to get Product Master Size,Color,Style, ConfigID and thier names then below three SQL queries can help you get data quickly from D365FO compared to X++ development. Normally the volume of data is high when we try to export these types of data.


1) Product Master Size


SELECT ecoresproduct.recid             [PRODUCTMASTERNUMBER],

       ecoressize.NAME                 [PRODUCTSIZEID],

       isnull(ecoresproducttranslation.NAME,'')   [PRODUCTNAME],

       inventitemgroupitem.itemgroupid [ITEMGROUP],

       ''''+ inventtable.itemid              [ITEMID],

   isnull(translate.NAME,'')   [SIZENAME]

FROM   ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue

       INNER JOIN ecoresproduct ecoResProduct

               ON ecoresproduct.recid =

                  ecoresproductmasterdimensionvalue.sizeproductmaster

       LEFT OUTER JOIN ecoresproducttranslation

                    ON ecoresproducttranslation.product = ecoresproduct.recid

       LEFT JOIN inventtable

              ON inventtable.product = ecoresproduct.recid

       LEFT JOIN inventitemgroupitem

              ON inventitemgroupitem.itemid = inventtable.itemid

                 AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid

       INNER JOIN ecoressize ecoressize

               ON ecoressize.recid = ecoresproductmasterdimensionvalue.size_

       LEFT JOIN ecoresproductmasterdimvaluetranslation translate

              ON translate.productmasterdimensionvalue =

                 ecoresproductmasterdimensionvalue.recid

WHERE  inventtable.DataAreaId ='@DataAreaID'


2) Product Master Color


SELECT ecoresproduct.recid             [PRODUCTMASTERNUMBER],

       ecorescolor.NAME                [PRODUCTCOLORID],

       isnull(ecoresproducttranslation.NAME,'')   [PRODUCTNAME],

       inventitemgroupitem.itemgroupid [ITEMGROUP],

       ''''+ inventtable.itemid              [ITEMID],

   isnull(translate.NAME,'')   [COLORNAME]

FROM   ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue

       INNER JOIN ecoresproduct ecoResProduct

               ON ecoresproduct.recid =

                  ecoresproductmasterdimensionvalue.colorproductmaster

       LEFT OUTER JOIN ecoresproducttranslation

                    ON ecoresproducttranslation.product = ecoresproduct.recid

       LEFT JOIN inventtable

              ON inventtable.product = ecoresproduct.recid

       LEFT JOIN inventitemgroupitem

              ON inventitemgroupitem.itemid = inventtable.itemid

                 AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid

       INNER JOIN ecorescolor ecoResColor

               ON ecorescolor.recid = ecoresproductmasterdimensionvalue.color

       LEFT JOIN ecoresproductmasterdimvaluetranslation translate

              ON translate.productmasterdimensionvalue =

                 ecoresproductmasterdimensionvalue.recid

WHERE  inventtable.DataAreaId ='@DataAreaID'


3) Product Master Style


SELECT ecoresproduct.recid             [PRODUCTMASTERNUMBER],

       ecoresstyle.NAME                [PRODUCTSTYLEID],

       ecoresproducttranslation.NAME   [PRODUCTNAME],

       inventitemgroupitem.itemgroupid [ITEMGROUP],

       ''''+ inventtable.itemid              [ITEMID],

   isnull(translate.NAME,'')   [STYLENAME]

FROM   ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue

       INNER JOIN ecoresproduct ecoResProduct

               ON ecoresproduct.recid =

                  ecoresproductmasterdimensionvalue.styleproductmaster

       LEFT OUTER JOIN ecoresproducttranslation

                    ON ecoresproducttranslation.product = ecoresproduct.recid

       LEFT JOIN inventtable

              ON inventtable.product = ecoresproduct.recid

       LEFT JOIN inventitemgroupitem

              ON inventitemgroupitem.itemid = inventtable.itemid

                 AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid

       INNER JOIN ecoresstyle ecoresstyle

               ON ecoresstyle.recid = ecoresproductmasterdimensionvalue.STYLE

       LEFT JOIN ecoresproductmasterdimvaluetranslation translate

              ON translate.productmasterdimensionvalue =

                 ecoresproductmasterdimensionvalue.recid

WHERE  inventtable.DataAreaId ='@DataAreaID'


4) Product Master Config


SELECT ecoresproduct.recid             [PRODUCTMASTERNUMBER],

       ecoresconfiguration.NAME        [PRODUCTCONFIGURATIONID],

       ISNULL(ecoresproducttranslation.NAME,'')   [PRODUCTNAME],

       inventitemgroupitem.itemgroupid [ITEMGROUP],

       ''''+ inventtable.itemid              [ITEMID],

   isnull(translate.NAME,'')   [CONFIGNAME]

FROM   ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue

       INNER JOIN ecoresproduct ecoResProduct

               ON ecoresproduct.recid =

                  ecoresproductmasterdimensionvalue.CONFIGPRODUCTMASTER

       LEFT OUTER JOIN ecoresproducttranslation

                    ON ecoresproducttranslation.product = ecoresproduct.recid

       LEFT JOIN inventtable

              ON inventtable.product = ecoresproduct.recid

       LEFT JOIN inventitemgroupitem

              ON inventitemgroupitem.itemid = inventtable.itemid

                 AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid

       INNER JOIN ecoresconfiguration ecoresconfiguration

               ON ecoresconfiguration.recid = ecoresproductmasterdimensionvalue.configuration

       LEFT JOIN ecoresproductmasterdimvaluetranslation translate

              ON translate.productmasterdimensionvalue =

                 ecoresproductmasterdimensionvalue.recid

WHERE  inventtable.DataAreaId ='@DataAreaID'

Get released product variant in AX 2012 R3

 If you want get released product variants of AX 2012 using SQL, then SQL script can help you. Here I am fetching Item Number, Product Variant Number, Style,Color, Size, Product Name. 


Please notice the join condition of InventDimCombination with EcoResProduct table. I wrote this query while doing data migration from AX 2012 R3 to D365F&O.


SELECT inventdimcombination.itemid               [ITEMNUMBER],
       ecoresproduct.displayproductnumber        [PRODUCTVARIANTNUMBER],
       inventdim.inventstyleid                   [PRODUCTSTYLEID],
       inventdim.inventcolorid                   [PRODUCTCOLORID],
       inventdim.inventsizeid                    [PRODUCTSIZEID],
       inventdim.configid                        [PRODUCTCONFIGURATIONID],
       Isnull(ecoresproducttranslation.NAME, '') [PRODUCTNAME],
       inventitemgroupitem.itemgroupid           [ITEMGROUP]
FROM   inventdimcombination
       INNER JOIN inventdim
               ON inventdim.inventdimid = inventdimcombination.inventdimid
       INNER JOIN inventtable
               ON inventtable.itemid = inventdimcombination.itemid
       INNER JOIN ecoresproduct
               ON ecoresproduct.recid =
                  inventdimcombination.distinctproductvariant
       INNER JOIN ecoresproducttranslation
               ON ecoresproducttranslation.product = ecoresproduct.recid
       INNER JOIN inventitemgroupitem
               ON inventitemgroupitem.itemid = inventtable.itemid
                  AND inventitemgroupitem.itemdataareaid =
                      inventtable.dataareaid
WHERE  inventtable.dataareaid = '@DataAreaID' 

Thursday, May 6, 2021

Dynamic SSRS report name in D365 FnO

 When there is a requirement to generate dynamic report name. For example when saving report, the report name should be appended with Customer ID, Invoice ID etc. To do this in the controller class we have to write code for parmDialogCaption() method shown below:-

 

class DemoController extends SrsReportRunController
{
 
    public void prePromptModifyContract()
    {
        ProjInvoiceJour              projInvoiceJour;
        DemoContract             contract;
        FormDataSource               fds;
        
        contract    = this.parmReportContract().parmRdpContract() as DemoController;
        projInvoiceJour = args.record();
        fds = args.record().dataSource();
        contract.parmProjInvoiceId(projInvoiceJour.ProjInvoiceId);  
    }

    public static client void main(Args args)
    {
        DemoController             demoController;
        ProjInvoiceJour              projInvoiceJour;
        demoController= new DemoController();
        demoController.parmArgs(args);
        demoController.parmReportName(ssrsReportStr(RAR_AmazonBillingReport, Report));
        demoController.parmShowDialog(false);
       
        demoController.parmReportContract().parmReportCaption("Project Invoice Report");
        
        projInvoiceJour = args.record();
        demoController.parmDialogCaption("Project Invoice Report" + "_"+ projInvoiceJour.ProjInvoiceId); // This will generate dynamic report name        
        demoController.startOperation();
    }


when you export the report in excel, pdf or csv, the report name will be appended with ProjInvoiceId.

Monday, November 2, 2020

Deploy all the SSRS Report using power shell in D365FnO


Deploy all the SSRS Reports using power shell in D365FnO. Please follow below two steps:

1) Open Windows Power Shell
 Start > Run > Windows PowerShell

2) Run the below Power Shell command
K:\AosService\PackagesLocalDirectory\Plugins\AxReportVmRoleStartupTask\DeployAllReportsToSSRS.ps1 -PackageInstallLocation "K:\AosService\PackagesLocalDirectory"

Wednesday, October 14, 2020

Upload Excel Data in D365FnO

 This is common requirement in X++ to create a class to upload data from Excel in a table. So below code you can use and customize as per your requirement.


using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class ExcelUpload
{
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = “_args”>The specified arguments.</param>
    public static void main(Args _args)
    {
        System.IO.Stream stream;

        ExcelSpreadsheetName        sheeet;
        FileUploadBuild             fileUpload;
        DialogGroup                 dlgUploadGroup;
        FileUploadBuild             fileUploadBuild;
        FormBuildControl            formBuildControl;
        ProjTable                   projTable;
        Dialog                      dialog = new Dialog("Import the Projects data from Excel");

        dlgUploadGroup      = dialog.addGroup("Select excel file");
        formBuildControl    = dialog.formBuildDesign().control(dlgUploadGroup.name());
        fileUploadBuild     = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');
        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
        fileUploadBuild.fileTypesAccepted('.xlsx');

        if (dialog.run() && dialog.closedOk())
        {
            FileUpload fileUploadControl = dialog.formRun().control(dialog.formRun().controlId('Upload'));
            FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();

            if (fileUploadResult != null && fileUploadResult.getUploadStatus())
            {
                stream = fileUploadResult.openResult();
                using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(stream))
                {
                    int rowCount, i;
                    Package.Load(stream);
                    OfficeOpenXml.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;
                    try
                    {
                        for (i = 2; i<= rowCount; i++)
                        {
                            str projId      = range.get_Item(i, 1).value;
                            str projStatus  = range.get_Item(i, 2).value;
                            //str startDate   = range.get_Item(i, 3).value;

                            select firstonly projTable where projTable.ProjId == projId;
                            if(projTable)
                            {
                                ttsbegin;
                                projTable.selectForUpdate(true);
                                projTable.reread();
                                if(projTable.Status == ProjStatus::Created)
                                {
                                    projTable.status().runStartUp();
                                }
                                //projTable.StartDate = str2Date(startDate, 213);
                                if(range.get_Item(i, 3).value)
                                {
                                    projTable.StartDate = DateTimeUtil::date(range.get_Item(i, 3).value);
                                    projTable.doUpdate();
                                }
                                ttscommit;
                            }
                        }
                        info("Data inserted succesfully");
                    }
                    catch (Exception::Error)
                    {
                        throw error("error here");
                    }
                }
            }
        }
    }

}

Friday, October 2, 2020

How to use strSplit() Function in x++

While doing customization I came across a scenario where the datatype of a column was of string type and value was stored as "1111.93799999999999994". And the requirement was to get  exact two digits after the decimal point. No rounding up/down was required. In the below code example you can achieve this.


class stringSplitFunctionDemo
{
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        str str1='1111.93799999999999994';
       
        List strlist=new List(Types::String);
        ListIterator iterator;
        str _Value,beforeDec,afterDec,finalOutput;
        int x;
        ;

        strlist=strSplit(str1,'.');
        iterator = new ListIterator(strlist);
        
        while(iterator.more())
        {
            x= x+1;

            if(x==1)
            {
                beforeDec =iterator.value();            
            }
            if(x==2)
            {
                afterDec = iterator.value();
            }
            iterator.next();
        }

        finalOutput = beforeDec +'.'+ subStr(afterDec,1,2);
        Info(finalOutput);


    }

}

using SQL Query in X++

 If you need to execute sql query inside your X++ code then below example can help you. To pass value to SQL query we need to use strfmt() f...