X++ Export an Unicode CSV file

From the previous post. X++ create a simple CSV file.We made a simple CSV, but sometimes we need to export a field as Unicode. For example the japanese font.

We probably get the result file like as below.

We can fix this issue easily by enhance the existing code a bit.
from

private void closeFile()
{
    textBuffer.toFile(fileName);    
    CodeAccessPermission::revertAssert();
}

to

private void closeFile()
{
    textBuffer.toFile(fileName, FileEncoding::UTF8);    
    CodeAccessPermission::revertAssert();
}

The result looks nice eventually.

X++ Create a simple CSV file

Hello, I found that there are many ways to create a CSV file. Today I will show the simple code which made by textBuffer class.

class GenerateSimpleCSV
{
    //Class
    FileIOPermission    fileIOPermission;

    //Variable
    TextBuffer          textBuffer;
    str                 fileName;

    //Macro
    #File
    #xppTexts
}

---

private void openFile()
{
    fileName = WINAPI::getTempPath() + "test" + #CSV;
    new FileIoPermission(filename, #io_write).assert();
    textBuffer = new TextBuffer();
}

---

private void writeHeader()
{
    textBuffer.appendText('No,');
    textBuffer.appendText('Name,');
    textBuffer.appendText('Surname');

    textBuffer.appendText(#newline);
}

---

private void appendLine()
{
    int     i;
    str     name    = 'aa',
            surname = 'bbb';
    ;

    for (i=1; i<=3; i++)
    {
        textBuffer.appendText(strFmt('"%1",',i));
        textBuffer.appendText(strFmt('"%1",',name));
        textBuffer.appendText(strFmt('"%1"' ,surname));

        textBuffer.appendText(#newline);
    }
}

---

private void closeFile()
{
    textBuffer.toFile(fileName);
    CodeAccessPermission::revertAssert();
}

---

public void execute()
{
    this.openFile();
    this.writeHeader();
    this.appendLine();
    this.closeFile();
}

After finished creating the class, we call it by the below job.

static void testGenSimpleCSV(Args _args)
{
    //Class
    GenerateSimpleCSV   genSimpleCSV;
    ;

    genSimpleCSV = new GenerateSimpleCSV();
    genSimpleCSV.execute();
    info('done!');
}

The result looks like the below picture. Have a nice day!

X++ Query in tree joining pattern

Generally X++ query can be created in a linear joining pattern for example

SalesTable - SalesLine - InventTable

However sometimes we would like write a query in a tree joining pattern as follows.

For that case, we can apply the code as the following way.

void TreeJoinExample()
{
    Query                   q;
    QueryBuildDataSource    qbdsSalesLine,
                            qbdsSalesTable,
                            qbdsInventTable;
    ;


    q = new Query();
    
    qbdsSalesLine = q.addDataSource(tableNum(SalesLine));

    qbdsSalesTable = qbdsSalesLine.addDataSource(tableNum(SalesTable));
    qbdsSalesTable.relations(false);
    qbdsSalesTable.addLink(fieldNum(SalesLine, SalesId), fieldNum(SalesTable, SalesId));
    qbdsSalesTable.fetchMode(queryFetchMode::One2One);

    qbdsInventTable = qbdsSalesLine.addDataSource(tableNum(InventTable));
    qbdsInventTable.relations(false);
    qbdsInventTable.addLink(fieldNum(SalesLine, ItemId), fieldNum(InventTable, ItemId));
    qbdsInventTable.fetchMode(queryFetchMode::One2One);
}

X++ Union query

Let's see this scenario.

Condition: We'd like to have the following results. 1) all sales lines which has all status, except invoiced and cancelled and 2) all sales lines which has status = invoiced, and invoiceDate(in CustInvoiceJour) >= today

For above purpose, we can apply an union query like below.

void initQuery()
{
    Query                   query;
    QueryBuildDataSource    qbdsSalesLine,
                            qbdsCustInvoiceJour;
    ;

    query               = new query();
    query.queryType(QueryType::Union);

    qbdsSalesLine       = query.addDataSource(tableNum(SalesLine), identifierstr(SalesLine_1));
    qbdsSalesLine.addRange(fieldnum(SalesLine, SalesStatus)).value(queryValue(SalesStatus::Backorder));
    qbdsSalesLine.addRange(fieldnum(SalesLine, SalesStatus)).value(queryValue(SalesStatus::Delivered));
    qbdsSalesLine.addRange(fieldnum(SalesLine, SalesStatus)).value(queryValue(SalesStatus::None));

    qbdsSalesLine       = query.addDataSource(tablenum(SalesLine), identifierstr(SalesTable_2), UnionType::Union);
    qbdsSalesLine.addRange(fieldnum(SalesLine, SalesStatus)).value(queryValue(SalesStatus::Invoiced));

    qbdsCustInvoiceJour = qbdsSalesLine.addDataSource(tableNum(CustInvoiceJour));
    qbdsCustInvoiceJour.relations(false);
    qbdsCustInvoiceJour.addLink(fieldNum(SalesLine, SalesId), fieldNum(CustInvoiceJour, SalesId));
    qbdsCustInvoiceJour.joinMode(JoinMode::ExistsJoin);
    qbdsCustInvoiceJour.addRange(fieldnum(CustInvoiceJour, InvoiceDate)).value(SysQuery::range(today(), dateMax()));

    queryrun = new SysQueryRun(query);
}

Note that:

Union – remove duplicated records

UnionAll – keep duplicated records

AX2012 Location of table CompanyInfo in SQL Server

Recently, I had a serious problem about field dataAreaId. The issue is when creating a new record on InventTable, It always got a wrong dataAreaId. (For example, I hope it created 'c01', but it did 'c99' instead.)

After check, I found that method initValue() generate that dataAreaId from somewhere else which not in table CompanyInfo.

So it would save a lot of time if we know the exact table in SQL Server which kept AX's table CompanyInfo.

Here it's.
SQL Server ---> table 'DataArea' or view 'CompanyView'
AX             ---> table 'CompanyInfo'

From above, I can get the cause of this issue. The wrong dataAreaId came from the virtual company and table collection setting cause.

Have a good day!

AX2012 Fixed: The relatedTableRole specified in one of the relationships is incorrect. (query TaxSpecPerLedgerTrans)

I found this issue when I move model from an environment to another one. Actually, I see others found this issue when they upgrade code to CU7 as well. One of solution someone recommend is to reinitialize model store, but it didn't work for my case.

See also these useful topics:
https://community.dynamics.com/ax/f/33/t/136281
https://community.dynamics.com/ax/f/33/t/138281

Solution:  update hotfix KB2984666. After I appied that hotfix and reinitialize model store, the issue is resolved.

AX2012 Export and Import Model

Speak as a beginner, even Microsoft prepares many great tools however deploying(or moving) AX environment still is quite complicated. There are many ways to do for an objective. Usually I start by check or make sure what scenario I want to do exactly, and then looking for the proper steps for it.

Useful resources
Deploying customizations across Microsoft Dynamics AX 2012 environments
Models, Layers, and the Model Store [AX 2012]
Model Store Deployment Best Practices

Moving model is a good choice when we are dealing with dev or test environment. Below it shows the simple steps to move a model from an environment to another one.

Export model
     -     At source environment
     -     Stop AOS

     -     Backup databases (transactions and model)

     -     Run command-prompt as administrator and go to the path which has ManagementUtilities as following example

     -     Export model by this command
           axutil export /model:"CUS model" /file:aaa.axmodel /s:machine01\AX2012R2_DEV /db:AX2012R2_DEV_model

           model name                 -> CUS model
           result file                     -> aaa.axmodel
           server name                -> machine01
           SQL server instance     -> AX2012R2_DEV
           model db                     -> AX2012R2_DEV_model

Import model
     -     At target environment
     -     Stop AOS
     -       Backup databases (transactions and model)
     -       Run command-prompt as administrator and go to the path which has ManagementUtilities
     -       Delete model by this command
           axutil delete  /model:"CUS model"  /s:machine02\AX2012R2_QA  /db:AX2012R2_QA_model

     -     Start AOS
     -     Sync db
     -     Stop AOS
     -     Import model by this command
           axutil import /file:aaa.axmodel /s:machine02\AX2012R2_QA /db:AX2012R2_QA_model

-          Start AOS

-          Launch AX client

Until the next post!

X++ Method parameters actually pass by value or reference?

Good morning everyone. This is just a short note. I've seen a code which update/change the value of a parameter variable.(see below) This make me very confuse because as far as I knew normally X++ parameters are passed by value.

void test(EcoResProductMaster _EcoResProductMaster = null)

{

    ;

    EcoResProductMaster.data(_EcoResProductMaster);

    this.aaa();

    this.bbb();

    this.ccc();

    _EcoResProductMaster.data(EcoResProductMaster);

} 

I googled and then I found the following useful link.

https://gatesasbait.wordpress.com/2007/12/05/parameter-passing-in-dynamics-ax

So there are two base types in X++ which are passed by reference. They are record and class.

String	Assigned and passed by VALUE
Integer	Assigned and passed by VALUE
Real	Assigned and passed by VALUE
Date	Assigned and passed by VALUE
Enum	Assigned and passed by VALUE
Container	Assigned and passed by VALUE
Record	Assigned and passed by REFERENCE
Class instance (any object instanciated with ‘new()‘)	Assigned and passed by REFERENCE
AnyType	Assigned and passed by VALUE, even when holding a Record or Class instance
Guid	Assigned and passed by VALUE
Int64	Assigned and passed by VALUE

Hope that helps, enjoy your day.

X++ Copying a record

Hi guys. This post is written base on the sample code from well-known recipe 'Copying a record' in Microsoft Dynamics AX 2012 Development Cookbook. However I also show some scenarios we might apply this useful technique.

Simple copying by .data() and buf2Buf()

First, I have table 'SourceA' which looks like as follows:

number	fieldA	fieldB	fieldC
001	aaa	bbb	ccc

I copy the above record by this code.
static void TestCopy(Args _args)
{
    SourceA     source,
                destination;
    
    source = sourceA::find('001');
    
    ttsBegin;
        destination.data(source);     
        destination.number = '002';
        if (!destination.validateWrite())
            throw Exception::Error;
        destination.insert();
    ttsCommit;
    info('done');
}

the result will  looks like this.

number	fieldA	fieldB	fieldC
001	aaa	bbb	ccc
002	aaa	bbb	ccc

It can also be done by buf2Buf().
destination.data(source);   --- replace by ---> buf2Buf(source, destination);

The result is exactly same.

Mechanism of buf2buf()

static void buf2Buf(Common _from, Common _to)
{
     DictTable dictTable = new DictTable(_from.TableId);
     FieldId fieldId = dictTable.fieldNext(0);
     while (fieldId && ! isSysId(fieldId))
     {
          _to.(fieldId) = _from.(fieldId);
          fieldId = dictTable.fieldNext(fieldId);
     }
}

From above code, two things buf2Buf() are different to .data() are buf2Buf excludes the system fields and slower than .data() because of individual each fields copying.


Updating by buf2Buf()

We can also reduce the tradition line of code when we update a record by using copy instead. This is useful when we get a record from the other sources, for example we get the data from file and would like to update the new data on the same number.

Before update

Code
static void TestUpdate(Args _args)
{
    SourceA     source,
                destination;

    source.number   = '001';
    source.fieldA   = 'xxx';
    source.fieldB   = 'yyy';
    source.fieldC   = 'zzz';   
    destination     = sourceA::find('001',true);

    ttsBegin;       
        buf2Buf(source, destination);
        if (!destination.validateWrite())
            throw Exception::Error;
        destination.update();
    ttsCommit;
    info('done');
}

After update

We can apply .data() also but not exact above code as .data() include the system fields so it needs to update record before. Therefore when we apply 'Copying' to 'Updating' like this, buf2Buf would be easier to apply.

Thanks for reading. Having a good time!
Shoot

X++ How to create a lookup which stores data from column A, but show data from column B

Hi everybody. This is a very simple lesson for dev. Most of times we'd like to have a simple "Lookup" which store a field data but display another field on the control.

The techniques we need are Edit method, lookup and jumpRef.

Let us learn this lesson through the example scenario, at module Product information management. I will add some characteristics of the soccer ball on it.

The final result would looks like below.

Step#1

- Create Base Enum.

- Create EDT.

- Create table "BrandCategory" also added its index.

- Fill data into the table. 

Then add fields in table EcoResProduct and modify form EcoResProductDetails.

At the end of this step, you can see that we can enter the data on the new fields of EcoResProduct but no lookup, just a simple textbox.

Step#2

- Add relation on table EcoResProduct

- Modify autolookup on table BrandCategory

At the end of this step, we got the lookup! Great, but still display the number of value.

Step#3

From step#2 you see that lookup works however our main purpose is to display another value on the control so we need "Edit method" here.

- Create a find method on table BrandCategory.

- Create an edit method on table EcoResProduct.

public edit BrandNumber editBallBrandName(boolean _set, BrandNumber _value)

{

    if (_set)

        this.BallBrandName = _value;

    return  BrandCategory::find(BrandTypeBase::Name, this.BallBrandName).BrandName;

}

then change the property on the control

Before change

After change

- Create method lookup on the control.

public void lookup()

{

    SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(BrandCategory), this);

    Query query = new Query();

    QueryBuildDataSource qbds;

    ;

    sysTableLookup.addLookupField(fieldnum(BrandCategory, BrandNumber), true);

    sysTableLookup.addLookupField(fieldnum(BrandCategory, BrandName));

    qbds = query.addDataSource(tablenum(BrandCategory));

    qbds.addRange(fieldnum(BrandCategory, BrandType)).value(queryValue(BrandTypeBase::Name));

    sysTableLookup.parmQuery(query);

    sysTableLookup.performFormLookup();

}

OK, at the end of step3, the result looks good. We have both edit method and lookup which enable us to store a field and show another field. 

Unfortunately, we can't stop here, because users note that when they do right-click, the "view details" menu disappear. 

This is because we use datamethod instead of datafield so we have to add somethings.

Step#4 (The last one)

- Create a simple form BrandCategoryFrm for table BrandCategory.

- Add method init on that form.

public void init()

{

    BrandCategory    localBrandCategory;

    ;

    super();

    if (element.args().caller())

    {

        if (element.args().caller() is FormRun)

        {

            callerForm = element.args().caller();

            if (callerForm.name() == formstr(EcoResProductDetails))

            {

                if (element.args())

                {

                    callerRecord = element.args().record();

                    if(callerRecord.TableId == tableNum(BrandCategory))

                    {

                        localBrandCategory = callerRecord;

                        BrandCategory_ds.query().dataSourceTable(tableNum(BrandCategory)).addRange(fieldNum(BrandCategory, BrandType)).value(queryValue(localBrandCategory.BrandType));

                    }

                }

            }

        }

    }

}

- Then add method jumpRef on the control.

public void jumpRef()

{

    //super();

    Args             args;

    MenuFunction     menuFunction;

    BrandCategory    brandCategory;

    ;

    brandCategory = BrandCategory::find(BrandTypeBase::Name, EcoResProduct.BallBrandName);

    if (!BrandCategory)

        return;

    args = new Args();

    args.caller(element);

    args.lookupRecord(BrandCategory);

    args.record(BrandCategory);

    menuFunction = new MenuFunction(menuitemDisplayStr(BrandCategoryMnu), MenuItemType::Display);

    menuFunction.run(args);

}

Finally, the things looks ok. 

Thank you for your reading. Have a good day!

Shoot