How to add Image for a Product in ax 2012

static void insertimage(Args _args)
{
    DocuActionArchive               docuActionArchive;
    EcoResProductImageManagement    productImageManagement;
    EcoResProductImageThumbnail     ecoResProductImageThumbnail;
    DocuRef                         docuRef;
    DocuValue                       docuValue;
    EcoResProductImage              ecoResProductImage;
    InventTable                     inventTable;
// Specify the display product number

    InventTable = InventTable::find("M0011");
    ttsBegin;
    docuRef.TypeId     = "File";
    docuRef.RefTableId = inventTable.TableId;
    docuRef.RefRecId   = InventTable.RecId;
    docuRef.RefCompanyId = inventTable.dataAreaId;
    docuRef.ActualCompanyId = curext();
    docuRef.insert();
    docuActionArchive = DocuAction::newDocuRef(docuRef);
    docuActionArchive.add(docuRef,"C:\\Users\\rajendra.c\\Desktop\\Audi-A1.jpg");

    ecoResProductImage.RefRecId         = docuRef.RecId;
    ecoResProductImage.RefRecord        = docuRef.RefRecId;
    ecoResProductImage.ImageFormat      = 'jpg';
    ecoResProductImage.FileName         = "Audi-A1.jpg";
    ecoResProductImage.Usage            = EcoResProductImageUsage::External;
    ecoResProductImageThumbnail         = new EcoResProductImageThumbnail(false);
    ecoResProductImage.MediumSize       = ecoResProductImageThumbnail.generateThumbnail(204,204,docuRef);
    ecoResProductImage.ThumbnailSize    = ecoResProductImageThumbnail.generateThumbnail(48,48,docuRef);

    if (ecoResProductImage.MediumSize == connull())
    {
        info("@SYS301935");
    }
    if (ecoResProductImage.ThumbnailSize == connull())
    {
        info("@SYS301936");
    }
    ecoResProductImage.insert();
    ttsCommit;
}

Dynamic Queries in Ax 2012

                                                                                

Query

            The Query class embodies the structure of a query. Objects of this type are not used for fetching records from the database. Instead, use a QueryRun object that may be assigned a query object. The static behavior is defined by the Query class.

                       Queries contain one or more data sources that correspond to tables in the database. The data sources are specified by using QueryBuildDataSource objects.

The following example creates a query object that is used to create a QueryRun object.     

Query q = new Query (QueryStr(queryname));

    // Use the query to build a queryRun object.

QueryRun qr = new QueryRun (q);

     // Traverse some records.

 while (qr.next())

 {

        // ...

  }                               

QueryBuildDataSource

The QueryBuildDataSource class provides the building blocks that queries are made of.

Ex :

                                              QueryBuildDataSource ds;

                                              Query q = new Query();                       

                                              ds = q.addDataSource(TableNum(TableName));}

QueryFilter

QueryFilter class gives you a way to filter the result set of an outer join to obtain a different outcome than you can obtain from X++ SQL. The QueryFilter class applies the filters to the WHERE clause in standard SQL, instead of to the ON keyword of the JOIN clause. Filters that are specified on the WHERE clause are applied later in the process than filter on the ON keyword. The Application Object Server (AOS) sends standard SQL to the underlying database management system. Applying the filters on the WHERE clause can produce a result set that contains no records that are built from default values.

As an alternative way to filter an outer join query, you can use the QueryBuildRange class. It produces results that match what you get from X++ SQL with an OUTER JOIN.

static void Raj_Queryfilter(Args _args)

{

    Query                   query = new Query();

    QueryBuildDataSource    salesTableDS;

    QueryBuildDataSource    salesLineDS;

    QueryBuildRange         qbr;

    QueryFilter             qf;

    QueryRun                qr;

    SalesTable              salesTable;

    SalesLine               salesLine;

 

    salesTableDS    = query.addDataSource(tableNum(SalesTable));

    salesLineDS     = salesTableDS.addDataSource(tableNum(SalesLine));

   // salesLineDS.relations(true);

    salesLineDS.joinMode(JoinMode::OuterJoin);

    salesLineDS.addLink(fieldNum(SalesTable, SalesId), fieldNum(SalesLine, SalesId));

    //qbr = salesLineDS.addRange(fieldNum(SalesLine, SalesId));

    //qbr.value('000006');

    qf = query.addQueryFilter(salesLineDS,fieldStr(SalesLine, SalesId));

    qf.value('000006');

    qr = new QueryRun(query);

    while(qr.next())

    {

        salesTable= qr.get(tableNum(salesTable));

        salesLine = qr.get(tableNum(salesLine));

  

        info(strFmt("%1---%2",salesTable.SalesId,salesLine.SalesId));

    }

}

ü  Matched records will be displayed.

ü If Query Build range is applied

QueryBuildDynalink

Contains information regarding a relation to an external record. When the query is run, this information is converted to additional entries in the WHERE clause of the query SQL statement. Can only exist on the parent data source of a query. The function is used by forms, when two data sources are synchronized. Then the child data source will contain a dynalink or dynalinks to the parent data source. The function is used even if the two data sources are placed in two different forms but are still synchronized.

QueryBuildFieldList

                       Defines which fields are returned from the database. The default is that the field list is dynamic, which returns all fields from the data source table, map, or view. Each data source has only one QueryBuildFieldList object, which contains information on all selected fields. It’s possible to specify aggregate functions like SUM, COUNT, and AVG on the field list object.

QueryBuildLink

                         Specifies the relation between the two data sources in the join. Can only exist on a child data source.

ü The QueryBuildLink class enables for the creating, reading, updating, and deleting of X++ code.

QueryBuildStaticLink

                        QueryBuildStaticLink class provides the information about the static links that are defined on a QueryBuildDataSource class

QueryHavingFilter

                      In Sql, Having clause  mainly used whenever we are using Aggregate Functions that time Where clause will not be used,that time Having clause is used. Coming to Ax we are not having Having clause, in ax 2012 r3 they implemented QueryHavingFilter Class.

Scenario

The CUSTTABLE table has a field called CUSTGROUP, indicating the customer group the customer belongs to. We would like to get a list of all customer groups that have less than 4 customers in them.

Traditionally, in AX queries, we can group by the CUSTGROUP field, COUNT the RecIds. However, there was no way to filter on that counted RecId field. However, in SQL, the having statement gives you that ability:

SELECT CUSTGROUP, COUNT(*) FROM CUSTTABLE

        GROUP BY CUSTGROUP  HAVING COUNT(*) < 4

In Ax

static void Raj_Queryhavingfilter(Args _args)

{

    Query                   query;

    QueryBuildDataSource    datasource;

    QueryBuildRange         range;

    QueryHavingFilter       havingFilter;

    QueryRun                queryRun;

    int                     counter = 0, totalCounter = 0;

    CustTable               custTable;

    query = new Query();

    datasource = query.addDataSource(tableNum(CustTable));

    datasource.addSelectionField(fieldNum(CustTable, RecId),

            SelectionField::Count);

    datasource.orderMode(OrderMode::GroupBy);

    datasource.addGroupByField(fieldNum(CustTable, CustGroup));

    havingFilter = query.addHavingFilter(datasource, fieldStr(custTable, RecId),

            AggregateFunction::Count);

    havingFilter.value('< 4');

    queryRun = new QueryRun(query);

    while (queryRun.next())

    {

        custTable = queryRun.getNo(1);

        info(strFmt("Group %1: %2", custTable.CustGroup, custTable.RecId));

    }

}

Output

insert_recordset,update_recordset,delete_from in ax 2012

insert_recordset: 

                   copies data from one or more tables directly into one resulting destination table on a single server trip. 

•  Using insert_recordset is faster than using an array insert. However, array inserts are more flexible if you want to handle the data before you insert it.
• insert_recordset is a record set-based operator, which performs operations on multiple records at a time

Ex : ledgertranslistreport

helper class

insert_recordset processingStagingTable

        (

            Visibility,

            GeneralJournalAccountEntry,

            Txt,

            CurrencyCode,

            PostingType,

            TransactionCurrencyAmountDebit,

            AccountingCurrencyAmountDebit,

            ReportingCurrencyAmountDebit,

            IsCredit,

            GeneralJournalEntry,

            TransDate,

            JournalNumber,

            Voucher,

            AcknowledgementDate,

            AccountNum,

            AccountName,

            Dimension,

            MainAccount,

            GeneralJournalEntryDataArea)

        select showAcknowledgementDate,

               RecId,

               Text,

               TransactionCurrencyCode,

               PostingType,

               TransactionCurrencyAmount,

               AccountingCurrencyAmount,

               ReportingCurrencyAmount,

               IsCredit from generalJournalAccountEntry

        join RecId,

             AccountingDate,

             JournalNumber,

             SubledgerVoucher,

             AcknowledgementDate from generalJournalEntry

                where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry

                    && generalJournalEntry.AccountingDate >= _fromDate

                    && generalJournalEntry.AccountingDate <= _toDate

                    && generalJournalEntry.Ledger == Ledger::current()

        join MainAccount,

             Name,

             FullyQualifiedAccount,

             MainAccountRecId from mainAccountLedgerDimensionView

                where generalJournalAccountEntry.LedgerDimension == mainAccountLedgerDimensionView.LedgerDimensionId

        join ledger

                where ledger.RecId == generalJournalEntry.Ledger

        join DataArea from companyInfo

                where companyInfo.RecId == ledger.PrimaryForLegalEntity;

Syntax

                 The ListOfFields in the destination table must match the list of fields in the source tables. Data is transferred in the order that it appears in the list of fields. Fields in the destination table that are not present in the list of fields are assigned zero-values as in other areas in X++. System fields, including RecId, are assigned transparently by the kernel in the destination table.

insert_recordset  DestinationTable  (  ListOfFields  )

select  ListOfFields1  from  SourceTable  [ where  WhereClause  ]

[ join  ListOfFields2  from  JoinedSourceTable 

[ where  JoinedWhereClause  ]]

Ex : 

static void Raj_Insert_Recordset(Args _args)

{

    CustTable               custTable;  //Standard table

    Insert_Recordsettmp     table;      //myTable

    

    insert_recordset table(AccountNum) 

        select AccountNum from custTable;

        

}

update_recordset: 

Allows you to update multiple rows in a table on a single database trip.

ü  The update_recordset command allows the manipulation of many records in one operation. This command speeds up database processing because of fewer calls to the database. Review the update function using the update_recordset command.

Ex:

SalesTable salesTable;

update_recordset salesTable

setting salesName ="New Enterprises"

where salesTable.custAccount =="2001";

Note : You do not have to use the ttsbegin; or ttscommit; when you use the update_recordset command, however it is recommended to consider using a transaction when making other updates to the database at the same time.

update_recordSet processingStagingTable
                    setting HM_CustVendAccount = cust.AccountNum,
                            HM_CustVendName    = dirPartyTable.Name
                        join AccountNum from cust
                            where processingStagingTable.Voucher == cust.Voucher
                                && cust.TransType != ledgertransType::ExchAdjustment
                        join custTable
                            where custTable.AccountNum == cust.AccountNum
                        join Name from dirPartyTable
                            where dirPartyTable.RecId == custTable.Party;



updating customer name using recordset

update_recordSet customersVendors setting

        Name = dirParty.name

            join custVendTable

                where customersVendors.AccountNum == custVendTable.AccountNum

                    join dirParty

                        where custVendTable.Party == dirParty.RecId;

delete_from 

                      The delete_from command removes multiple records from the database at one time. Similar to the update_recordset command, delete_from consolidates many database calls into one operation, and increases database performance.

Ex:

 CustTable custTable;

delete_from custTable where custTable.Currency == "ABC";

Collection Classes in Ax 2012

Collection Classes

We cannot store objects in arrays (x++ class) or containers. The Microsoft Dynamics AX collection classes have been designed for storing objects. 

Below are collection classes: Set , Map , List , Array (Collection class)

A Set is used for the storage and retrieval of data from a collection in which the members are unique. The values of the members serve as the key according to which the data is automatically ordered. Thus, it differs from a List collection class where the members are placed into a specific position, and not ordered Automatically by their value.

static void Set(Args _args)

{

    Set setOne;

    Set setTwo;

    SetEnumerator enumerator;

    Int value;

    setOne = new Set(types::Integer);

    setOne.add(4);

    setOne.add(6);

    setOne.add(3);

   

    enumerator = setOne.getEnumerator();

    while (enumerator.moveNext())

    {

        value = enumerator.current();

        info(strFmt("%1",value));      

    }

}

Output :- 3

                 4

                 6

A List object contains members that are accessed sequentially. Lists are structures that can contain members of any X++ type. All the members in the same list must be of the same type.

static void List(Args _args)

{

    List integerList = new List(Types::Integer);

    ListEnumerator enumerator;

    // Add some elements to the list

    integerList.addEnd(1);

    integerList.addEnd(4);

    integerList.addEnd(3);

    // Set the enumerator

    enumerator = integerList.getEnumerator();

    // Go to beginning of enumerator

    enumerator.reset();

    //Go to the first element in the List

    while(enumerator.moveNext())

    {

        info(strfmt("%1", enumerator.current()));

    }

}

Output :- 1

                 4

                 3

A Map object associates one value (the key) with another value. Both the key and value can be of any valid X++ type, including objects. The types of the key and value are specified in the declaration of the map. The way in which maps are implemented means that access to the values is very fast.

static void Map(Args _args)

{

    Map mapTest;

    MapEnumerator enumerator;

  

    mapTest = new Map(Types::String, Types::Integer);

   

    mapTest.insert("One", 1);

    mapTest.insert("Two", 2);

   

    enumerator = mapTest.getEnumerator();

    while (enumerator.moveNext())

    {

        info(strfmt("Key - %1 , Value  - %2.",enumerator.currentKey(),enumerator.currentValue()));

    }

}

Output:- Key - One , Value  - 1.

                Key - Two , Value  - 2.

Array inserts, sometimes referred to as bulk inserts, are implemented in the kernel. They buffer a group of rows and insert them in a single trip to the SQL backend. This vastly reduces the number of trips, and speeds up inserts. You can use RecordSortedList or RecordInsertList to hold your rows until they are inserted. Both classes have an insertDatabase method that is used to insert the records into the database as efficiently as possible. However, the insertDatabase method does

RecordSortedList

                       

                         Use RecordSortedList when you want a subset of data from a particular table, and you want it sorted in an order that does not currently exist as an index.

• A RecordSortedList object holds records from a single table. The list has a unique key that is defined by the fields listed by using the sortOrder method. 
• Records are automatically sorted as they are inserted, they do not have to be inserted in sort sequence. 
• There is no limit to the size of a RecordSortedList object, but they are completely memory-based, so there are potential memory consumption problems. 
• RecordSortedList objects must be server-located before the insertDatabase method can be called. Otherwise, an exception is thrown. 
• Record level security (RLS) cannot be applied by the RecordSortedList class. RLS is applied by the RecordInsertList class).

Compared to temporary tables, RecordSortedList objects:

• are faster
• are not disk-based
• only have one index
• cannot be used in forms
• require a call between the client and server per (grouped) read

 Ex:

Student student;

RecordSortedList recordSortedList = new RecordSortedList(tablenum(Student));

recordSortedList .sortOrder(fieldname2id(tablenum(Student),’StudentId’));

student.clear();

student.StudentID=”123″;

student.FirstName=”DOM”;

student.LastName=”FED”;

recordSortedList.ins(student);

student.clear();

student.StudentID=”456″;

student.FirstName=”TOM”;

student.LastName=”GED”;

recordSortedList.ins(student);

 student.clear();

student.StudentID=”789″;

student.FirstName=”ROM”;

student.LastName=”TED”;

recordSortedList.ins(student);

recordSortedList.insertDatabase();

RecordInsertList: 

               The RecordInsertList class provides array insert capabilities in the kernel. This allows you to insert more than one record into the database at a time, which reduces communication between the application and the database.

               The array insert operation automatically falls back to classic record-by-record inserts when non-SQL based tables are used (for example, temporary tables), or when the insert method on the table is overridden (unless it is explicitly discarded).

Ex:

FiscalCalendar myTable;

 RecordInsertList insertList = new RecordInsertList(myTable.TableId, True);

  int i;

 for ( i = 1; i <=  100; i++ )

 {

        myTable.CalendarId = "F"+int2str(i);

        insertList.add(myTable);

 }

 insertList.insertDatabase();