Wednesday, September 14, 2011

Update Query Range at Runtime in Dynamics AX

     In Dynamics AX we can update the Query Range at run-time in code,  This is similar to "Filter by Grid" feature except that we are handling this in code. For example my user asked me to provide a Button on Sales LineItems which would toggle between displaying "Current SalesLineItems" VS "All SalesLineItems". By Current SalesLineItems i mean displaying LineItems with more than zero quantity, other addendum to original requirement is to preserve the existing user filters on the form.

In this image you can see a SalesOrder with 3 LineItems (notice that one LineItem has zero quantity), blue arrow pointing to new button("Show Only Current LineItems"):


If the User clicks this new button then :
a) LineItems with zero quantity will be filtered out, in the image below you can see only 2 LineItems
b) the label of the button will be updated to "Show All LineItems" , see the following image:
      
      
Let's start DAXing now - All i need to update for this requirement is SalesTable form:
1.  Add a new boolean variable to ClassDeclaration method
     boolean showOnlyCurrentLineItems;

2. If the user wants to display only current LineItems by default when the form loads, then initialize our boolean variable to true in the Form->init()
    showOnlyCurrentLineItems = true;

3. Now, let's add our query range just before the query is exceuted by overriding the SalesLine DataSource's executeQuery() method (SalesTable -> DataSources->SalesLine->Methods->executeQuery() ) .
Before the super()  method is called we need to add our range to the SalesLine query, AX form maintains two instances of the query objects at run-time (reference) :
 a)  formDataSource.query()  - original datasource query added at the design time to the form's datasource node
 b)  formDataSource.queryRun().query()   - the query originated from a) +  filters applied by the User
So it is better to update both the queries to cover all the scenarios. Here is the code to write within the executeQuery():


public void executeQuery()
{
  // instances for query type b)
 Query  queryForSalesLine;
 QueryBuildDataSource qbdsForSalesLine;
 QueryBuildRange qbrForSalesLine; 

 // instances for Query type a)
  Query  origQueryForsalesLine;
  QueryBuildDataSource origQbdsForSalesLine;
  QueryBuildRange origQbrForSalesLine;
   
  boolean updateOrigQuery;
 
  // check if query type b) exists, if so use it otherwise use Query type a)
  if(salesLine_DS !=null && salesLine_DS.queryRun() != null && salesLine_DS.queryRun().query() != null)
   {
        queryForSalesLine = salesLine_DS.queryRun().query();
        updateOrigQuery = true;
   }
   else
        queryForSalesLine = salesLine_DS.query();
         
   qbdsForSalesLine = queryForSalesLine.dataSourceName("SalesLine");
   qbdsForSalesLine.clearRange(fieldnum(SalesLine, SalesQty));
   qbrForSalesLine = qbdsForSalesLine.addRange(fieldnum(SalesLine,SalesQty));
 
   // update both the queries in both the cases
    if(showOnlyCurrentLineItems)  // this is initialized to true and we will update this variable variable thru a button
    {
        qbrForSalesLine.value(">0");
        if(updateOrigQuery)
         {
           origQbdsForSalesLine =  salesLine_ds.query().dataSourceName("SalesLine");
           origQbdsForSalesLine.clearRange(fieldnum(SalesLine, salesQty));
           origQbrForSalesLine = origQbdsForSalesLine.addRange(fieldnum(SalesLine,SalesQty));
           origQbrForSalesLine.value(">0");
         }
    }
    else
     {
       qbrForSalesLine.value("*");
       if(updateOrigQuery)
         {
           origQbdsForSalesLine =  salesLine_ds.query().dataSourceName("SalesLine");
           origQbdsForSalesLine.clearRange(fieldnum(SalesLine, salesQty));
           origQbrForSalesLine = origQbdsForSalesLine.addRange(fieldnum(SalesLine,SalesQty));
           origQbrForSalesLine.value("*");
         }
     }
 
    super();
} 
 
4. Add a new Button to the SalesLine section which will allow the User to toggle the Query's range
a)  Add a new Button named "UpdateQueryButton" at SalesTable Form->Designs->[Group:Line] -> [ButtonGroup: ButtonLine]
b) Set its Text property to "Show All Line Items"
c) Override the button's clicked event method with this code:


void clicked()
{
    super();
    if(showOnlyCurrentLineItems)
    {
        showOnlyCurrentLineItems = false;
        this.text("Show Only Current LineItems");  // Update button's text
    }
    else
    {
        showOnlyCurrentLineItems = true;
        this.text("Show All LineItems"); // Update button's text
    }
    
    salesLine_DS.research(true); // this will call executeQuery method that we overrode earlier
}


That is it, you can verify that it preserves the User added Filters/Ranges on the form.
Happy DAXing
-San

Monday, September 12, 2011

Strip Out HTML from Exported CRM data to Excel

    In CRM,  the exported data can have html tags in them for eg: Description field in Email Activities when exported to Excel will have whole lot of HTML tags that it would be almost impossible to clean them manually and extract the actual data. To solve this issue i have written an Add-In for Excel that would do this job.
Let's understand the problem first : In the figure below, the data reflects the exported CRM data to excel. I have highlighted the HTML tags in description column.


Once you install this new Add-In, all you would do is select Description column header (or any column header that contains HTML in its cells), goto Add-Ins tab-> Click "Clean HTML" Button. Once the process is done the program would display a message box to the user.
Image here shows Description column selction and "Clean HTML" button clicking:



Image below shows Description Column without HTML tags:



This is the code that cleans out all the HTML in a string, i.e., it takes the string with HTML tags in it and returns clean text:

public class Html2Text
    {
       private string _htmlStr = "";
       private string _textContents = "";
  
       public string GetContents(string htmlStr)
       {
           this._htmlStr = htmlStr;
           Thread t = new Thread(runInThread);
           t.TrySetApartmentState(ApartmentState.STA);
           t.Start();
           t.Join();
           return _textContents;
       }
   
       private void runInThread()
       {
           WebBrowser wb = new WebBrowser();
           wb.DocumentText = "Cleaning HTML";
           HtmlDocument htmlDoc = wb.Document.OpenNew(true);
           htmlDoc.Write(_htmlStr);
    
           HtmlElementCollection htmlColl =  htmlDoc.All;
           List<string> indvidualTextColl = new List<string>();
   
           for (int i = 0; i < htmlColl.Count; i++)
           {
               HtmlElement currElement =  htmlColl[i];
               if(currElement.TagName.ToUpper() == "P" &&
                               !string.IsNullOrEmpty(currElement.InnerText))
               {
                   indvidualTextColl.Add(currElement.InnerText);
               }
           }
    
           this._textContents = string.Join("\n", indvidualTextColl.ToArray());
       }
    }


Now, let's create the Excel AddIn project and use the above class to strip off HTML:
1. Create a project using VS 2010's Excel 2010 Add-In template and name it ExcelAddIn
Note: we are not going to touch ThisAddIn.cs
2. Add a new CS file and add the above Html2Text class code
3. Add New Item -> Ribbon (Visual Designer)
4. Add a button to the Visual designer with the following properties:
Name:  button1 , Label: Clean HTML
5. Double click the button to goto Code behind file, add the following code to the click event:
Note: In CRM i have noticed that mostly the data is within <P> tag, so i am extracting the data from within <P> tag.

try
  {
     Excel.Worksheet activeWS = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;

     int numOfRows = activeWS.UsedRange.EntireRow.Count;
     
     Excel.Range activeCellRange = Globals.ThisAddIn.Application.ActiveCell;
     int currRow = activeCellRange.Row;  // getting the number of rows for Description column
     int currColl = activeCellRange.Column;
     Html2Text obj = new Html2Text();  // instantiate Html2Text object 
     string htmlStr = "";
     string textContents = ""; 
     
     // looping thru all the rows of Description column and fixing the data
     for (int i = 1; i < numOfRows; i++)
     {
         Excel.Range currCell = (Excel.Range)activeWS.Cells[i + 1, currColl];
         //MessageBox.Show(currCell.Value.ToString());                
           if (currCell.Value != null && currCell.Value.ToString() != "")
            {
                  htmlStr = currCell.Value.ToString();
                  if (htmlStr.Contains("<htm") || htmlStr.Contains("<HTML") || htmlStr.Contains("<p>") ||          
                          htmlStr.Contains("<P>") || htmlStr.Contains("<!DOCTYPE") ||
                            htmlStr.Contains("<!doctype") || htmlStr.Contains("<FONT") || 
                             htmlStr.Contains("<font"))
                        { }
                   else
                        continue;
                }
            else
                 continue;
   
            textContents = obj.GetContents(htmlStr);
            currCell.Value2 = textContents;
       }
     
       MessageBox.Show("HTML cleaning is done.", "Office Extension");
   }

catch (Exception ex)
   {
        MessageBox.Show("Error: " + ex.Message);
    }

6. Now just build this project , VS will generate dll, pdb and a VSTO file. Just double click the VSTO file to install this new Office AddIn
7. Open your Excel 2010 or 2007 , verify the appearance of this new button within Ribbon's AddIn Tab and test its functionality

Note: User has to manually save the changes after running this AddIn, this is done intentionally so that User can verify the data and save.
Hope you found this article useful
-San.