WALKTHROUGH - Creating Fact Boxes in AX 2012 - PART III

In my last post (http://axwonders.blogspot.com/2011/12/walkthrough-creating-fact-boxes-in-ax_28.html ), we created 3 EDT’s, 4 tables and created the relationships we needed to hold the Auto Rental data.  In this short post we are going to talk about primary indexes, and then we will create one for the Eduardo_CustCarTable.  In addition, we are going to learn a little bit more about Form’s Parts in AX 2012 before jumping into creating the user interface forms for the Auto Rental data.

Indexes

For detailed information about indexes you can read the following: http://axwonders.blogspot.com/2011/11/indexes-in-microsoft-dynamics-ax.html

In a nutshell, we can say that a primary key is one or more columns that uniquely identify one record in a table from all the other records.  A primary index is a unique index for a table that defines the primary key for that table. To set an index as a unique index, set the index property Allow Duplicates to No. This is the same idea as creating a MS SQL table primary key (Check the following post for more info about this http://blog.sqlauthority.com/2008/09/08/sql-server-%E2%80%93-2008-creating-primary-key-foreign-key-and-default-constraint/).

NOTE: When a primary index is not specified, AX uses a Surrogate Key as the primary index. This key is the RecId field and, if the table is saved per company, the DataAreaId. The surrogate key is used on many relations between tables.

To set the primary index on a table, open the property sheet for the table. The PrimaryIndex property allows any unique index with a key that is mandatory and cannot be edited.

1.       In the AOT, locate the Eduardo_CustCarTable table.

2.       In the property sheet for the table, set the PrimaryIndex property on the table to RentalIdx

Parts

So, what are Parts? [From Microsoft - http://msdn.microsoft.com/en-us/library/gg844683.aspx ]A Part is a specialized type of control you use to retrieve and show a collection of data. A part specifies how to retrieve the data that appears in the FactBox pane of a form, the preview pane of a list page, or the enhanced preview of a control. You use parts to provide information related to a record that appears in a form…

…To create or modify a part, you use the properties of that part to describe how to retrieve and show data. For example, you use a part property to specify the data source for the part. Typically, you use a query as the data source for a part. You also use properties to specify the data fields that show when the part appears as a FactBox on a form.

Then a FactBox is the collection of data that appears on the form, which is based on a Part.  In addition, a FactBox always represents a single part, and if we want to modify a FactBox, we must modify the underlying part.

In AX 2012, a part is defined by using metadata and each part we create extends a common metadata model for parts. Further, one of the best advantages of the Part metadata model is that enables us to create parts that can appear in both the client and Enterprise Portal (EP).

Parts also allow us to represent data through cues in AX 2012. To show a cue in the FactBox pane of a form, you add the cue to a cue group part. A cue group is a type of part that contains one or more cues, and these can be created as a cue group that can appear in forms for both the client and EP, which can be used to add information to a role center page.

In my next and last post about this topic, we will create the user forms needed to display the Auto Rental data. In addition, we will create menu items, and of course a FactBox to display Auto Rental information related to a customer.

Take Care!

Indexes In Microsoft Dynamics AX

Today I would like to have a discussion about Indexes in AX. In my opinion, I think it is extremely important to create an architecture that will support the fast retrieving of records. In addition, there is no way we can provide a good service if we don’t provide a nice and easy to follow guide on indexes.  

So, basically indexes in databases are used to locate records and they are stored separately in the database. They contain a key that can be quickly located in the index, and this key will have a reference to a record.

For example, the let’s think on the SalesIdx index in the Sales Table. This table field index contains the SalesId of a Sales Order. Now, because the Sales order is unique and sequenced, an index will be used to quickly lookup the sales orders records through the reference.

Type of Indexes

Unique

A unique index is created based on a column; Microsoft Dynamics AX assures that no duplicate key values can occur in that same column. Also, when updating a column that contains a unique index will cause an error.

Non-Unique

Non-unique indexes are created for performance reasons. In other words, they give a fast way of retrieving data. For example, (and here we can think on the example above about the SalesIdx) instead of doing a full-table search of all the records in a table, and non-unique index will help us narrow down this search to a reference.

In general, indexes use system space and must be updated every time system data is created, edited, or deleted. I have seen indexes slowing down the updating process. However in most cases the overall performance improvement when selecting records far outweighs the performance loss when updating.

Microsoft recommends that when creating an index to give it a name that reflects the names of the fields on a table plus the suffix Idx. (i.e. SalesIdx)

Creating Indexes

Creating indexes is not very difficult at all. You just want to make sure that the fields you want to create the index on exist in the table (I guess this is obvious, but you’ll be surprise with some of the question I have been asked over the years). Anyway, the indexes can be unique or non-unique, and are based on a single column or multiple columns that exist within the table.

1. Locate a table in the AOT (This has to be a table in your Dev environment or some virtual machine AX)
2. Right-click the Indexes node in the table and select New Index. A new index Index1 is created.
3. Rename the index to the field name of the table/field you have chosen
4. Drag the field you have chosen to the index created in the step above node.
5. In the properties sheet for the created index node, set the property AllowDuplicates to No.

Just to make it more visually clear, please take a look at the following picture:

Take Care!

Unable to validate the AX 2009 Workflow Webservice URL on a Windows Server 2008 R2 x64 - The request failed with HTTP status 405: Method Not Allowed.

Hi,

I was having some issues yesterday validating an URL in Windows Server 2009 R2 - 64. I have done this a few times already and the process have been really smooth and straight forward. However, in the environment I'm working on right now I the The request failed with HTTP status 405: Method Not Allowed error.

I found this post that helped me solve the problem, which basically was changing the property value of the MicrosoftDynamicsAXWorkflow50 application pool in IIS Manager to "Enable 32-Bit Applications".

Here is the link:

http://blogs.msdn.com/b/emeadaxsupport/archive/2010/01/05/unable-to-validate-the-ax-2009-workflow-webservice-url-on-a-windows-server-2008-r2-x64.aspx

Get the length of a string in x++

str _myString = '123456789';
int _len;
;

len = strlen(myString);

info(strfmt('%1', len);

returns 9

SQL 2008R2 FULL TEXT INDEX SEARCH SETUP

In Microsoft SQL 2008 R2, the process of setting up full text search is done differently then in prior versions of SQL.


In earlier versions of SQL, we would have to right-click on the table we wanted to have full text index search capabilities by going to Design. Once in the table designer, we would choose Full Text Index and we would set the values in the window that would pop up.


In SQL 2008 R2, however, the process of setting up the full text index search is as follows:


The very first step is to create a new Catalog in our data base as follows (Let's not forget to click on the Data Base we want to setup the Full Text Index Search)


CREATE FULLTEXT CATALOG NameOfCatalog


Execute the stored procedure, which should be really quick and in where we should get a message like this:


Command(s) Completed Successfully


Then we go through the following steps: