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 myTrvExpenseLookupHelper_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;
    }

}

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

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.

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(myDescartesInbound, RecId), SortOrder::Descending);
}