Pages

Monday, May 11, 2015

Increment dates by months in X++

A question was asked in the forums, about how to increment dates by months like:
21/02/2015
21/03/2015
21/04/2015
etc.

nextMth and dateMthFwd was suggested, so I just wanted to list some options.

Consider these examples.

nextMth will be off by days after hitting uneven months:
static void TestNextMth(Args _args)
{
    date d = nextMth(31\12\2015);
    print d;
   
    d = nextMth(d);
    print d;
   
    d = nextMth(d);
    print d;
   
    d = nextMth(d);
    print d;

    pause;
}
  • 31/1/2016
  • 29/2/2016
  • 29/3/2016
  • 29/4/2016

dateMthFwd works ok:
static void TestDateMthFwd(Args _args)
{
    date d = mkDate(31, 12, 2015); 
    
    print dateMthFwd(d, 1);     
    print dateMthFwd(d, 2);
    print dateMthFwd(d, 3);
    print dateMthFwd(d, 4);    
    pause;
}
  • 31/1/2016
  • 29/2/2016
  • 31/3/2016
  • 30/4/2016

And System.DateTime.AddMonths works ok:
static void TestAddMonths(Args _args)
{
    System.DateTime dateTime = new System.DateTime(2015, 12, 31);
    
    print dateTime.AddMonths(1);
    print dateTime.AddMonths(2);    
    print dateTime.AddMonths(3);
    print dateTime.AddMonths(4);    
    pause;
}
  • 31/1/2016
  • 29/2/2016
  • 31/3/2016
  • 30/4/2016

Tuesday, May 5, 2015

Fix synchronization issue with fields in the AOT but not on SQL Server

Recently I was called in to help on an issue after installation of a third party module, where fields were in the AOT but wasn't synchronized to SQL Server. The fields also looked as they should in the SQLDictionary table.

New attempts to synchronize the database didn't create the fields on SQL. AX must have been under the impression that the fields were synchronized.

If I tried to delete any of the fields, I'd get an error from AX about it not being able to drop the fields on SQL Server, so I was kind of stuck.

Here is what I did to fix the situation:

  1. I looked up the SQL field names in SQLDictionary and created the fields manually on SQL Server. I was not too concerned with datatypes and other properties, as these fields were just created to be able to drop them from AX again.
  2. In the AOT I changed the SaveContents property of each field to No. This will make AX drop the fields from SQL Server.
  3. I synchronized.
  4. I changed SaveContents properties back to Yes. This will make AX create the fields again on SQL Server through the right synchronization process.
  5. I synchronized.
In the old days, this was something you could fix with SQL administration from the System administration menu, but the features to fix such issues have been broken  since version 4.0 of AX.

I never did figure out how the third party vendor brought themselves into this situation though. That would have been interesting enough to know.

Thursday, March 12, 2015

Endless loop: AX tries to compile form and crashes

I have tried this a couple of times now on AX 2012 R3.

I need to do major changes to a form, and during the work it is convenient for me to try and compile the form to get an idea about what I still miss to fix. Sometimes the compilation however makes the AX client crash.

The darnedest thing is that AX automatically tries to compile the same form when I restart the client, still resulting in a client crash. So what to do?

My new best friend, Set-AXModelStore -InstallMode comes to the rescue, allowing me to open the client without it starting to compile the form. See my earlier post about Set-AXModelStore -InstallMode comming to the rescue.

Wednesday, March 11, 2015

Transform AIF output to a CSV file

Start out by reading Shashi Sadasivans article series on import of CSV files with AIF: https://shashidotnet.wordpress.com/2011/04/11/dynamics-ax-2012-aif-import-csv-file-part-1-consume-web-service/. Much of the stuff you need to do for the outbound transformation is the same.

In this example I'm going to show how a Purchase Order Confirmation document can be transformed to a comma (actually semi colon in my example) separated file.

First thing you should do is to create classes to wrap to the XML schemas, so you don't have to work directly with the XML nodes. The class creation is described in Part 3 of Shashi's article series. You'll need classes for the Envelope, the Shared Types and the document in question.
You don't absolutely need to create these classes, but I prefer to work with these classes rather than working directly with the XML nodes.

With the classes in place you can create an instance of the document class (PurchRequistition.AxdPurchaseRequisition). But first you need to isolate the message part of the XML to be able to de-serialize this part of the XML into the document instance.

Here is a method I made for that:


That was the hard part to figure out. I have a gut feeling that you can do this smarter. Please comment if you know how.

I call this method in the beginning of my Transform method, and I then begin to loop over the journal, purchase headers and purchase lines. I use a StringBuilder for the output records.


I have created a method to add each field to the output string, check allowed length, remove unwanted characters and add the field separator. It's called AppendField and that's what I call in the following:


That is it. Remember to close the outgoing stream writer when done:


Here is the AppendField method:


And the GetSafeString method:


You can get the entire class, but not the XSD wrappers, here. You'll have to create the XSD wrappers yourself.

On the outbound port you could use the standard file adapter with this transformation. It will however create the output file with an XML extension. If you need an CSV extension you can't write a a new file adapter extending the standard file adapter. You'll have to override a single method to control the output file name.

Thank you Jacob Broberg for valuable input from your prototype to this solution.

Friday, February 13, 2015

Select Count Distinct

The Select statement doesn't have a way to let you do a count of distinct records.

Today I found this neat way to do it anyway, in the method \Data Dictionary\Tables\LedgerJournalTable\Methods\numOfVouchers:
...
sql = 'select count(distinct %1) from %2 where %3 = %4 and %5 = %6 and %7 = %8';

sql = strFmt(sql,
             ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, Voucher)),
             ReleaseUpdateDB::backendTableName(tableNum(LedgerJournalTrans)),
             ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, JournalNum)),
             sqlSystem.sqlLiteral(this.JournalNum),
             ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, DataAreaId)),
             sqlSystem.sqlLiteral(ledgerJournalTrans.DataAreaId),
             ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, Partition)),
             getcurrentpartitionrecid());
...

Thursday, February 12, 2015

Build a valid file name

I could not find anything in AX building a true valid file name. That is, just the name of the file itself, not the path.

So I came up with this:
public static Filename buildValidFilename(str  _filename)
{
    #xppTexts
    #define.LeftSquareBracket('[')
    #define.RightSquareBracket(']')

    Filename  validFilename;
    str       invalidFileNameChars;

    new InteropPermission(InteropKind::ClrInterop).assert();

    // Remove the characters that .NET defines as invalid
    invalidFileNameChars = new System.String(System.IO.Path::GetInvalidFileNameChars());
    validFilename = System.Text.RegularExpressions.Regex::Replace(_filename, #LeftSquareBracket + invalidFileNameChars + #RightSquareBracket, #emptyString);

    // Remove the characters that .NET doesn't remove
    invalidFileNameChars =  @'"\\/:*?\<>|' + "'";
    validFilename = System.Text.RegularExpressions.Regex::Replace(validFilename, #LeftSquareBracket + invalidFileNameChars + #RightSquareBracket, #emptyString);

    CodeAccessPermission::revertAssert();

    return validFilename;
}

Tuesday, February 10, 2015

Best Practice check for multiple models in the same layer

The Team Foundation Server integration in AX doesn't play very well with multiple models in the same layer. Without getting into too much detail, you for one thing risk having unexpected elements deleted from the AOT when you synchronize.

I'm on a project where we work with a few different models in the same layer. We have dependencies to these models, developed elsewhere. We have one specific model for my project, which also is the model where we merge all conflicts between models. If we have something in an element, like a class or table, where sub elements belongs to another model, we move these sub elements into our model. So in these cases our model contains the sum of changes to the entire root element.

In order to not forget moving the sub elements, I have developed an extra Best Practice check to check if an element belongs to more than one model, and raise an error in that case. To resolve the error I have to move all sub elements into a single model.

Here is the code for the Best Practice check (it is what it is...):
public void checkMultipleModelsInSameLayer()
{
    TreeNode            treeNode = sysBPCheck.treeNode();
    SysModelElement     sysModelElement;
    SysModelElement     sysModelElementRoot;
    UtilElements        utilElement;
    SysModelElementData sysModelElementData;
    SysModel            sysModel;
    SysModelManifest    sysModelManifest;
    Set                 modelNameSet;
    SetEnumerator       modelNameSetEnumerator;
    str                 modelNamesString;

    if (versioncontrol.ideIntegration())
    {
        if (versioncontrol.parmSysVersionControlSystem() &&
            !(versioncontrol.parmSysVersionControlSystem() is SysVersionControlSystemMorphX)) //MorphX VCS is not file based.
        {
            if (treeNode.treeNodeType().isUtilElement() && !SysTreeNode::hasSource(treeNode))
            {
                modelNameSet = new Set(Types::String);
                utilElement = treeNode.utilElement();

                // Only work with root objects
                if (utilElement.parentId != 0)
                {
                    return;
                }

                // Find the root model element
                select firstonly RecId from sysModelElementRoot
                    where sysModelElementRoot.Name          == utilElement.Name
                       && sysModelElementRoot.ElementType   == utilElement.RecordType
                       && SysModelElementRoot.ParentId      == 0;

                // Analyse all children of the root, in the same layer
                while select tableId from sysModelElement group by sysModelManifest.Name
                    where sysModelElement.RootModelElement == sysModelElementRoot.RecId
                join tableId from  sysModelElementData
                    where sysModelElementData.ModelElement == sysModelElement.RecId
                join tableId from sysModel
                    where sysModel.RecId   == SysModelElementData.ModelId
                       && sysModel.Layer   == currentAOLayer()
                join Name from sysModelManifest
                    where sysModelManifest.Model == SysModel.RecId
                {
                    modelNameSet.add(sysModelManifest.Name);
                }

                if (modelNameSet.elements() > 1)
                {
                    modelNameSetEnumerator = modelNameSet.getEnumerator();
                    while (modelNameSetEnumerator.moveNext())
                    {
                        if (modelNamesString)
                        {
                            modelNamesString += ', ';
                        }

                        modelNamesString += modelNameSetEnumerator.current();
                    }

                    sysBPCheck.addError(0, 0, 0, strFmt('Object associated with several models in the same layer. Models: %1', modelNamesString));
                }
            }
        }
    }
}
This method as added to relevant classes that extend SysBPCheckBase.