How to add a form range value when using a view as a datasource?

Question

I'd like to be able to create a custom filter on a new form datasource which is a custom view I have created.

I've created a custom view, ABCPurchLineBusUnitView, (from a custom query that joins the PurchLine table to the DefaultDimension view) to append to an existing form's datasources (PurchLineOpenOrder). The DisplayValue column in the form's main grid is returning the Business Unit dimension value for all PurchLines.

Customising the form:-

In the init() method of the new datasource I have joined the view to the existing PurchLine datasource as follows:

public void init()
{
    #DEFINE.DataSourceBusUnit(7)

    QueryBuildDataSource qbds;

    super();

    qbds = this.query().dataSourceNo(#DataSourceBusUnit);
    qbds.clearLinks();
    qbds.joinMode(JoinMode::InnerJoin);
    qbds.relations(true);
qbds.addLink(fieldNum(PurchLine,RecId),fieldNum(ABCPurchLineBusUnitView,RecId));

    qbrBusinessUnit = qbds.addRange(fieldNum(ABCPurchLineBusUnitView, DisplayValue));
}

In the executeQuery() method of the same datasource I've set a hard-coded range value to bring back only those PurchLines that relate to a particular Business Unit.

public void executeQuery()
{
    qbrBusinessUnit.value(queryValue('Business unit name here'));
    super();
}

The displayed column works fine. However, using CTRL+G to view the user filter it is clear that the filter value is not present and has not been applied.

On the same form, when I apply a filter on an existing datasource it works fine using the above technique which is confusing.

Also, I would like this to work on an outer-joined datasource and have tried using QueryFilters but no luck there either using the same datasource methods.

10p 10p · Accepted Answer · 2018-10-22T19:45:45

You should use executeQuery of the root datasource (PurchLine) because ABCPurchLineBusUnitView is joined using InnerJoin so executeQuery is not called there.

Other than that, a few observations:

Instead of using qbds = this.query().dataSourceNo(#DataSourceBusUnit); you can use qbds = this.query().dataSourceTable(tableNum(ABCPurchLineBusUnitView));, which I am sure you already know as standard code in \Forms\PurchLineOpenOrder\Data Sources\PurchLine\Methods\init has an example of this syntax.
If property LinkType of datasource ABCPurchLineBusUnitView is already set to InnerJoin, line qbds.joinMode(JoinMode::InnerJoin); is not needed and can be removed.
You shouldn't use both qbds.relations(true); and qbds.addLink(fieldNum(PurchLine, RecId), fieldNum(ABCPurchLineBusUnitView, RecId));. Try replacing qbds.relations(true); with qbds.relations(false); if you need to use addLink.

How to add a form range value when using a view as a datasource?

1 Answers