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);

}