About index

index: An index is a table-specific database structure that speeds the retrieval of rows from a table.

Uses: Indexes are used to improve the performance of data retrieval and occasionally to ensure the existence of unique records.

location in AOT: AOT>Data Dictionary>Tables

* Once created, indexes are managed automatically by the DBMS every time a record is inserted, updated, or deleted.

* An index is defined by one or more fields.
*  When selecting table fields for an index consider the following:

Fields that are often searched by a range.

Fields that frequently participate in joins.

Fields that are frequently used to order or group a result set.

Types of index: unique or primary
                          Non-unique or cluster
unique or primary:Whether an index is unique is defined by the index's AllowDuplicates property. When this property is set to No, a unique index is created.
The database prevents you from inserting records with duplicate key values by rejecting the insert
Non-unique:Setting the index's AllowDuplicates property to Yes creates a non-unique index.
These indexes allow you to enter duplicate values for the indexed fields and are used for performance reasons.
Note: A field of data type memo or container cannot be used in an index.
system index:Microsoft Dynamics AX requires a unique index on each table so if there are no indexes on a table or all the indexes are disabled, a system index is automatically created.
The system index is created on the RecId and DataAreaId fields if the DataAreaId field exists. Otherwise the system index is created on the RecId field. You can see system indexes in the database but they aren't visible in the AOT.

How to create an index: 
http://msdn.microsoft.com/EN-US/library/aa607289.aspx

Table Keys:
http://msdn.microsoft.com/EN-US/library/hh812105.aspx

Difference between primary and cluster index:

Primary index: this index used to organize both the data store and other indexes for more efficient updating and faster access.
cluster index: this index are created for performance reasons. they provide a quick way of retriving data, instead of performing a full-table search of all the records in the table.
Difference between Index and Index Hint :
 
Index: when you add the statement "index MyIndex",  the axapta kernel will add an "ORDER BY" with all the fields of the index.
Index Hint: when you add the statement "index hint MyIndex", the axapta kernel will add a statement to instruct the database to use that index and no other one.
 

inheritence hierarchy

object hierarchy

 

 

 

 

for more information

http://msdn.microsoft.com/EN-US/library/object.aspx
http://msdn.microsoft.com/EN-US/library/objectrun.aspx




 

Table, Form and Report level override Methods

Table level Methods:

http://msdn.microsoft.com/en-us/library/aa625830.aspx

Form level Methods:

http://msdn.microsoft.com/en-us/library/aa674599.aspx

Methods On Form level DataSource:

http://msdn.microsoft.com/en-US/library/aa893931(v=ax.50).aspx

Methods on Form Control:

http://msdn.microsoft.com/en-us/library/aa607131.aspx

Report level Methods(Morphx Reports):

http://msdn.microsoft.com/en-US/library/aa854897(v=ax.50).aspx

Report DataSource level Methods:

Methods on DataSet level 

How to change from one layer to another layer

The default current layer that is used by Microsoft Dynamics AX is set during the client installation. By default, the current layer is set to USR.
How to change the current layer from USR to another layer when Microsoft Dynamics AX is started.

To change the current layer:

1. Click Start, point to Control Panel, point to Administrative Tools, and then click Microsoft Dynamics AX 2012 Configuration. The Microsoft Dynamics AX Configuration Utility opens.
   
2. In the Microsoft Dynamics AX Configuration Utility, click Manage, and then click Create configuration. The Create Configuration window opens.
   
3. In the Configuration name field, enter a name for the configuration, and then click OK. The Create Configuration window closes.
   
4. On the Developer tab, in the Application object layer to open field, select a new layer from the drop-down list.
   
5. Click OK to save the configuration. The Microsoft Dynamics AX Configuration Utility window closes.
   
6. Close and restart the client in the new layer.

What is Extended DataType and Benfites

Extended data types (EDTs) are user-defined types, based on the primitive data types boolean, integer, real, string, and date, and the composite type container.
You can also base EDTs on other EDTs.

Benfits:
1. Code is easier to read because variables have a meaningful data type. For example, Name instead of string.
2. The properties you set for an EDT are used by all instances of that type, which reduces work and promotes consistency.
Ex: account numbers (AccountNum data type) have the same properties throughout the system.
3. You can create hierarchies of EDTs, inheriting the properties that are appropriate from the parent and changing the other properties.
Ex: the ItemCode data type is used as the basis for the MarkupItemCode and PriceDiscItemCode data types.

Declartion of EDT variables:

// A UserGroupID (integer) variable is declared and initialized to 1.
UserGroupID groupID = 1;
// An Amount (real) variable is declared.
Amount currency;

 

 

DataTypes in Axapta

primitive datatypes:

Anytype:	A placeholder for any data type.
Booleans:	Can only contain the values false and true.
Dates :	Contains day, month, and year.
Enums:	An abbreviation for enumerated text—a set of literals.
GUIDs :	A globally unique identifier.
Integers :	A number without a decimal point. To declare an integer, use the keyword int.
Reals :	Numbers with a decimal point, also called decimals.
Strings:	A number of characters. To declare a string, use the keyword str.
TimeOfDay:	Contains hours, minutes, and seconds. To declare a time, use the system type timeOfDay.
utcdatetime:	Contains year, month, day, hour, minute and second

composite Datatypes:

Arrays	An array is a list of items with the same data type and the same name—only the index differs.
Containers	A container is a dynamic list of items containing primitive data types and/or some composite data types.
Classes as Data Types	A class is a type definition that describes both variables and methods for instances (objects) of the class.
Delegates as data types	A delegate collects methods that subscribe to the delegate. The delegate specifies the parameter signature that all its subscriber methods must share. When the delegate is called, the delegate calls each of its subscribers.
Tables as Data Types	All tables defined in the database can be handled as class defination

For more information:  http://msdn.microsoft.com/en-us/library/aa853792.aspx
 

Delete Actions

The DeleteAction element helps maintain database consistency when a record is deleted.
Define delete actions to specify what should occur when data being deleted in the current table is related to data in another table.

Best Practices:
Have a delete action on every relation between two tables.
Use table delete actions instead of writing code to specify whether deletes are restricted or cascaded.

Types of Delete Actions:

1. None: Delete action disabled.

2. cascade: Deletes related records.
Ex: On the CustTable table, a cascading delete action has been defined for the CustBankAccount table. When a customer is deleted from the CustTable table, the delete method also ensures that the corresponding bank account information is automatically deleted.

3. restricted: Restricts deletion in the current table if data is present in related tables.
Ex: On the CustTable table, a restricted delete action has been defined for the CustTrans table. When a customer is deleted in the CustTable table, the validateDelete method ascertains whether transactions exist for the customer in the CustTrans table. If so, validateDelete returns false.

4. cascade+restricted: Cascade the delete, even though records exist on related tables.

Ex: we have three tables  Person ,Customer & Order

Now Person is a parent of Customer table, and Customer is a parent of Order table having (One-To-Many) relations.

If I set a Delete action property on Person table to “CASCADE” for customer table  and If I set a  Delete Action property on a CUSTOMER table for Order table to “CASCADE +RESTRICTED”.

So if I delete a record from Customer table then It will first check the record in the child table(order table) and if exist that warning prompt saying that first we need to delete a record from child table.

But if I delete a record from Person table it will automatically delete a record in Customer table and all records related to customer table in Order table would also be deleted.

The Cascade+Restricted delete action is used in the standard application for LedgerJournalTrans on LedgerJournalTable.

This type of delete action is useful when you prefer a total clean-up—when you delete a customer, you also delete all the transactions associated with that customer.


How to Create a Delete Actions:

http://msdn.microsoft.com/EN-US/library/bb315018.aspx

Relations

The foundation of Microsoft Dynamics AX is the relational database.
In AX, the relationship between tables is called a relation, that contain related data.

Relations in Microsoft Dynamics AX:

1. Keep the database consistent (enforce referential integrity).
2. Enable the look up of values in other tables .
3. Are used to validate data.
4. Are used by the Auto Join system in forms.
5. Automatically propagate changes from one table to another.
6. Auto-define table relationships in queries.

Uses: Table relations are most commonly used in form fields to enable the look up of information in another table.
If a table relation exists, the lookup button can be used to display a lookup list of values for a particular field.

Different Types:

Normal to specify relation fields without conditions.

Ex: 

Field fixed to specify relation fields to restrict the records in the primary table.
Format: Table.Field == <EnumValue>
Restricts the records selected in the primary table. Only records that meet the condition are selected.The condition is ANDed with your relation.
Ex:

 

Related field fixed to specify relation fields that restrict the records in the related table.
Format: <EnumValue>==Table.Field
Restricts the records selected in the related table. Only records that meet the condition are selected.The condition is ANDed with your relation.

Ex


Newly Added in Axapta-2012:
ForeignKey to specify a correspondence between a foreign key field in the present table to the primary key field in another parent table.
For more information: http://msdn.microsoft.com/en-us/library/aa556809.aspx


 

What are the default Field Groups in axapta and Use

FieldGroup:
Field groups are objects that group together fields that logically belong together.
Field groups can exist on tables, maps, or views.
You should define a field group when several fields participate in a single function or are related in some way and are shown together on forms and reports.
In Microsoft Dynamics AX, field groups are found in the AOT--> Data Dictionary -->Field Groups.
A field can belong to more than one field group within the same table, map, or view.

Default Field Groups:
When a table, map, or view is created, two field groups are automatically created:

1. AutoReport - this field group is used to create the system's automatic reports. The fields placed in this group are displayed when a user clicks Print in the File menu from a form. The AutoReport field group is empty until you add fields to it.
2. AutoLookup - this field group is used in lookup forms. The fields in the field group display when the user clicks the lookup button from a form control.The AutoLookup group is empty until you add fields to it.

important:
If a table has a display or edit method the method can also be added to a field group.