Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance forum / Switch based QueryBuil...
Finance forum
Answered

Switch based QueryBuildDataSource remembering previous links

(1) ShareShare
ReportReport
Posted on by Justin Kruger 32
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.
Categories:
Development / Customization / SDK
  • Justin Kruger Profile Picture
    Justin Kruger 32 on at
    Switch based QueryBuildDataSource remembering previous links
    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.
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 228,815 Most Valuable Professional on at
    Switch based QueryBuildDataSource remembering previous links
    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.
  • Justin Kruger Profile Picture
    Justin Kruger 32 on at
    Switch based QueryBuildDataSource remembering previous links
    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();
    }
}
     
  • Martin Dráb Profile Picture
    Martin Dráb 228,815 Most Valuable Professional on at
    Switch based QueryBuildDataSource remembering previous links
    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.
  • Suggested answer
    Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,572 Moderator on at
    Switch based QueryBuildDataSource remembering previous links
    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. 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Replay: Dynamics 365 Community Call ERP Edition | October 9, 2024

Another great gathering of our Community!

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,576 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,815 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans