Skip to main content

Notifications

Announcements

Share Your Journey with Power Pages and Help Shape Its Future!
News and Announcements icon
Dynamics 365 Community / Forums / Finance forum / Switch based QueryBuil...
Finance forum
Suggested answer

Switch based QueryBuildDataSource remembering previous links

(1) ShareShare
ReportReport
Posted on by Justin Kruger 17
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
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 228,317 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,548 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

News and Announcements

Share Your Journey with Power Pages and Help Shape Its Future!

Quick Links

Dynamics 365 Community Update – Sep 16th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Announcing Our 2024 Season 2 Super Users!

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

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 228,317 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans