web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Form Main datasource d...
Finance | Project Operations, Human Resources, ...
Answered

Form Main datasource does not gets refreshed after removing the range from joined datasource

(0) ShareShare
ReportReport
Posted on by rama.krishna.tannee... 104

Hi All

I have a requirement in D365FO to filter the form data based on a condition on one of the joined datasource. Here in this case I took a checkbox (yes/No) to filter the data.

when the checkbox is yes then data needs to be filtered by specific range on joined datasource. When the checkbox is made "NO" then entire data of Main datasource needs to be displayed.

Here the challenge seems to be due to the even handlers I took instead of chain of commands in D365 FO form. 

the issue is when we first filter the data by clicking the checkbox "Yes" then formdatasource query has the llinked datasource that are added using query build framework, range is perfectly set and filter is happening correctly. But when we make the checkbox "NO", the querybuildDatasource of main table still has the linked datasource Range that was set previously even though we clear the range (using clearrange() or clearRanges()). Instead of clearing the ranges, the datasources are getting added to the query when we clcik multiple times Yes/No...

Please look at the below code and let me know if I can make some changes to remove the range or clear the range when we make the checkbox NO.

[FormControlEventHandler(formControlStr(XYZFashionStoreItems, FashionItemsCheckBox), FormControlEventType::Modified)]
public static void FashionItemsCheckBox_OnModified(FormControl sender, FormControlEventArgs e)
{
FormDataSource inventTable_ds;
QueryBuildDataSource qbdsInventTable;
QueryBuildDataSource qbdsDimensionAttributeValueSet;
QueryBuildDataSource qbdsDimensionAttributeValueSetItem;
FormCheckBoxControl fashionItemsCheckBox = sender.formRun().design(0).controlName("FashionItemsCheckBox") as FormCheckBoxControl;

Query query;
QueryRun queryRun;

inventTable_ds = sender.formRun().dataSource(formDataSourceStr(XYZFashionStoreItems, InventTable));

qbdsInventTable = inventTable_ds.queryBuildDataSource();

qbdsDimensionAttributeValueSet = qbdsInventTable.addDataSource(tableNum(DimensionAttributeValueSet));
qbdsDimensionAttributeValueSet.relations(true);

qbdsDimensionAttributeValueSet.joinMode(JoinMode::ExistsJoin);

qbdsDimensionAttributeValueSetItem = qbdsDimensionAttributeValueSet.addDataSource(tableNum(DimensionAttributeValueSetItem));

qbdsDimensionAttributeValueSetItem.relations(true);
qbdsDimensionAttributeValueSetItem.joinMode(JoinMode::ExistsJoin);

if (fashionItemsCheckBox.value() == NoYes::Yes)
{

qbdsDimensionAttributeValueSetItem.addRange(fieldNum(DimensionAttributeValueSetItem, DisplayValue)).value('09');

}
else
{
qbdsDimensionAttributeValueSetItem.sortClear();
qbdsDimensionAttributeValueSetItem.clearRange(fieldNum(DimensionAttributeValueSetItem, DisplayValue));
qbdsInventTable.clearDynalinks();
qbdsInventTable.clearLinks();
qbdsInventTable.clearRanges();

}

inventTable_ds.executeQuery();

I have the same question (0)
  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at

    You can use SysQuery::findOrCreateRange instead of formDataSource.addRange. This way it will not create a new range if an old one exists for the same field.

  • rama.krishna.tanneeru@munich-airport.de Profile Picture
    rama.krishna.tannee... 104 on at

    Hi Nikolaos

    Thanks for your reply... But it did not help the issue :(

    The issue is, everytime we enter the even handler after we modify the checkbox, the previously saved Query is seen in the qbds of the main datasource. Even though after adding the SysQuery::findOrCreateRange, the qbds for the main datasource is growing.

    "{SELECT FIRSTFAST FORUPDATE * FROM InventTable(InventTable) USING INDEX ItemIdx JOIN FORUPDATE * FROM RetailInventTable(RetailInventTable) ON InventTable.ItemId = RetailInventTable.itemId JOIN FORUPDATE * FROM InventItemGroupItem(InventItemGroupItem) ON InventTable.dataAreaId = InventItemGroupItem.ItemDataAreaId AND InventTable.ItemId = InventItemGroupItem.ItemId AND InventTable.dataAreaId = InventItemGroupItem.ItemDataAreaId AND InventTable.ItemId = InventItemGroupItem.ItemId OUTER JOIN FORUPDATE * FROM KRFBrandTable(KRFBrandTable) ON InventTable.KRFBrandId = KRFBrandTable.BrandId OUTER JOIN FORUPDATE * FROM InventItemBarcode(InventItemBarCode) ON InventTable.ItemId = InventItemBarcode.itemId JOIN FORUPDATE * FROM InventTableModule(InventTableModule) ON InventTable.ItemId = InventTableModule.ItemId AND ((ModuleType = 2)) JOIN FORUPDATE * FROM EcoResProduct(EcoResProduct) ON InventTable.Product = EcoResProduct.RecId EXISTS JOIN FORUPDATE 'x' FROM DimensionAttributeValueSet(DimensionAttributeValueSet_1) WHERE InventTable.DefaultDimension = DimensionAttributeValueSet.RecId EXISTS JOIN FORUPDATE 'x' FROM DimensionAttributeValueSetItem(DimensionAttributeValueSetItem_1) WHERE DimensionAttributeValueSet.RecId = DimensionAttributeValueSetItem.DimensionAttributeValueSet AND ((DisplayValue = N'09')) EXISTS JOIN FORUPDATE 'x' FROM DimensionAttributeValueSet(DimensionAttributeValueSet_2) WHERE InventTable.DefaultDimension = DimensionAttributeValueSet.RecId EXISTS JOIN FORUPDATE 'x' FROM DimensionAttributeValueSetItem(DimensionAttributeValueSetItem_1_1) WHERE DimensionAttributeValueSet.RecId = DimensionAttributeValueSetItem.DimensionAttributeValueSet EXISTS JOIN FORUPDATE 'x' FROM DimensionAttributeValueSet(DimensionAttributeValueSet_3) WHERE InventTable.DefaultDimension = DimensionAttributeValueSet.RecId EXISTS JOIN FORUPDATE 'x' FROM DimensionAttributeValueSetItem(DimensionAttributeValueSetItem_1_2) WHERE DimensionAttributeValueSet.RecId = DimensionAttributeValueSetItem.DimensionAttributeValueSet AND ((DisplayValue = N'09')) EXISTS JOIN FORUPDATE 'x' FROM DimensionAttributeValueSet(DimensionAttributeValueSet_4) WHERE InventTable.DefaultDimension = DimensionAttributeValueSet.RecId EXISTS JOIN FORUPDATE 'x' FROM DimensionAttributeValueSetItem(DimensionAttributeValueSetItem_1_3) WHERE DimensionAttributeValueSet.RecId = DimensionAttributeValueSetItem.DimensionAttributeValueSet EXISTS JOIN FORUPDATE 'x' FROM DimensionAttributeValueSet(DimensionAttributeValueSet_5) WHERE InventTable.DefaultDimension = DimensionAttributeValueSet.RecId EXISTS JOIN FORUPDATE 'x' FROM DimensionAttributeValueSetItem(DimensionAttributeValueSetItem_1_4) WHERE DimensionAttributeValueSet.RecId = DimensionAttributeValueSetItem.DimensionAttributeValueSet AND ((DisplayValue = N'09')) EXISTS JOIN 'x' FROM DimensionAttributeValueSet(DimensionAttributeValueSet_6) WHERE InventTable.DefaultDimension = DimensionAttributeValueSet.RecId EXISTS JOIN 'x' FROM DimensionAttributeValueSetItem(DimensionAttributeValueSetItem_1_5) WHERE DimensionAttributeValueSet.RecId = DimensionAttributeValueSetItem.DimensionAttributeValueSet}"

    This is happening because, we are clicking the value "NO" on checkbox, during that time the QBDS for the main datasource has the linked datasources and the range. In the starting lines of the modified event handler, we are deriving the value for InventTable_ds as "sender.formRun().dataSource(formDataSourceStr(EUTFashionStoreItems, InventTable));", this is actually causing the issue I believe.

    Kindly suggest me a way to remove the datasources and its ranges that were previously added using Qbds.

    pastedimage1576682777598v1.png

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at

    You are also adding new and new data sources every time your code is executed.

    I don't think you need to remove any data sources, just don't add new data sources all the time.

    You can find the existing data source via yourDataSource.query().dataSourceTable method.

  • Suggested answer
    rama.krishna.tanneeru@munich-airport.de Profile Picture
    rama.krishna.tannee... 104 on at

    Yes it worked, I wrote the query design in the Initialized event handler and set the ranges in the modified event handler

    Thanks a lot Nikolaos!!!

    below is the code :

    [FormDataSourceEventHandler(formDataSourceStr(XYZFashionStoreItems, InventTable), FormDataSourceEventType::Initialized)]

       public static void InventTable_OnInitialized(FormDataSource sender, FormDataSourceEventArgs e)

       {

           FormDataSource          inventTable_ds;

           QueryBuildDataSource    qbdsInventTable;

           QueryBuildDataSource    qbdsDimensionAttributeValueSet;

           QueryBuildDataSource    qbdsDimensionAttributeValueSetItem;

           Query query;

           QueryRun queryRun;

           inventTable_ds = sender.formRun().dataSource(formDataSourceStr(XYZFashionStoreItems, InventTable));

           qbdsInventTable = inventTable_ds.queryBuildDataSource();

           qbdsDimensionAttributeValueSet = qbdsInventTable.addDataSource(tableNum(DimensionAttributeValueSet));

           qbdsDimensionAttributeValueSet.relations(true);

           qbdsDimensionAttributeValueSet.joinMode(JoinMode::ExistsJoin);

           qbdsDimensionAttributeValueSetItem = qbdsDimensionAttributeValueSet.addDataSource(tableNum(DimensionAttributeValueSetItem));

           qbdsDimensionAttributeValueSetItem.relations(true);

           qbdsDimensionAttributeValueSetItem.joinMode(JoinMode::ExistsJoin);

       }

       [FormControlEventHandler(formControlStr(XYZFashionStoreItems, FashionItemsCheckBox), FormControlEventType::Modified)]

       public static void FashionItemsCheckBox_OnModified(FormControl sender, FormControlEventArgs e)

       {

           FormDataSource          inventTable_ds;

           FormCheckBoxControl fashionItemsCheckBox       = sender.formRun().design(0).controlName("FashionItemsCheckBox") as FormCheckBoxControl;

           inventTable_ds = sender.formRun().dataSource(formDataSourceStr(XYZFashionStoreItems, InventTable));

           if (fashionItemsCheckBox.value() == NoYes::Yes)

           {

               SysQuery::findOrCreateRange(inventTable_ds.query().dataSourceTable(tableNum(DimensionAttributeValueSetItem)), fieldNum(DimensionAttributeValueSetItem, DisplayValue)).value('09');

           }

           else

           {

               inventTable_ds.query().dataSourceTable(tableNum(DimensionAttributeValueSetItem)).clearRange(fieldNum(DimensionAttributeValueSetItem, DisplayValue));

               inventTable_ds.query().dataSourceTable(tableNum(DimensionAttributeValueSetItem)).clearRanges();

           }

           inventTable_ds.executeQuery();

       }

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 565 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 450 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 250 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans