How to add Search/ Find /Filter functionality to Display method in dynamics AX

Dynamics AX, can retrieve output values by using table field and display method. As we know, usually standard dynamics AX filter functionality is working only for table field. But sometimes we have to do filter functionality for display method too. Below I’m going to explain how to add a filter functionality for Display method.

1.      Here I have added display method (disCustName) to display customer name on the “IND_BusRelation” form. In this form main data source is “smmBusRelTable”.

2.      Change “AutoDeclaration” property from No to Yes of the “disCustName” data field.

3.      Override “context()” method of the “disCustName” data field and add following code.

  

public void context()

{

    int             selectedMenu;

    formrun         fr;

    Args            ag;

    Name            strtext;

    querybuilddataSource qb1;

    queryrun    qr;

    query       q;

    PopupMenu menu = new PopupMenu(element.hWnd());

    int a = menu.insertItem('Filter By Field');

    int b = menu.insertItem('Filter By Selection');

    int c = menu.insertItem('Remove Filter');

    ;

    selectedMenu = menu.draw();

    switch (selectedMenu)

    {

    case -1: //Filter by field

            break;

    case a:

            ag = new args('SysformSearch');

            fr = new formrun(ag);

            fr.run();

            fr.wait();

//Reading User entered value for filter process

            strtext = fr.design().controlName('FindEdit').valueStr(); 

            if(strtext)

            {

//Creating a query for filter

                q   = smmBusRelTable_ds.query();

                qb1 = q.dataSourceTable(tablenum(smmBusRelTable));

                qb1 = qb1.addDataSource(TableNum(CustTable));

               qb1.addLink(FieldNum(smmBusRelTable,CustAccount),FieldNum(CustTable,AccountNum));

                qb1.addRange(FieldNum(CustTable,Name)).value(strtext);

                smmBusRelTable_ds.query(Q);

                smmBusRelTable_ds.executeQuery();

            }

            break;

    case b:   // Filter By Selection

            q   = smmBusRelTable_ds.query();

            qb1 = q.dataSourceTable(tablenum(smmBusRelTable));

            qb1 = qb1.addDataSource(TableNum(CustTable));

           qb1.addLink(FieldNum(smmBusRelTable,CustAccount),FieldNum(CustTable,AccountNum));

           qb1.addRange(FieldNum(CustTable,Name)).value(disCustName.valueStr());

            smmBusRelTable_ds.query(Q);

            smmBusRelTable_ds.executeQuery();

            break;

    case c :   // Remove Filter

            q   = new Query();

            qb1 = q.addDataSource(tablenum(smmBusRelTable));

            qb1.clearLinks();

            qb1.clearRanges();

            smmBusRelTable_ds.query(Q);

            smmBusRelTable_ds.removeFilter();

            break;

    Default:

            break;

    }

}

4.       Run the form and do right Click on Customer Name field.

from : http://dynamicsaxsolutionsworld.blogspot.com/2011/09/how-to-add-search-find-filter.html

How to Count(Sum) of Form in AX 2009

DataSource excuteQuery method
A) Support for multiple DataSources
B) Support the user to filter the data.
C) Support mass of data

smmBusRelTable.excuteQuery()
{...
    m_qrCount = new QueryRun(smmBusRelTable_ds.queryRun().query().pack());
    m_qbdsCount =   m_qrCount.query().dataSourceNo(1);
    m_qbdsCount.addSelectionField(fieldNum(smmBusRelTable,RecId),SelectionField::Count);
    while (m_qrCount.next())
    {
        m_count = any2int(m_qrCount.getNo(1).RecId);
    }
...
}

How to rebuild Table Ids - out of sync remedies/prevention

Note that the below was written in 2010 with AX 4.0 and 2009 in mind. 

Sometimes table Id's will become out of sync between environments. The number one way that this is found out is when someone promotes tables that were created after other tables that are unpromoted to a new environment without the 'Import with ID values' checked and then other tables promoted with that check box checked on the import. Remember that AX manages and drives what is in SQL including indexes, fields, relations, etc. 





About table Ids:
SQL uses Table Ids to uniquely identify tables in SQL. AX determines, stores, and manages table Ids, not SQL. For this reason, if table Ids need to be changed, they should be changed through AX. When a SQL refresh


Why it is important to have the table Id's in sync between environments?
Simple answer: SQL refreshes to lower environments are only possible when Id's are in sync. If they are out of sync, there are all sorts of issues that can pop up including data loss. Honestly, there is no reason that ID's should be out of sync as long as everything is managed properly. Be diligent or you're going to have a bad time.
 
When would you import code without table Id's checked?
If there are multiple development environments where developers are working and will ultimately be moving all code to one environment (e.g. 4 dev environments, 1 test), the id's from the test environment will be where the id's will be created.
 
How are Id's created in AX?
AX 2012 Ids will not be the same formula as below. AXUtil assigns base Ids at install and new ones regardless of layer increment off of a 6 digit number (eg 105067)
When tables, fields, classes, etc are saved in the AOT, they grab the next available Id based on the layer they are imported into. Each layer has 10000 ids that it can use. The below shows the values of the Ids that would be created based on the layer.

• BUS – 2xxxx (e.g. 20014)
• VAR – 3xxxx (e.g. 30014)
• CUS – 4xxxx (e.g. 40014)
• USR – 5xxxx (e.g. 50015)

How to correct the scenario where table Id's are out of sync.
There are multiple ways to take care of syncing environments but some methods may be better than others depending on the situation. If different data is needed to be retained in different environments, option 1 below cannot be taken as it would wipe out the data in the non-master environment when the tables are synchronized

• Syncing all environments based on a single, 'master' environment (more than likely PROD)
• Put a code freeze on all environments
• Export any code that is not in the 'master' environment
• Refresh environments' app folders with the 'master' environment's app folder
• Reimport unpromoted work back into the appropriate environments making sure that table Id's will not overlap between environments
• Manually change Table Id's in Notepad from XPO
  

  • Try exporting the object (with id's), then edit the xpo in notepad to change the id, then reimport the object (with id's) from the edited xpo.
  • Export the tables of interest (specific ones or an entire layer)
  • Import the modified XPO with ID values
• Manually change Table Id's in AX via X++
  • This can be tricky if you are trying to change an id to a value that is already set to another table. This approach could cause data loss in sync if not careful.
  • Use the following methods to do change the table ids. They're pretty self explanatory. 
  • The most useful ones
  • ReleaseUpdateDB::changeTableId(_oldTableId, _newTableId, _tableName);
  • Changes the table Id from one value to another
  • ReleaseUpdateDB::changeFieldId(_tableId, _oldFieldId, _newFieldId, _tableName, _fieldName); 
  • Changes the field Id from one value to another.  
  • Other options 
  • ReleaseUpdateDB::changeTableByName(_sqlName, _oldTableId, _newTableId);
  • ReleaseUpdateDB::changeFieldByName(_sqlNameOld, _oldTableId, _newTableId, _fieldIdNew);
  • ReleaseUpdateDB::changeTableByAOTName(_nameAOT, _oldTableId, _newTableId);
  • ReleaseUpdateDB::changeFieldByAOTName(_nameAOT, _oldTableId, _newTableId, _newFieldId)
  • I have some code to do this but you can check sample code here for some ideas. I thought it was pretty solid:http://dev.goshoom.net/en/2011/11/id-change/  Thanks to 
     Martin Dráb

SysDictTable dictTable;
DictField dictField;
TableId tableId;
FieldId fieldId;
SqlDictionary sqlDictionaryTable;
SqlDictionary sqlDictionaryField;
 
setPrefix("Update of data dictionary IDs");
tableId = dictionary.tableNext(0);
ttsbegin;
 
while (tableId){
    dictTable = new SysDictTable(tableId);
    setPrefix(dictTable.name());
 
    if (!dictTable.isSystemTable())
    {
        //Finds table in SqlDictionary by name in AOT, if ID was changed.
        //Empty field ID represents a table.
        select sqlDictionaryTable
            where sqlDictionaryTable.name == dictTable.name()
            && sqlDictionaryTable.fieldId == 0
            && sqlDictionaryTable.tabId != dictTable.id();
 
        if (sqlDictionaryTable)
        {
            //Updates table ID in SqlDictionary
            if (ReleaseUpdateDB::changeTableId(
                sqlDictionaryTable.tabId,
                dictTable.id(),
                dictTable.name()))
            {
                info(strFmt("Table ID changed (%1 -> %2)", sqlDictionaryTable.tabId, dictTable.id()));
            }
        } 
        fieldId = dictTable.fieldNext(0);
 
        //For all fields in table
        while (fieldId)
        {
            dictField = dictTable.fieldObject(fieldId);
 
            if (dictField.isSql() && !dictField.isSystem())
            {
                //Finds fields in SqlDictionary by name and compares IDs
                select sqlDictionaryField
                    where sqlDictionaryField.tabId == dictTable.id()
                    && sqlDictionaryField.name == dictField.name()
                    && sqlDictionaryField.fieldId != 0
                    && sqlDictionaryField.fieldId != dictField.id();
 
                if (sqlDictionaryField)
                {
                    //Updates field ID in SqlDictionary
                    if (ReleaseUpdateDB::changeFieldId(
                        dictTable.id(),
                        sqlDictionaryField.fieldId,
                        dictField.id(),
                        dictTable.name(),
                        dictField.name()))
                    {
                        info(strFmt("Field %1 - ID changed (%2 -> %3)",
                            dictField.name(),
                            sqlDictionaryField.fieldId,
                            dictField.id()));
                    }
                }
            }
            fieldId = dictTable.fieldNext(fieldId);
        }
    }
    tableId = dictionary.tableNext(tableId);
}ttscommit;

Inventory Aging Report analysis

库存账龄分析

本文作者:老木(wood9999@163.com)

摘要：讨论库存账龄分析的目的与后续的分析方法，为降低库存提供实际的操作方法。

   

1.     库存账龄与库存周转率

……提到库存账龄，就不得不说到库存周转率。库存周转率是在某特定的周期，销售成本与存货平均余额的比率。用以衡量一定时期内存货资产的周转速度，是反映企业的供应链的整体效率的绩效指标之一，而且很多企业都把它作为整体经营业绩的考核指标之一。而库存账龄是在某时间节点，某种或者某类存货的库存时间的加权平均值。很明显，库存周转率越高，库存账龄越低，但是二者又不是反比关系（比较简单的证明就是同样的平均库存，入库时间的不同就会引起库存账龄很大的差异），所以虽然这二者有着千丝万缕的联系，但是不能简单的把库存账龄看成库存周转率的一个衍生指标来对待。

2.     库存账龄分析的目的

……在库存账龄分析中,其目的主要有以下两点：

一、库存成本的控制。库存账龄在ERP系统内，应该可以查询指定的时间点，各库存存货的库存账龄情况，即从入库起在仓库中放置了多久。与应收账款的账龄一样，存货的库存账龄越长，说明周转越慢，占压的资金也就越多。这也就是我们大家平常所说的呆滞料。对于呆滞料，应该分析其产生的原因，从计划的源头控制入手，才能最有效的降低无效的库存，达到降低库存总额的目的。呆滞料实际上包括两部分：不周转的物料和周转慢的物料，对于不周转的物料，显然除了上述工作外，还应该做相应的处理：比如代用或者变卖。

二、存货跌价准备的计提。等同于应收账款的坏账计提，对于超龄的库存，也应该做好存货损失的准备，更真实的反映库存的实际价值。

3.     库存账龄的计算

……手工计算库存账龄是很困难的。在ERP系统中，库存账龄的计算相当方便。如果要计算某一仓库或者全部存货的库存账龄（虽然该数字可能没有实际的意义），那么公式如下：

库存账龄=∑(批次入库金额/统计时点库存总额*批次入库时间)。

下面我举例一说明：

在2008年10月6日，存货A的库存为1000，系统自动搜索入库单，得到如下入库单：

入库单号  日期     数量

NO.025    10月5日入库200

NO.023    10月4日入库300

NO.022    10月3日入库300

NO.020    10月2日入库400

NO.015    10月1日入库100

系统默认先进先出的原则，从10月6日的入库单倒推满1000为止，也就是200（10月5日）+300（10月4日）+300（10月3日）+200（10月2日入库的400只取值200即可）=1000.

那么：库存账龄=200*/1000*1+3000/100*2+300/1000*3+200/1000*4=0.2+0.6+0.9+0.8=2.5天

库存账龄还有另外一种报表方式，反映的是库存账龄的集中度，举例二如下：

库存30天以下的金额：   1000

库存30天--90天的金额： 2000

库存90天--180天的金额：1500

库存180天以上的金额：   500

4.     库存账龄标准的设定

……通过上面的讨论，我们已经知道了库存账龄分析的目的，和库存账龄的计算方法，但是，在现实的管理中，如何知道某种存货的库存时间长了呢？如何定义呆滞料，标准是什么呢？

我下面介绍一下我个人的想法：

1、          按照财务对存货跌价准备的计提的期限设定。比如：财务规定超过180天，存货跌价准备计提比例为2%；超过360天，存货跌价准备计提比例为5%。那么结合库存账龄集中度的表格，对超过180天以上的物料应该立即处理，上面例二中超过180天的500就应该按照比例计提了。对于90天以上的物料应该额外关注。

2、          按照公司制定的存货周转率设定库存账龄标准。举例如下：

存货周转率设定为1次/月，那么库存账龄的平均值是0.5月，最大值不能超过1月。按照ABC分类，A类物料占存货总额的80%，B类物料占存货总额的15%，C类物料占存货总额的5%。显然，把所有的物料设置为同样的周转速率是不对的，对A类物料的额外关注，多订货，分批次，对于降低库存总额是最有效的。那么：假定，B类物料的为2个月，C类物料的账龄为4个月，在这个条件下，只有A类物料的账龄不大于0.625月时才可以满足要求：80%*0.625+15%*2.0+5%*4.0=1

3、          按照每种物料的经济批量设定。也就是说，物料的存储时间不应超过经济批量的平均消耗时间。这种计算方式忽略了安全库存对于库存账龄的影响，但是很明显，只适用于在物料均匀比较消耗，标准方差很小的情况下。

4、          参考采购提前期确定，物料的存储时间不应超过采购的周期。显然，这种方法比第3种更容易获得参考值。

5、          如果该物料有保质期，库存账龄超过保质期就是报废了。那么结合ERP系统中的保质期和批次管理，该库存账龄的危险期必然要小于保质期。

以上的各种方法要针对不同类型的物料，选择使用。

5.     ……后续的工作

……在物料管理工作中，库存账龄分析的方法，是发现库存积压最有效的手段之一。但是发现问题很重要，更重要的如何解决问题。

所以，在发现物料积压的情况之后，一定要把发现的问题立项解决，完成一个PDCA的循环。当库存降低后，更容易发现库存的问题，这是一个良性的循环过程。

需要注意的是：

1、          对于发现积压的物料，不能一概予以同样的关注，因为管理也需要成本的。发动机周转速度变慢，要比标准件积压更可怕。

2、          在库存账龄分析中，用金额表示比用数量表示，更直观，更有效。

3、          对于标准账龄的设定，也是需要不断修正的，因为提高库存的周转速度，降低无效库存为最终目的。

4、          库存账龄分析是事后诸葛亮，是纠正方法，远不如事先控制也就是预防有效。在这点上，与质量管理的原理是一样的。