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

Filtering dataitems in a query before joining

(4) Share

Report

Posted on by Chris Brill

112

Hello community,

I'm currently working on a query, which includes two FullOuterJoins.
(FYI: I'm summing up orders and transfers for locations on item variant level)

As you might guess, this query has quite some rows and it takes some time to calculate it. So it would be great to put filters on each dataitem before joining them. But as far as I know ColumnFilter as well as DataItemTableFilter are applied to the final table. Is that correct? If so, is there any way to apply filters before they are joined?

Thanks in advance for some input!

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (7)

Answers (0)

Sagar Dangar, MCP 799 Super User 2025 Season 2 on at

Like (1)

Report

Hi,

Yes, right. it takes all rows.

Another way is by creating Custom temporary buffer tables and insert filtered required data only and take buffer table as dataitem

write query like below:

query 50100 CustomQuery
{
QueryType = API;
APIPublisher = 'Sagar';
APIGroup = 'Dangar';
EntityName = 'CustomQuerys';
EntitySetName = 'CustomQuery';
elements
{
dataitem(SalesHeaderTemp; "Custom Sales Header Temp")
{

column(No_; "No.")
{

}
}
}
trigger OnBeforeOpen()
var
SalesHeaderL: Record "Sales Header";
TempSalesHeaderL: Record "Custom Sales Header Temp";
begin
SalesHeaderL.Reset();
SalesHeaderL.SetRange("Document Type", SalesHeaderL."Document Type"::Order);
if SalesHeaderL.Findset() then begin
TempSalesHeaderL.init();
TempSalesHeaderL.TransferFields(SalesHeaderL);
TempSalesHeaderL.Insert();
end;
end;
}

Was this reply helpful? Yes No
Chris Brill 112 on at

Like (0)

Report

Hi!

Thanks for confirmation and the suggested alternative.
I'm gonna give it a try! :-)

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,739 Super User 2025 Season 2 on at

Like (0)

Report

Hi, hope the following helps as well.

Filtering in Query objects

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-query-filters?wt.mc_id=DX-MVP-5004336

Thanks.

ZHU

Was this reply helpful? Yes No
Chris Brill 112 on at

Like (0)

Report

@Yun Zhu:
Thanks, I've looked on that page before. I do find the description "Filter directly on a data item in query object" for the DataItemTableFilter quite misleading though. For me it implies that this just filters the dataitem and not the whole query and therefore is applied before two dataitems are joined.
This is really a feature I'm missing in AL, especially as it available in SQL.

@Sagar Dangar:
I tried out your approach today, but I can't get it to work.
The transferFields and Insert in the OnBefore - trigger works, but the query is empty.

My assumption was, that the Record "Custom Sales Header Temp" in your code refers to a temporary table that just includes the fields that are needed. Is that correct?
Any idea, how I can make the code work?

query 50100 "Orders Query"

{

QueryType = Normal;

elements

{

dataitem(PurchaseLine; "Temp Purchase Line")

{

column(Location_Code; "Location Code")

{ }

column(No_; "No.")

{ }

column(Variant_Code; "Variant Code")

{ }

}

}

trigger OnBeforeOpen()

var

PurchaseLine_L: Record "Purchase Line";

WTTempPurchaseLine_L: Record "WT Temp Purchase Line";

begin

PurchaseLine_L.Reset();

PurchaseLine_L.SetRange("Location Code", '0110');

PurchaseLine_L.SetRange("Document Type", PurchaseLine_L."Document Type"::Order);

PurchaseLine_L.LoadFields("Document Type", "Location Code", "No.", "Variant Code");

if PurchaseLine_L.FindSet() then

repeat

WTTempPurchaseLine_L.Init();

WTTempPurchaseLine_L.TransferFields(PurchaseLine_L);

if WTTempPurchaseLine_L.Insert() then;

until PurchaseLine_L.Next() = 0;

end;

Was this reply helpful? Yes No
Sohail Ahmed 11,150 Super User 2025 Season 2 on at

Like (0)

Report
This might be helpful for resolving your issue.

You're right, ColumnFilter and DataItemTableFilter in Business Central queries apply after joins, causing performance issues with large datasets. To filter before a FullOuterJoin, the best approach is to:

Use Temporary Tables:

Create temporary tables for each dataset you want to join (e.g., orders and transfers).

Populate these temporary tables with only the filtered records by applying your conditions in the OnAfterGetRecord trigger of the original dataitems.

Then, perform the FullOuterJoin on these smaller temporary tables.

Consider Views (for static filters):

If your filters are predefined, create SQL views with the filtering logic and use these views as your DataItem sources.

Applying filters within the OnAfterGetRecord of the original dataitems using CurrQry.Skip() is less efficient as the data is still fetched initially.

For your case, temporary tables are the recommended way to filter order and transfer data before the FullOuterJoin to improve query performance.

Did this help you? Mark this answer as verified if it did!

Was this reply helpful? Yes No
Chris Brill 112 on at

Like (0)

Report

Hello Sohail Ahmed,

I'm a little confused: As far as I can see, Query-DataItems just have the OnBeforeOpen-Trigger. What OnAfterGetRecord-Trigger do you mean?

Was this reply helpful? Yes No
Suggested answer

Khushbu Rajvi. 20,614 Super User 2025 Season 2 on at

Like (0)

Report

Hope this helps: https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-query-filters

Was this reply helpful? Yes No