Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Data not filtering out...
Finance | Project Operations, Human Resources, ...
Unanswered

Data not filtering out if query range changes dynamically

(0) ShareShare
ReportReport
Posted on by Dinesh Tyagi 37
Hi,
I have a form in which I have used a query which supplying data to that form. I have created a string control on that form & based on selection, resources which belong to that category should filter out. I am adding range dynamically but data is not getting filtered, I have debug & checked ranges are setting properly to the query. I have tried given range directly in AOT query in TSTimesheetTable Resource field & its worked but dynamically adding not working.
What could be the issue.
 
 
below is the code in string control NAVEmploymentType modified event handler: 
        public boolean modified()        {            container employemntResource, resources;            QueryBuildDataSource    qbds;            QueryRun                queryRun;            boolean ret;            ret = super();                    queryRun = new queryRun(TSTimesheetTable_ds.query());            while(queryRun.next())            {                TSTimesheetTable timesheetTable = queryRun.get(tableNum(TSTimesheetTable));                int resource = conFind(resources, timesheetTable.Resource);                if(resource == 0)                {                    resources += timesheetTable.Resource;                }            }            int resource = conLen(resources);            for(int i = 1; i <= resource; i++)            {                HcmWorker hcmWorker;                HcmEmployment hcmEmployemnt;                HcmEmploymentDetail hcmEmploymentDetailRec;                BenefitEmploymentType benefitEmploymentTypeRec;                RecId resourceRecId = conPeek(resources,i);                HcmWorkerRecId hcmworkerRecId = ResourceView::find(resourceRecId).Worker;                select firstonly hcmWorker                    join hcmEmployemnt                    join hcmEmploymentDetailRec                    join benefitEmploymentTypeRec                    where hcmWorker.RecId == hcmEmployemnt.Worker                    && hcmEmploymentDetailRec.Employment == hcmEmployemnt.RecId                    && benefitEmploymentTypeRec.RecId == hcmEmploymentDetailRec.BenefitEmploymentType                    && hcmWorker.RecId == hcmworkerRecId;                if(benefitEmploymentTypeRec && benefitEmploymentTypeRec.EmploymentTypeId == this.text())                {                    employemntResource += conPeek(resources,i);                }                hcmWorker.clear();                hcmEmployemnt.clear();                hcmEmploymentDetailRec.clear();                benefitEmploymentTypeRec.clear();            }            qbds = TSTimesheetTable_ds.query().dataSourceTable(tableNum(TSTimesheetTable));            qbds.clearRange(fieldNum(TSTimesheetTable, Resource));            int workerLeft = conLen(employemntResource);            for(int i = 1; i <= workerLeft; i++)            {                RecId workersLeft = conPeek(employemntResource,i);                qbds.addRange(fieldNum(TSTimesheetTable, Resource)).value(queryValue(workersLeft));            }            TSTimesheetTable_ds.executeQuery();            //TSTimesheetTable_ds.reread();            //TSTimesheetTable_ds.research(true);            return ret;        }
 
Thanks & Regards,
Dinesh
  • Martin Dráb Profile Picture
    Martin Dráb 230,466 Most Valuable Professional on at
    Data not filtering out if query range changes dynamically
    I formatted your code and make it easier to follow, such as by throwing away code without any useful effect: 
    public boolean modified()
{
    boolean ret = super();

    container resources;

    QueryRun queryRun = new queryRun(TSTimesheetTable_ds.query());
    while (queryRun.next())
    {
        TSTimesheetTable timesheetTable = queryRun.get(tableNum(TSTimesheetTable));
        int resource = conFind(resources, timesheetTable.Resource);
        if (resource == 0)
        {
            resources += timesheetTable.Resource;
        }
    }
    
    container employmentResource;

    for (int i = 1; i <= conLen(resources); i++)
    {
        HcmWorker hcmWorker;
        HcmEmployment hcmEmployment;
        HcmEmploymentDetail hcmEmploymentDetailRec;
        BenefitEmploymentType benefitEmploymentTypeRec;
        RecId resourceRecId = conPeek(resources,i);
        HcmWorkerRecId hcmWorkerRecId = ResourceView::find(resourceRecId).Worker;
        
        select firstonly hcmWorker
            where hcmWorker.RecId == hcmWorkerRecId
            join RecId from hcmEmployment
                where hcmEmployment.Worker == hcmWorker.RecId 
            join RecId from hcmEmploymentDetailRec
                where hcmEmploymentDetailRec.Employment == hcmEmployment.RecId
            join benefitEmploymentTypeRec
                where benefitEmploymentTypeRec.RecId == hcmEmploymentDetailRec.BenefitEmploymentType;

        if (benefitEmploymentTypeRec.EmploymentTypeId == this.text())
        {
            employmentResource += conPeek(resources, i);
        }
    }
    
    QueryBuildDataSource qbds = TSTimesheetTable_ds.queryBuildDataSource();
    
    // Note that this clear just one range. If there are multiple ones for Resource field, the others will still stay there.
    qbds.clearRange(fieldNum(TSTimesheetTable, Resource));

    for (int i = 1; i <= conLen(employmentResource); i++)
    {
        RecId resourceRecId = conPeek(employmentResource, i);
        
        qbds.addRange(fieldNum(TSTimesheetTable, Resource)).value(queryValue(resourceRecId));
    }

    TSTimesheetTable_ds.executeQuery();

    return ret;
}
    Your approach looks very inefficient to me - adding joins and ranges to the query would be much better than running so many extra queries. But all right, we can ignore performance problems for now.
     
    What is the query string generated by your code and how it differs from your intention? For example, look at qbds object in debugger; you should see the string there. As you see, just observing data returned by the query doesn't tell you much; you need to pay attention to the query itself.
  • Martin Dráb Profile Picture
    Martin Dráb 230,466 Most Valuable Professional on at
    Data not filtering out if query range changes dynamically
    Your code looks very strange to me. Your intention is filtering the query, but your first step is executing the query with current filters. Why? I thought you just want to add a range (or ranges) and call executeQuery().
  • Layan Jwei Profile Picture
    Layan Jwei 7,266 Super User 2024 Season 2 on at
    Data not filtering out if query range changes dynamically
    Hi Community User,
     
    Can you please post your code in the comments section as it's not well formatted.
     
    Also maybe check if this link will help
     
    Thanks,
    Layan Jweihan
    Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future

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

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,735 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,466 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans