≡ Menu




Customization in New Dynamics AX (AK 7) Part 4- Custom Tables

Suppose we are writing a custom module for Payroll. According to requirement, we have to create a custom table for Earning code. As well as Earning group.

 

At minimum we required following fields

Earn  Code Group Table

Earning Group of length 20.

Earning Group description length 60

 

Earning Code Table

Earning Code possible length 20.

Earning Code Description length 60.

Variable of fixed, If Variable the base value will be used as base percentage of Basic Salary, If fixed then base value will used as fixed amount.

Amount, The base value described as previous step.

And reference of Earning Code Group.

 

Lest explore some Extended data Type and enum and we create them with.

 

Extended Data Type:

For Extended Data Type is some kind of reusable data type based in primitive data type with some extra properties.

For example In Dynamics AX there is out of box extended Data type with “AmountCur”. It is Real based data Type. This extended Data Type usually used in amount related fields. So one of the many benefit of Extended data type is that we can change one place and it reflect where ever it used. For example, some organization want two decimal point while some required 4 decimal point. So if update decimal point at extended data type it update all fields where In our case we need to extended data Type, one for Earning code and one for Earning Group code,  For description fields we used the Out of the box field.

Now we have to create two extended data type .

2016-08-22_21-09-01

 

 

Currently I am creating objects with AL extension. So I added a extended Data type of EDT String type and name it ALEarngingCode.  Clicking on Add. A new Extended will be added in Visual studio project. Double click on it and It will open in Designer window. Right click on it and open property window and set its size properties and 20.

Size

Similarly, you can create ALEarningGroupCode

 

extendedDataType

 

 

 

Enum:

Similar there is enum is basically name of literal for example if we have Week days and enum then Sunday , Monday…. Saturday are literals.  In our current example we need Earning type as Fixed or Variable.

AddNewEnumBase

Double click on it and describe its detail on designer window

 

Add new Element

 

Double click and update Name and label.

Add name and label

Similarly add one more element

newElements

 

Tables.

Now we have to add new tables.

 

 

Now we explore some important properties of Tables in

Table types.  We can define regular table in database or required temporary table. Temporary table stay in memory until its related object stay in memory. There are two type of Temporary tables.

In memory or Temp db, In memory are usually used in report. Temp db type are used when larger data and larger processes are instantiated.

In current example we use the regular tables.

 

 

 

 

 

 

 

Now add new tables First we create ALEarningGroup and then AlEarningCodes.

Add new item in visual studio and select table from add new Item dialog.

New Table

 

Set table name as as AlEarningCodeGroup, right click on table object in designer window solution explorer and open set table type as regular.

TypeGroup

 

 

 

 

Now add two fields  with EarningCode and Description of string type

 

Fields

 

 

 

 

EarninCodeGroup

 

DescriptionProperties

 

 

 

 

 

 

Now expand field group and add new group Named to Grid and drag both fields into it

EarnCodingDetailss

 

 

 

 

 

 

Similarly create another table for EarningCode

 

Add two fields AlEarningCode and fields EarningCode and description.

earningCodeTable

Now add one more field with type Enum

EnumField

Set its name EarningcodeType  and set its enum Type to AlEarningCodetype, We created this enum type in previous steps.

EnumSelection

 

 

Now add one more field with real type and set its

 

 

EarnCodeAmountCur

 

 

 

similarly create a Field group name Grid and drag all fields there.

EarnCodeTableee

 

Add one more string type for EarningCodeGroup we will map this field with Earning Code table for primary and foreign key relationship.

WithEarningCodeGroup

Indexes – are a way to speed up retrieval of data from a table

 

There are three types of Index in New Dynamics Ax.

A primary Index. This will provide the unique key to each record.  And field used in primary index used as primary or alternative key for table.

Second one is Clustered and not Clustered which are not scope of this post, we will discuss them in later post.

A clustered index organizes the data in the table in the order of the index.

Non-Clustered index is some kind of reference stored somewhere else to pointed to data. Example of the book index, placed at the end of book, reference of sorted title with page number.

Now we create Index in AlEarningCodeGroup table and then AlEarningCode

EarningCodeGroup

Update the following properties to    Alternate Key Yes, Allow Duplicates to now and set its name as idxEarnGode p

 

Index

Now drag EarningCodeGroup Under index.

 

IndexGroup

Now open table properties and add following  update Primary Index  to IdxEarningGrpCode and also select

IndxGroupProperties

Similar create index in ALEarningCodes

EarningCodeTableIndex

 

 

Table Relationship

There are two types of relationship in new Dynamics ax.

The first relationship type is called a normal relationship.  A normal relation is used to specify a relation without any conditions.

Another type of relation is called a foreign key relation.  This is used to specify a correspondence between a foreign key field in the present table to the primary key field in another parent table.  Foreign key relations are usually set on a child table.

In our current Scenario, we want to we want EarningGroup code as reference in EarningCode table. For this we expand relationship node of AlEarningCode and add new Relation based on Primary key relation

PrimaryRelation

Updates relation Name and also select related table

RelationProperties

 

Save it again right click and Select Normal relation

 

PrimaryKeyRelationDetails

 

Click on Related Field fixed.

FieldSelectionInRelation

Now from selected screen select both related fields.

Save It compile, so Tables will be synchronizing to database.

 

In next post we will build simple data entry forms on these two tables.

 

{ 0 comments… add one }

Leave a Comment