Switch based QueryBuildDataSource remembering previous links

(1) Share

Report

Posted on by Justin Kruger

186

Hi,

I have a custom filter combo box on the EcoResProductDetailsExtended form, this filter is based on an enum.

The purpose of this is to filter the EcoResProductDetailsExtended based on the "Stopped" state of the products.

The filter field has a selectionChanged() event handler with the below logic.

internal final class ABCEcoResProductDetailsExtendedHandler
{
    [FormControlEventHandler(formControlStr(EcoResProductDetailsExtended, ABCProductStateFilter), FormControlEventType::SelectionChanged)]
    public static void ABCProductStateFilter_OnSelectionChanged(FormControl sender, FormControlEventArgs e)
    {
        FormComboBoxControl  callerControl = sender as FormComboBoxControl;
        FormRun              form = callerControl.formRun();
        FormDataSource       inventTable_ds = form.dataSource(formDataSourceStr(EcoResProductDetailsExtended, InventTable));

        ABCEcoResProductDetailsExtendedFilterHelper::applyProductStateFilter(inventTable_ds, callerControl.selection());

    }

}

This handler in turn calls a helper class with the below logic.

internal final class ABCEcoResProductDetailsExtendedFilterHelper
{
    public static void applyProductStateFilter(FormDataSource _formDataSource, int selection)
    {
        QueryBuildDataSource inventTableDS, inventItemSetupDS;

        inventTableDS = SysQuery::findOrCreateDataSource(_formDataSource.query(), tableNum(InventTable));
        inventTableDS.clearLinks();

        switch (selection)
        {
            case ABCProductStateFilter::Active :

                inventItemSetupDS = inventTableDS.addDataSource(tableNum(ABCInventItemSetupStoppedUnion));
                inventItemSetupDS.addLink(fieldNum(InventTable, ItemId), fieldNum(ABCInventItemSetupStoppedUnion, ItemId));
                inventItemSetupDS.joinMode(JoinMode::NoExistsJoin);
                inventItemSetupDS.relations(false);
                break;

            case ABCProductStateFilter::StoppedInvent :

                inventItemSetupDS = inventTableDS.addDataSource(tableNum(InventItemInventSetupStopped));
                inventItemSetupDS.addLink(fieldNum(InventTable, ItemId), fieldNum(InventItemInventSetupStopped, ItemId));
                inventItemSetupDS.joinMode(JoinMode::ExistsJoin);
                inventItemSetupDS.relations(false);
                break;

            case ABCProductStateFilter::StoppedPurch :

                inventItemSetupDS = inventTableDS.addDataSource(tableNum(InventItemPurchSetupStopped));
                inventItemSetupDS.addLink(fieldNum(InventTable, ItemId), fieldNum(InventItemPurchSetupStopped, ItemId));
                inventItemSetupDS.joinMode(JoinMode::ExistsJoin);
                inventItemSetupDS.relations(false);
                break;

            case ABCProductStateFilter::StoppedSales :

                inventItemSetupDS = inventTableDS.addDataSource(tableNum(InventItemSalesSetupStopped));
                inventItemSetupDS.addLink(fieldNum(InventTable, ItemId), fieldNum(InventItemSalesSetupStopped, ItemId));
                inventItemSetupDS.joinMode(JoinMode::ExistsJoin);
                inventItemSetupDS.relations(false);
                break;

            case ABCProductStateFilter::All:
                break;
        }

        _formDataSource.executeQuery();
    }

}

Each of these filter queries work individually, meaning when the form initializes and you execute the first selection change. However when a subsequent selection change is actioned the query still has previous links from the previous joined data sources. ie. where changing from "Active" to "StoppedInvent" it would correctly show only items that are stopped for Invent. When then changing from "StoppedInvent" to "StoppedPurch" it will compound the 2 links and the query will display products that are stopped for both Invent and Purch. This is not the desired outcome. I would only then expect to see items stopped for Purch.

It appears that clearLinks() is not actually clearing the previous links.

I know I am doing something wrong, if anyone has had this issue before some guidance would be greatly appreciated.

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Suggested answer

Bharani Preetham Pe... 3,634 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Did you try clearing the links before creating the datasource? I mean to say that in the selectionChange() event, you are able to fetch formdatasource. You can try clearing the links at that point itself and pass this buffer as FormdataSource in the method. Inside the helper class, you can check if this formdatasource is not null and can proceed with switch statement.

Was this reply helpful? Yes No
Martin Dráb 240,095 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
The bug is that you keep adding more and more data sources (by calling addDataSource() every time when applyProductStateFilter() is executed). You should disable those you don't want to use at the moment.

You can add data sources in code (which can be simplified by using existing relations), but I would add them as normal form data sources and just control them in code.

My idea is something like this:
internal final class ABCEcoResProductDetailsExtendedFilterHelper { public static void applyProductStateFilter(FormDataSource _inventTableQbds, ABCProductStateFilter _selection) { Query query = _inventTableQbds.query(); QueryBuildDataSource inventStoppedUnionDs = _query.dataSourceTable(tableNum(ABCInventItemSetupStoppedUnion)); QueryBuildDataSource inventStoppedDs = query.dataSourceTable(tableNum(InventItemInventSetupStopped)); QueryBuildDataSource purchStoppedDs = query.dataSourceTable(tableNum(InventItemPurchSetupStopped)); QueryBuildDataSource salesStoppedDs = query.dataSourceTable(tableNum(InventItemSalesSetupStopped)); // Disable all inventStoppedUnionDs.enabled(false); inventStoppedDs.enabled(false); salesStoppedDs.enabled(false); salesStoppedDs.enabled(false); // Enable just what you need switch (_selection) { case ABCProductStateFilter::Active: inventStoppedUnionDs.enabled(true); break; case ABCProductStateFilter::StoppedInvent: inventStoppedDs.enabled(true); break; case ABCProductStateFilter::StoppedPurch: purchStoppedDs.enabled(true); break; case ABCProductStateFilter::StoppedSales: salesStoppedDs.enabled(true); break; } _inventTableQbds.executeQuery(); } }

If you looked at the query used by the form, you'd see the extra data sources and likely understood the problem by yourself. You're trying to generate a query by code, so you should test whether it really created the query you wanted. Testing whether the query returns the right data is another step. Ignoring the actual query makes your debugging much more complicated.

Was this reply helpful? Yes No
Justin Kruger 186 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi Martin,

Thank you for your response.

If I want to refrain from adding additional data sources onto the form object directly and rather handle that in code while incorporating your suggested solution of disabling the data sources that I do not need, I arrive at the below code.

When I debug this I can see the data sources being disabled correctly, however the query is still compounding joins on subsequent selection changes, similar to the original issue.

Is there a better way to add the data sources in the code ?

internal final class ABCEcoResProductDetailsExtendedFilterHelper { public static void applyProductStateFilter(FormDataSource _inventTableQbds, ABCProductStateFilter _selection) { QueryBuildDataSource inventStoppedUnionDs, inventStoppedDs, purchStoppedDs, salesStoppedDs; inventStoppedUnionDs = _inventTableQbds.queryBuildDataSource().addDataSource(tableNum(ABCInventItemSetupStoppedUnion)); inventStoppedUnionDs.addLink(fieldNum(InventTable, ItemId), fieldNum(ABCInventItemSetupStoppedUnion, ItemId)); inventStoppedUnionDs.joinMode(JoinMode::NoExistsJoin); inventStoppedDs = _inventTableQbds.queryBuildDataSource().addDataSource(tableNum(InventItemInventSetupStopped)); inventStoppedDs.addLink(fieldNum(InventTable, ItemId), fieldNum(InventItemInventSetupStopped, ItemId)); inventStoppedDs.joinMode(JoinMode::ExistsJoin); purchStoppedDs = _inventTableQbds.queryBuildDataSource().addDataSource(tableNum(InventItemPurchSetupStopped)); purchStoppedDs.addLink(fieldNum(InventTable, ItemId), fieldNum(InventItemPurchSetupStopped, ItemId)); purchStoppedDs.joinMode(JoinMode::ExistsJoin); salesStoppedDs = _inventTableQbds.queryBuildDataSource().addDataSource(tableNum(InventItemSalesSetupStopped)); salesStoppedDs.addLink(fieldNum(InventTable, ItemId), fieldNum(InventItemSalesSetupStopped, ItemId)); salesStoppedDs.joinMode(JoinMode::ExistsJoin); // Disable all inventStoppedUnionDs.enabled(false); inventStoppedDs.enabled(false); purchStoppedDs.enabled(false); salesStoppedDs.enabled(false); // Enable just what you need switch (_selection) { case ABCProductStateFilter::Active: inventStoppedUnionDs.enabled(true); break; case ABCProductStateFilter::StoppedInvent: inventStoppedDs.enabled(true); break; case ABCProductStateFilter::StoppedPurch: purchStoppedDs.enabled(true); break; case ABCProductStateFilter::StoppedSales: salesStoppedDs.enabled(true); break; } _inventTableQbds.executeQuery(); } }

Was this reply helpful? Yes No
Verified answer

Martin Dráb 240,095 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

You indeed still have the same problem. Every time applyProductStateFilter() gets executed, you call addDataSource() to add all the data source again.

If you insist on adding the data sources in code, do it once when initializing the form. And if you want to do it in applyProductStateFilter() anyway, use findOrCreateDataSource() to either create a data source or reuse it if it already exists.

Was this reply helpful? Yes No
Justin Kruger 186 on at

Like
a
(1)

Report
Copy link

Link copied!

Thank you Martin.

The combination of disabling the data sources and utilizing findOrCreateDataSource() solved my issue.

I understand where I went wrong by continuously compounding data sources with addDataSource()

your help is appreciated.

Was this reply helpful? Yes No