Le lingue
Pagine
Legale
Chapter 2: Data Dictionary
2-1
CHAPTER 2: DATA DICTIONARY Objectives
The objectives are:
Explain the MorphX development environment and the Application Object Tree.
Design tables using MorphX. Describe the different data types within Microsoft Dynamics AX. Create a new table. Learn how to create and modify indexes using MorphX. Describe relations on Microsoft Dynamics AX tables. Learn how to create and modify Enumerated data types. Learn how to create and modify views.
Introduction This chapter introduces the tools and procedures used to create tables, data types, and relations that are required in Microsoft Dynamics AX.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-2
MorphX and the Application Object Tree The Application Object Tree (AOT) is a tree view of all application elements in Microsoft Dynamics AX. Each branch node contains a different application element type. Within the AOT, a developer can create or modify Microsoft Dynamics AX application elements using drag-and-drop functionality. The system has default settings for all application object properties.
This chapter focuses on those elements contained in the Data Dictionary node. It briefly discusses all the elements within the data dictionary. Some of the other application elements are discussed in later chapters. The course goes into much more detail later in the chapter with tables, maps, views, extended data types, and base enums. First, it is important to know how to use the AOT.
Procedure: Opening the AOT
Access the AOT from the Microsoft Dynamics AX application. There are four different ways to open the AOT:
Navigate to Windows > New Development Workspace. A new development workspace will open with the AOT visible.
FIGURE 2.1 OPENING THE DEVELOPMENT WORKSPACE
Press Ctrl-Shift-W.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Chapter 2: Data Dictionary
2-3
Press CTRL + D. Add the -development switch to the ax32.exe command when
starting Microsoft Dynamics AX.
FIGURE 2.2 DEVELOPMENT WORKSPACE AND AOT
Features of the AOT
AOT Toolbar The toolbar located in the AOT contains five icons, and each icon also has a short-cut key.
The functions of these icons are as follows:
CTRL + O: Opens the application element directly from the AOT. ALT + ENTER: Opens the properties sheet for the selected element. CTRL + SHIFT + S: Saves any changes made to an element. CTRL+SHIFT+I: Imports an .xpo file containing definitions of
elements. F7: Compiles the object to locate syntax errors.
NOTE: Microsoft Dynamics AX elements are exported as .xpo files. These are the only types of files Microsoft Dynamics AX recognizes when trying to import into the AOT.
AOT Right-Click Menu The right-click menu in the AOT differs slightly depending on the node. This section shows all the functions of the right-click menu on the top level of the AOT, excluding the Data Dictionary.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-4
The following figure shows a right-click menu on the Tables node.
FIGURE 2.3 AOT RIGHT-CLICK MENU
NOTE: The Open New Window command makes it possible to have more than one instance of the AOT open at a time. This makes it simpler to work with multiple elements within the MorphX environment.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Chapter 2: Data Dictionary
2-5
Elements in the Data Dictionary
This section focuses on the application elements located in the Data Dictionary node of the AOT. The other elements in the AOT are discussed in later sections in this course. The following list shows all the element types in the Data Dictionary:
FIGURE 2.4 DATA DICTIONARY ELEMENT TYPES
Tables store data in the system. They contain fields of various data types, depending on the type of data they hold. Data is entered, edited, and deleted through forms. Tables are discussed in more detail later in the section.
Maps are used to link tables. By creating a map, fields can be accessed in different tables if the fields are of similar types but have different names. For example, a class that implements functionality to process data in the tables. If these tables use identical names for the columns, you can reuse data processing classes.
Views are read only data representations that combine multiple table objects. Views present a selected set of rows and columns by joining different tables through data sources and relations between them. Frequently views represent a subset of fields from a single table to simplify reporting.
Extended Data Types are customized data types based on the primitive MorphX data types including strings, integers, reals, dates, times, enums, or containers.
Base Enums are a list of literals that can be used throughout the development environment in MorphX. Enums (enumerable type) are an extended data type that inherits from a base enum. The elements in an enumerable type are created by using the AOT, where the name of the type and the literals can be described in the definition.
Configuration keys disable features in the application for all users. Each key controls access to a specific feature, and once it is disabled, the feature is automatically removed from the user view.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-6
License codes control which elements can be enabled. If a license code is not purchased for a module, then any element attached to that license code cannot be used.
Security keys are a legacy from previous versions of Microsoft Dynamics AX and will be removed in the next version. Security is covered in detail later in this course.
Table collections do not contain any data. Virtual companies are built on table collections. They contain tables shared by more than one company. A table can be a part of more than one table collection.
Perspectives organize tables, folders, fields, and roles, in subsets according to application modules in an intuitive way, matching the same concept as Microsoft SQL Server Reporting Services. Perspectives are also discussed in more detail with the use of OLAP cubes.
Tables The Microsoft Dynamics AX SQL database created during installation contains the complete table repository. Tables are at the core of the Microsoft Dynamics AX application and enable storage of a company's information and transactions.
Understanding Microsoft Dynamics AX Tables
Tables storing data about company transactions, inventory, general ledger, journals and more, form the base of the Microsoft Dynamics AX application. These are referred to as base tables or application tables. Some frequently used tables include:
AX Table Name
Application Data Detail
CustTable The table for basic customer information.
VendTable The table for basic vendor information.
LedgerTable The table for the general ledger that contains the chart of accounts information.
InventTable The table for the general inventory catalog.
PurchTable The table for purchase order creation information.
Other tables in Microsoft Dynamics AX contain metadata. These tables contain descriptions of data. Metadata is housed in system tables.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Chapter 2: Data Dictionary
2-7
System tables are differentiated from application tables by the Sys prefix that starts the name of the table. The following figure shows examples of system and application tables.
FIGURE 2.5 SYSTEM TABLES
Microsoft Dynamics AX Table Structure and Components
Tables in Microsoft Dynamics AX contain multiple elements that define the table. There are seven categories that define tables:
Fields Field Groups Indexes Full Text Indexes Relations Delete Actions Methods
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-8
The following figure shows these elements:
FIGURE 2.6 TABLE ELEMENTS
Fields
Access the Fields node using MorphX to create or modify the physical columns in the database table. The fields defined within the Fields node are the columns belonging to the physical table.
CAUTION: Any fields created, deleted, or modified using MorphX in the Table node are reflected in the database when the changes are saved. This can potentially damage the Microsoft Dynamics AX application if not used correctly.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Chapter 2: Data Dictionary
2-9
Display all the fields for the LogisticsAddressZipCode table by opening the Fields node. The following figure shows how MorphX is used to view all the columns within a Microsoft Dynamics AX table.
FIGURE 2.7 FIELDS IN THE ZIP CODE
Field Groups
Field groups in Microsoft Dynamics AX are logical groupings of physical database fields. Microsoft Dynamics AX uses Field Groups to cluster fields into logical groups so that these groups can be used on forms and reports. An example of such a group is the group Measurement located on InventTable. InventTable holds data relating to products, and the measurement fields are height, width, depth and density of the products.
FIGURE 2.8 FIELD GROUPS
NOTE: If a modification is made to a field group, application elements that refer to the field group are automatically updated to reflect the new content of the group.
IntelliMorph is the term given to the technology that adjusts the layout of forms and reports which use the modified field group. Adding new fields to a field group can be a powerful technique for updating forms that use field groups with a new field.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-10
BEST PRACTICES: Define a group when several fields logically belong together and are shown together on forms. Every field on a table should belong to a group.
Indexes
The indexes in the Microsoft Dynamics AX table definition are the physical indexes that exist on the tables in the database. Indexes accelerate a search on a table's data. More detailed information on indexes is discussed later in this chapter.
Full-text Indexes
A full-text index contains location information about each significant word in a string field of a table. Some queries can use this information to run more efficiently and to complete more quickly. These are queries that search for words embedded in the middle of string fields.
A full-text index can be created by creating a new full-text index under the full-text index node on the table, and adding fields to this new full-text index.
Queries can use the full-text index by creating a query range of type Full-Text Index. If the value in that range has multiple words, the space is treated like a boolean OR, so any records with at least one of the words in the full text indexed fields will be returned.
X++ select statements cannot use a full-text index.
Relations
Microsoft Dynamics AX uses a relational database, which means that relationships can be created between tables that contain related data. In Microsoft Dynamics AX, the relationship between tables is called a relation.
Relations are discussed in more detail later in this chapter.
Delete Actions
Microsoft Dynamics AX uses a category of methods called Delete Actions to govern how data is handled when a deletion event occurs in a parent table. There are four types of delete actions:
None: The row in this table is deleted but nothing occurs in the specified table.
Cascade: The row in this table is deleted and all related data in the specified table is also deleted. This ensures that no orphaned records remain after a deletion.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Chapter 2: Data Dictionary
2-11
Restricted: The row in this table is only deleted if there is nothing that relates to it in the specified table. This is used when a parent record should only be deleted when no child record exists, and the child record is important to keep, for example, transaction records.
Cascade + Restricted: Behaves the same as Restricted.
NOTE: It is important to create delete actions and to create them correctly to ensure data integrity.
Methods
Microsoft Dynamics AX uses methods within the table node to perform functions specific to the table. This can be data lookups to other tables based on data in that table, or manipulation of the data during specific events. Table methods are covered in more detail in later development courses.
Inheritance
A table can extend another table, by specifying the Extend property on the table. This means one table inherits all of the fields, properties and methods of the table that it extends. New fields and methods can be added to this extending table that will not be visible from the extended table.
Data Types: Primitive and Extended Microsoft Dynamics AX uses data types to define how data is stored and restricted within the database. There are primitive and extended data types (EDT).
Primitive Data Types
The basic set of primitive data types in Microsoft Dynamics AX map to underlying data types that exist in the physical database. These primitive data types can be expanded to create Extended Data Types, which are reusable data types having a specific definition.
The primitive data types used in Microsoft Dynamics AX are as follows:
Primitive Description String A number of characters.
Integer A number without a decimal point. 32 bits wide.
Real A number with a decimal point.
Date Contains day, month and year.
Time Contains hours, minutes and seconds.
UTCDateTime A combination of date and time types into one data type that also holds time zone information.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-12
Primitive Description Enum A set of literals that are represented internally as integers.
Container A composite data type that is a dynamic list of items containing primitive data types and/or some composite data types.
GUID Globally Unique Identifier. A 16-byte number generated that uniquely identifies a network or interface.
Int64 A number without a decimal point. 64 bits wide.
Boolean Only contains the values false and true.
Extended Data Types
EDTs extend primitive data types or other EDTs. The benefit of creating EDTs is the reuse of their properties. For example, the label for one EDT can be inherited from the EDT that it extends.
A table field that is created based on an EDT will also inherit properties from that EDT.
Another benefit is more efficient maintenance. The properties of many fields can change at one time by changing the properties on the EDT.
Most fields in Microsoft Dynamics AX tables are based on EDTs.
Scenario: Veterinary Surgery
The following step procedures and labs will demonstrate how to create a modification that can be used in a veterinary surgery. The surgery procedure will need to keep track of each animal that arrives, the owners of the animal, the species, the breed, and details of each visit including the date, reason and the cost.
To do this you will need to create the following new tables.
Species table: This will contain entries such as dog, cat, ferret and so on.
Breed table: This will contain the breed such as Labrador, Old English Sheepdog, Bengal, Siamese and so on. This should be related to the Species Table.
Pet table: This information is related to the customer table and breed table.
Visit transactions: This will contain the details of each visit for each pet.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Chapter 2: Data Dictionary
2-13
Procedure: Creating a New Extended Data Type
User the following procedure to create a new EDT that will define how the Id of the species is stored.
1. Open a new development workspace. 2. In the AOT, expand the Data Dictionary node. 3. Right-click the Extended Data Types node. 4. Select New > String. A new EDT called Type1 is created. 5. Right-click Type1 and select Properties. 6. Modify the property Name to VetSpeciesId. 7. Modify the property Extends to SysGroup. 8. Modify the property Label to Species Id. 9. Close the property sheet and click Save in the AOT to save your
changes. 10. On the Database Synchronization dialog, click Yes. This will take a
few minutes to complete.
Setting the Extends property to SysGroup inherits properties from the SysGroup EDT. This sets the size of the EDT to 10 characters, which is the standard size for an ID field such as this one.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-14
The new EDT and property sheet should appear as follows:
FIGURE 2.9 CREATE VETSPECIESID EDT
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Chapter 2: Data Dictionary
2-15
Lab 2.1 - Create a New EDT The Veterinary Surgery module also requires a new EDT for the Id of the Breed.
Scenario
The scenario for this lab continues from the preceding scenario.
Challenge Yourself!
Create a new EDT that will be used for the breed Id. Set the label and help as appropriate. Ensure the size of the field is consistent with the standard application.
Step by Step
1. Open a new development workspace. 2. In the AOT, expand the Data Dictionary node. 3. Right-click the Extended Data Types node. 4. Select New > String. A new EDT called Type1 is created. 5. Right-click Type1 and select Properties. 6. Modify the property Name to VetBreedId. 7. Modify the property Extends to SysGroup. 8. Modify the property Label to Breed Id. 9. Close the property sheet and click Save in the AOT to save your
changes. 10. On the Database Synchronization dialog, click Yes.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-16
Creating Tables Existing tables in Microsoft Dynamics AX can be modified or new tables can be created using the MorphX tools in the AOT.
Procedure: Creating a New Table
Use the following step procedure to create a new table that will store the records for the species. There are two fields on the table called SpeciesId (type VetSpeciesId), and Name (type Name).
1. Open a new development workspace. 2. In the AOT, expand the Data Dictionary node. 3. Right-click the Tables node and select New Table. A new Table
called Table1 is created. 4. Right-click Table1 and select Properties. 5. Modify the property Name to VetSpeciesTable. 6. Modify the property Label to Species. 7. Close the property sheet. 8. Press Ctrl-D to open another AOT. Ensure window is not
maximized by clicking the restore down button to enable you to view both AOT windows.
9. Expand the Data Dictionary node. 10. Expand the Extended Data Types node. 11. Locate the VetSpeciesID EDT. 12. Drag the VetSpeciesID EDT to the Fields node of the
VetSpeciesTable table. A new field called VetSpeciesID is created. 13. Right-click the VetSpeciesId field and click Properties. 14. Note that the Extended Data type for this field is set to VetSpeciesId. 15. In the Name property, set the name of the field to SpeciesId. 16. Close the property sheet. 17. Locate the Name EDT in the second AOT. 18. Drag the Name EDT to the Fields node of the VetSpeciesTable
table. A new field called Name is created. 19. Save your changes to the table. When changes to a table are saved,
Microsoft Dynamics AX automatically synchronizes the changes made within its Data Dictionary with SQL: this ensures that the definitions for the tables remain consistent at all times.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Chapter 2: Data Dictionary
2-17
The new table and property sheet should appear as follows:
FIGURE 2.10 CREATE THE VETSPECIESTABLE TABLE
TIP: To view data in a table use the table browser, by pressing Ctrl-O. This opens the table in a basic grid form. From here you can view, edit, create and delete data. This should be used only as a developer tool, and should not be used to edit live data.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-18
Lab 2.2 - Create a New Table Create a table that will store data for the breed.
Scenario
The scenario for this lab continues from the preceding scenario.
Challenge Yourself!
Create a new table that will store the breed of the pet. The table should have the fields BreedId (type VetBreedID) and Name (type Name).
Step by Step
1. Open a new development workspace. 2. In the AOT, expand the Data Dictionary node. 3. Right-click on the Tables node and select New Table. A new Table
called Table1 is created. 4. Right-click Table1 and select Properties. 5. Modify the property Name to VetBreedTable. 6. Modify the property Label to Breed. 7. Close the property sheet. 8. Press Ctrl-D to open another AOT. Ensure window is not
maximized by clicking the restore down button to enable you to view both AOT windows.
9. Expand the Data Dictionary node. 10. Expand the Extended Data Types node. 11. Locate the VetBreedID EDT. 12. Drag the VetBreedID EDT to the Fields node of the VetBreedTable
table. A new field called VetBreedId is created. 13. Right-click the VetBreedId field and click Properties. 14. Note that the Extended Data type for this field is set to VetBreedId. 15. In the Name property, set the name of the field to BreedId. 16. Close the property sheet. 17. Locate the Name EDT in the second AOT. 18. Drag the Name EDT to the Fields node of the VetBreedTable table.
A new field called Name is created.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Chapter 2: Data Dictionary
2-19
Indexes Indexes in databases are used to locate records. They are stored separately in the database, and contain a key that can be quickly located in the index, and a reference to the record. For example, the CustIdx index of the customer table contains the account number of the customer. The account number is unique and sequenced. This is then used to quickly lookup the customer record through the reference.
There are two types of indexes: unique and non-unique. If a unique index is created based on a column, Microsoft Dynamics AX ensures no duplicate key values occur in that column. Also, updates on a column that contain a unique index, which violates the uniqueness of the column, cause an error.
Non-unique indexes are created for performance reasons. They provide a quick way of retrieving data, instead of performing a full-table search of all the records in a table. Do not create an index if it is not needed because they use system space and must be updated every time system data is created, edited, or deleted. This can slow down the updating process. However in most cases the performance gain when selecting records far outweighs the performance loss when updating.
When an Index is created, it is recommended to give the Index a name reflecting the names of the fields in the index, followed by the suffix Idx.
Creating Indexes
Creating an Index in Microsoft Dynamics AX always relates to the table where it is created. The index can only be created on fields that exist in the table. These indexes can be unique or non-unique, and are based on a single column or multiple columns that exist within the table.
NOTE: A field of data type memo or container cannot be used in an index.
Procedure: Creating an Index
Use the following procedure to create a new index on the SpeciesId field on the VetSpeciesTable.
1. Locate the VetSpeciesTable table in the AOT. 2. Right-click the Indexes node in the table and select New Index. A
new index Index1 is created. 3. Rename the index to SpeciesIdx.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-20
4. Drag the field SpeciesId to the index SpeciesIdx node. 5. In the properties sheet for the SpeciesIdx node, set the property
AllowDuplicates to No.
FIGURE 2.11 CREATING AN INDEX
Primary Index
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.
A typical example of the primary key is the CustTable where the customer's AccountNum is used as a primary key. The AccountNum is used in the AccountIdx index, which is a unique index. This index is then set as the primary index for the CustTable table in the properties for the table.
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.
Procedure: Set a Primary Index
Use the following procedure to set the index created in the previous procedure to be a primary index.
1. In the AOT, locate the VetSpeciesTable table. 2. In the property sheet for the table, set the PrimaryIndex property on
the table to SpeciesIdx. 3. Close the property sheet for the table.
Surrogate Key
When a primary index is not specified, Microsoft Dynamics 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.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Chapter 2: Data Dictionary
2-21
Lab 2.3 - Create a Primary Index Create primary indexes for the tables you have created previously in this chapter.
Scenario
The scenario for this lab continues from the preceding scenario.
Challenge Yourself!
Set the index created in the last procedure as a primary index for the table. Create a primary index for the Breed Table.
Step by Step
1. Locate the VetBreedTable table in the AOT. 2. Right-click the Indexes node in the table and select New Index. A
new index Index1 is created. 3. Rename the index to BreedIdx. 4. If you used the property sheet to rename the index, close the property
sheet. 5. Drag the field BreedId to the index BreedIdx node. 6. In the properties sheet for the BreedIdx node, set the property
AllowDuplicates to No. 7. Close the property sheet. 8. In the property sheet for the table, set the PrimaryIndex property on
the table to BreedIdx. 9. Close the property sheet for the table.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-22
Relations Relations between tables are used to associate rows in one table to rows in another table.
Relations are also used to:
Enforce business rules across tables Create auto joins in forms (join one table to another table). Look up values in other tables (using lookups and the View Details
command). Validate data by providing constraints. Automatically propagate changes from one table to another by
defining cascading relations. Generating field help (hover over a field, and data from the related
table is displayed).
Relationships can be created between tables using MorphX.
These relations are defined within the Microsoft Dynamics AX application and not at the physical database level.
Relations govern the relationships between data in different tables such as foreign key relationships.
Foreign key relationships between tables are always formed from the parent table.
Under the relations node you can create different types of relationships between the parent table and other tables in Microsoft Dynamics AX.
Normal specifies related fields in another table. Multiple fields can be included in this relation. For example, table LogisticsPostalAddress has a relation called State that defines the relation to the LogisticsAddressState table. The values allowed in the state field are limited depending on which country is entered.
Field fixed specifies related fields that restrict the records in the primary table. The fixed field is normally an enum.
Related field fixed specifies related fields that restrict the records in the related table. The related fixed field is normally an enum.
ForeignKey specifies a correspondence between a foreign key field in the present table to the primary key field in another parent table. A ForeignKey can be a PrimaryKey or a Single Field Altenate Key. When it is a Primary Key, the relation is on the primary key field, for example customer account. When it is a Single Field Alternate Key, the relation is on the surrogate key, which is usually the RecId.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Chapter 2: Data Dictionary
2-23
Procedure: Creating a Relation
Use this step procedure to create a relation between the breed and the species table. The breed is related to the species table - a breed of Old English Sheepdog, is related to the record Dog in the Species table. You should not be able to have a cat that has a breed of Old English Sheepdog.
1. In the AOT, locate the VetSpeciesId EDT. 2. Right-click the VetSpeciesId EDT and select Properties. 3. In the ReferenceTable property enter VetSpeciesTable. 4. Close the properties sheet. 5. Expand the VetSpeciesId node. 6. Right-click the Table References node and select New > Table.
Reference. A new table reference is created. 7. Right-click the VetSpeciesId Table Reference and select properties. 8. In the related field property, enter SpeciesId. 9. Close the property sheet and save your changes to the EDT. 10. If the database synchronization dialog appears, click Yes. 11. Drag the VetSpeciesID EDT to the field node on the
VetBreedTable table. 12. When prompted to add the ForeignKey relation from the EDT, click
Yes. This will automatically create the relation between the VetBreedTable table and the VetSpeciesTable.
13. Rename the VetSpeciesId field to SpeciesId.
FIGURE 2.12 CREATING A RELATION
Relations can also be added manually by right-clicking the relations node on the table. However when you create the reference on the EDT and set it as a primary key, once the EDT is added to a table, the system will prompt to add the reference automatically.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-24
Lab 2.4 - Create a Relation Create another new table that will link customers to pets they own.
Scenario
The scenario for this lab continues from the preceding scenario.
Challenge Yourself!
Create a new table that will store data about the pets belonging to each customer. The table will need to have the following fields: Customer Account (Type CustAccount), PetName (Name), Species (VetSpeciesId), Breed (VetBreedId). You should create two relations - one a foreign key to the species table, and one a normal relation to the breed table so that it is limited based on the species field entered.
Step by Step
1. Open a new development workspace. 2. In the AOT, expand the Data Dictionary node. 3. Right-click the Tables node and select New Table. A new Table
called Table1 is created. 4. Right-click Table1 and select Properties. 5. Modify the property Name to VetCustPetTable. 6. Modify the property Label to Pets. 7. Close the property sheet. 8. Press Ctrl-D to open another AOT. 9. Expand the Data Dictionary node. 10. Expand the Extended Data Types node. 11. Locate the CustAccount EDT. 12. Drag the CustAccount EDT to the Fields node of the
VetCustPetTable table. 13. Locate the VetSpeciesId EDT. 14. Drag the VetSpeciesId EDT to the Fields node of the
VetCustPetTable table. A new field called VetSpeciesId is created. 15. Click Yes to create the foreign key 16. Right-click the VetSpeciesId field and click Properties. 17. In the Name property, set the name of the field to SpeciesId. 18. Close the property sheet. 19. Locate the VetBreedID EDT. 20. Drag the VetBreedID EDT to the Fields node of the
VetCustPetTable table. A new field called VetBreedId is created. 21. Right-click the VetBreedId field and click Properties. 22. In the Name property, set the name of the field to BreedId.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Chapter 2: Data Dictionary
2-25
23. Close the property sheet. 24. Locate the Name EDT in the second AOT. 25. Drag the Name EDT to the Fields node of the VetBreedTable table.
A new field called Name is created. 26. Right-click the Relations node on the VetCustPetTable node and
select New Relation. 27. In the properties sheet for the new relation, set the Name property to
VetBreedTable and the Table property to VetBreedTable. 28. Right-click the VetBreedTable relation, select New > Normal. 29. In the property sheet for the new normal relation, set the Field to
BreedId and the RelatedField to BreedID. 30. Right-click the VetBreedTable relation, select New > Normal. 31. In the property sheet for the new normal relation, set the Field to
SpeciesId and the RelatedField to SpeciesId. 32. Save your changes.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-26
Base Enumerations Base enumerations, also called Base Enums, are pre-defined text values for a field that are referenced through an integer value in the database.
FIGURE 2.13 GENDER BASE ENUM
The figure shows the Gender Base Enum, with the value of Male corresponding to the integer value of 1.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Chapter 2: Data Dictionary
2-27
Lab 2.5 - Add an Enum The pet table needs to store whether the animal is male or female.
Scenario
The scenario for this lab continues from the preceding scenario.
Challenge Yourself!
Add the Gender Enum to the VetCustPetTable table.
Step by Step
1. Locate the Gender enum in the Base Enums node in the AOT. 2. Drag the Gender enum to the Fields node in the VetCustPetTable
table. 3. Save your changes to the table.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-28
Views A Microsoft Dynamics AX view is a virtual table that provides the data records and fields that are specified by a query. The following sections describe views and how they retrieve data.
Like a table, a view uses fields and rows to represent data records. However, the data in a view is not stored as a database object but is dynamically created when the view is accessed. A view uses a query to retrieve data fields from one or more database tables.
In Microsoft Dynamics AX, use views where tables are used. For example, use a view in a form, a report, and in X++ code. The following table shows the benefits of using a view instead of a table.
Benefit Description Focused Data
A view is used to retrieve and return only the data that is relevant for a particular user or scenario.
Customized Data
A view enables the use of a complex query to create a highly-customized set of data. For example, a view often represents data as a single table that is retrieved from multiple joined tables and uses many conditions.
Performance
A view can improve performance by returning only relevant fields to the user. In addition, a view definition is compiled which can provide better performance than calling an equivalently complex query.
When a view is created, the view definition is generated and stored in the database. When that view is accessed, the view dynamically retrieves the data that satisfies the view definition.
NOTE: Views are read-only. The data fields and tables that a view uses cannot be updated from that view.
Views are synchronized like tables. When a view is synchronized, the view definition is saved to the database. Synchronization of an existing view causes the definition of that view to be dropped and re-created in the database.
Summary This course discussed the Microsoft Dynamics AX Data Dictionary and the related objects in the Application Object Tree.
This course also introduced the tools and procedures for creating tables, data types, and relations that are required to build the foundation of any table in Microsoft Dynamics AX.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Chapter 2: Data Dictionary
2-29
Test Your Knowledge Test your knowledge with the following questions.
1. Put the following steps in order to create a new Primary Index in a table.
Step:
_____: Set the Primary Index on the Table to the Unique Index
_____: Set the name for the new index
_____: Create a new index
_____: Synchronize the AOT
_____: Set the Allow Duplicates property on the index to No
_____: Add the primary key field(s) to the index
2. Which of the following are allowable data types for an index?
( ) String, Integer, Real, and Container ( ) String, Enumerated, Real, and Memo ( ) String, Integer, Real, and Enumerated ( ) Memo, Container, Map, and integer
3. Which of the following nodes are required to define a table? (Select all that apply)
( ) Fields ( ) Field Groups ( ) Maps ( ) Enumerated data types
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-30
Quick Interaction: Lessons Learned Take a moment and write down three key points you have learned from this chapter
1.
2.
3.
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Chapter 2: Data Dictionary
2-31
Solutions Test Your Knowledge
1. Put the following steps in order to create a new Primary Index in a table.
Step:
6 : Set the Primary Index on the Table to the Unique Index
2 : Set the name for the new index
1 : Create a new index
5 : Synchronize the AOT
3 : Set the Allow Duplicates property on the index to No
4 : Add the primary key field(s) to the index
2. Which of the following are allowable data types for an index?
( ) String, Integer, Real, and Container ( ) String, Enumerated, Real, and Memo () String, Integer, Real, and Enumerated ( ) Memo, Container, Map, and integer
3. Which of the following nodes are required to define a table? (Select all that apply)
() Fields () Field Groups ( ) Maps ( ) Enumerated data types
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
Development I in Microsoft Dynamics AX 2012
2-32
Microsoft Official Training Materials for Microsoft Dynamics
Your use of this content is subject to your current services agreement
/ColorImageDict > /JPEG2000ColorACSImageDict > /JPEG2000ColorImageDict > /AntiAliasGrayImages false /CropGrayImages false /GrayImageMinResolution 244 /GrayImageMinResolutionPolicy /Warning /DownsampleGrayImages true /GrayImageDownsampleType /Bicubic /GrayImageResolution 300 /GrayImageDepth -1 /GrayImageMinDownsampleDepth 2 /GrayImageDownsampleThreshold 1.00000 /EncodeGrayImages true /GrayImageFilter /DCTEncode /AutoFilterGrayImages true /GrayImageAutoFilterStrategy /JPEG /GrayACSImageDict > /GrayImageDict > /JPEG2000GrayACSImageDict > /JPEG2000GrayImageDict > /AntiAliasMonoImages false /CropMonoImages false /MonoImageMinResolution 1200 /MonoImageMinResolutionPolicy /Warning /DownsampleMonoImages false /MonoImageDownsampleType /Average /MonoImageResolution 1200 /MonoImageDepth -1 /MonoImageDownsampleThreshold 1.50000 /EncodeMonoImages true /MonoImageFilter /CCITTFaxEncode /MonoImageDict > /AllowPSXObjects false /CheckCompliance [ /PDFX1a:2001 ] /PDFX1aCheck true /PDFX3Check false /PDFXCompliantPDFOnly true /PDFXNoTrimBoxError false /PDFXTrimBoxToMediaBoxOffset [ 0.00000 0.00000 0.00000 0.00000 ] /PDFXSetBleedBoxToMediaBox true /PDFXBleedBoxToTrimBoxOffset [ 0.00000 0.00000 0.00000 0.00000 ] /PDFXOutputIntentProfile (U.S. Web Coated \050SWOP\051 v2) /PDFXOutputConditionIdentifier (CGATS TR 001) /PDFXOutputCondition () /PDFXRegistryName (http://www.color.org) /PDFXTrapped /False
/CreateJDFFile false /Description