Skip to main content

Notifications

Announcements

Community blog subscriptions are now enabled!
News and Announcements icon
Dynamics 365 Community / Forums / Finance forum / Executive query doesn'...
Finance forum
Suggested answer

Executive query doesn't filter the form

(1) ShareShare
ReportReport
Posted on by D365 beginner 512
Hi Experts,
 
Can any one help me to find out what is missing in the below code. I need to filter the records by  transdate using  from and to dates (Outbound controls in the form). The form opens fine with filters when I add the required values in the form init method. But the datasource executequery method doesn't reset the form query once the From or To date controls are modified in the form.  It seems the ranges are not being cleared once the executequery is called. 
 
[Form]
public class ProdReportingOverview extends FormRun
{
   
    [DataSource]
    class ProdJournalRoute
    {
        /// <summary>
        ///
        /// </summary>
        public void executeQuery()
        {
            QueryBuildDataSource  qbds;
            QueryBuildRange       qbr;
            qbds = this.query().dataSourceTable(tableNum(ProdJournalRoute));           
            
            qbds.clearDynalinks();
            qbds.clearRanges();
            qbr = qbds.addRange(fieldnum(ProdJournalRoute, TransDate));
            qbr.value(queryRange(fromDate.datevalue(), toDate.dateValue()));
            super();
        }
    }
}
   
Categories:
Development / Customization / SDK
  • Martin Dráb Profile Picture
    Martin Dráb 225,302 Super User on at
    Executive query doesn't filter the form
    As you see, just writing some code and then checking if it works or not doesn't allow you to understand what's wrong and how to fix it. You need to do a better job in bug isolation and debugging; asking in a forum is not a replacement for debugging and even if it was, you need to learn how to do the job on your own.
     
    A big mistake is writing a lot of code without testing and then testing it all together, because then you don't know which parts works and which doesn't. You should test smaller pieces and then integrate them together when you know they work.
     
    Another huge problem is that you write code generating a query and then you don't look at the result. You check whether the query returns the right data but you ignore the query itself. You should test whether your code creates the query you want.
     
    For example, you have a critical bug already at the beginning of executeQuery(). The call of addDataSource(tableNum(ProdTable) adds a ProdTable data source every time you run the query, therefore you'll end up with many inner-joined ProdTable data sources with (potentially) different ranges. You would easily see that if you looked at the resulting query. There is no point in testing the rest of your code until you fix this bug.
  • D365 beginner Profile Picture
    D365 beginner 512 on at
    Executive query doesn't filter the form
    Hi Martin,
     
    The code returns invalid data while opening the form and when I change the location field in the form (which is the InventLocationId in the InventDimTable) it doesn't return any records.  
  • Martin Dráb Profile Picture
    Martin Dráb 225,302 Super User on at
    Executive query doesn't filter the form
    I'm sorry, but it tells me nothing about what kind of problem you have with your code.
  • D365 beginner Profile Picture
    D365 beginner 512 on at
    Executive query doesn't filter the form
    Hi Martin,
     
    I need to show the records from ProdJournalRoute Table for the selected date range with condition hours != 0 OR QtyGood != 0 OR QtyError != 0. Additionally I need to link the ProdTable in the form to show item id and Item name in the grid and link the records with InventDimTable to be able to filter the records by warehouses. 
     
    I have created the outbound controls Fromdate, Todate in the form design and based on the selection the records should change in the form. Can you help me to create range for the above conditions. 
  • Martin Dráb Profile Picture
    Martin Dráb 225,302 Super User on at
    Executive query doesn't filter the form
    Please tell us more about the problem. Are you saying that you isolated the problem to your query expression and the rest of code isn't actually relevant to the problem? Are you getting an error, is the range ignored or are you getting different filters than expected?
     
    Also note that you assign three values to qbr1, but each assignment overwrites the previous one, therefore the variable will hold the last value only. You can throw away the other two assignments.
  • D365 beginner Profile Picture
    D365 beginner 512 on at
    Executive query doesn't filter the form
    Thanks for the help Mr. Martin & Mr. Waed,
     
    Actually the problem was with the datasource relations in the form as it had a wrong join with another table. I solved it. Now I need to use 3 tables in my form those are ProdJournalRoute, ProdTable & InventDim. I am trying to add some more filters in the form but it doesn't work as expected can you please help me to fix the issue in the below code and help me to simplify it. I am not much familiar with query range expressions when we have multiple datasources. 
     
    The form contains 3 tables ProdJournalRoute(1), ProdTable(2) (inner join with 1) & InventDim(Inner join with2). 
     
    [Form]
    public class ProdReportingOverview extends FormRun
    {

        /// <summary>
        ///
        /// </summary>
        public void init()
        {
            super();
            FromDate.dateValue(dateStartMth(today()));
            Todate.dateValue(endMth(today()));
        }
        [DataSource]
        class ProdJournalRoute
        {
            /// <summary>
            ///
            /// </summary>
            public void executeQuery()
            {
                QueryBuildDataSource qbds,qbds1,qbds2;            
                QueryBuildRange      qbr,qbr1,qbr2;        
                qbds = this.query().dataSourceTable(tableNum(ProdJournalRoute));  
              
                qbds1 = qbds.addDataSource(tableNum(ProdTable));
                qbds1.joinMode(JoinMode::InnerJoin);
                qbds1.relations(true);
                qbds2 = qbds1.addDataSource(tableNum(InventDim));
                qbds2.joinMode(JoinMode::InnerJoin);
                qbds2.relations(true);
     
                qbds.clearDynalinks();
                qbds.clearLinks();
            
                qbr     = qbds.addRange(fieldnum(ProdJournalRoute, TransDate));
                qbr1    = qbds.addRange(fieldnum(ProdJournalRoute, Hours));
                qbr1    = qbds.addRange(fieldnum(ProdJournalRoute, QtyGood));
                qbr1    = qbds.addRange(fieldnum(ProdJournalRoute, QtyError));
                qbr2    = qbds2.addRange(fieldnum(InventDim, InventLocationId));
                qbr.value(queryRange(fromDate.datevalue(), toDate.dateValue()));
                qbr1.value(strFmt('((Hours != %1) || (QtyGood != %2) || (QtyError != %3))'
                ,SysQuery::value('0'),SysQuery::value('0'),SysQuery::value('0')));
                qbr2.value(Location.valueStr());        
                super();
            }
        }
        [Control("Date")]
        class FromDate
        {
            /// <summary>
            ///
            /// </summary>
            /// <returns></returns>
            public boolean modified()
            {
                boolean ret;
            
                ret = super();
                ProdJournalRoute_ds.executeQuery();
            
                return ret;
            }
        }
        [Control("Date")]
        class ToDate
        {
            /// <summary>
            ///
            /// </summary>
            /// <returns></returns>
            public boolean modified()
            {
                boolean ret;
            
                ret = super();
                ProdJournalRoute_ds.executeQuery();
            
                return ret;
            }
        }
        [Control("String")]
        class Location
        {
            /// <summary>
            ///
            /// </summary>
            /// <returns></returns>
            public boolean modified()
            {
                boolean ret;
            
                ret = super();
                ProdJournalRoute_ds.executeQuery();
            
                return ret;
            }
        }
    }
  • Suggested answer
    Waed Ayyad Profile Picture
    Waed Ayyad 3,332 on at
    Executive query doesn't filter the form
    Hi,

    Is your issue resolved?  If yes, mark the answers that helped you as verified.

    Thanks
    Waed Ayyad
  • Suggested answer
    Waed Ayyad Profile Picture
    Waed Ayyad 3,332 on at
    Executive query doesn't filter the form
    Hi,
     
    As Martin said your code is correct, but what do you mean by "doesn't reset the form query once the From or To date controls are modified in the form". the data still the same or the query add the new ranges to previous one.
     
    Did you try to call refresh and research methods on the end on modified methods?
     
     
    Thanks,
    Waed Ayyad
    If this helped, please mark it as "Verified" for others facing the same issue
     
  • Martin Dráb Profile Picture
    Martin Dráb 225,302 Super User on at
    Executive query doesn't filter the form
    By the way, your code can be simplified in the following way while still doing exactly the same thing. 
    public void init()
{
    super();
    
    FromDate.dateValue(dateStartMth(today()));
    Todate.dateValue(endMth(today()));
}

[DataSource]
class ProdJournalRoute
{
    public void executeQuery()
    {
        QueryBuildDataSource qbds = this.queryBuildDataSource();        
        qbds.clearDynalinks();
        qbds.clearRanges();
        
        QueryBuildRange qbr = qbds.addRange(fieldnum(ProdJournalRoute, TransDate));
        qbr.value(queryRange(fromDate.datevalue(), toDate.dateValue()));
        
        super();
    }
}

[Control("Date")]
class FromDate
{
    public boolean modified()
    {
        boolean ret = super();
    
        ProdJournalRoute_ds.executeQuery();
    
        return ret;
    }
}

[Control("Date")]
class ToDate
{

    public boolean modified()
    {
        boolean ret = super();
    
        ProdJournalRoute_ds.executeQuery();
    
        return ret;
    }
}
  • Martin Dráb Profile Picture
    Martin Dráb 225,302 Super User on at
    Executive query doesn't filter the form
    I don't see any problem in your code, except of the call of executeQuery() in init(). Please use the debugger to check what happens in your code at runtime. Note that it shows the actually query on Query/QueryBuildDataSource objects.

Helpful resources

News and Announcements

Community blog subscriptions are now enabled!

Quick Links

Community Spotlight of the Month

Kudos to Mohamed Amine Mahmoudi!

Blog subscriptions now enabled!

Follow your favorite blogs

TechTalk: How Dataverse and Microsoft Fabric powers ...

Explore the latest advancements in data export and integration within ...

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 284,753 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,302 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,146

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans