Filter form's main datasource through the linked datasource

Question

I have a custom form which has two datasouces. Lets say to make it easer that my form has the Salestable and SalesLines datasources.

for example I could say that I have a filter which is bounded with the ItemGroup edt .

I want to filter the SalesTable Datasource through this filter in order to 'show' in a grid which is connected with the SalesOrders datasouce :

''all SalesOrders which 'have' saleslines with saleslines.ItemGroup == somethingfromFilter''.

Whatever I tried is faild. Can someone help me?

FYI: the datasources properties I asume that are proper linked: SalesLine.JoinSource = SalesTable All of my tries was in modified method of the filter.

Answer 1

I will assume you have an ItemGroupId field in the SalesLine table. This is not standard.
Also I will assume you have a filter field in the form called ItemGroupIdCtrl.

Add a helper datasource SalesLineEx:

• Name: SalesLineEx
• Table: SalesLine
• Allow...: No
• JoinSource: SalesTable
• LinkType: ExistJoin

In the SalesTable datasource executeQuery modthod:

public void executeQuery()
{
    SysQuery::findOrCreateRange(salesLineEx_ds.queryBuildDataSource(), fieldNum(SalesLine,ItemGroupId)).value(ItemGroupIdCtrl.text());
    salesLineEx_ds.queryBuildDataSource().enabled(ItemGroupIdCtrl.text() != '');
    super();
}

This will check the existence of sales lines with a matching field if the filter field has a value. If no value is entered the filtering datasource is disabled.

Most likely you will want to research after change of filter value:

public boolean modified()
{
    boolean ret = super();
    salesTable_ds.executeQuery();
    return ret;
}

Answer 2

Filter in a code:

I have a custom form which has two datasouces.

form has the Salestable and SalesLines datasources.

I want to filter

'all SalesOrders which 'have' saleslines with saleslines.ItemGroup == somethingfromFilter''

Prerequisite: a custom form use two joined datasources. Join type is one of: Delayed, InnerJoin.

Solution

It need to modify a method Init in SalesLine datasource.

this.query().datasource(tablenum(salesLine)).addDatasource(...)
...

See examples in any form with init-method and addDatasource text )

Answer 3

I may be misreading your question, but if you are asking how to filter with ranges, it's a pretty basic task.

Look at \Forms\PurchTable\Data Sources\PurchLine\Methods\init to see how they exclude lines that are "Deleted".

If you want to join SalesLines to InventTable in order to get the item group, you can look at how you can modify the form's query in another example here:

\Forms\PurchTable\Data Sources\PurchTable\Methods\linkActive

Answer 4

Disclimer: I do not have a AX2012 now. I have posted screenshots from AX2009. Functional is the same

Prerequisite: you should have a cross-references to use this functional.

Repro steps:

1. open a form
2. click toolbar button Advanced Filter/Sort or Ctrl+F3
3. mouse right-click on Sales order table in the Structure tree.
4. choose and click on 1:n and than Order Lines to add additional table in a filter (you should have a cross-reference)
5. mouse right-click on Order line table in the Structure tree.
6. choose and click on n:1 and Items (Item number) to add additional table in a filter (you should have a cross-reference)
7. click on Add button and choose talble Items and field Item groups in a Range grid. Specify a criteria = somethingfromFilter

See screenshots below.

Note: Axapta uses additional tables in query only. You need to change a form business logic in AOT to display additional tables/fields on the form.