Using update_recordset with Views

We know that update_recordset statements should be used in place of while select forupdate wherever we can. We get the benefit of time saved by reduced server-sql trips.

Lets discuss a unique scenario.
Imagine you have to update table A with a status based on count of a particular column on another related table B. While select would look like this.

   ttsbegin;  
   while select forupdate stagingImportTable  
   exists join stagingPurchImport  
   where stagingPurchImport.ImportId == stagingImportTable.RecId  
   exists join stagingImportQueue  
   where stagingImportQueue.ImportID == stagingImportTable.ImportId &&  
   stagingImportQueue.ToDelete  
   {  
     select count(RecId) from stagingPurchImport
       where stagingPurchImport.ImportId == stagingImportTable.RecId &&  
       !stagingPurchImport.PostedId;  
     if (stagingPurchImport.RecId != 0)  
       stagingImportTable.Status = MMSImportStatus::Incomplete;  
     else  
       stagingImportTable.Status = MMSImportStatus::Posted;  
     stagingImportTable.update();  
   }  
   ttscommit;  

Essentially we need to know the count (or any other aggregate value for that matter) of a column to decide which status needs to be set. The above statement can be very time consuming and that's where Views come to the rescue. How do we go about it? First we need a query with table B as primary datasource and range as the column we need the count of, joined by table A. See below image.

Now we create a View with the above query as datasource (just drag and drop) and add two fields. First one we call CountOfRecId, which is an aggregation of RecIds. See below. (Note: when you try adding the field to a view, by default its a string, but when you choose the actual field, the datatype changes). Second field we add (ImportID) is to assist the join in the update statement we see next.

     ttsbegin;  
     update_recordset stagingImportTable  
     setting Status = MMSImportStatus::Incomplete  
     exists join purchView  
     where purchView.CountOfRecId != 0 &&  
     purchView.ImportId == stagingImportTable.ImportId  
     exists join stagingImportQueue  
     where stagingImportQueue.ImportID == stagingImportTable.ImportId &&  
     stagingImportQueue.ToDelete;  
     ttscommit;  
     ttsbegin;  
     update_recordset stagingImportTable  
     setting Status = MMSImportStatus::Posted  
     exists join stagingImportQueue  
     where stagingImportQueue.ImportID == stagingImportTable.ImportId &&  
     stagingImportQueue.ToDelete  
     notexists join purchView  
     where purchView.ImportId == stagingImportTable.ImportId;  
     ttscommit;  

Since we have leveraged the power of queries and views, we get the count super-fast, which we then use in the update_recordset statement by making a join with the View, which behaves like a table. If you browse the View, it looks like this. Note the RecId value.

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.

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.