Job to fetch sales orders with lot of lines

A quick job to fetch sales orders with lot of lines.

static void salesTableFetch(Args _args)
{
    SalesLine     salesLine;    
    ; 
   
    while select count(recId), SalesId from salesLine
        group by SalesId

    {
        if (salesLine.RecId > 100)
        {
            info(salesLine.salesId);
        }
    }
}

Cross company traversing

Today's discussion is around traversing a table across companies. In our example, a record sits in a custom parameters table, which needs to be updated. Instinctively first thing to check would be  the SaveDataPerCompany property of the table, which in our case is set to Yes. So this table stores records per company and for implementing the feature in discussion we will forcefully update the record across all companies by overriding the table's update method.

Logic centers around
1. Traversing DataArea table, which is a system table containing all legal entities
2. Using keyword changeCompany which swaps the companies one by one and
3. Clearing the table object after each change, failing to do so makes the logic not work.

 public void update()   
 {   
   YourTable yourtable;  
   DataArea DataArea;   
   super();   
   while select DataArea where !DataArea.isVirtual   
   {   
     changecompany (DataArea.Id)   
     {   
       yourtable= null;   
       ttsBegin;   
       yourtable= Yourtable::find(true);        
       if (yourtable.RecId)   
       {   
         yourtable.Field = "";   
         yourtable.update();   
       }   
       ttsCommit;   
     }   
   }   
 }  

UnitOfWork - with example code

AX 2012 has a new concept of UnitOfWork aimed at managing database transactions. It's better than ttsbegin/ttscommit way of batching multiple transactions since that had some shortcomings like

•    transactions will start and commit on the client, UOW is on server.
•    transactions are open for a longer period
•    multiple RPC calls to the server, UOW takes a single call for multiple transactions
•    blocked in case there is user interaction inside tts scope

One interesting feature in UOW is that it automatically propagates the primary key value to the corresponding foreign key field, when the row with the foreign key field is inserted.

Methods at work
The UOW object takes a series of individual rows as parameters. It is designed to successfully process each row or it will reject all changes if any issue arises. In addition, the UOW class cannot support set based operations. It operates only on the specific rows that it is given.

Insert
insertOnSaveChanges()

Select
optimisticLock keyword on the select statement for retrieving the objects for updates/deletes

Update
updateOnSaveChanges()

Delete
deleteOnSaveChanges()

Persist to DB
saveChanges()

Example
We will create a parent/child tables example to understand UOW.

1. Create two tables.
TabSale has a primary key (RecId) foreign relation with TabLineItemOfSale.
Fields for the parent table TabSale:
• SaleName
• SaleComment

Fields for the child table TabLineItemOfSale:
• LiosName
• LiosComment
• MasterSaleRecIdFky – the foreign key

Create a foreign key relation on TabLineItemOfSale, name it TabSale, table property as TabSale, RelatedTableRole as masterSale and CreateNavigationPropertyMethods as Yes.
Note that the method masterSale is not a physical instance method on TabLineItemOfSale but is created run time to propagate the primary key value to the foreign key.

Below text is copied from MSDN link.
http://msdn.microsoft.com/en-us/library/hh803130.aspx

When you set the CreateNavigationPropertyMethods property to Yes on a table relation, the system generates navigation methods for the table buffer class. A navigation method links two table buffer instances by their foreign key relationship. The UnitOfWork class is one area where this navigation linkage is used.
The name for a navigation method is copied from the value of the RelatedTableRole property on the table relation. This is true when the RelatedTableRole value is set explicitly in the Properties window, and when the RelatedTableRole value is generated by setting the UseDefaultRoleNames to Yes.

2. Create a class UnitOfWorkEg with a server static method runUnitOfWorkEg and paste below code in that.

server static public void runUnitOfWorkEg()
{
    UnitOfWork uow = new UnitOfWork();
    TabSale tSale; // Buffer for parent table.
    TabLineItemOfSale tLineIos; // Buffer for child table.
    int64 i64MasterSaleRecIdFky;


    // Delete all rows, without using UoW.
    delete_from tLineIos;
    delete_from tSale;
    // Prepare a parent row for insert.
    // We let the system assign a RecId value, the primary key.
    tSale.SaleName = "Big";
    tSale.SaleComment = "A row in the parent table.";

    // Prepare a child row for insert.
    // Again, we let the system assign a RecId value, the primary key.
    // We also let the system assign SaleRecIdFky foreign key value!
    tLineIos.LiosName = "Chair";
    tLineIos.LiosComment = "To sit in.";
    // Method name is the RelatedTableRole property value.
    tLineIos.masterSale(tSale);
    // Prepare the UoW to do the inserts.

    uow.insertOnSaveChanges(tSale);
    uow.insertOnSaveChanges(tLineIos);
    // Before saving changes, prepare more inserts.
    // Add a second child to the current parent record.
    tLineIos.LiosName = "Desk";
    tLineIos.LiosComment = "To work at.";
    tLineIos.masterSale(tSale);
    uow.insertOnSaveChanges(tLineIos);
    // Add a second pair of parent + child records.
    tSale.SaleName = "Small";
    tSale.SaleComment = "Another row in the parent table.";
    tLineIos.LiosName = "Shirt";
    tLineIos.LiosComment = "To wear.";
    tLineIos.masterSale(tSale);
    uow.insertOnSaveChanges(tSale);
    uow.insertOnSaveChanges(tLineIos);
    // Make the changes to the SQL database, and commit.
    uow.saveChanges();

    //------------------------------------
    // Read the newly inserted child row.
    // Use optimistic concurrency, in case OccEnabled=No on the table.
    select optimisticLock
    LiosComment, MasterSaleRecIdFky
    from tLineIos
    where tLineIos.LiosName == "Desk";
    i64MasterSaleRecIdFky = tLineIos.MasterSaleRecIdFky;
    tLineIos.LiosComment = tLineIos.LiosComment + " Appended.";
    // Prepare the UoW to do the update. Then update.
    uow.updateonSaveChanges(tLineIos);
    uow.saveChanges();
    // All changes are complete. Display the results.
    tLineIos = null;
    tSale = null;

    select LiosName, LiosComment, RecId, MasterSaleRecIdFky
    from tLineIos
    where tLineIos.LiosName == "Desk";

    select SaleName, SaleComment, RecId
    from tSale
    where tSale.RecId == i64MasterSaleRecIdFky;
    // Display the parent RecId and the matching child foreign key.
    info(strFmt("TabSale: RecId=%1 , SaleName=%2",
    tSale.RecId, tSale.SaleName));
    info(strFmt("TabLineItemOfSale: MasterSaleRecIdFky=%1 , LiosName=%2 , RecId=%3 , LiosComment=%4",
    tLineIos.MasterSaleRecIdFky, tLineIos.LiosName, tLineIos.RecId, tLineIos.LiosComment));
}

3. Create a job UnitOfWorkExample with below code.

static void UnitOfWorkExample(Args _args)
{
    UnitOfWorkEg::runUnitOfWorkEg();
}

4. Run the job and see the output.

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.