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.

Create a New Financial Dimension

AX 2012 supports unlimited number of Financial Dimensions. System entities like Projects, Customers, Customer groups etc can be made into Dimensions. Form opens at General ledger > Setup > Financial dimensions > Financial dimensions. One option is to create a user-defined financial dimension in the Use values from field by selecting < Custom dimension >. Another way it to customize AX to include your own dimensions.
For example, one requirement i got was to have Mode of Delivery as a Financial dimension.

So what needs to be done?

Luckily, only thing required is to create a view.

1. Create a view named DimAttributeModeOfDelivery. Why such a name, little later.
2. Define its datasource name as BackingEntity.
3. Define three fields on it namely, Key(RecId), Value(Code like Id) and Name

Whats going on here?
Form DimensionDetails's run method calls Class DimensionDetails.run() method which calls a method DimensionEnabledType::getSystemDefinedDimensions()

Next call is made to DimensionEnabledType::getSystemDefinedDimensionsServer()

Here the code loops through and if an entity is a view and matches proper naming conventions (prefix DimAttribute*), considers it "dimension enabled"