Put custom business process into MS Dynamics AX. Part I: Data design

Some time ago I had to make acquaintance with Microsoft Dynamics AX 2012 R2 (hereafter DAX). It should be investigated as one of potential applicant to the deployment as ERP system in company I am working on now. Furthermore first considerations show that some our business processes can not be placed into DAX from-the-box. So a customization, extensive enough, would be coming.

I decided to share my little experience and show how to put your specific processes into DAX.
I have obtained the information from MSDN, Technet and related blogs.

Just to remind, I am not an expert in DAX but all recipes below work for me and I hope would be usefull for you at the beginning of knowing DAX.

Custom Business Process overview

Foremost let's talk about custom business process we are going to represent in DAX. It's a very, very simplistic history of appointments in a dummy clinic. We will focus on date when an appointment happened, how much it cost and by what physician it was processed. We will leave behind the scenes all information about patients, diagnosis, chosen treatment, etc.

The following database schema illustrates all I have been talking about.

Appointment table stores appointments and contents its date, amount and priority (I only added this field to show enum datatype in action). Also Appointment table references on Physician table - list of physicians who are working on our clinic. Phisician table contents name of a physician and its speciality by referencing on Speciality table - simple list of medical specialities.

MorphX overview

Now, when we have considered the business process and its reflection in database, it's time to take a glance on the environment where we can implement all of this. in DAX it's called MorphX. To run it press Ctrl+D key combination in DAX main window or click Windows menu and select New Development Workspace item.

To call MorphX you, as DAX user, should have respective role, e.g. System Administrator.

Application Object Tree

The heart of MorphX is Application Object Tree (AOT). 

It contains all DAX resources: database metadata (tables and views), user-defined data types, elements of GUI (forms and menus), X++ classes and so on. All customization, we will do, is made in AOT.

The most of objects in AOT has properties which are maintained in Properties window.

Projects

Inasmuch count of objects in AOT is huge it's a good idea to use a project to gather all objects we will create in one place. Besides this using of projects provide some other advantages as storing a state of your last work, an ability to save all changed objects in one step, etc.

To open Projects window select View -> Project or press respective button in toolbar. We may create private or shared project, the latter will be accessable for other DAX users. Select Shared, right click and select New -> Project. Select created Project1 and change its Name to zzAppointmetsProject in Properties window.

Notice we will add "zz" symbols at start of name for all new objects, just to distinguish them from existing system objects.

Double click on created project to open it in new window.

Creation of simplest table - Speciality

Let's add tables shown above into DAX. Start with simplest one - Speciality.

In opened window with project zzAppointmetsProject select the project, right click, New -> Data Dictionary -> Table. Just rename the table to zzSpeciality (Name property) and save changes.

After that expand zzSpeciality in project tree, select Fields, right click, New -> String and rename created field to Speciality_Code (Name property). And the same actions to create another field - Speciality_Name. Save changes.

That's all. Table has been created in database and is ready to use.

How it looks in database

If we connect to MSSQL database and look at structure of zzSpeciality table, we will detect that the table has a lot more fields than we have defined in DAX. Well, consider them closely.

• RECID - this is surrogate primary key. As you would notice, we did not define Speciality_Id field that has been shown as primary key in database schema above. Instead, we allow DAX to do this. PrimaryIndex property on table level is responsible for what field is primary key. By default the property has value SurrogateKey and it means that integer autoincrement RECID field will be created and used as primary key.
• RECVERSION - this system field is created always and used when concurrency control is turned on optimistic mode. What concurrency control method should be used OccEnabled table level property defines (Yes, by default). Anyway, even the propert set as No, pessimistic concurrency control works only when we update or delete records manually. In case when form is used for data entry concurrency control is always optimistic.
• PARTITION - this system field is added by DAX in almost all new tables to provide a approach of data isolation by partitions (this field stores reference on RecId of Partition table). Such tehnique allows to separate data by custom requirements, so an user has access to only the data, which are limited by partitions, avaliable to him. Emergence of PARTITION field is controlled by table property SaveDataPerPartition. Actually for all tables with bussines data this property is read-only and its value is set to Yes. To enable the property for edit a table should has Yes in Systemtable property or Framework in TableType property.
• DATAAREAID - this system field reflects one more approach of separating data, for this once by companies. But unlike previous case emergence of this field in a new created table is fully controlled by table property SaveDataPerCompany. By default it's set to Yes, but in our example we do not use companies and will set it to No for all our tables.

In addition we can add into a table so-called WHO fields to monitor who, when and by what transaction has created or updated a row in the table. To turn this ability on set Created... or Modified... table properties to Yes.

Extended Data Types

For the time being put other table properties aside and cognize Extended Data Types (ETD).

EDT is based on primitive datatype (integer, string, date, etc.) and amplifies it by meaningful custom name and lot of extra properties. EDT is defined only in AOT but can be used both either in table's fields definition or in X++ code. Also EDT supports inheritance - properties from parental EDT move to descendants and can be overriden.

In the matter of table fields all changes in EDT are reflect at once for all linked fields.

Moreover, before DAX 2012, EDT used to create a relation between tables, but this trick is completely deprecated now and strongly not recommended to use further.

Let's create own EDTs for Speciality_Code and Speciality_Name fields. Select our project, right click, New -> Data Dictionary -> Extended Data Type -> String. Rename created EDT to zzCode (Name property), set a label to Code (Label property) and restrict length of input string by 4 symbols (StringSize property). Then select Speciality_Code field and link with zzCode EDT (ExtendedDataType property). Repeat these actions to create zzName EDT (Label: Name; StringSize: 32) and link it to Speciality_Name field. Save changes.

Thereafter if value of a property is not set on field level it will be taken from linked EDT.

Creation of table with a relation - Physician

The next table we will create contains data about physicians. Likewise we have done with zzSpeciality we create zzPhysician table and define Last_Name and First_Name fields, both are string and based on zzName EDT. Add First Name and Last Name as their labels (Label property).

Let's implement the reference between Physician and Speciality tables as was shown on database schema above.

To create this reference select Relations node under zzPhysician table, right click, select New Relation. Rename the relation to zzSpecialityRelation (Name property) and set zzSpeciality as the table which the relation references to (Table property). Then select zzSpecialityRelation node, right click, New -> Foreign Key -> Primary Key based.

If we take a closer look what happened we will see that new integer field, named zzSpeciality, appeared for zzPhysician table. And the relation links this new field and RecId field of zzSpeciality table. Set label of zzSpeciality field (Label property) to Speciality and almost all we needed has been done.

A relation ensures that values of a reference field in child table (zzPhysician) will be selected strictly from values of key field of parent table (zzSpeciality). But what happens if we deleted a row from zzSpeciality table that had been used in zzPhysician table? Now the reference on deleted speciality would remain in zzPhysician table.

Behavior on delete of a row in parent table has to be controlled by so-called delete actions. To create it inside zzSpeciality node select DeleteActions node, right click, New DeleteAction. For created action set Table property to zzPhysician, Relation property will be filled automatically by zzSpecialityRelation relation. And by itself about DeleteAction property. By default it has None value and it means the behavior we have considered above.

Other modes of DeleteAction property:

• Cascade - if we delete a row in zzSpeciality table all rows in zzPhysician table that are referenced on deleted row will be deleted too, without any warning or error.
• Restricted -  if we delete a row in zzSpeciality table all rows in zzPhysician table that are referenced on deleted row won't be deleted and error message "zzSpeciality cannot be deleted while dependent zzPhysician exist. Delete dependent zzPhysician and try again." will be shown.
• Cascade + Restricted - this mode repeats the functionality of Restricted one. But it has extra ability. Imagine that there is one more table, say zzSpecialityGroup, and it is parent table for zzSpeciality table. Also there is a delete action that describe cascade deletion between zzSpecialityGroup and zzSpeciality tables. Then if we delete a row in zzSpecialityGroup table all linked rows of zzSpeciality will be deleted, and all linked rows in zzPhysician table will be deleted too, without error.

For created delete action set DeleteAction property to Restricted.

Creation of final table - Appointment

Last table we should create is Appointment table - the data with happened appointments.

In the same way, as we did before, create zzAppointment table and add Appt_Date (Date datatype) and Appt_Amount (Real datatype) fields. Create respective EDTs as well: zzApptDate and zzApptAmount. Link fields and EDTs together.

Also create relation zzPhysicianRelation between zzAppointment and zzPhysician tables. As a result zzAppointment will be expanded by new zzPhysician field. In addition, like we did for a relation between zzPhysician and zzSpeciality tables, we should create a delete action in Restricted mode on zzPhysician table.

And in more detail about leftover field - Appt_Priority. As I mentioned before it was included to Appointment table just for showing Enum datatype in action.

So, firstly create a new enum datatype. Select zzAppointmentsProject, right click, New -> Data Dictionary -> Base Enum. Rename created enum to zzApptPriorityEnum (Name property) and add list of its values. For this select the enum, right click, New Element. Rename created element to LowPriority (Name propert), leave its value as 0 (EnumValue property) and set Low as its label (Label property). Make the same to create other enum's elements - Medium (value is 1) and High (value is 2).

After own enum datatype has been added, create new Appt_Priority field for zzAppointment table (choose Enum type of field). In EnumType property of this field set zzApptPriorityEnum. That's all, the field based on enum datatype is completely created.

And that's all with database part. We will make some changes in this structure further, when will consider the data entry.

Now let's go ahead to investigate the abilities we have for filling our tables.