RSS

Category Archives: Tables

Image

Using table method on Document Handling generation via COM

Hi Guys,

I just got a chance to work on creating document using word or excel template. in Dynamics AX we have a very good feature in document handling Create Word document via COM or Create Microsoft Office Excel worksheet via COM. Using these features we can create word or excel template, and create and attached the document to the record filling the template fields from the table record.

I don’t want to explain how to setup this type of document, you can read the blow link for that,

http://technet.microsoft.com/EN-US/library/aa548928.aspx

The problem arises when i try to use the the field which is not on the table or don’t have a direct relation with the table i have setup. For example, i have used the SMAServiceOrderTable and i have requirement to fill the “Payment term” on the template, which is on the ProjInvoiceTable. Which has a relation with SMAServiceOrderTable through ProjTable (SMAServiceOrderTable->ProjTabe->ProjInvoiceTable).

The solution i think for this problem is to customize Document handling feature to have the provision of using the method of the table instead of just the fields.

So the problem solved, I added a field on the “DocuField” table for methodName modify some classes. I have provided the link for the XPO for that.

XPO,

https://docs.google.com/file/d/0B7b9JMHh3V2eYnpaSldVNTg0TFE/edit?usp=sharing

I have modified the DocuField table so that method name of the table can be specified.

Then i make changes on the \Classes\DocuActionTrans\insertField  method, to get the value from method using DictTable class.
Thanks for Reading.

Advertisements
 

Tags: , , , , ,

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.
 

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

Exceptions Inside Transactions

When an exception is thrown inside a transaction, the transaction is automatically aborted (a ttsabort operation occurs). This applies both for exceptions thrown manually and for exceptions thrown by the system.

If an exception is thrown inside a ttsBegin/ttsCommit block, it will be caught by the first matching catchlist that is outside the transaction block. If there is a catch block within the ttsBegin/ttsCommit, it will be ignored

 

Example

Example  shows how an exception in a ttsBegin/ttsCommit block is caught by the first catchlist outside the block rather than by the catch within the ttsBegin/ttsCommit block. If you run this code, “outside tts” will be printed before an Infolog displays “Message”.

try

{

ttsbegin;

try

{

throw error(“Message”);

}

catch

{

print “not here”;

pause;

}

ttscommit;

}

catch

{

print “outside tts”;

pause;

}

 

Source: http://msdn.microsoft.com/en-us/library/aa893385(AX.10).aspx

 
2 Comments

Posted by on January 20, 2011 in AX, Basics, Dynamics, SQL, Tables, X++

 

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: , , ,

Working with Multiple Datasource on a single Form

Today we will discuss how we can display records from multiple datasources in a single form. Data sources on a form allow the form to display the data from the table specified on the data source. A table in a data source can be sorted and filtered by the user, which is an important feature of Microsoft Dynamics AX.

Forms can have multiple data sources, and each data source can be joined to another data source on the form using the defined relations between the tables. Relations or Dynalinks can also be added in the form code.

A form data source produces a query that can be manipulated in the same way as any other query in the system.

If you wish to display records from two tables on one form, you can limit the records in one of the tables to the related records in the other table. You can do this by specifying a join between the two tables. The join is specified on the JoinSource property of the second or joined table. Here you should enter the data source name of the main data source. You can also specify the type of join.

 
 

The following table describes the types of join available:

  • Passive: The query on the joined data source is only executed when the form is opened. A later change in the controlling data source does not change the view.
  • Delayed: The query on the joined data source is executed every time that the controlling data source is changed. The query execution is delayed to avoid the fetch of data, if the controlling data source is changed multiple times in a short time. This is the case when the user is scrolling through data on a grid.
  • Active: This option is similar to Delayed, except there is no delay before the query is executed.
  • InnerJoin: Selects records from the main table that have matching records in the joined table and vice versa. If the joined table does not have any records related to the main table record, the main table record is not displayed. Each match returns a result set of the main table record and joined table record joined together as one record. This is useful when wanting to display records from both tables in a grid.
  • OuterJoin: Selects records from the main table whether they have matching records in the joined table. Each match returns a result set of the main table record and joined table record joined together as one record. If there is no match, the fields from the joined table will be empty.
  • ExistsJoin: Selects a record from the main table only if there is a matching record in the joined table. As soon as a matching record is found, the main table record is returned. The record in the joined table is never retrieved.
  • NotExistsJoin: Select records from the main table that do not have a match in the joined table.

     
     

Using these two properties JoinSource and LinkType on the child Datasouce of the form you can display your desired result from multiple datasource. I hope that clears the use of these two properties to display record from multiple datasources.

 
1 Comment

Posted by on December 28, 2010 in AX, Dynamics, Forms, Tables, X++

 

Tags: , , ,

Read XML file in AX

This post will not take your lots of time, just giving you one example to make you understand how can we read XML file. This post will enable you to read a file that is in the same format as the example in the previous post. You can try on your own to create a Job that reads an XML file that uses attributes such as the second example in the

previous post.

 

static
void ReadXml(Args _args)
 

{

XmlDocument xmlDoc;

XmlElement xmlRoot;

XmlElement xmlField;

XmlElement xmlRecord;

XmlNodeList xmlRecordList;

XmlNodeList xmlFieldList;

CarTable carTable;

DictTable dTable = new DictTable(tablenum(CarTable));


int i, j, fieldId;

#CarsXmlTags


// Create an XmlDocument object to hold the


// contents of the xml-file

xmlDoc = new XmlDocument();


// Load the content of the xml-file


// into the XmlDocument object

xmlDoc.load(@”c:\temp\cars.xml”);


// Get the root node

xmlRoot = xmlDoc.getNamedElement(#CarRootNode);


// Get all child nodes (records)

xmlRecordList = xmlRoot.childNodes();


// Loop through the list of records


for (i=0; i<xmlRecordList.length(); i++)

{

carTable.clear();


// Get the current record from the


// record list

xmlRecord = xmlRecordList.item(i);


// Get all child nodes (fields)

xmlFieldList = xmlRecord.childNodes();


// Loop through the list of fields


for (j=0; j<xmlFieldList.length(); j++)

{


// Get the current field from the


// field list

xmlField = xmlFieldList.item(j);


// Set the matching field in the carTable


// to be equal to the inner text


// (the text between the tag and end tag).

carTable.(dTable.fieldName2Id(xmlField.name())) =

xmlField.innerText();

}


// Insert the record into the carTable

carTable.insert();

}

}


 
Leave a comment

Posted by on December 27, 2010 in AX, Basics, Dynamics, Tables, X++, XML

 

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: , , ,