Create ledger dimension for LedgerJournalTrans, to use in LedgerDimension and OffsetLedgerDimension fields

This simple code below may be used, when you want to get a RecId for LedgedDimension of OffsetLedgerDimension field in LedgerJournalTrans table.
It is working in Dynamics AX 2012 and in Dynamics 365, but it is good to know that in Dynamics 365 it is rather obsolete, and it is advised to create ledger dimension value in different way.

container ledgerDimensionContainer;

RefRecId ledgerDimension;

//First two items in contaner is MainAccountId, in that example "430-01"

//Third element is number of dimensions which you want to use. In that example is '3'

//As fourth, sixth, eights, ...., you have to put dimension name. In that example are "Department", "CostCenter", "Project"

//As fifth, seventh, nineth, ...., you have to put dimension's values, in that example are "Warsaw", "IT", "Scrum"

ledgerDimensionContainer = ['430-01', '430-01', 3, 'Department', 'Warsaw', 'CostCenter', 'IT', 'Project', 'Scrum'];

ledgerDimension = AxdDimensionUtil::getLedgerAccountId(ledgerDimensionContainer);

//Here in ledgerDimension variable is LedgerDimension ready to use in LedgerJournalTrans table

return ledgerDimension;

Data dictionary icon is red. Needed to be synchronize

Sometimes, when you're creating a new Extended Data Type, system is asking you about synchronization.
You don't want to synchronize all the time, when new EDT has been created, because you know, that you have to create more EDTs in that project.

But, when you will not synchronize, an icon of Data dictionary turns red.

It means that system knows that it is needed to be synchronized, and it can start long synchronization process when you don't want to do that.

To avoid such unnecessary synchronization, you can just use that job

static void Job23(Args _args)
{
    new SqlSyncPending().databaseTouched(false);
}



Just remember to synchronize system, after you've created all needed EDTs.

Date to DateTime conversion and comparison

If you want just to convert specified date to dateTime, and it should be beginning of this day (date time), you can use

DateTimeUtil::newDateTime(mkDate(17, 12, 2019), 0);
//17th of December 2019

Or just
DateTimeUtil::newDateTime(systemdateGet(), 0);
//current system date

But if you want to have maximum time from that date, so it means hour 23:59:39
DateTimeUtil::newDateTime(mkDate(17, 12, 2019), 86400);

As 86400 is maximum time value for given date (24 hours * 60 minutes * 60 seconds)

In Dynamics 365FO, systemDateGet() is usable but obsolete, and you'll get Best Practice warning.
You should rather use
DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone())

How to change Created by on batch job. Change user on which batch job will be executed

It's not good idea to create batch jobs using standard user account, because such user may be disabled after some time, and there will be nobody with knowledge, why do some batches (even very old) just  not work.

But, if we have plenty of such batches, and we want them to be executed on some service account or on admin account, we don't have to recreate them.

There are two tables which keep information which user will execute those batches:
- Batch
- BatchJob

You have to change fields ExecutedBy and CreatedBy in Batch table, and only CreatedBy in BatchJob table

You can do that manually from Microsoft SQL Server Management Studio, or just writing a script

begin transaction

update BATCHJOB
set CREATEDBY='SomeServiceAccount'
where CREATEDBY='admin_10'

update BATCH
set EXECUTEDBY='SomeServiceAccount', CREATEDBY='SomeServiceAccount'
where CREATEDBY='admin_10'

commit transaction

How to build complex query range in query

Sometimes there is a need to query some records using OR and put it to a query range
For example: we need to query records which are from the past and from the future.

Below, you can find an example of such query on table called just Table1
There is no date effective property on the table, so fields ValidFrom and ValidTo are just Date type fields created on that table.
As you can see all range has been built on ValidFrom field, even when it touches also ValidTo.


static void Job21(Args _args)
{
    Query                   query = New Query();    
    QueryRun                queryRun;
    QueryBuildDataSource    qbds;
    str                     range;
    Table1                  table1;
    
    qbds = query.addDataSource(tableNum(Table1)); 
      
    qbds.addRange(fieldNum(Table1, ValidFrom));
    
    range = strFmt('((%1.%2 < %3) && (%4.%5 < %6)) || ((%7.%8 > %9) && (%10.%11 > %12))',
        qbds.name(),
        fieldStr(table1, ValidFrom),
        date2StrXpp(today()),
        qbds.name(),
        fieldStr(table1, ValidTo),
        date2StrXpp(today()),
        /// after or
        qbds.name(),
        fieldStr(table1, ValidFrom),
        date2StrXpp(today()),
        qbds.name(),
        fieldStr(table1, ValidTo),
        date2StrXpp(today()));    
    
    qbds.addRange(fieldNum(Table1, ValidFrom)).value(range);    
}

Progress bar

How to create a progress bar from X++



static void Job39(Args _args)
{
    SalesLine               salesLine;
    Counter                 i;
    SysOperationProgress    progressBar = new SysOperationProgress();    
    #avifiles

    select count(RecId) from salesLine;
    
    progressBar.setCaption('Searching...');
    progressBar.setAnimation(#AviUpdate);    
    progressBar.setTotal(salesLine.recId);    
    
    while select salesLine
        where !salesLine.RemainInventFinancial
            && !salesLine.RemainSalesPhysical
    {
        i++;
        
        progressBar.setText(strfmt('Row %1', i));
        progressBar.setCount(i, 1);        
    }

}

Get file name from file path by X++

When you want to get a file name from long file path, you can use a method fileNameSplit() which you may find in Global class.

This method returns a container, where the first value is a path, the second file name (without extension), and the last third one is an extension.

You have to remember that path is returned already with last backslash '\', so for example C:\Test\
And also what is important, that last value (extension) is returned with dot '.', for example '.xml'

An example of code below

static void SplitFileName(Args _args)
{
    Filename    fileNameOrig = @'\\NetworkPath\In\FileName.xml';
    fileName    filePath;
    Filename    fileName;
    fileName    fileExt;
    
    [FilePath, fileName, fileExt] = fileNameSplit(fileNameOrig);

    info(FilePath);
    info(Filename);
    info(fileExt);

}

And a result

Get exchange rate

Somehow it may be not obvious how to get exchange rate for currency code and given date

I've just put a code below to Global class, to have an access from any place in system to this easy way of getting exchange rate.
But of course you can put it wherever you want.

public static CurrencyExchangeRate getExchangeRate(CurrencyCode _transactionCurrency, TransDate _transactionDate)

{

    ExchangeRateHelper      exchangeRateHelper;

    CurrencyExchangeRate    exchangeRate;

    exchangeRateHelper  = ExchangeRateHelper::newExchangeDate(Ledger::current(), _transactionCurrency, _transactionDate);

    exchangeRate       = exchangeRateHelper.getExchangeRate1();

    exchangeRate = exchangeRate / 100; //please be aware of that 100! Check how it is in your system.

    return exchangeRate;

}

There are similar methods in the system in class ExchangeRateHelper, but you have to give one more argument, which in my opinion should be defaulted, but it isn't

Anyway, you can use standard method getExchangeRate1_Static()

Get file from table field which is a container type

Last time I had a task to modify an image which is displayed on collection letter.
Just simple change.
The issue was, that nobody at company had original image.

Image was stored in one of Dynamics AX tables, so I had to take it out from there

Below you can find a simple code how to get this image and save it to some disk location

static void GetFile_PKI(Args _args)
{

    CustCollectionLetterLine    collectionLetterLine;
    container                   image;    
    Bindata                     bin = new bindata();    
    str                         tmpImageHolder;    


    //I knew what RecId of that record is, so it's just simple select statement
    select collectionLetterLine 
        where collectionLetterLine.recId == 5637144577;

    //This is a table field where I have my image. Field is container type
    image           = collectionLetterLine.NotesLogo_PKI;
    
    bin.setData(image);
    
    // This will give me a string encoded as base64
    tmpImageHolder  = bin.base64Encode();

    // I've chosen .jpg extension but in fact it does not matter
    //You have to have a look on file header (for example in notepad) to get
    //information what type of file it is.
    AifUtil::saveBase64ToFile(@"C:\temp\test.jpg", tmpImageHolder);

}

LedgerDimension for newly created Customer or Vendor

There are plenty of blogs which shows how to create/merge ledgerDimension for ledgerAccount. 

But the issue is with Vendor or Customer account.

And...
There are plenty of blogs which describes how to import transactions to ledger journal for customer's or vendor's transactions.

But, the problem is, when customer account or vendor account is just created, there where no transactions posted yet for that account in ledger journals.
In such case there is no record in  DimensionAttributeValueCombination.
Such record is created automatically when user creates a record in LedgerJournalTrans with such Customer or Vendor account. Even without posting.

So, how to create an entry for that table for Customer or for Vendor from X++?
Because we need that to import transactions, and without asking users to put 

We need a recId to use it in multitype account in ledger journal

DimensionStorage::accountNum2LedgerDimension(vendTable.AccountNum, LedgerJournalACType::Vend); 

or

DimensionStorage::accountNum2LedgerDimension(custTable.AccountNum, LedgerJournalACType::Cust); 

Those examples are returning recId for such ledgerDimension , ready to use

For example

ledgerJournalTrans.ledgerDimension = DimensionStorage::accountNum2LedgerDimension(custTable.AccountNum, LedgerJournalACType::Cust); 

or if you want to have it  done better

AxLedgerJournalTrans                trans;

trans.parmLedgerDimension(DimensionStorage::accountNum2LedgerDimension(custTable.AccountNum, LedgerJournalACType::Cust));

Enjoy

Multi select lookup on form field

We have ready-to-use lookups in system.

Unfortunately we can choose only one item from such lookup.

What if we need to select more than one element?

We have to use class SysLookupMultiSelectCtrl

I've prepared an example how to use this on AOT form, but there is no issue to use it on dialog field from SysOperation Framework.

You must remember that it can be used when dialog is already build, so on SysOperation framework it can be used on method postBuild or postRun.

It's not so easy to use it on RunBase classes or when you're trying to build, because you have to overload a method lookup for that dialog field. Overloading method can be done only once, and this SysLookupMultiSelectCtrl is overloading it once again, it cause runtime errors.

If you really need to have such multilookup on Runbase dialog you have to copy some pieces of code from SysLookupMultiSelectCtrl and overload lookup method.

So....this example is for form fields, and last line from init method can be transfered to postBuild() method on SysOperation framework to UIBuilder class.

I've created very simple form with one field ItemId

I've changed two properties on that field

Autodeclaration: Yes

ExtendedDataType: ItemId

You don't have to put EDT on this form control. You'll receive multilookup anyway.

I've used EDT to have proper label, help text and size of that control.

Remember that EDT ItemId has limitation of lenght, so it may be not long enough to store more than 1-2 item Ids! It's just an example

You have to modify three method on form

classDeclaration

init

close

Instead of modifying close method, you can add some button on our form.

I'm using close method to catch value from our ItemId control and display this value.

This is how our lookup looks like

When we press OK on lookup, we will get chosen values to our field

After form closing, we will receive a message with chosen values.

Unretrieved fields

Sometimes there might be a problem with new field just added to a table.

There is no possibility to put a value to such field on form, neither in Table Browser.
Unretrieved value is visible, and such field is grayed out.

There are few steps which may help, but the bad news is that, that all those steps may not help and at the end you'll have to restart AOS (it worked always for me).
So, if you have this issue with "Unretrieved" fields on your DEV or TEST machine and you can restart it.....just do it.

So, there are few steps which may help.
1. Sychronize this table
2. Restore form where is this field. (right click on form from AOT and then -> Restore)
2. Clean all cache (Dev enviromnent, Tools\Caches\...)
3. Close AX client and delete all .auc files (C:\Users\YOURUSER\AppData\Local\Microsoft\Dynamics Ax\VSAssemblies{xxxxxxxxxxx\


There is one known additional issue regarding this subject. 
This new field may be visible on form and usable, but all select statement will return blank/null or (even worse) default value of Enum from that field, if that field is a type of Enum.
This last thing is the worst scenario in my opinion, because it's very hard to find why select statement is fetching wrong data.

FilePath folder lookup does not work. Why?

There is a new field on our table. Field bases on Extended Data Type FilePath.

This fields has been added to a form. We can see an automatic lookup button. It looks like a folder button.

The issue is, that this folder button does not work. We can click on it, but there is no new window when we can choose a folder to store files.

The "magic trick" is, that we have to add to method node of our form new method
Metod name is filePathLookupTitle

This method returns some string.

Example

str filePathLookupTitle()
{
    // Archive directory
    return "@SYS26605";
}

The good example is on system form DocuType

New SSRS instance. How to deploy all reports

We may have more then one SSRS instance on our server.

Every time when we will create such instance we have to create new configuration in our AX and create new report folder (also from AX)

System administration\Setup\Business intelligence\Reporting Services\Report servers
There is a button for creating a new report folder.

This folder will be empty.

We can deploy every report from AOT one by one: right click on report and position Deploy

It's much faster to use AX PowerShell for that purpose.
It has to be run from server where Reporting Services are installed.
There should be also installed a part of Dynamics AX, to have AX PowerShell and Dynamics AX Configuration. PowerShell will use default config set in AX Configuration tool.

We have to export AX configuration to have .axc file with stored information about that config. This file .axc has to be renamed to Microsoft.Dynamics.AX.ReportConfiguration.axc
When name of that file will be changes, we have to copy that file to folder with our new SSRS instance.
Default folder is usually:
C:\Program Files\Microsoft SQL Server\OurInstanceName\Reporting Services\ReportServer\bin

After that we have to run Microsoft Dynamics AX 2012 Management Shell (our Powershell with AX extensions)

command to use Publish-AXReport -ReportName *

Instead of '*' we can put a name of report which we want to deploy, or we can put for example S* - it will deploy all reports with names starting from S (SalesInvoice for example).

Such deploy process started from Shell is much faster then deploying from AOT.

How to get Cust account or Vend account from ledgerJournalTrans?

LedgerJournalTrans may keep information about few types of account.
Two of them may be information about customer and vendor account.

There are two ways to find a CustTable record or VendTable record from ledgerJournalTrans.

First option:
Harder but it will show how does it work.

At the beginning it's good to know that this information is stored in LedgerJournalTrans in field LedgerDimension

So before you'll start searching vendor or customer, it would be good to check what type of account is stored in LedgerJournalTrans.
You will now that from field AccountType.

LedgerDimension stores an info about record of table DimensionAttributeValueCombination
And this last table has a field which may be interesting for us: DisplayValue

So, let's make a job which will give us a record from CustTable or from VendTable

static void accountHardWay(Args _args)
{
    LedgerJournalTrans                      ledgerJournalTrans;
    DimensionAttributeValueCombination      combination;
    CustTable                               custTable;
    VendTable                               vendTable;
    
    //I assume that we already have a record 
    //from ledgerJournalTrans
    
    combination = DimensionAttributeValueCombination::find(ledgerJournalTrans.LedgerDimension);
    
    if (combination)
    {
        if (ledgerJournalTrans.AccountType == LedgerJournalACType::Cust)    
        {
            custTable = CustTable::find(combination.DisplayValue);    
        }
        else
        {
            if (ledgerJournalTrans.AccountType == LedgerJournalACType::Cust)    
            {
                vendTable = VendTable::find(combination.DisplayValue);    
            }                
        }
    }

}



Second Option:
For dummies :)

ledgerJournalTrans has a method accountDisplay()

static void accountEasyWay(Args _args)
{
    LedgerJournalTrans                      ledgerJournalTrans;
    CustTable                               custTable;
    VendTable                               vendTable;
    
    //I assume that we already have a record 
    //from ledgerJournalTrans

    if (ledgerJournalTrans.AccountType == LedgerJournalACType::Cust)    
    {
        custTable = CustTable::find(ledgerJournalTrans.accountDisplay());    
    }
    else
    {
        if (ledgerJournalTrans.AccountType == LedgerJournalACType::Cust)    
        {
            vendTable = VendTable::find(ledgerJournalTrans.accountDisplay());    
        }                
    }

}

Run as different user. Strange behaviour

Dynamics AX shortcut may start different AX session for your currently user, and for user which has been chosen from menu "Run as different user"

This is because Dynamics AX is starting with default configuration which has been set in Dynamics AX Configuration utility. 
This configuration is set separately for every single user from that Windows system when Dynamics AX Client is installed.

So, when you're starting Dynamics AX for different user, but from your user session (Run as different user) it takes AX configuration from that different user, not from yours.

May be obvious, but sometimes it can be a little surprise.

Catch insert, update or delete. Database log

Sometimes there is a need to find out, which class or other element in system is inserting, updating or deleting a record in a table which is interesting for us.

The easiest way is just to put a breakpoint on insert(), update() or delete() method of that table.
If there is no such method, it has to be added of course.
When some piece of code will call a method on table, we will see a full stack, and probably a reason why this table has been modified and where.

An example of insert() method on SalesTable and a breakpoint at the beginning.

The problem is, when a record is inserted/updated/deleted using doInsert(), doUpdate(), doDelete().
In that case, such breakpoint on table method will not work.

To find out what changes data in our table, we have to put a breakpoint in class Application
There are many methods, but for us, there are three interesting: logDelete(), logInsert() and logUpdate().

And in one of them we should put a breakpoint.

An example of breakpoint on logInsert()

But it's not enough just to put a breakpoint.

We have to go to 'System administration\Database\Database log setup'.
There will be a form with listed tables for which system is saving a log of changes and a type of those changes (Insert/Update/Delete).

Let's say that we want to find when a record is inserted to a table SalesTable using doInsert() method.

We have to press "New" button on Database log setup form
It will start a Wizard.
Just press Next.
It will take a while to build a table structure.
In my opinion it would be much more useful, just to see a list of tables with their AOT names and without any structure. Much faster, that's for sure, because this window with structure built for the first time appears after about 10 minutes! 

Please remember, that you will see a list of tables sorted by modules, and you will see their printed names. It's important because I had two AX, both in English language, but one was in UK English, and one in US English. And of course those tables had different printed names.

So, I need to catch a doInsert on SalesTable, so I have to choose a table "Sales orders"

Press Next

On next window please tick a checkbox "Track new transactions" for row with table name "Sales orders". There may be only one row, but on older working environments there may be more

Press Next, and on next (last) window of that Wizzard, please press Finish.

There was a breakpoint few lines above on that blog on logInsert() method of Application class.

So, after this database log setup has been made for SalesTable, a breakpoint will stop a trace, and it will be ease to find out what process has inserted a record to our table.

Infolog levels. Setprefix and other tricks.

Sometimes we have to display some kind of treeview in info message.
We need to make somehow sub-level of infolog.

To do that we can use a setPrefix() build-in function.

As an example:

setPrefix ('A new node of infolog');
info('A sub node of infolog');

Instead of using setPrefix(), we can just use a tab '\t' to format our message

info(strFmt('A new node of infolog%1A sub node of infolog', '\t'));

Result in both cases will be the same

To have a possibility to go back to level 1, we have to use another methods, sub methods or loop statements.

So, before calling our method or loop we have an infolog level X, and after this method or statement we will have exactly the same level X of our infolog.

Please have a look in that code below. This will produce few levels of our message, and sometimes it will get back to previous level or dig deeper.

I'll use sub method in job and for loop to show how does it work.


static void Job201(Args _args)
{
    Counter i, k;
    
    void sendMessage(str _message, str _prefix = '')
    {
        if (_prefix)
        {
            setPrefix(_prefix);    
        }
        
        info(_message);
    }
    
    
    //A prefix on the very top of infolog window
    setPrefix('Build a tree');
    
    //Two lines on first level of infolog. 
    //Just to show that two of them will be on the same level
    info('Just a message on first level');
    info('Another message on the same level');    
    
    //Sub method of that job used to display sub node of 
    //that infolog and display a message 
    //under that subnode
    sendMessage('This is message on sub level 2', 'Sub level 1 prefix');
    
    //When trace went out from submethod 'sendMessage' 
    //infolog level is again on level one!!
    //Be careful. In next line there will be 
    //a little trick to display another message 
    //on sub level 2from 'sendMessage' submethod. 
    //so this line above will be displayed after 
    //this all sublevel 2. 
    //Infolog will change an order of those lines
    info('This should be a message on level 1. Where it has been displayed? :)');    
    
    //You can use a tab '\t' instead of setPrefix().
    //When you'll place exactly the same string 
    //as a prefix as it has been previously used
    //an Infolog will put not create new node, 
    //but will just put a message to previous prefix node 
    //with the same name
    info(strfmt('Sub level 1 prefix%1This is another message on sub level 2', '\t'));
    
    //and again, another sublevel 2 node, 
    //but different prefix
    //so it will be displayed as separate subnode.
    info(strfmt('Another sub level 1 prefix%1This is another message on sub level 2', '\t'));    
    
    //and third level of infolog. 
    //Just used a tab '\t' to dig there.
    info(strfmt('Another sub level 1 prefix%1This is prefix message on sub level 2%1and message on sublevel 3!', '\t', '\t'));      
    
    //and we are getting back to level 1.
    info('This should be a message on level 1');
    
    //Let's dig one level deeper.    
    setPrefix('Well, level 2 before loop');
    
    //we can use a loop instead of subMethod.
    //It will do the same for us, just close 
    //a prefix level in one statement
    //and outside of that statement infolog level will 
    //go back to level before loop.    
    
    for (i = 1;i <= 2;i++)
    {
        //next level of prefix. This will be level 3   
        setPrefix('Level 3 inside of loop');
        
        for(k = 1; k <= 2; k++)
        {
            info('Message on level 4');    
        }
    }
    
    //and after a loop, Infolog will display 
    //this message on level 3
    //because before a loop there was a setPrefix statement
    info('This should on level 3');
}

This is how it will look like after that

COM error: invalid number of parameters

Well, I've met last days a strange error message when I wanted to import data from Excel.
I'm not sure (haven't tested that) but the same problem may occur with other applications connected by COM (Outlook for example).

Error executing code: The method has been called with an invalid number of parameters

The problem was, that there was good number of parameters.

Method looked like that


    SysExcelApplication excelApp;
    SysExcelWorkbooks   excelWorkbooks;
    SysExcelWorksheets  excelWorksheets;
    SysExcelCells       excelCells;
    SysExcelWorksheet   excelWorksheet;
    ComVariant          cellValue;

....

    //line calling error
    cellValue   = excelCells.item(row, col).value();


....


I found workaround of that problem, which is just a....workaround, and nothing more. The problem is still there, and probably has to be fixed by Microsoft.


The main issue is, that this problem is not possible to replicate every time. Sometimes it is, and sometimes not.

I've added a line just before this line which is responsible for that error

     infolog.yield();

Yes, that's it!

There is another workaround for that which works, but I think that it does not give a guarantee that it will work - how can you know that 50 (or another number) will be enough?
There might a coincidence that it will work after 51st iteration.

Just add an error counter with maximum value, let's say 50.
After that, run this problematic line in try catch statement, iterating as many times as code will reach a limit gave as maximum (hardcoded)

Something like that

  Counter limitCounter;

  try
  {
    cellValue   = excelCells.item(row, col).value();
  }
  catch(Exception::Error)
  { 
     limitCounter++;

     if(limitCounter <= 50) //hardcoded!!
        retry;
     else
        throw(Exception::Error);
  }