Skip to main content

Using Having function In Microsoft Dynamics AX

Hi,

Today I came across a requirement to use Having function In DAX X++ query .

Here is my Requirement ,
Need to find All the Products added into retail Catalogue, if any of the product more than one time then we need to send a message with that Product name.

Below is the query I used to satisfy my requirement.

 
    CatalogProductCategory      catalogProductCategory;

    CatalogProduct              catalogProduct;

    EcoResProduct               ecoresProduct;

    Query                       query = new Query();

    QueryBuildDataSource            productCategoryQuery,CatalogProductQuery,productQuery;

    QueryBuildRange             catalogProductRange;

    Queryrun                    queryRunUniquness;

 

 

 

    productCategoryQuery = Query.addDataSource(tableNum(CatalogProductCategory));

 

    CatalogProductQuery = productCategoryQuery.addDataSource(tableNum(CatalogProduct));

    CatalogProductQuery.addSelectionField(fieldNum(CatalogProduct,RECID));

    CatalogProductQuery.addSelectionField(fieldNum(CatalogProduct,PRODUCT));

    CatalogProductQuery.addGroupByField(fieldnum(CatalogProduct,RecId));

    CatalogProductQuery.addGroupByField(fieldnum(CatalogProduct,PRODUCT));

    CatalogProductQuery.relations(true);

    catalogProductRange = CatalogProductQuery.addRange(fieldNum(CatalogProduct,Catalog));

    catalogProductRange.value(queryValue(catalogId));

    productQuery = CatalogProductQuery.addDataSource(tableNum(EcoResProduct));

    productQuery.addSelectionField(fieldNum(EcoResProduct,DISPLAYPRODUCTNUMBER));

     productQuery.addGroupByField(fieldnum(EcoResProduct,DISPLAYPRODUCTNUMBER));

    productQuery.relations(true);

 

    query.addHavingFilter(productCategoryQuery, fieldStr(CatalogProduct, RecId), AggregateFunction::Count).value('>1');

    //query.

    queryRunUniquness = new QueryRun(query);

 

    while(queryRunUniquness.next())

    {

        ret             = false;

        catalogProduct = queryRunUniquness.get(tableNum(CatalogProduct));

        EcoResProduct = queryRunUniquness.get(tableNum(EcoResProduct));

        this.populateValidationInfo(ETK_RetailValidationType::Product,catalogProduct.RecId,

         strFmt("Product : %1 exists in more than one node", EcoResProduct.DisplayProductNumber));

        //this.parmcatalogInternalOrg(5637144579);

    }

 

Comments

Post a Comment

Popular posts from this blog

Form Auto refresh in D365 fo

Here I will explain one of the requirements I came across to refresh from automatically without User interaction.  Note: Make sure we do have all the handlers to stop UIrefreshing after certain hours of execution, else it causes the system to slow down after a certain time of continuous usage of this UI in the below example to overcome this case we use to check session login time and stopping form to refresh after certain hours of execution. Recommended to use this on pages that hold less data or smaller data sets. - NoofSeconds .value() is an integer control on UI that allows the user to choose a value.( best to fix a value like 5 seconds or a Minimum value to avoid unnecessary executions) Create a new form method and create as of below. void refreshFormWithTimeout (AsyncTaskResult _result)     {         System.Exception ex;         try         {              if(!element.closed...
Post a Comment
Read more

Debugging Technics in X++ (AX)

Interrupting the code execution : When you have written some code that is causing an endless loop (or a very long execution) and you want to know what code is currently keeping the client busy. The easiest way is pressing [Ctrl] + [Break] on the keyboard. This will stop the code execution and show up a message box: Are you sure that you want to cancel this operation? If you hold down the [Shift] button on the keyboard while you click at the No button of the message box, the AX Debugger will be launched exactly at the line of code that was about to be executed. From here you can continue to step over / into line by line and try to identify the cause of your endless loop. If breakpoint is sometimes not hit? Sometimes it happens that a breakpoint is not hit although you are sure the respective code was executed  instead of putting a breakpoint at a line of code insert the breakpoint keyword. public void executeQuery() { breakpoint; super(); } How can I get the curr...
Post a Comment
Read more

Exporting xpo'ss throught X++

 Job used for build batch application to take backup of developement in terms of xpo . Following job illustrates how to export/backup all AOT Classes object. void ExportXpoExample() { TreeNode treeNode; FileIoPermission perm; #define.ExportFile(@”c:\AOTclasses.xpo”) #define.ExportMode(“w”) perm = new FileIoPermission(#ExportFile, #ExportMode); if (perm == null) { return; } perm.assert(); treeNode = TreeNode::findNode(@”\Classes”); if (treeNode != null) { // BP deviation documented. treeNode.treeNodeExport(#ExportFile); } CodeAccessPermission::revertAssert(); }
Post a Comment
Read more