SQLJoinType Not Working as expected

(2) Share

Report

Posted on by One_&_Only_Naveen

Why in below code SQL Join now working when I add DataItemTableFilter Property.

Please some one explain. and what is the workaround for same.

dataitem(CGST_LE; "Detailed GST Ledger Entry")
                {
                    DataItemLink = "Document Line No." = Purch__Inv__Line."Line No.",
                                   "Document No." = Purch__Inv__Line."Document No.",
                                   "GST Group Code" = Purch__Inv__Line."GST Group Code";
                    SqlJoinType = LeftOuterJoin;
                    // DataItemTableFilter = "GST Component Code" = Const('CGST');
                    column(GST_Amount; "GST Amount") { Method = Sum; }
 

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

Gerardo Rentería Ga... 22,524 Most Valuable Professional on at

Like (1)

Report

SQLJoinType Not Working as expected

Hi, good day
I hope this can help you, and give you some hints.

Dynamics 365 Business Central Queries: Erm…where are the rest of my rows?! – James Pearson

Data Item Links - Business Central | Microsoft Learn

Best Regards
Gerardo

Was this reply helpful? Yes No
One_&_Only_Naveen 68 on at

Like (0)

Report

SQLJoinType Not Working as expected

Why I try to apply Filter (*Refer Commented Line) inside data item, Resulting records are only those where filter condition is true, except all records from outer table are not shown.

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 89,830 Super User 2025 Season 2 on at

Like (1)

Report

SQLJoinType Not Working as expected

DataItemTableFilter is just a method to add filtering to Query. This does not conflict with SqlJoinType.

Here is an example provided by Microsoft:

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-query-filters#dataitem

Thanks.

ZHU

Was this reply helpful? Yes No
Suggested answer

Khushbu Rajvi. 18,797 Super User 2025 Season 2 on at

Like (2)

Report

SQLJoinType Not Working as expected

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

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-query-links-joins

Was this reply helpful? Yes No
Alexander Drogin 262 on at

Like (0)

Report

SQLJoinType Not Working as expected

You need to give more information for anyone to be able to help. What is the expected result and what happens when you add the filter?

Was this reply helpful? Yes No