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

Business Central Query Complex

(3) Share

Report

Posted on by dejadpower

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:

Business Central

All responses (4)

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!