RSS

Tag Archives: Table

Dynamics AX2012 Fact boxes

Fact boxes in Dynamics AX2012 is another very pretty feature. Before writing anything see the below screenshot of the Customer Listpage.

The area in the red box are the fact boxes, which are new in AX2012. These fact boxes provides extra information of the selected record for example the related information fact box shows the quotation, invoices of the customer selected. By this feature user don’t need to move to other form to get the information. User can get the summarized view of extra information in the fact boxes.

On AOT, there is a new node called “Part”, which holds the fact box objects. The available type of fact boxes are,

  • Form part
  • Info part
  • Cue part
I will provide more detail about how to develop fact boxes and place it to any form. Fact boxes can be placed on on listpage or forms.
Wait for my new post to know more about fact boxes. Keep reading my posts.
Advertisements
 

Tags: , , , , , , , , , , ,

RecordSortedList and RecordInsertList

For those who are wondering, Is there any way in AX to reduce the client server call when inserting a bunch of records into the database. With no surprise Yes!, AX provides a way to do that. Two Collection classes are there in AX RecordSortedList and RecordInsertList for this purpose.

The RecordSortedList class inserts multiple records in a single database trip, and can hold a subset of data from a table, in a particular sort order that does not exist as an index. There are built-in methods in RecordSortedList which can be used to perform the insertion to database. You must provide the sort order in order to perform the insertion properly. The following code sample demonstrates how to insert multiple Student records in the same database trip, using RecordSortedList:

 
 

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();

 
 

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.

RecordInsertList is similar to RecordSortedList, but it has built-in client/server support (it automatically packs data from one tier to another when needed), and it lacks the sort order features available in RecordSortedList.

 
 

The following is an example using RecordInsertList

Student student;

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

int i;

 
 

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

{

student.StudentrId = “F”+int2str(i);

insertList.add(student);

}

insertList.insertDatabase();

 
 

Let me know if you guys have any queries. I will appreciate your comments and feedback.

 
2 Comments

Posted by on December 30, 2010 in AX, Basics, Dynamics, Framework, Tables, X++

 

Tags: , , ,

Insert_recordset, Update_recordset, and delete_from single transaction command.

In AX, you can manipulate a set of data by sending only one command to the database. This way of manipulating data improves performance a lot when trying to manipulate large sets of records. The commands for manipulations are insert_recordset, update_recordset, and delete_from. With these commands, we can manipulate many records within one database transaction, which is a lot more efficient than using the insert, update, or delete methods.

Lets discuss about these commands one by one.

 

  • Insert_recordset

    A very efficient way of inserting a chunk of data is to use the insert_recordset operator, as compared to using the insert() method. The insert_recordset operator can be used in two different ways; to either copy data from one or more tables to another, or simply to add a chunk of data into a table in one database operation.

    The first example will show how to insert a chunk of data into a table in one database operation. To do this, we simply use two different table variables for the same table and set one of them to act as a temporary table. This means that its content is not stored in the database, but simply held in memory on the tier where the variable was instantiated.

    static void Insert_RecordsetInsert(Args _args)

    {

    CarTable carTable;

    CarTable carTableTmp;

     

    /* Set the carTableTmp variable to be a temporary table.

    This means that its contents are only store in memory

    not in the database.

    */

    carTableTmp.setTmp();

    // Insert 3 records into the temporary table.

    carTableTmp.CarId = “200”;

    carTableTmp.CarBrand = “MG”;

    carTableTmp.insert();

    carTableTmp.CarId = “300”;

    carTableTmp.CarBrand = “SAAB”;

    carTableTmp.insert();

    carTableTmp.CarId = “400”;

    carTableTmp.CarBrand = “Ferrari”;

    carTableTmp.insert();

    /* Copy the contents from the fields carId and carBrand

    in the temporary table to the corresponding fields in

    the table variable called carTable and insert the chunk

    in one database operation.

    */

    Insert_Recordset carTable (carId, carBrand)

    select carId, carBrand from carTableTmp;

    }

    The other, and perhaps more common way of using the insert_recordset operator, is to copy values from one or more tables into new records in another table. A very simple example on how to do this can be to create a record in the InventColor table for all records in the InventTable.

    static void Insert_RecordsetCopy(Args _args)

    {

    InventColor inventColor;

    InventTable inventTable;

    This material is copyright and is licensed for the sole use by ALESSANDRO CAROLLO on 18th December

    Chapter 6

    [ 169 ]

    InventColorId defaultColor = “B”;

    Name defaultColorName = “Blue”;

    ;

    insert_recordset inventColor (ItemId, InventColorId, Name)

    select itemId, defaultColor, defaultColorName

    from inventTable;

    }

    The field list inside the parentheses points to fields in the InventColor table.

    The fields in the selected or joined tables are used to fill values into the fields in

    the field list.

     

  • Update_recordset

    The update_recordset operator can be used to update a chunk of records in a table in one database operation. As with the insert_recordset operator the update_recordset is very efficient because it only needs to call an update in the database once.

    The syntax for the update_recordset operator can be seen in the next example:

    static void Update_RecordsetExmple(Args _args)

    {

    CarTable carTable;

    ;

    info(“BEFORE UPDATE”);

    while select carTable

    where carTable.ModelYear == 2007

    {

    info(strfmt(“CarId %1 has run %2 miles”,

    carTable.CarId, carTable.Mileage));

    }

    update_recordset carTable setting Mileage = carTable.Mileage + 1000

    where carTable.ModelYear == 2007;

    info(“AFTER UPDATE”);

    while select carTable

    where carTable.ModelYear == 2007

    {

    info(strfmt(“CarId %1 has now run %2 miles”,

    carTable.CarId, carTable.Mileage));

    }

    }

     

    When this Job is executed it will print the following messages to the Infolog:

    Notice that no error was thrown even though the Job didn’t use selectforupdate, ttsbegin, and ttscommit statements in this example. The selectforupdate is implicit when using the update_recordset, and the ttsbegin and ttscommit are not necessary when all the updates are done in one database operation. However, if you were to write several update_recordset statements in a row, or do other checks that should make the update fail, you could use ttsbegin and ttscommit and force a ttsabort if the checks fail.

     

  • Delete_from

    As with the insert_recordset and update_recordset operators, there is also an option for deleting a chunk of records. This operator is called delete_from and is used as the next example shows:

     

    static void Delete_FromExample(Args _args)

    {

    CarTable carTable;

     

    delete_from carTable

    where carTable.Mileage == 0;

    }

     

    Thanks for reading the post, any comments or questions are welcomed. Keep visiting the blog.

 
Leave a comment

Posted by on November 8, 2010 in AX, Dynamics, SQL, Tables, X++

 

Tags: , , ,

Fetching Records Using Query Object

In my previous post “Playing with data”, I have made you guys learn how to retrieve data using the select query which you can write anywhere. You just need to declare the table buffer for that. However, there are some cases where it is not recommended to use the select statement to retrieve data for example it is not recommended to use select statement at Form level methods. So, what could be used to retrieve data? The answer is Using Query Object. Normally queries are stored in AOT (see Queries Node in AOT), but they can also

be created from code dynamically. The Query object is used when a user interaction is required, like user wants to add range or sort the data.

In order to create query from code, the objects that needs to be created are,

  • Query:

    The query class object defines the structure of the 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.

     

  • QueryBuildDataSource :

    The QueryBuildDataSource class provides the building blocks that queries are made of. It holds the tables that needed to be added in the query. The hierarchy of the datasources of query are defined in this object. Like if query has a join with another table, so child table is added using the QueryBuildDatasource object of parent table.

     

  • QueryRun:

    The QueryRun class traverses tables in the database while fetching records that satisfy constraints given by the user, and helps gather such constraints from user input.

     

     

The object to add the range to the query is,

  • QueryBuildRange:

    The QueryBuildRange class represents the ranges that define which records should be fetched from the data source in which the QueryBuildRange is associated. The value property can be used to set the string that defines the range.

     

 

Now, lets have a code example how to use the objects of these classes. Create a new job and add the following code.

 

static void CustTableSales(Args _args)

{

Query query;

QueryBuildDataSource qbds1;

QueryBuildDataSource qbds2;

QueryBuildRange qbr1;

QueryBuildRange qbr2;

QueryRun queryRun;

CustTable custTable;

 

query = new Query();

qbds1 = query.addDataSource(tablenum(CustTable));

qbr1 = qbds1.addRange(fieldnum(CustTable, Blocked));

qbr1.value(queryvalue(CustVendorBlocked::No));

qbr2 = qbds1.addRange(fieldnum(CustTable, CustGroup));

qbr2.value(queryvalue(’10’));

qbds2 = qbds1.addDataSource(tablenum(SalesTable));

qbds2.joinMode(JoinMode::ExistsJoin);

qbds2.addLink(fieldnum(CustTable, AccountNum), fieldnum(SalesTable, CustAccount));

 

queryRun = new QueryRun(query);

while (queryRun.next())

{

custTable = queryRun.get(tablenum(CustTable));

info(strfmt(“%1 – %2”,custTable.Name,

custTable.AccountNum));

}

}

 

 

This job runs the query that selects all active customers who belong to group 10 and have at least one sales order. To set the range value a global function “queryValue(anyType _anyType)” is used which takes a value of any type. There is another static function in the SysQuery class, e.g SysQuery::value().

If the addRange method is called for same field then an OR is added to where clause and if the addRange is called for different field the AND is added to the where clause in the query.

To add OR in the where clause in different field, you can make the expression using the strfmt() global function as,

 

qbr2.value(strfmt(‘((%1 = “%2”) || (%3 = “%4”))’,

fieldstr(CustTable,CustGroup),

queryvalue(’10’),

fieldstr(CustTable,Currency),

queryvalue(‘EUR’)));

 

Yes, you can make the expression as you like using the strfmt(), you can use greater than, less than in the query as,

qbr3.value(strFmt(‘(ModifiedDate > %1)’, Date2StrXpp(01012000)));

 

One thing which should be noted here is the use of Date2StrXpp() global function. It is really a need for a range on date field to use this function. Without this function the query will not be executed correctly and wrong results will be displayed. So, whenever there is a need of applying range on Date field use Date2StrXpp() function.

 

There are a lot to discuss about query, which cannot be covered in a single post. And it will be boring if I mention everything about query in single post. So wait for new post to explore more about Query Object.

Thanks guys, comments and suggestion will be appreciated.

 
Leave a comment

Posted by on July 30, 2010 in AX, Dynamics, SQL, Tables, X++

 

Tags: , , , , ,

Creating simple table and data entry form

We will be experiencing the simplest example of creating a simple data entry form. And you will know how easy is to work on AX. I will let you guys know how to create table, what properties need to be set, what methods need to be created, how to define indexes, etc. After creating the table, I will create a simple form with the datasource of the table that we will create. So, lets start by creating a table having data of “Students”. To create a table open the AOT in the development environment of DAX by clicking the AOT button or by short cut key Ctl+D.

Expand the “Data Dictionary” node of the AOT, you will find the “Tables” node. Right click Tables node and click “New table”.


 
 

Now, name the table as “Student”. To add fields to the table, expand the “Student” table, right click and add fields. I am adding these fields.


The properties of fields that must be defined are, “ExtendedDataType” (for FirstName I am defining “Name” as EDT), “Label” & “HelpText” (if different from EDT selected). And The optional properties that’s depends upon requirement are “AllowEdit”, “AllowEditOnCreate”, “Visible” etc. I think the name of the property explains its use, so I am not going into details of defining the use of the property.

 
 

In AX, every table has field RecId which is the Surrogate Key which is being used as a Primary key of the table. If you see the property of the table you will find the Primary Key property and this property is set as “SurrogateKey”. You can define alternate key by creating an Index. To do this, right click the Index node of the “Student” table and add the index. Name the index and set the “AllowDuplicate” property to “No”. Right click this index and add field(s) that you want to make as alternate key. In my case I am adding only the “RegNo” on the index. You can define more indexes as well.

Other properties that need to be set of the tables are “Label”, “TitleField1”, “TitleField2”, “ConfigurationKey”, “TableGroup”. If you want to know the detail of these properties, ask me question, I will answer it.

There is also a “method” node on the table, you can define methods at table level as well. The standard methods that must be there in every tables are “find”, “exists”, “checkExists”. You can find the example of these methods in any of the table present in the DAX. There are also override-able methods of tables, like “validateWrite”, “validateField”, “insert”, “update” and so on. Right click the method node and see override method>.

So, you have created a simple table, now you can use this table as a datasource of a form. To create a form, go to AOT>Forms node and right click to add a new form. Name the new form as “Student”. Expand the form you will find these nodes.


 
 

To add the datasource you can do by dragging the table to the “Data sources “node of the form or by right click and add the table. I am dragging “Student” table on the datasource of the form. Now, the datasource has been added, its time to design the form. Expand the Designs node, and right click the design to add controls, you will have following list of controls.


 
 

In AX 6.0 we have a control “Action pane”, you can use the Action pane as an “Action pane strip” by setting the style property of “Action pane” as “strip”. On that you can add buttons and its looks like the following,


 
 

The controls I am adding in our case are, Action pane strip with command button “New” and “Delete Record”, Two tab pages “Overview” which contains grid and “General” which contains fields.

On the grid you can drag fields from datasource node of the form. Same for other controls, you can drag fields from datasource to any group control or tab page.

After all this you can open the form, and you can add or remove records. The form will look like this.

 
 


 

Thanks guys for reading the post. Any suggestion or question will be highly appreciated.

 
1 Comment

Posted by on April 27, 2010 in AX, Dynamics, Forms, Tables, Uncategorized

 

Tags: , , , ,