Skip to main content

Notifications

Announcements

🌸 Community Spring Festival 2025 Challenge & Updates🌸
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance forum / Clearing datasource qu...
Finance forum

Clearing datasource query ranges

(0) ShareShare
ReportReport
Posted on by Sona Jee 432

Hi expets,

I have a form with two data source.

In first data source there is text box with lookup.

When i select first value i.e(CNT-00128) in lookup according to that record is filter in second data source.

But when i select other value  in lookup then range will not clear.

I have used qbds.clearRanges();.

7853.filter.png

Please suggest me the right path.

Thanks

Sona Jee

Categories:
Development / Customization / SDK
  • Sona Jee Profile Picture
    Sona Jee 432 on at
    RE: Clearing datasource query ranges

    Hi Nikolaos,

    I have resolved the issue by using childDataSourceCount() as below:

    qbdsPurchTable = this.query().dataSourceTable(tableNum(PurchTable));
    qbdsPurchTable.clearRanges();
    qbdsPurchTable.addRange(fieldNum(PurchTable,OrderAccount)).value(VendAccount);
    int dbcount = this.query().dataSourceTable(tableNum(PurchTable)).childDataSourceCount();
    if(!this.query().dataSourceTable(tableNum(PurchTable)).childDataSourceCount())
    {
    qbds = qbdsPurchTable.addDataSource(tableNum(CustomTable));
    qbds.addRange(fieldNum(CustomTable, DocumentType)).value(SysQuery::value(DocumentType::PurchaseOrder));
    qbds.joinMode(JoinMode::ExistsJoin);
    qbds.fetchMode(QueryFetchMode::One2Many);
    qbds.relations(false);
    qbds.addLink(fieldNum(PurchTable, PurchId),fieldNum(CustomTable, DocumentNumber));
    }
    else
    {
    qbds = this.query().dataSourceTable(tableNum(PurchTable)).childDataSourceNo(1);
    qbds.clearRanges();
    qbds.addRange(fieldNum(CustomTable, DocumentType)).value(SysQuery::value(DocumentType::PurchaseOrder));
    }
    if(ContainerId.valueStr())
    {
    qbds.addRange(fieldNum(CustomTable, Field1)).value(ContainerId.valueStr());
    }

    Thanks

    Sona

  • Sona Jee Profile Picture
    Sona Jee 432 on at
    RE: Clearing datasource query ranges

    thank you

    let me try it.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,158 Moderator on at
    RE: Clearing datasource query ranges

    Your form should have only one data source, your custom table. After all, that table has the data that you want to show on your form!

    The lookup field should be just a normal unbounded field. Not linked to any data source. In the lookup method of that field you can control what values will be shown in the lookup.

    Whenever the lookup field is modified, you should refresh the data source of your custom table, and handle the filtering like I described in my previous post.

  • Sona Jee Profile Picture
    Sona Jee 432 on at
    RE: Clearing datasource query ranges

    Hi ,

    Can you tell a bit more about your form? What is the first data source (PurchTable) used for? Only for the filter field? -

    We have one text box with lookup

    Also your code adds a data source to qbdsPurchTable each time you run this code. Maybe you should get the existing CustomTable data source from the query instead of adding a new data source every time.

    - yes you are right, we are adding eachtime, because we don't have that customTable at form data source level,

    shall I try adding it at datasource level ?

    Also I don't see anywhere any refreshing of the second data source. So the contents will not be changed.

    yes we are not re-freshening datasource.

    In this case what should I do please suggest.

    Thank you

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,158 Moderator on at
    RE: Clearing datasource query ranges

    Thanks. Can you tell a bit more about your form? What is the first data source (PurchTable) used for? Only for the filter field?

    Also your code adds a data source to qbdsPurchTable each time you run this code. Maybe you should get the existing CustomTable data source from the query instead of adding a new data source every time.

    Also I don't see anywhere any refreshing of the second data source. So the contents will not be changed.

    Anyway here's how I would do it:

    1) When the user selects a new value in the lookup, refresh the CustomTable data source, with reread() and research() commands.

    2) In executeQuery method of the second data source, clear all ranges of that data source. Then add a new range for DocumentNumber field, taking the value from the lookup / filter field. This should be done before the super() call.

  • Sona Jee Profile Picture
    Sona Jee 432 on at
    RE: Clearing datasource query ranges

    Hi Nikolaos,

    Here is the code:

    code of execute query
    Str ContId;
    ContId = ContainerId.valueStr();

    qbdsPurchTable = this.query().dataSourceTable(tableNum(PurchTable));
    qbdsPurchTable.clearRanges();
    qbdsPurchTable.addRange(fieldNum(PurchTable,OrderAccount)).value(VendAccount);
    qbds = qbdsPurchTable.addDataSource(tableNum(CustomTable));

    qbds.addRange(fieldNum(CustomTable, DocumentType)).value(SysQuery::value(DocumentType::PurchaseOrder));

    qbds.joinMode(JoinMode::ExistsJoin);
    qbds.fetchMode(QueryFetchMode::One2Many);
    qbds.relations(false);
    qbds.addLink(fieldNum(PurchTable, PurchId),fieldNum(CustomTable, DocumentNumber));
    qbds.clearRange(fieldNum(CustomTable, Field1));
    if(Field1.valueStr())
    {
    qbds.addRange(fieldNum(CustomTable, Field1)).value(ContainerId.valueStr());
    }

  • nmaenpaa Profile Picture
    nmaenpaa 101,158 Moderator on at
    RE: Clearing datasource query ranges

    Is there any chance to see your code?

  • Sona Jee Profile Picture
    Sona Jee 432 on at
    RE: Clearing datasource query ranges

    Hi Nikolaos,

    Whatever I put in add Range , its not getting cleared 2nd time.

    It coming in "and" condition in query , I checked using debugger

  • Sona Jee Profile Picture
    Sona Jee 432 on at
    RE: Clearing datasource query ranges

    Hi Nikolaos,

    "qbds" pointing to second data source.

    Yes i need to clear the ranges of the second data source.

  • nmaenpaa Profile Picture
    nmaenpaa 101,158 Moderator on at
    RE: Clearing datasource query ranges

    And "qbds" is pointing to which data source? You need to clear the ranges of the second data source.

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

🌸 Community Spring Festival 2025 Challenge & Updates🌸

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

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

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 294,161 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 232,942 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,158 Moderator

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans