How to Filter Values by using Dynamic Queries In Ax 2012 X++

Hi guys, Happy Friendship day to all.

To day we see how to filter values using Dynamic Queries.

I Take example like Filtering Purchase Orders of vendor Account. For that I gave VendAccount Number And Status. Depending up on my input values it filter the data from PurchTable And it insert Data in my customized Temporary Table.

Simple list Form:

        

Write Below code Button Clicked method:

void clicked()

{

    Query query = new Query();

    QueryBuildDataSource qbds;

    QueryBuildRange qbr,Qbr1;

    QueryRun qr;

    PurchTable purchTable;

    if (StringEdit.valueStr() )//Sting field name(Auto Declaration --YES) 

    {

        qbds=query.addDataSource(tableNum(purchTable));

        qbr = qbds.addrange(fieldnum(purchTable,OrderAccount));

        qbr.value(StringEdit.valueStr());

        if (ComboBox.valueStr())//Sting field name

        {

            qbr1 = qbds.addrange(fieldnum(purchTable, PurchStatus));

            qbr1.value(ComboBox.valueStr());

        }

    }

    else

    {

        qbds=query.addDataSource(tableNum(purchTable));

        qbr1 = qbds.addrange(fieldnum(purchTable, PurchStatus));

        qbr1.value(ComboBox.valueStr());

    }

    qr = new QueryRun(query);

    while (qr.next())

    {

        purchTable = qr.get(tablenum(PurchTable));

        DAXPurchTable.PurchaseOrder = purchTable.PurchId;

        DAXPurchTable.VendorAccount= purchTable.OrderAccount;

        DAXPurchTable.PurchStatus = purchTable.PurchStatus;

        DAXPurchTable.Insert();

    }

    DAXPurchTable_ds.executeQuery();

}

If we have apply any dates Like from date and To date:

        qbr2 = qbds.addrange (fieldnum(PurchTable,DeliveryDate));

        qbr2.value(SysQuery::range(DateEdit.datevalue(),DateEdit1.datevalue()));

If we want to apply Any EnumType values:

         qbr3 = qbds.addrange (fieldnum(PurchTable,DocumentStatus));

         qbr3 .value(SysQuery::value(DocumentStatus::Invoice));

Output:

Keep Daxing !!

How to make Financial dimension as mandatory in purchTable form in ax 2012 Using X++

Hi guys, To day we see how to make financial dimensions as mandatory in purch table form in ax 2012 Using X++.

Just check this DimensionDefaultingControllerBase Class, addEditControls method.

                    if (dimLinkMarkFieldOptionalEventArgs.parmCancel())

                    {

                            valueStringControl.mandatory(true);//This is set to mandatory

                     }

If we have any requirement To set mandatory Just write code here.But this is not the best way.For best way . . .  

Just follow the below steps:

1. Create Class in AOT node and set name as PurchEventHandler.

2.Right Click on Class and select New, Click on Pre- or-Post EventHandler.

3.Write below method on Class.

public static void purchValidateWrite(XppPrePostArgs _args)

{

    DimensionAttribute                  dimAttr;

    DimensionAttributeValue             dimAttrValue;

    DimensionAttributeValueSetItem      dimAttrValueSetItem;

    PurchLine                           purchLoc,purchLineLoc;

    RefRecId                            defaultDimension;

    VendGroup                           vendGroup;

    boolean                             ret;

    ;

    #define.CostCenter('CostCenter')

    purchLoc   = _args.getThis();

    ret         = _args.getReturnValue();

    defaultDimension    =   purchLoc.DefaultDimension;

    dimAttr             =   DimensionAttribute::findByName(#CostCenter);

    select firstonly RecId, DisplayValue from dimAttrValueSetItem

            where dimAttrValueSetItem.DimensionAttributeValueSet == defaultDimension

     join dimAttrValue

             where dimAttrValue.RecId == dimAttrValueSetItem.DimensionAttributeValue &&

              dimAttrValue.DimensionAttribute == dimAttr.RecId &&

              dimAttrValue.IsDeleted == false;

        if (!dimAttrValueSetItem.DisplayValue)

        {

            ret     = checkFailed("CostCenter must be specified.");

        }

//   }

    _args.setReturnValue(ret);

}

4.Select PurchLine in AOT and select ValidateWrite_server and Right click on that method.Select New Event Handler Subscription.

5.Give properties Name,CalledWhen::Post,ClassName,Method.

    

Create New purch Order and Enter line Details And save It throws the error.

Process Done.

                                

For Sales Table Form

Same Like SalesTable From

1.Create New class Name it.

2.Right Click on Class and select New, Click on Pre- or-Post EventHandler.

3.Write below code in method.

public static void salesLineValidateWrite(XppPrePostArgs _args)

{

    DimensionAttribute                  dimAttr;

    DimensionAttributeValue             dimAttrValue;

    DimensionAttributeValueSetItem      dimAttrValueSetItem;

    SalesLine                           salesLoc,salesLineLoc;

    RefRecId                            defaultDimension;

    CustGroup                           custGroup;

    boolean                             ret;

    ;

    #define.CostCenter('CostCenter')

    salesLoc   = _args.getThis();

    ret         = _args.getReturnValue();

     select DaxCustGroupMandatory from custGroup

        join salesLineLoc

            where salesLineLoc.CustGroup==custGroup.CustGroup &&

            salesLineLoc.CustAccount == salesLoc.CustAccount;

    if (custGroup.DaxCustGroupMandatory==NoYes::Yes)

    {

        defaultDimension    =   salesLoc.DefaultDimension;

        dimAttr             =   DimensionAttribute::findByName(#CostCenter);

        select firstonly RecId, DisplayValue from dimAttrValueSetItem

                    where dimAttrValueSetItem.DimensionAttributeValueSet == defaultDimension

                join dimAttrValue

                    where dimAttrValue.RecId == dimAttrValueSetItem.DimensionAttributeValue &&

                    dimAttrValue.DimensionAttribute == dimAttr.RecId       &&

                    dimAttrValue.IsDeleted == false;

        if (!dimAttrValueSetItem.DisplayValue)

        {

            ret     = checkFailed("CostCenter must be specified.");

        }

    }

    _args.setReturnValue(ret);

}

4. Select SalesLine in AOT and select ValidateWrite_server and Right click on that method.Select New Event Handler Subscription.

5.Give properties Name,CalledWhen::Post,ClassName,Method.

Keep Daxing !!