Table Relationships - Learning with an Example

In AX 2012, Table relationships have gone through many changes with many new kind of keys coming into the picture. I had a previous blog entry here.

Today let's see Foreign Keys, Replacement Keys in action.

We will find out that it is possible now to define parent/child relationship without having to define the same primary key in both the tables. For eg, till AX 2009, a header/lines relationship used to exist on the basis of a unique key like SalesId field in both SalesTable and SalesLine. This is no more required in AX 2012. Such relations are defined on RecIds and  values are displayed on forms depending on what is defined on your ReplacementKey (thats why the name Replacement!)

Lets go one by one.

1. Table HeaderTable

2. HeaderTable has two fields AccountId and Name

3. Two indexes AccountIdx and NameIdx. Both have their AllowDuplicates property set as No and AlternateKey property set as Yes.

4. Header table's ReplacementKey property set as NameIdx.

5. Second table LineTable will have two fields LineName and AccountId.

6. Create an Int64 type EDT HeaderTableRefRecId with property ReferenceTable as HeaderTable and Extends as RefRecId (this is important!)

7. Drop this EDT on LineTable.

8. Click Yes on the prompt.

9. New field, new index and new relations are created. Relation is between HeaderTable's RecId and the new EDT field HeaderTableRefRecId.

10. Create a Form with these two tables as datasources. Drop the fields in HeaderView and LineView nodes. 
(Important Note: when you drop the HeaderTableRefRecId field on the Line node, it uses a special ReferenceGroup control, which stores a int64 recId field of the Header table but effectively displays Name. You cannot use a normal int64 control.)

11. Create some dummy records within the table browser. Set the field HeaderTableRefRecId's value from the dropdown. Notice the values displayed in the dropdown. One is the header's RecId and second is the Name, the ReplacementKey.

12. Checkout the values on the form. Field HeaderTableRefRecId's value on UI will not be some random RecId but the Name of the header record. This happens because of the ReplacementKey.

Valid Time State Tables

What are we talking about?

A valid time state table helps you simplify the maintenance of data for which changes must be tracked at different points of time.

• Rows that are valid at different points of time
• Tracks historical changes
• Tracks changes that will be valid in future
• ValidTimeStateFieldType property. Once set and if there is data in the table, AX will not allow property to be made None.
• AX kernel prevents overlaps among date ranges of records keeping the data valid.
• ValidFrom and ValidTo columns are added automatically. Can be type Date or UtcDateTime.
• Records in this table cannot have multirow insert, delete or update operations. They are done one row at a time.
• A ValidTimeStateTable should also have an Index that will be used as an AlternateKey.

How to make a Valid Time State Table?

• Create a new table and set the property ValidTimeStateFieldType to Date or UtcDateTime.         
• Create a new index that will be used as an AlternateKey.
• Add the ValidFrom and ValidTo fields to the index
• Set the AllowDuplicates to No
• AlternateKey to Yes
• ValidTimeStateKey to Yes
• ValidTimeStateMode can be Gaps or NoGaps. NoGaps doesnt allow gaps between a ValidTo field of one record and ValidFrom field of the next record. 

Inserts

When inserting a new record, existing record's ValidTo field is updated and then the new record is inserted.

Selects

In case of select statements, all records effective in the date range provided are selected. Refer code below.

dateFrom = 01/01/1900;

dateTo = 31/12/2154;

while select validTimeState(dateFrom, dateTo) * from address

{

               info(address.address);

}

Updates

In case of updates, a new enum ValidTimeStateUpdate is introduced.

It has 2 possible selections.

1. Correction - ValidFrom and ValidTo values of existing rows are updated to maintain data validity after the update_recordset statement. No new records can be created during this mode.

2. CreateNewTimePeriod - new record is inserted to maintain data validity after the update_recordset statement.

table.ValidTimeStateUpdateMode(ValidTimeStateUpdate::Correction);

Deletes

Deleting a current record in a ValidTimeState enabled table, will update the previous record's ValidTo date to current record's ValidTo date.

Query classes

Methods added to the Query class enabling querying valid time state tables.

1. ValidTimeStateAsOfDate()

//returns records effective as of the date parameter specified
qry.validTimeStateAsOfDate(dateMax);

2. ValidTimeStateAsOfDateTime()

//returns records effective as of the dateTime parameter specified
qry.validTimeStateAsOfDateTime(dateTime);

3. ValidTimeStateDateRange()

//returns records effective between the two dates
qry.validTimeStateDateRange(dateMin, dateMax);

4. ValidTimeStateDateTimeRange()

//returns records effective between the two dates and times
qry.validTimeStateDateTimeRange(dateTimeMin, dateTimeMax);

Using this table as a DataSource in Forms

Two properties are provided on datasources

1. ValidTimeStateAutoQuery
Value AsOfDate returns records on that date and value DateRange returns all the records

2. ValidTimeStateUpdate

CreateNewTimePeriod - Existing records are not modified instead new records are created. If this value is selected, you cant select DateRange in ValidTimeStateAutoQuery.

Correction - Allows updates to existing records. New records are not created.

EffectiveBased - Records in the past cannot be edited. Records that are currently active are edited in a manner similar to CreateNewTimePeriod mode. Future records are edited in a manner similar to Correction mode.

2012 Table Inheritance

In AX 2012, a table can derive from another table. Two properties SupportInheritance and Extends together control table inheritance

Few important points to remember:

1. Table inheritance is described in terms of base table and derived table. Terms Parent/Child table are used to describe foreign key relationships.

2. When a row is inserted into a derived table, AX kernel inserts the fields from the derived table into the derived table and the fields from the base table into the base table.

3. The RecId field in both the base and derived tables is the same.

4. The InstanceRelationType field on the base table stores the Id of the derived table that the record originated in.

5. In case there are more than 2 tables in the hierarchy, a field relationType is also filled by the AX kernel. The relationType field stores the Id for the table directly above the current table in the hierarchy. The field in invisible in Table browser but can be seen in SQL.

6. Deleting a record in a base table or a derived table will automatically cascade delete the record in other table(s).

Downcasting

Downcasting is casting a base table to a derived table. If a base table has more than one table derived from it and you cast to a derived table, you will see the fields in the derived and base tables that exist for that record.

Imagine a case of three tables.

TableDerived2  extends TableDerived1 extends TableBase

Syntax for downcasting from TableDerived1 to TableDerived2 :

TableDerived1 tableDerived1;

TableDerived2 tableDerived2;

select * from tableDerived1 where tableDerived1.Field1 == 'Demo';

if(tableDerived1 is TableDerived2)  //is keyword

{

            tableDerived2 = tableDerived1 as TableDerived2;  //as keyword

}

is and as - the new yin and yang?

The "is" operator returns true if the table is a derived of the base table, or if the object is the same type as the class.

The "as" keyword is used to downcast a base table variable to a derived table variable. The "as" keyword tells the compiler that you believe the downcast will be valid during run time. The "as" keyword applies to the downcasting of both tables and classes. If the cast is not valid at runtime, null is assigned to the variable.

Like downcasting, we have upcasting as well. More on that later.

Also, once a table has been downcasted to a type using the "as" keyword, the methods on that table are available for use as are the methods on its base table.

Abstract err... table?

The Abstract property on a table indicates that a table buffer cannot be instantiated directly. It can only be instantiated through its descendent/derived tables. This is very similar in concept to an abstract class.

Derived Data Sources

If you drop a base table as a datasource in a form, a new type of node called Derived Datasources appears. The Derived Datasources node on a form contains all of the tables that inherit from the base table added as a data source to that form.

If a form is using a data source that has multiple derived data sources, the query the form uses to retrieve data can be very expensive. To reduce the size of the query use the OnlyFetchActive property located on a form data source. When the OnlyFetchActive property is set to Yes the query generated by the form only joins to the tables that have bound controls on the form's design.

Very interesting concept indeed. But table inheritance should be used sparingly and only where it truly makes sense. Because it has its own performance degradations.

2012 Table Relationships

Table relations have undergone a drastic makeover in 2012. Let's take a peek.

Surrogate keys, Natural keys and Foreign key relations are the new concepts in 2012 tables. EDTs no longer support relations defined on them and Microsoft wants developers to avoid using Field fixed and Related field fixed relations and instead use Foreign key relations.

What's a Surrogate Key?

Surrogate key is

• A single column index on a table that uniquely identifies each record
• Also referred to as the Primary key, RecId index or the PrimaryIndex
• No business meaning
• System generated in Microsoft Dynamics AX 2012

Note: On new tables, the PrimaryIndex property will be set to SurrogateKey by default. Existing tables will NOT automatically have their PrimaryIndex property set to SurrogateKey.

And Natural Key?

• Defines a unique index that can be used instead of the SurrogateKey on lookup forms
• Optional Property
• User-friendly index with business meaning
• Also known as Alternate Key

Index properties
On table indexes, there is a new AlternateKey property. When set to Yes, this property allows for an index to be specified in the PrimaryIndex and NaturalKey properties on a table. The AlternateKey property can only be set to Yes on indexes with the AllowDuplicates property set to No since both the PrimaryIndex and NaturalKey properties require indexes that are unique.

Additionally on table indexes, a property called IncludedColumn has been added. A field with the IncludedColumn property set to Yes is added to a non-clustered index to improve the performance of a query by covering all of the fields that are referenced in a query using this index including the key and non-key fields. To set the IncludedColumn property to Yes, more than one field must exist on the index.

Relation properties

On a relation there is a property RelationshipType which can take 2 values. Composition and Association

In a Composition relationship, the parent table OWNS the child table. No records can be created in the child table without having a corresponding header in the parent table. For example, a sales line cannot exist without a sales header. A department can exist without an employee and an employee can be deleted without deleting the department, this relationship is an Association.

Foreign Keys: Parent/Child tables

Child table is one that has a foreign key column. Parent table is one that supplies the value for the foreign key column. Normal, Field fixed and Related field fixed table relations should be avoided going forward.

Try yourself.

Drag SalesId EDT on to your table fields. This will prompt you to add foreign key relation. SalesId field, SalesTable relation and SalesTableIdx index will be created for you.

Virtual Convergence

Microsoft Convergence 2012 is over. For all those like me, who couldn't attend, Microsoft is making available Virtual Convergence, which includes online keynotes and general sessions.

Non-attendees will need to signup on a simple form and you are good to go.

I especially enjoyed the opening song just before Kirill Tatarinov’s keynote, check it out.

Enjoy! After all, its a World of Opportunity ;)

Android Time

Some new flavour today. Checkout my Android App published on the Android Market. Something I do at my leisure apart from blogging. Try out and give feedback! :)

https://play.google.com/store/apps/details?id=com.amannain.android.missedcalls