COMVariant type to work with Excel in AX

To avoid eventual issues while working with Excel objects in X++, do not forget about COMVariant type in AX!

Lets say we output some information directly to an Excel file and want to change the current row height based on the number of strings on notes.

...
COM             rng;
COMVariant      rowHeightVariant;
real            rowHeight;
...

rng = wks.Range(strFmt("A%1", curRow));   cell.Value2(_route.OprNum);
// to keep the current row height
rowHeightVariant = rng.RowHeight();
rowHeight = rowHeightVariant.double();
...
//to adjust the row height accordingly to the number of strings in notes
rng.RowHeight(rowHeight*this.countLines(notes));
...

Unfortunately, I had spent so much time trying to understand the reason of errors until I found this posting. Thank you Max!

RouteId Lookup

In AX2012 we cannot input a new route number manually because the type RouteId has a direct reference to RouteTable.

To solve this issue I created a new extended type WmpRouteIdBase and a related lookup form WmpRouteIdLookup similarly to BOMIdBase type.

The final step is to rewrite promptCreateRoute method on RouteTable:

if (! inventTable.inventItemType().canHaveRoutes())
        throw error(strfmt("@SYS22874",inventTable.ItemId));

    dialog = new Dialog("@SYS25123");
    if (manual)
        // to allow to input the route id manually
        //-->
        fieldNumber = dialog.addField(extendedtypestr(WmpRouteIdBase),"@SYS21709");
        //<--
    fieldName       = dialog.addField(extendedtypestr(Name),"@SYS6303","@SYS50977");

The whole project you download from here.

How to make a temporary instance of a database table to be shown on the form

This short code shows how to work with a temporary table without creating it in AOT.

// how to use temporary table
// method Init of the form
public void init()
{
    WmpInventNutrition        nut; // regular table in AOT
    WmpInventNutrition        tmp; // instead of creating in AOT a temporary table
                                   // we can create a temporary instance of the preivous regular table
    ;
     super();

    // here we make it temporary;
    // it will disappear from memory when loses the scope
    // in other words, when we close the form
    tmp.setTmp();

    // simply selecting some of records from the regular table
    while select nut
        where nut.ItemId like '_wmp*'
    {
        // and putting them in the temporary one
        tmp.data(nut);
        tmp.doInsert();
    }
    // finally to show them on the form
    // we set the form data source to the temporary table
    wmpInventNutrition.setTmp();
    wmpInventNutrition.setTmpData(tmp);
}

Alternatively, if you know exactly how the field match, it will be faster to use insert_recordset:

 // simply selecting some of records from the regular table
    tmp.skipDataMethods();
    insert_recordSet tmp (itemid) select ItemId from nut where nut.ItemId like '_wmp*';
    

How to go to the object from Infolog

Usually, it is more useful if the user can go directly to the object from an Infolog line.

For example, we need to see all the BOM without any active version. By the way, this is a good example, too, for what we cannot do by means of Advanced Filtre but quickly by coding a short job.

static void SIBOMwithNoActiveVersion(Args _args)
{
    BOMTable    bomTable;
    BOMVersion  bomVersion;
    int         nmbr, i;
    boolean     printOnly = false;
    ;

    SetPrefix('BOMs without any active version');
    Nmbr = 0;

    while select bomTable
    notexists join bomVersion
    where bomVersion.Active == NoYes::Yes
    && bomVersion.BOMId == bomTable.BOMId
    {
        nmbr++;
        info(strfmt("%1 %2, %3, %4, %5, %6, %7",
                                          nmbr,
                                          bomTable.BOMId,
                                          bomTable.Name,
                                          bomVersion.ItemId,
                                          bomVersion.Name,
                                          bomVersion.Active
                                          ),"",
        SysInfoAction_TableField::newBufferField(bomTable, fieldnum(bomTable, BOMId)));

    }

}

Now the user can opt for Show from the context menu or just double-click on the interesting line to open the related form.

Universal Field Changer new version for Microsoft Dynamics AX2012

So, I took my old project from the case just to add a new feature: this time I would like to get all the table fields with their labels in the user's language.

Frankly seaking I was going to get it in a grid in order to export to Excel; but unfortunately I did not find a fast way to show my temporary table. Finally, I just use InfoLog in the comma separated format that can be used lately to open in Excel.

Enjoy, anyway!

Description:

Universal Field Changer class for Microsoft Dynamics AX2012:

- collects all the fields from all the tables in AOT in temporary tables;
- makes possible to change any values using filtres by table and field names and existing values;
- provides access to SQL query string;
- prints the field lists with labels in user's language;
- creates dynamically all the form controls and uses method overloading and can be used as a tutorial;

Dates In An Extended Query Range

Let's say we need to range all the BOM versions that are active as of today. For this purpose we can use an extended range in the query.

{
    QueryBuildRange qbr;
    ;
    if(!_args.record())
        return;
    // there is an active caller!
    switch (_args.record().TableId)
    {
        case tablenum(InventTable):
            inventTable = element.args().record();
            this.query().dataSourceTable(tablenum(InventTable)).addRange(fieldnum(InventTable, ItemId)).value(inventTable.ItemId);
            qbr = this.query().dataSourceTable(tablenum(BOMVersion)).addRange(fieldnum(BOMVersion, RecId));
            qbr.value('(fromDate <= '+date2StrXpp(today())+') && (toDate >= '+date2StrXpp(today())+')');
            break;

The final SQL query will look like this (a fragment):

= BOMVersion.ItemId AND ((Active = 1)) AND (((fromDate <=13\12\2011) && (toDate >= 13\12\2011))) JOIN * FROM BOM(BOM_1) ORDER BY BOM.LineNum ASC ON BOMVersion.BOMId = BOM.BOMId

The idea was taken from the AX forum (in Russian).

Two tricks about stopping AX service via PowerShell

Generally speaking, it is true for all Windows services but my particular example is about stopping Microsoft Dynamics AX service (AOS) via PowerShell.

As recommended in Deploying customizations across Microsoft Dynamics AX environments white paper, before importing a metadata model store into the target environment, one must stop all AOS instances in it.

First trick is on how to provide the correct name of the service supposed to be stopped.



If you try to use the service name you see in Services, you will fail.

PS C:\Windows\system> Set-Service -name AOS60$01 -status stopped
Set-Service : Service AOS60 was not found on computer '.'.
At line:1 char:12
+ Set-Service  -name AOS60$01 -status stopped + CategoryInfo : ObjectNotFound: (.:String) [Set-Service], Invali dOperationException + FullyQualifiedErrorId : InvalidOperationException,Microsoft.PowerShell.C ommands.SetServiceCommand 

It is evident that the problem is in $ sign. To fix it you can simply quoted it with "'" (not with "!).

PS C:\Windows\system32> Set-Service -name 'AOS60$01' -status stopped

Unfortunately, this command does not work anyway because of an error:

Set-Service : Cannot stop service 'Microsoft Dynamics AX Object Server 6.0$01-a
x2012_std (AOS60$01)' because it is dependent on other services.
At line:1 char:12
+ Set-Service  -name 'AOS60$01' -status stopped + CategoryInfo : InvalidOperation: (System.ServiceProcess.Service Controller:ServiceController) [Set-Service], ServiceCommandException + FullyQualifiedErrorId : ServiceIsDependentOnNoForce,Microsoft.PowerShell .Commands.SetServiceCommand 

Nevertheless, you can stop the service as usual without any issue. I do not why it does not work but I would suggest that one use another commands to stop and start the service, namely:

PS C:\Windows\system32> Stop-Service 'AOS60$01'
WARNING: Waiting for service 'Microsoft Dynamics AX Object Server
6.0$01-ax2012_std (AOS60$01)' to finish stopping...

PS C:\Windows\system32> Start-Service 'AOS60$01'
WARNING: Waiting for service 'Microsoft Dynamics AX Object Server
6.0$01-ax2012_std (AOS60$01)' to finish starting...

It takes its time, be patient!