News and Announcements icon

Dynamics 365 CommunityForums Small and medium business | Business Central, N... Business Central Query...

Small and medium business | Business Central, N...

Suggested answer

Business Central Query Complex

(2) Share

Report

Report

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

All responses Img

All responses (4)

Answers Img

Answers (0)

dejadpower 16 on at

Like (0)

Report

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
Ariba Mehdi 160 on at

Like (0)

Report

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 16 on at

Like (0)

Report

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.
Suggested answer

Ariba Mehdi 160 on at

Like (0)

Report

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!

Helpful resources

Quick Links

Leaderboard

#1

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

#2

Martin Dráb 230,371 Most Valuable Professional

#3

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans