Thursday, April 18, 2019

D365: Bug in Payment method lookup in Expense report form

You cannot select a payment method in an Expense report line, if you opt for any category containing comma separated value.



There is a bug in standard TrvExpenseLookupHelper class in paymentMethodLookupQuery method: it must use old good queryValue() method.


Otherwise you will get a few values instead of one in the range. Compare them before and after the fix.



[ExtensionOf(classStr(TrvExpenseLookupHelper))]
final static class avrTrvExpenseLookupHelper_Extension
{
    /// <summary>
    /// A fix for the standard method which does not treat comma separated cost type correctly
    /// </summary>
    /// <param name = "_costType"></param>
    /// <param name = "_excludeImportOnly"></param>
    /// <returns></returns>
    public static Query paymentMethodLookupQuery(TrvCostTypeEDT _costType, boolean _excludeImportOnly)
    {
        Query                   query = next paymentMethodLookupQuery(_costType, _excludeImportOnly);
        QueryBuildDataSource    qbPayMethod, qbValidatePayment;
        TrvCostType             trvCostType;

        trvCostType = TrvCostType::find(_costType);

        query = new Query();

        qbPayMethod = query.addDataSource(tableNum(TrvPayMethod));

        if (trvCostType)
        {
            query.queryType(QueryType::Join);

            qbValidatePayment = qbPayMethod.addDataSource(tableNum(TrvValidatePayment));
            qbValidatePayment.addLink(fieldNum(TrvPayMethod,PayMethod),fieldNum(TrvValidatePayment,PayMethod));
            qbValidatePayment.joinMode(JoinMode::ExistsJoin);
            //it should be converted by standard queryValue()
            qbValidatePayment.addRange(fieldNum(TrvValidatePayment, CostType)).value(queryValue(trvCostType.CostType));
        }

        if (_excludeImportOnly)
        {
            QueryBuildRange qbr = qbPayMethod.addRange(fieldNum(TrvPayMethod, AutomaticPayment));
            qbr.value(queryValue(NoYes::No));
            qbr.Status(RangeStatus::Hidden);
        }

        return query;
    }

}

Friday, April 12, 2019

Dangerous bug in CustCollectionsPoolsListPage form AX2012/D365

There is a form CustCollectionsPoolsListPage where two data sources are outer joined to the root data source with no relations (no links).



If by any reason the initial query does not contain links for these two aforementioned, SQL starts producing a Cartesian product and generating a huge temporary table. The latter can potentially lead to SQL server crash, like it happened in our environment.



The following fix may leave much to be desired but at least it creates needed links in case they are absent.

On CustTable data source we have to add an additional check for the existing query.


public void executeQuery()
{
    element.populateAgingIndicators(selectedCustAging);

    // Use the query from the cue?
    if (!useInitialQuery 
                        // Begin
                        || !this.wblCheckQuery(this.query())
                        // End: 
                        )
    {
        this.query(element.addOriginalPoolQuery(listPageHelper.getCurrentPoolQuery()));
    }


    super();

    element.setButtonAccess();
    element.setGridColumnLabels();
}


// to avoid the cartesian product in case of absent link for this outer join
private boolean wblCheckQuery(Query _query)
{
    QueryBuildDataSource custAgingDs;
    QueryBuildDataSource custAgingLegalEntityDs;
    boolean ret = true;

    custAgingDs             = _query.dataSourceName(#CustAgingDsName);
    custAgingLegalEntityDs  = _query.dataSourceName(#CustAgingLegalEntityName);
    if (!custAgingDs || !custAgingLegalEntityDs || custAgingDs.linkCount() <= 0 || custAgingLegalEntityDs.linkCount() <= 0)
    {
        ret = checkFailed("Saved query is corrupted. Try to recreate the cue");
    }
    return ret;
}

From SQL perspective we can catch such an issue by the following query.


use tempdb
select * from sys.dm_db_session_space_usage spu
join sys.dm_exec_sessions s on s.session_id = spu.session_id
join sys.dm_exec_requests r on s.session_id = r.session_id
cross apply sys.dm_exec_sql_text(sql_handle) t 
order by internal_objects_alloc_page_count desc

Friday, April 5, 2019

AX2012-D365 Bug: TrvWorkflowExpLines query misses fields

The issue appears once you post project or inter-company expenses.

Once you approved and never opened Accounting distribution form for an expense, Project activity number field will be empty, or, for the Inter-company case, even the project information will be lost.




This is how TrvWorkflowExpLines query should look like to overcome the issue.


Wednesday, April 3, 2019

Accessing FormDataSource

Say, we need to change the form data source sorting order or any other more complex change.

This is an old but good note by Vania Kashperuk about the subject in AX 2012.


public void init()
{
    super();
    this.queryBuildDataSource().addSortField(fieldNum(wblDescartesInbound, RecId), SortOrder::Descending);
}



Tuesday, March 26, 2019

How to get number of rows loaded in a grid

In fact we can use numberOfRowsLoaded on a linked form data source; however, it shows the number of those cached only.

So, if you are editing row number 28, for example, and then call ExecuteQuery() on the data source, you will probably get a lower number of rows loaded in the grid yet, say, 19.

In order to get the right number, you should use the aforementioned method together with allRowsLoaded() one.

public int wblNumberOfRowsLoaded()
{
    int wblNumOfRec;
    int wblCounter;

    wblNumOfRec = PSAActivityEstimates_DS.numberOfRowsLoaded();
    // nothing to load yet
    if(!wblNumOfRec)
    {
        return wblNumOfRec;
    }
    // so far we numbered those lines cached only
    while(!PSAActivityEstimates_DS.allRowsLoaded())
    {
        // so let's move it on until the end of loading
        PSAActivityEstimates_DS.setPosition(wblNumOfRec);
        PSAActivityEstimates_DS.getNext();
        // just to avoid never ending adventure
        if(wblCounter>1000)
        {
            break;
        }
        wblCounter++;
        // get the number for the next iteration
        wblNumOfRec = PSAActivityEstimates_DS.numberOfRowsLoaded();
    }
    // now all numberOfRowsLoaded() calls will return the correct number of rows in grids
    wblNumOfRec = PSAActivityEstimates_DS.numberOfRowsLoaded();

    return wblNumOfRec;
}

Friday, March 8, 2019

D365 table name from tableid

Open SQL MSSMS and run the following script with a sought number.


SELECT 
   [ID]
      ,[NAME]
      ,[RECVERSION]
      ,[RECID]
  FROM [AxDB].[dbo].[TABLEIDTABLE]
  where id = 1459

Wednesday, March 6, 2019

Extended version of Universal Field Changer for Microsoft Dynamics AX2012

Oh yeah! The Field changer is still on the road! Now equipped with two updating options:


Feel free to use this surgeon's tool at your own risk! Grab it from here.