Translating enumerations to T-SQL strings

If you are anything like me, you are spending a lot of time designing querys to transfer data from your ERP system to a data warehouse, in Dynamics AX a lot of the data is represented by enumerations, now that kind of data does not look very nice and user friendly in a data warehouse, to translate them in your T-SQL query I use this simple script to get the labels for each enumeration index. Simply replace the “PriceType” string with any enum in the system.

static void GAB_PrintEnumValues(Args _args)
{
    DictEnum DEnum;
    int      i;
    ;

    DEnum = new DictEnum(enumName2Id("PriceType"));
    for (i=0; i < DEnum.values(); i++)
    {
        info("when PostingType  = " + int2str(DEnum.index2Value  (i)) + "
        then  \'" + DEnum.index2Label(i) + "\'");
    }
}

The outcome should look like this, simply right click and paste this into your query window.

Parsing XML files to Excel or webpage

I found myself needed to go over a large number of XML files, to check the content. I love XML, it's simple structure and flexibility but you do not want to use XML to browse through thousands of rows of data, the structure simple does not allow that. So what do you do? I wrote a very simple program that loops over all files in a directory, opens the files and outputs the key data in a table format, either to Excel or to a web page. The result is easy to read, easy to summarize and analyze.

Write this in a windows form, web form or even an CMD application.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Xml;

using System.IO;

using System.Drawing;

using Microsoft.Office.Interop.Excel;

namespace ParseXMLFiles

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            ParseXML();

            ParseXMLToExcel();

        }

        public void ParseXMLToExcel()

        {  

            int NoOfFiles = 1;

            int NoOfNodes = 1;

            string folder = "\\\\euex1app11\\data\\BaseLogistics\\SOPackingSlip\\Original\\";

            string[] files = Directory.GetFiles(folder, "*.xml");

            Microsoft.Office.Interop.Excel.Application app = new Application();        

Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(System.Reflection.Missing.Value);

Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.Sheets.Add(System.Reflection.Missing.Value);

            sheet.Cells[1, 1] = "Rec ID";

            sheet.Cells[1, 2] = "File";

            sheet.Cells[1, 3] = "Item ID";

            sheet.Cells[1, 4] = "Sales ID";

            sheet.Cells[1, 5] = "Quantity";

            NoOfNodes++;

            foreach (string file in files)

            {

                XmlDocument doc = new XmlDocument();

                doc.Load(file);

                XmlNode root = doc.DocumentElement;

                XmlNodeList node = root.SelectNodes("SalesTable/SalesLine/Fields");

                foreach (XmlNode nodes in node)

                {                

                    sheet.Cells[NoOfNodes, 1] = nodes["RecId"].InnerText;                

                    sheet.Cells[NoOfNodes, 2] = file;                

                    sheet.Cells[NoOfNodes, 3] = nodes["ItemId"].InnerText;                

                    sheet.Cells[NoOfNodes, 4] = nodes["SalesId"].InnerText;                

                    sheet.Cells[NoOfNodes, 5] = nodes["InventDeliverNow"].InnerText;

                    NoOfNodes++;

                }

                NoOfFiles++;

            }

            app.Visible = true;

        }    

               

        public void ParseXML()

        {

            TableRow row;

            TableCell cell;

            TableCell cell1;

            TableCell cell2;

            TableCell cell3;

            TableCell cell4;

            int NoOfFiles = 1;

            int NoOfNodes = 1;

            string folder = "\\\\euex1app11\\data\\BaseLogistics\\SOPackingSlip\\Original\\";

            string[] files = Directory.GetFiles(folder, "*.xml");

                       

            NoOfNodes++;

            foreach (string file in files)

            {

                XmlDocument doc = new XmlDocument();

                doc.Load(file);

                XmlNode root = doc.DocumentElement;

                XmlNodeList node = root.SelectNodes("SalesTable/SalesLine/Fields");

               

                foreach (XmlNode nodes in node)

                {

                    row = new TableRow();

                    cell = new TableCell();

                    cell1 = new TableCell();

                    cell2 = new TableCell();

                    cell3 = new TableCell();

                    cell4 = new TableCell();

                    cell.Text = nodes["RecId"].InnerText;                   

                    cell1.Text = file;                   

                    cell2.Text = nodes["ItemId"].InnerText;                   

                    cell3.Text = nodes["SalesId"].InnerText;                   

                    cell4.Text = nodes["InventDeliverNow"].InnerText;                   

                    row.Cells.Add(cell);

                    row.Cells.Add(cell1);

                    row.Cells.Add(cell2);

                    row.Cells.Add(cell3);

                    row.Cells.Add(cell4);

                    Table1.Rows.Add(row);

                    NoOfNodes++;

                }

                NoOfFiles++;

            }

           

        }      

    }

}

Suppressing nodes in BizTalk mapper

I was recently presented with a challnage in XML schema translation. The source schema had a structure like so:

<message>

  <Header>

    <SomeHeaderdata></SomeHeaderdata>

    <SomeMoreheaderData></SomeMoreheaderData>

  </Header>

  <Line>

    <LineDetails>

      <SomeLineData></SomeLineData>

      <LineType></LineType>

      <Amount></Amount>

    </LineDetails>

    <LineDetails>

      <SomeLineData></SomeLineData>

      <LineType></LineType>

      <Amount></Amount>

    </LineDetails>

  </Line>

</message>

My destination schema was more flat, in fact I needed to be sure only one line would be translated, based on the line type.

Destionation schema:

<InboundTransportCostSONew>

  <SalesTable>

    <Fields>

      <SalesId>seuI18316</SalesId>

    </Fields>

    <SalesLine>

      <Fields>

        <ShipOrderId>50848</ShipOrderId>

        <TransportCost>133.99</TransportCost>

        <SalesId>seuI18316</SalesId>

      </Fields>

    </SalesLine>

  </SalesTable>

</InboundTransportCostSONew>

The solution is to use the Value mapping(Flattening) functoid in the BizTalk mapper.

The script functiod check the value of the Line type, only if it matches a certain criteria, will it return true, each element is then mapped to the value mapping functiod, wich by desgin will only return it's value if the first input parameter (the script, returning either true or false).

Connectivity studio for MS DAX 4.0 Part II

Somewhat late but here is next part of this

To post a packing slip in AX there are two main ways, either post it manually by clicking on button and filling in forms or writing X++ code. In Con Studio, each action can be overwritten with a custom handler The custom handler inherits the standard (part of Con studio) handler, like so;

class GAB_PackingSlipHandlerPO extends AppDataDocumentHandler
{
    PurchFormLetter PurchFormLetter;
    PurchLine purchLine;
    PurchTable purchTable;
    boolean success;
}

In this case, we need to read an XML file and based on some values in the file, update the purchase order and then post a packing slip.  In theory there are a few different way to do this, in the end I decided to simply update the Receive Now quantity and then post the packing slip with the quantity (PurchUpdate::ReceiveNow). This will post the packing slip and update the inventory accordingly

First we need to set the Receive now; this is done in the BlockRead method.

boolean blockRead(AppDataBlock blockDefinition, AppEntity theEntity, AppBlock theBlock)
{
  boolean ret;
  int currentLine;
  str textToLog;
  ;
  success = false;


if(theEntity.getRecord().TableId == tablenum(PurchLine))
{
  purchLine = theEntity.getRecord();

  try
  {
    purchLine.PurchReceivedNow = purchLine.PurchReceivedNow + str2int   (theBlock.value("InventReceivedNow"));
    purchLine.setInventReceivedNow();
    purchLine.update();
    success = true;
  }
  catch(Exception::Error)
  {
    for(currentLine = 1; currentLine <= infolog.line(); currentLine++)
    {
      textToLog += 'n' + infolog.text(currentLine);
    }
    this.writeErrorToLog(purchLine, textToLog);
    success = false;
    return false;
  }
}
else
{
  ret = super(blockDefinition, theEntity, theBlock);
}

if(theEntity.getRecord().TableId == tablenum(PurchTable))
{
  purchTable = theEntity.getRecord();
}return ret;
}

This method will iterate over all occurrences of the transaction level, set on the file. In this case that is the PurchTable (can be any table level defined in the file or the whole XML document)

After the entire file have been read, we need to post the packing slip, based on the now updated quantities. This is done in the TransactionCommit method.

void TransactionCommit(AppDataBlock blockDefinition, AppEntity theEntity, AppBlock theBlock)
{
    PurchInternalPackingSlipId num;
    str textToLog;
    int currentLine;
    InventTable inventTable;
    ;
    super(blockDefinition, theEntity, theBlock);
    num = NumberSeq::newGetNum(PurchParameters::numRefPurchPackingSlipId(),true).num();
    purchFormLetter = purchFormLetter::construct(DocumentStatus::PackingSlip);
    purchLine = PurchLine::findRecId(theBlock.value("RecId"));
    if(success == true)
    {
        try
        {
            ttsbegin;
            purchFormLetter.update(purchTable, num, Today(),PurchUpdate::ReceiveNow);
            ttscommit;
        }
        catch(Exception::Error)
        {
            success = false;
            for(currentLine = 1; currentLine <= infolog.line(); currentLine++)
            {
                textToLog += 'n' + infolog.text(currentLine);
            }
            this.writeErrorToLog(purchLine, textToLog);
            purchLine.PurchReceivedNow = 0;
            purchLine.setInventReceivedNow();
        }
    }

}
This should give you a good idea on how to use the Con Studio. There are more details but this is the basic logic required to get it working.