This post is machine-translated. The original post in german language can be found here.

AX 2012: Using computed colums to create subselects

Using computed columns of view creates you the ability, to write subselects/subqueries.

Find below a method, which is inspired by inventTable.productName() and can be used to display the name of an item.

private static server str compColItemName()
{
    #define.ViewName("ItemNameView")

    #define.DataSourceName("InventTable")
    #define.FieldItemId("itemId")
    #define.FieldProduct("Product")
    str sProduct;
    DictView dictView;
    str translationSQLStr;
    str productSQLStr;

    dictView = new DictView(tableNum(#ViewName));

    sProduct = dictView.computedColumnString
        (#DataSourceName,
        #FieldProduct,
        FieldNameGenerationMode::FieldList,
        true);

    translationSQLStr = strFmt("SELECT Name FROM EcoResProductTranslation WHERE EcoResProductTranslation.Product = %1 AND LanguageId = '%2'", sProduct, CompanyInfo::find().LanguageId);
    productSQLStr = strFmt("SELECT DisplayProductNumber FROM EcoResProduct WHERE EcoResProduct.RecId = %1", sProduct);

    return strFmt("isNUll((%1), (%2))", translationSQLStr, productSQLStr);
}

ViewA view cotaining the above method could look like in the screenshot shown.

Such a view could be, for example, a replacement for display methods in forms. This would have the advantage that you could sort or filter by the item name.

However, such use may also have disadvantages, such as the contents of a computed column is not updated immediately after entering the item number (like a display method do).

These post applies to following version:
Dynamics AX 2012

Peter 09/26/2014 14:26 | #1


Heinz Schweda 10/01/2014 09:50 | #2


Peter 10/01/2014 10:17 | #3


Jos Litjens 11/19/2014 10:17 | #4

Thank you very much for this code! Upon implementation however I noticed it wasn't complete. InventTable.productname() at one point calls EcoResProductTranslation::findByProductOrSystemLanguage(). That method searches for a translation in the companyInfo language, but when that translation isn't found it also searches for a translation in the system language. That last piece was missing from the snippt. i added it and the method now looks like this:

private static server str compColItemName()
{
#define.ViewName("AXTdps_ProdRouteDimReqTransView")

#define.DataSourceName("InventTable")
#define.FieldItemId("ItemId")
#define.FieldProduct("Product")
str sProduct;
DictView dictView;
str ret;
str translationSQLStrCompLang, translationSQLStrSysLang;
str productSQLStr;

dictView = new DictView(tableNum(#ViewName));

sProduct = dictView.computedColumnString
(#DataSourceName,
#FieldProduct,
FieldNameGenerationMode::FieldList,
true);

translationSQLStrCompLang = strFmt("SELECT Name FROM EcoResProductTranslation WHERE EcoResProductTranslation.Product = %1 AND LanguageId = '%2'", sProduct, CompanyInfo::find().LanguageId);
translationSQLStrSysLang = strFmt("SELECT Name FROM EcoResProductTranslation WHERE EcoResProductTranslation.Product = %1 AND LanguageId = '%2'", sProduct, SystemParameters::getSystemLanguageId());
productSQLStr = strFmt("SELECT DisplayProductNumber FROM EcoResProduct WHERE EcoResProduct.RecId = %1", sProduct);

ret = strFmt("isNUll((%1), (%2))", translationSQLStrCompLang, translationSQLStrSysLang);
ret = strFmt("isNUll((%1), (%2))", ret, productSQLStr);
return ret;
}

Heinz Schweda 11/26/2014 09:53 | #5

Thank you very much for your extension.

So everybody, who is looking for a replacement for the itemName()-method, should use the code of Jos.

Jon 06/10/2015 21:11 | #6

Can this be added to PurchLineOpenOrder form? How would you set up the joinsource on the view once added to the above form?

Heinz Schweda 06/16/2015 10:43 | #7

Yes, you can use the abobe view in form PurchLineOpenOrder.

You have to do the following steps:
1) Add view as new dataSource to form
2) Set properties of this new datasource:
JoinSource: PurchLine
LinkType: InnerJoin
3) Overwrite the init()-Method of the new datasource like the following

public void init()
{
super();

this.query().dataSourceName(this.name()).clearLinks();
this.query().dataSourceName(this.name()).clearDynalinks();

this.query().dataSourceName(this.name()).addlink(fieldNum(PurchLine, ItemId), fieldnum(ItemNameView, ItemId));
}

Please note, that there are additional steps needed, depending on your form. Especially if your form is not a "read-only" form like PurchLineOpenOrder is.

Max 09/15/2015 18:12 | #8


Heinz Schweda 09/19/2015 10:16 | #9


Rene Fauland 05/31/2016 07:15 | #10

I think the computed column gets only executed once (during synchronization) - just check the generated view direct in SQL.
In this case the language info from SystemParameters should only work if you have just one single partition and language from CompanyInfo will not work (i assume it uses the company settings from dat or to be more precise, the company it's in during synchronization) :-/

Add comment
 
 

 

 
 
 
Posts of the actual month
November 2019
MoTuWeThFrSaSu
 123
45678910
11121314151617
18192021222324
252627282930 
 
© 2006-2019 Heinz Schweda | Imprint | Contact | German version | Mobile version
In order to provide you with better service, this site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies.