Filtering on linked table in Axapta/Dynamics Ax

axapta

I have a form in Axapta/Dynamics Ax (EmplTable) which has two data sources (EmplTable and HRMVirtualNetworkTable) where the second data source (HRMVirtualNetworkTable) is linked to the first on with "Delayed" link type.

Is there a way to set an filter on the records, based on the second data source, without having to change the link type to "InnerJoin"?

Best Solution

You could use "Outer join" instead of "Delayed" then change the join mode programmaticly when there is search for fields on HRMVirtualNetworkTable.

Add this method to class SysQuery:

static void updateJoinMode(QueryBuildDataSource qds)
{
    Counter r;
    if (qds)
    {
        qds.joinMode(JoinMode::OuterJoin);
        for (r = 1; r <= qds.rangeCount(); r++)
        {
            if (qds.range(r).value() && qds.range(r).status() == RangeStatus::Open)
            {
                qds.joinMode(JoinMode::InnerJoin);
                break;
            }
        }
    }
}

In the executeQuery() on the EmplTable datasource:

public void executeQuery()
{;
    SysQuery::updateJoinMode(this.queryRun() ? this.queryRun().query().dataSourceTable(tableNum(HRMVirtualNetworkTable)) : this.query().dataSourceTable(tableNum(HRMVirtualNetworkTable)));    
    super();
}

Sometimes this.queryRun() return null so use this.query() instead.

Update:

Note that the above is not relevant for AX 2012 and later, where you can use query filters in outer joins. See How to Use the QueryFilter Class with Outer Joins.

Related Question