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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Business Central Query...
Small and medium business | Business Central, N...
Suggested answer

Business Central Query Complex

(2) ShareShare
ReportReport
Posted on by dejadpower 16
Hello All,
 
 
How to achieve the below result in business central query:
 For every milestone , if the status is true  then , it should count and show the result grouped by jobno and milestone
 
 
please guide
 
thanks for helping
 
 
 
Categories:
Dynamics 365 Business Central
I have the same question (0)
  • Suggested answer
    Ariba Mehdi Profile Picture
    Ariba Mehdi 164 on at
    Business Central Query Complex
    Hi there,

    You may share more details if this doesn't work. 

    Refer to this link. The last section of the website contains an equivalent SQL statement similar to your problem, check it for mapping the query correctly. 
    https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-query-totals-grouping

    To filter Status as true:
    You can use DataItemTableFilter or ColumnFilter.

    Hope it helps!
  • dejadpower Profile Picture
    dejadpower 16 on at
    Business Central Query Complex
    thanks for your time
     
    i've already tried dataitemtable filter, it doesn't work
     
    I need to display the milestone only if all milestone status are completed,  
     
    in the example posted in question, it should not show the milestone M4, because all status of Milestone M4 is not true.
     
     
     
  • Ariba Mehdi Profile Picture
    Ariba Mehdi 164 on at
    Business Central Query Complex
    Try filtering it in onBeforeOpen trigger:
     
    trigger OnBeforeOpen()
        var
            jobplanningline: Record "Job Planning Line";
            Textvar: Text;
        begin
            jobplanningline.SetFilter(workorderstatus, false);
            Textvar := '<>';
            if jobplanningline.FindSet() then begin
                repeat
                    Textvar += jobplanningline.milestone + '|';
                until jobplanningline.Next() = 0
            end;
            currQuery.SetFilter(milestone, Textvar);
        end;

     
  • dejadpower Profile Picture
    dejadpower 16 on at
    Business Central Query Complex
    Thank you @Ariba
     
    There will be multiple jobs like this. I have to display milestone count for each job. the code you provided will not work for multiple jobs
    kindly suggest

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…

Pallavi Phade – Community Spotlight

We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…

Congratulations to the October Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 2,791

#2
YUN ZHU Profile Picture

YUN ZHU 2,091 Super User 2025 Season 2

#3
Sumit Singh Profile Picture

Sumit Singh 2,090

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans