From Ax 2012

How to add a range or data source to a form data source

Adding a second datasource with X++, or a range to a form datasource should be simple, yet today I spent ages trying strange things with multiple records. In the end it took me, two senior developers and a existing example in Dynamics AX to get to this simple solution. It might look silly, but I am definitely documenting this one for future reference!

I used to do the same thing by adding code in the form’s class declaration and the data source’s init and executeQuery methods. That is way too complex. Just add the code below to the data source’s init. Also, make sure you do this after the super() call, otherwise this query will not be initialized.

Add a range to a form
Suppose you have a form, with a PurchLine datasource added as a node in the AOT:


If you only want to see purchase lines that are linked to open orders, then you need to add a range with PurchStatus equal to Backorder. You create this range in the data source’s init method:

  1. public void init()
  2. {
  3.     QueryBuildDataSource        qbdsPurchLine;
  5.     super();
  7.     qbdsPurchLine = this.query().dataSourceTable(tableNum(PurchLine));
  8.     qbdsPurchLine.addRange(fieldnum(PurchLine, PurchStatus)).value(queryvalue(PurchStatus::Backorder));
  9. }

Add an additional datasource to the form
Now suppose you want to filter the PurchLine based on some criteria on the PurchTable. For example, you only want to see lines from derived intercompany orders. (This is just an example, PurchLine actually has it’s own intercompany origin field.) You could add an additional PurchTable datasource to the form, and join the datasources in the form properties. Every easier is adding the following to the init of the PurchLine datasource:

  1. public void init()
  2. {
  3.     QueryBuildDataSource        qbdsPurchLine;
  4.     QueryBuildDataSource        qbdsPurchTable;
  6.     super();
  8.     qbdsPurchLine = this.query().dataSourceTable(tableNum(PurchLine));
  9.     qbdsPurchLine.addRange(fieldnum(PurchLine, PurchStatus)).value(queryvalue(PurchStatus::Backorder));
  11.     qbdsPurchTable = qbdsPurchLine.addDataSource(tableNum(PurchTable));
  12.     qbdsPurchTable.addLink(fieldNum(PurchTable, PurchId), fieldNum(PurchLine, PurchId));
  13.     qbdsPurchTable.addRange(fieldnum(PurchTable, InterCompanyOrigin)).value(queryValue(InterCompanyOrigin::Derived));
  14. }

You can declare as many QueryBuildDataSource variables as you need, and add them like the PurchTable above.

Errors in AxCompileAll.html after AxBuild compile

When you do a full compile with AxBuild, a AxCompileAll.html log file is created with all the compile errors and warnings that you would normally see in the Compiler output window. Normally it looks something like this in a browser:
Compiler error log

If your log file looks like the one above, you can simply go to the 3 errors in the AOT, investigate and fix the problems. Unfortunately the compiler output sometimes looks like this:
Compiler error log errors

You could investigate all 100+ errors listed, but the frustrating thing is that these errors are often not real errors. For some reason AxBuild sometimes logs a list of ‘fake’ compile errors. Luckily it is easy and quick to solve the fake errors by recompiling the list in the AX client.

To recompile AxCompileAll.html do the following:

  1. Import AxCompileAll.html into the compiler window by clicking on the double arrow next to the Setup button:
  2. Compiller output import

  3. Click on Recompile all, also found under the double arrow next to the Setup button:
  4. Compiller output recompile

Since it is only compiling the classes with errors, this is much faster that recompiling the entire AOT. If all goes well, all ‘fake’ errors should be solved and the compiler output window should have no or few errors.

The AxCompileAll.html is usually found at C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\Log\ on the AOS.

UPDATE 26 December 2014:
I just learned an even more useful way to recompile your errors in AxCompileAll.html in a post by Kenny Saelen on Axilicious. The basics are:

  • Import AxCompileAll.html
  • In the compiler windows, select create project
  • Compile the project

In Kenny Saelen’s post, he has a tool that does these steps automatically.

Workbook.ExportAsFixedFormat in Microsoft Dynamics AX

How to export a file to PDF in Dynamics AX

Microsoft has an extremely useful .NET method named ExportAsFixedFormat() for exporting office files to PDF. For example, you can invoke the ExportAsFixedFormat() method on an Excel Workbook object and a PDF file is then created in the folder of your choice.

Unfortunately the documentation on how to use this in function in Dynamics AX is a little lacking. In the .NET documentation most of the parameters are described as optional, while in Dynamics AX you have to pass all the parameters.

The last parameter is a pointer to the FixedFormatExt class. It is not necessary to pass an actual value, but the method does not accept null. Saveen Reddy describes in this post how to pass an empty value in C#. In Dynamics AX is even more tricky, since you do not have access to System.Reflection.Missing.Value. You need to create an object like this:

  1.     System.Type                  type           = System.Type::GetType("System.Reflection.Missing");
  2.     System.Reflection.FieldInfo  info           = type.GetField("Value");
  3.     System.Object                missingVariant = info.GetValue(null);

The final job is below, notice:

  1. All the parameters are passed, none are optional (line 32)
  2. The last parameter is a ‘null’ object that uses System.Reflection.Missing (lines 20-22)
  3. The method is in a try and is followed by a catch for a CLRError. This makes debugging much easier since it displays the .NET error which would otherwise not have been shown (lines 42-54 )
  4. I have used System.Type since I was worried AX variables might confuse .NET. As far as I know you could use normal AX str and int variables (lines 13-18)

  1. static void ExcelExportPDFAsFixedFormat(Args _args)
  2. {
  3.     #File
  4.     Microsoft.Office.Interop.Excel.Application  xlApp;
  5.     Microsoft.Office.Interop.Excel.Workbooks    xlWBS;
  6.     Microsoft.Office.Interop.Excel.Workbook     xlWB;
  8.     System.Exception                            exception;
  10.     Filename                                    filename =  @'C:\PDF\test3.pdf';
  11.     Filename                                    excelFileName = @'C:\PDF\TR1-000058.xlsx';
  13.     System.Int32                                toPage                  = 1;
  14.     System.Int32                                fromPage                = 1;
  15.     System.Boolean                              OpenAfterPublish        = false;
  16.     System.Boolean                              IncludeDocProperties    = true;
  17.     System.Boolean                              IgnorePrintAreas        = false;
  18.     System.String                               fileNameDotNet          = filename;
  20.     System.Type                                 type = System.Type::GetType("System.Reflection.Missing");
  21.     System.Reflection.FieldInfo                 info = type.GetField("Value");
  22.     System.Object                               missingVariant = info.GetValue(null);
  24.     new InteropPermission(InteropKind::ClrInterop).assert();
  26.     xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
  27.     xlWBs  = xlApp.get_Workbooks();
  28.     xlWB   = xlWBS.Add(excelFileName);
  30.     try
  31.     {
  32.         xlWB.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType::xlTypePDF,
  33.                                  fileNameDotNet,
  34.                                  Microsoft.Office.Interop.Excel.XlFixedFormatQuality::xlQualityStandard,
  35.                                  IncludeDocProperties,
  36.                                  IgnorePrintAreas,
  37.                                  fromPage,
  38.                                  toPage,
  39.                                  OpenAfterPublish,
  40.                                  missingVariant);
  41.     }
  42.     catch(Exception::CLRError)
  43.     {
  44.         exception = ClrInterOp::getLastException();
  45.         while (exception)
  46.         {
  47.             error(exception.get_Message());
  48.             exception = exception.get_InnerException();
  49.         }
  50.     }
  51.     catch
  52.     {
  53.         error("An unknown exception has occurred");
  54.     }
  55.     CodeAccessPermission::revertAssert();
  56. }

It is also possible to create PDF’s form Word Documents. I’ll post an example if I write a job to do that in the future.

UPDATE 26 December 2014:
A senior colleague wanted to achieve something similar and I offered him this job. He pointed out that an instance of Excel is opened every time the job is run, but never closed. You can see this clearly in the Task Manager.

If you want to use this job, experiment with adding xlWB.Close and/or xlApp.Quit, and keep an eye op the running applications in the Task Manager.

How to generate a list of field names and field properties

Today I quickly wanted to see which fields in the PurchLine table are mandatory. Purchline is a large table, so instead of checking the properties of each field, I quickly wrote a job that displays an info log of all the mandatory fields. I used the DictClass example script from Microsoft, and adapted it so that it generates a list with all the fields in the table.

This job can be easily changed to display the fields of other tables, simply change the tableName variable. It can also be changed to display other properties like ‘Allows edit on create’. For this you need to change the #DBF_Mandatory flag to another flag defined in the macro:
DictField Macro

This script uses a number of interesting things like macros, dictField and dictTable. I’ll write separate posts for these topics soon. Until then, here is a simple jobs that lists all the fields in a table and their properties:

  1. static void getMandatoryFields(Args _args)
  2. {
  3.    #macrolib.dictfield  
  4.     DictField   dictField;
  5.     DictTable   dictTable;    
  6.     FieldId     fieldId;
  7.     int         nFlags;
  8.     TableName   tableName = "PurchLine";
  9.     ;
  10.     dictTable   = new dictTable(tableName2Id(tableName));
  11.     fieldId     = dictTable.fieldNext(0);    
  12.     while (fieldId)
  13.     {
  14.         dictField = new DictField(tableName2Id(tableName), fieldId);
  15.         if (dictField)
  16.         {
  17.             nFlags = dictField.flags();      
  18.             if (bitTest(nFlags, #DBF_MANDATORY))
  19.             {
  20.                 info(strFmt("The field %1 is mandatory.",;
  21.             }
  22.             else
  23.             {
  24.                 //info(strFmt("The field %1 is not mandatory.",;
  25.             }
  26.         }        
  27.         fieldId = dictTable.fieldNext(fieldId);
  28.     }
  29. }

The resulting info log should look something like this:
Mandatory purchline fields