Dynamics Ax Query Extended Range Value Expression

Dynamics Ax Query Extended Range Value Expression

By Rahul Sharma . on Monday, March 29, 2010

In this article we will see how to define extended query ranges using expressions. These expressions can be used in any query where you need to express a range that is more complex than is possible with the usual range value notations.

The rules for creating query range value expressions are:

• Enclose the whole expression in parentheses.
• Enclose all subexpressions in parentheses.
• Use the relational and logical operators available in X++.
• Only use field names from the range's data source.
• Use the dataSource.field notation for fields from other data sources in the query.
• Values must be constants in the expression, so any function or outside variable must be calculated before the expression is evaluated by the query. This is typically done by using thestrFmt function.

Let's do it with some examples:

OR clause on same field:
    Query q;
    QueryBuildDataSource qbd;
    QueryBuildRange qbr;
    q = new Query();
    qbd = q.addDataSource(TableNum(CustTable));
    qbr = qbd.addRange(FieldNum(CustTable, AccountNum));
    qbr.value('4005, 4006');

Result: SELECT * FROM CustTable WHERE ((AccountNum = N'4005' OR AccountNum = N'4006'))

or use this expression to achieve the same...

    qbr.value(strFmt('((AccountNum == "%1") || (AccountNum == "%2"))',
            QueryValue('4005'),
            QueryValue('4006')));

Result: SELECT * FROM CustTable WHERE ((((AccountNum == "4005") || (AccountNum == "4006"))))

OR clause on different fields: In this example we are using DataAreaId field to obtain range object but actual range is on AccountNum and Name. This means when you use range value expressions you can use any field to obtain range object and use it to insert your range in the query. The field you use to get range object is not included in the query.  I usually use DataAreaId to denote that this is a special range.

    qbr = qbd.addRange(FieldNum(CustTable, DataAreaId));

    qbr.value(strFmt('((%1 == "4000") || (%2 == "The Bulb"))',

            fieldStr(CustTable, AccountNum),

            fieldStr(CustTable, Name)));

Result: SELECT * FROM CustTable WHERE ((((AccountNum == "4000") || (Name == "The Bulb"))))

The above result can also be obtained with this below example. The only difference is we are using DataSource name as well. This will also give you some idea on how to use expressions when more than one DataSources are involved.

    qbr = qbd.addRange(FieldNum(CustTable, DataAreaId));

    qbr.value(strFmt('((%1.%2 == "4000") || (%1.%3 == "The Bulb"))',

            qbd.name(),

            fieldStr(CustTable, AccountNum),

            fieldStr(CustTable, Name)));

Result: SELECT * FROM CustTable WHERE ((((CustTable_1.AccountNum == "4000") || (CustTable_1.Name == "The Bulb"))))

Query range value expressions are evaluated only at run time, so there is no compile-time checking. If the expression cannot be understood, a modal box will appear at run time that states "Unable to parse the value."


Link:http://blog.rahulsharma.in/2010/03/query-extended-range-value-expression.html

About ALL Lookup Forms in ax 2012

http://www.slideshare.net/MAnasKhan/microsoft-dynamics-ax-lookups

X++ code for to update journal number's in ax 2009

static void JournalNumbersUpdate2009(Args _args)

{

    SysExcelApplication application;

    SysExcelWorkbooks workbooks;

    SysExcelWorkbook workbook;

    SysExcelWorksheets worksheets;

    SysExcelWorksheet worksheet;

    SysExcelCells cells;

    COMVariantType type;

    ledgerJournalid     oldjournalnum,newjournalnum;

    int     row;

    LedgerJournalTable  journaltable;

    LedgerJournalTrans  journaltrans;

    ledgertrans         ledgertrans;

    vendtrans           vendtrans;

    Filename filename;

    ;

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    //specify the file path that you want to read

    filename ="C:\\JounralNumbersUpdate.xlsx";

    try

    {

    workbooks.open(filename);

    }

    catch (Exception::Error)

    {

    throw error("File cannot be opened.");

    }

    workbook = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(1);

    cells = worksheet.cells();

    do

    {

        row++;

        oldjournalnum = cells.item(row, 1).value().bStr();

        newjournalnum = cells.item(row, 2).value().bStr();

        ttsbegin;

        while select forupdate journaltable

            where journaltable.JournalNum == oldjournalnum

        {

            journaltable.JournalNum = newjournalnum;

            journaltable.doUpdate();

        }

        while select forupdate journaltrans

            where journaltrans.JournalNum == oldjournalnum

        {

            journaltrans.JournalNum = newjournalnum;

            journaltrans.doUpdate();

        }

        while select forupdate ledgertrans

            where ledgertrans.JournalNum == oldjournalnum

        {

            ledgertrans.JournalNum = newjournalnum;

            ledgertrans.doUpdate();

        }

        while select forupdate vendtrans

            where vendtrans.JournalNum == oldjournalnum

        {

            vendtrans.JournalNum = newjournalnum;

            vendtrans.doUpdate();

        }

        ttscommit;

        info(oldjournalnum + '-' + newjournalnum);

        type = cells.item(row+1, 1).value().variantType();

    }

    while (type != COMVariantType::VT_EMPTY);

    application.quit();

    info("Done");

}

X++ code for calling Multiple selected records in ax 2012

First Create A form with SalesTable as datasource drag some fields to form grid,and create a new button a design level ,keep its Multiselect property as YES and write the clicked method as below



void clicked()
{
    int             recordsCount;
    SalesTable     SalesTable1 ;
    super();
 
    recordsCount = SalesTable_ds.recordsMarked().lastIndex();  // Total number of marked records.
    SalesTable1    = SalesTable_ds.getFirst(1);
 
    while (SalesTable1 )
    {
        info(SalesTable1 .CustAccount);
        SalesTable1 = SalesTable_ds.getNext();
    }
}

X++ code for Mark all Records using Checkbox in ax 2012

First Create CheckBox field and select property AutoDeclaration as Yes Overide Clicked method

 public void clicked()
{
    SK_CustomerAddress SK_CustomerAddress1;

    super();

    ttsBegin;
    while select forUpdate IsPrimary from SK_CustomerAddress1
    {
        if(Mark.value()==1)
        {
            SK_CustomerAddress1.IsPrimary = Noyes::Yes;
        }
        else
        {
            SK_CustomerAddress1.IsPrimary=Noyes::No;

        }
        SK_CustomerAddress1.doUpdate();
    info(strFmt("%1",SK_CustomerAddress1.IsPrimary));

    }
    ttsCommit;

    SK_CustomerAddress_ds.executeQuery();
    //SK_CustomerAddress_ds.refresh();
    //SK_CustomerAddress_ds.research();
}

Dynamic Query to pass Range b/w two Dates in ax 2012

static void DateRange2(Args _args)
{
    date            dateFrom    = 01\01\2012 ;
    date            dateTo      = 31\01\2012 ;
    CustInvoiceJour custInvoiceJour;
    Query           query = new Query(queryStr (CustInvoiceJour)); // Query name.
    QueryRun        qr;
    QueryBuildRange qbr;
    ;

    // Find the InvoiceDate range on AOT Query.
    qbr = query.dataSourceTable( tablenum (CustInvoiceJour))
            .findRange( fieldNum (CustInvoiceJour, InvoiceDate));

    // We can check if the query range does exist, if not, we can create it.
    if (!qbr)
    {
        qbr = query.dataSourceTable( tableNum (CustInvoiceJour))
            .addRange( fieldNum (CustInvoiceJour, InvoiceDate));
    }

    // Assigning query range value.
    qbr.value(SysQuery::range(dateFrom, dateTo));

    // We can also define an Order By through code.
    query.dataSourceTable( tableNum (CustInvoiceJour))
        .addOrderByField( fieldNum (CustInvoiceJour, OrderAccount));

    // Executing our query.
    qr = new QueryRun(query);

    // Looping through query results.
    while (qr.next())
    {
        // Assinging query results to table buffer.
        custInvoiceJour = qr.getNo( 1 );

        // Showing results.
        info( strFmt ('%1 - %2 - %3' , custInvoiceJour.InvoiceDate,
                                    custInvoiceJour.SalesId,
                                    custInvoiceJour.OrderAccount));
    }

}

How To: Sending Reports to file in ax 2012

Please cfollow below link :

file/https://blogs.msdn.microsoft.com/dynamicsaxbi/2012/03/02/how-to-sending-reports-to-file/

X++ code for to convert report to Pdf file format & also saves in Destination Folder( Ax 2009)

static void PrinttoPDF(Args _args)
{
  PurchFormLetter purchFormLetterp;
  PrintJobSettings printJobSettings;
  VendInvoiceJour  vendInvoiceJour;
  PrintFormat PrintFormat;
  Args args = new Args();
  #File

    purchFormLetterp = PurchFormLetter::construct(DocumentStatus::Invoice,false);
    printJobSettings = new PrintJobSettings();
    printJobSettings.setTarget(PrintMedium::File);
   // printJobSettings.preferredTarget(PrintMedium::File);
    printJobSettings.format(PrintFormat);
    printJobSettings.fileName( @'c:\TEMP\myfile2.pdf');
    printJobSettings.warnIfFileExists(false);

    purchFormLetterp.updatePrinterSettingsFormLetter(printJobSettings.packPrintJobSettings());
 
    select vendInvoiceJour where vendInvoiceJour.Purchid == 'PO/15-16/00421';
   // vendInvoiceJour.printJournal(purchFormLetterp);
    args.record(vendInvoiceJour);
    args.caller(purchFormLetterp);

   new MenuFunction(menuitemoutputstr(Purchinvoicecopy), MenuItemType::Output).run(args);


}

Dialog field with multiple choice

http://alexvoy.blogspot.in/2014/11/dialog-field-with-multiple-choice.html

X++ code for Lookup form using SysFieldGroupLookup class in ax 2012

As we all know that we have extensively used SysTableLookup in older versions of AX to get the custom lookups. This class is still available in AX 2012.

But, in AX 2012 a new class SysFieldGroupLookup has been introduced which will help to get the lookup from the field groups of the table. This is a very useful feature as most of the fields to be added to the lookup fields can be reduced and can be pulled from the Table >> field groups directly.

Let me explain with an example:

public void lookup()

{

    Query  query;

    QueryBuildDataSource qbds;

    Querybuildrange   qbr;

    SysTableLookup sysTableLookup;

    SysFieldGroupLookup  sysFieldGroupLookup;

   

    super();

    query = new Query();

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

    qbr = qbds.addRange(fieldNum(SalesLine,SalesId));

    qbr.value(SalesId.valueStr());

    sysFieldGroupLookup  =sysFieldGroupLookup::newParameters(tableNum(SalesLine),this);

    sysFieldGroupLookup.addLookupfield(fieldNum(SalesLine,ItemId));

    sysFieldGroupLookup.parmFieldGroupNameForLookup(literalStr(Autoreport));

  

    sysFieldGroupLookup.parmQuery(query);

    sysFieldGroupLookup.performFormLookup();

}

ReferralLink:
https://dynamicsaxgyan.wordpress.com/2012/04/04/sysfieldgrouplookup-class-in-ax-2012lookup-from-table-fieldgroupsx/

X++ code for Multiple Field Lookup to get all fields of a query in ax 2012

First create a table
Create a query with above table
Create  a Form using above Query as DataSource

Create a new String Edit field in Design and name it as Lookup ,its property should be auto declaration yes

public void init()

{

    SysLookupMultiSelectCtrl sysLookupMultiSelect;

    super();

    sysLookupMultiSelect = SysLookupMultiSelectCtrl::construct(element,Lookup,queryStr(parenchild));

}

Note: here parenchild is query name

Import Data from Excel Using(RunBase Class) Dialog (X++)

Please go with below link:

http://kunaldynamicsax.blogspot.in/2014/09/import-data-from-excel-using-dialog-x.html

X++ Code to create Lookup for DialogField in ax 2012 r3

class Dialoglookup

{

    Dialog       dialog;

    DialogField  dlgworkerid,dlgpurchase,dlgdate;

}

Public dialog dialog()

{

    FormStringControl control,controlname;

    ;

    dialog = new Dialog();

    dlgworkerid = dialog.addField(extendedTypeStr(HcmPersonnelNumberId));

    dlgpurchase = dialog.addfield(extendedTypeStr(UserId));

    dlgpurchase.value(curUserId());

    control = dlgworkerid.control();

    dlgdate  = dialog.addField(extendedTypeStr(TransDate));

    //controlname =dlgpurchase.control();

    control.registerOverrideMethod(methodStr(FormStringControl,lookup),methodStr(Dialoglookup,Worker_LookUp),this);

    //controlname.registerOverrideMethod(methodStr(FormStringControl,lookup),methodStr(Dialoglookup,name_lookup),this);

  return dialog;

}

public void Worker_LookUp(FormStringControl _control)

{

    Query                 query;

    QueryBuildDataSource  qbds;

    SysTableLookup sysTableLookup;

    ;

    query = new Query();

    query.addDataSource(tableNum(HcmWorker));

    sysTableLookup = SysTableLookup::newParameters(tablenum(HcmWorker),_control);

    sysTableLookup.addLookupfield(fieldNum(HcmWorker,PersonnelNumber),true);

    sysTableLookup.addLookupMethod(tableMethodStr(HcmWorker,name));

    sysTableLookup.parmQuery();

    sysTableLookup.performFormLookup();

}

How to use Event Handler in Microsoft Dynamics AX 2012

Click below link

http://daxtechies.blogspot.in/2013/04/how-to-use-event-handler-in-microsoft.html

X++ Code for Creating New Customer and also update Address of the customer

X++ Code for Creating New Customer and also update Address of the customer

static void SK_CreateCustomerandupdateCustomerAddres(Args _args)

{

        CustTable                          custTable;

        AccountNum                      accountNum = 'ABC-123';

        CustGroupId                     custGroupId = '10';

        Name                            name = 'ABC';

        DirParty                        dirParty;

        DirPartyPostalAddressView       dirPartyPostalAddressView;

        custTable    = CustTable::find(accountNum);

        if(!custTable)    // if customer not exits

           {

                ttsBegin;

                custTable.clear();

                custTable.initValue();

                custTable.AccountNum = accountNum;

                custTable.CustGroup = custGroupId;

                custTable.insert(DirPartyType::Organization, name);

                dirParty = DirParty::constructFromCommon(custTable);

                dirPartyPostalAddressView.LocationName = 'ABC-AUTO';

                dirPartyPostalAddressView.City = 'London';

                dirPartyPostalAddressView.Street = 'Dover Street';

                dirPartyPostalAddressView.StreetNumber = '123';

                dirPartyPostalAddressView.CountryRegionId = 'GBR';

                dirParty.createOrUpdatePostalAddress(dirPartyPostalAddressView);

                ttsCommit;

           }

    else                    // if customer exits updates the address of the customer

        {

           ttsBegin;

          dirParty = DirParty::constructFromCommon(custTable);

          dirPartyPostalAddressView.LocationName = 'ABC-AUTO';

          dirPartyPostalAddressView.City = 'Germany';

          dirPartyPostalAddressView.Street = 'Germany Street';

          dirPartyPostalAddressView.StreetNumber = '123';

          dirPartyPostalAddressView.CountryRegionId = 'GBR';

          dirPartyPostalAddressView.IsPrimary=1;

          dirParty.createOrUpdatePostalAddress(dirPartyPostalAddressView);

           ttsCommit;

        }

}

Referred link:

http://dynamics-ax-chronicles.blogspot.in/2011/07/in-ax-2012-gab-global-address-book-has.html

How to get Popup for Custom Table lin ax 2012

Create Two tables One is  Parent and One is Child as below

How to get Popup for a Particular Field of table based on Related table in ax 2012

Create Two tables One Parent and One  Child as below Images

Create  a foreign-key relation in child table as below

Now Go to Parent table set auto Identification Name,id  and put that both as TitleField1, TitleField2 as Name,id respectively

Now Go to Parent table set auto Identification Name,id  and put that both as TitleField1, TitleField2 as Name,id respectively like above

Now open Child Table sk_child  it shows like below now here the below field shows an popup

X++ code from inserting image for Multiple items Using Excel

static void CopyOfSK_insertdatathroughExcel(Args _args)

{

    SysExcelApplication application;

    SysExcelWorkbooks workbooks;

    SysExcelWorkbook workbook;

    SysExcelWorksheets worksheets;

    SysExcelWorksheet worksheet;

    SysExcelCells cells;

    EcoResProductImageThumbnail     ecoResProductImageThumbnail;

    COMVariantType type;

    Name name;

    FileName filename;

    Sk_Exceltable  sk_Exceltable;

    BinData  bindata   = new BinData();

    container      imagecontainer;

    InventTable     inventTable;

    EcoResProduct   ecoResProduct;

    DocuRef         docuref;

    DocuOpenFile    docuOpenFile;

    EcoResProductImage ecoResProductImage;

    DocuActionArchive  docuActionArchive;

    int row;

    str Name1;

    str path;

    str filetype;

    str imagename,imagetype;

    int i;

    FileIoPermission perm;

    DocuValue docuvalue;

    #define.FileMode('W')

    ;

//specify the file path that you want to read

filename = "C:\\Users\\shivakumar.p\\Desktop\\Excel.xlsx"; //path of excel

    perm = new FileIOPermission(filename, #FileMode);

    perm.assert();

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

        throw error("File cannot be opened.");

    }

    workbook = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(1);

    cells = worksheet.cells();

    //progress.setAnimation(#AviTransfer);

    try

    {

      ttsbegin;

        do

        {

            row++;

            if (row >= 1)

            //Name1 = any2int(cells.item(row, 1).value().toString());

            Name1     = cells.item(row,1).value().bStr();  // Itemid

            path      = cells.item(row,2).value().bStr(); //path

            filetype  = cells.item(row,3).value().bStr(); //FileType

            imagename = cells.item(row,4).value().bStr(); //Imagename

            imagetype = cells.item(row,5).value().bStr(); //imageformat

            ecoResProduct = EcoResProduct::findByDisplayProductNumber(Name1);

            inventTable   = InventTable::find(Name1);

            if(ecoResProduct)

            {

                if(inventTable)

                {

                    //if(WinAPI::pathExists(path))

                    //{

                        bindata.loadFile(path);

                        imagecontainer = bindata.getData(); //loads the image to container from path

                   // }

                    ttsBegin;

                    docuref.TypeId          = filetype;

                    docuref.RefTableId      = ecoResProduct.TableId;

                    docuref.RefRecId        = ecoResProduct.RecId;

                    docuref.RefCompanyId    = ecoResProduct.dataAreaId;

                    docuref.ActualCompanyId = curext();

                    docuref.insert();

                    docuActionArchive = DocuAction::newDocuRef(docuRef);

                    docuActionArchive.add(docuRef,path);

                    ecoResProductImage.RefRecId    = docuref.RecId;

                    ecoResProductImage.RefRecord   = docuref.RefRecId;

                    ecoResProductImage.ImageFormat = imagetype;

                    ecoResProductImage.FileName    = imagename;

                    ecoResProductImage.Usage       = Ecoresproductimageusage::External;

                    ecoResProductImageThumbnail    = new EcoResProductImageThumbnail(false);

                    ecoResProductImage.MediumSize  = ecoResProductImageThumbnail.generateThumbnail(204,204,docuref);

                    ecoResProductImage.ThumbnailSize    = ecoResProductImageThumbnail.generateThumbnail(48,48,docuRef);

                  

                    ecoResProductImage.insert();

                    ttsCommit;

                }

                else

                {

                    checkFailed("Product is not released ");

                }

            }

            type = cells.item(row+1, 1).value().variantType();

        }

        while (type != COMVariantType::VT_EMPTY);

        ttscommit;

    }

    catch(Exception::Error)

    {

        workbooks.close();

        CodeAccessPermission::revertAssert();

        application.quit();

        ttsabort;

    }

    workbooks.close();

    CodeAccessPermission::revertAssert();

    application.quit();

}