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 .
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.
Similarly, you can create ALEarningGroupCode
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.
Double click on it and describe its detail on designer window
Double click and update Name and label.
Similarly add one more element
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.
Set table name as as AlEarningCodeGroup, right click on table object in designer window solution explorer and open set table type as regular.
Now add two fields with EarningCode and Description of string type
Now expand field group and add new group Named to Grid and drag both fields into it
Similarly create another table for EarningCode
Add two fields AlEarningCode and fields EarningCode and description.
Now add one more field with type Enum
Set its name EarningcodeType and set its enum Type to AlEarningCodetype, We created this enum type in previous steps.
Now add one more field with real type and set its
similarly create a Field group name Grid and drag all fields there.
Add one more string type for EarningCodeGroup we will map this field with Earning Code table for primary and foreign key relationship.
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
Update the following properties to Alternate Key Yes, Allow Duplicates to now and set its name as idxEarnGode p
Now drag EarningCodeGroup Under index.
Now open table properties and add following update Primary Index to IdxEarningGrpCode and also select
Similar create index in ALEarningCodes
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
Updates relation Name and also select related table
Save it again right click and Select Normal relation
Click on Related Field fixed.
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.