Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / I need help with outer...
Finance | Project Operations, Human Resources, ...
Answered

I need help with outer join

(0) ShareShare
ReportReport
Posted on by source258147 1,021

Hello expert,

I need to make a relation between two tables HCMEMPLOYMENT and DefaultDimensionView

when I run this query in SQL server its return the exact result I want

select * from HCMEMPLOYMENT 
left join DefaultDimensionView on (DefaultDimensionView.DEFAULTDIMENSION = HCMEMPLOYMENT.DEFAULTDIMENSION)
where DefaultDimensionView.NAME = 'Department'
and DefaultDimensionView.DISPLAYVALUE >= '022'
and DefaultDimensionView.DISPLAYVALUE <= '022'
order by 1

but with this query below it returns all the data in the tables. I need to edit this query to bring the employees in the department 022

query = this.parmQuery();

qbdsEmployment = query.dataSourceTable(tableNum(HcmEmployment));

qbdsDimensions      = qbdsEmployment.addDataSource(tableNum(DefaultDimensionView));
qbdsDimensions.firstOnly(true);
qbdsDimensions.joinMode(JoinMode::OuterJoin);
qbdsDimensions.relations(false);
//qbdsDimensions.fetchMode(QueryFetchMode::One2One);
//qbdsDimensions.addLink(fieldNum(HcmEmployment,DefaultDimension),fieldNum(DefaultDimensionView,DefaultDimension));
qbdsDimensions.addLink(fieldNum(DefaultDimensionView,DefaultDimension),fieldNum(HcmEmployment,DefaultDimension));
qbdsDimensions.addRange(fieldNum(DefaultDimensionView,Name)).value(queryValue("Department"));
qbdsDimensions.addRange(fieldNum(DefaultDimensionView,DisplayValue))
              .value(queryRange(queryValue("022")
                     ,queryValue("022")));

I don't know why the filter is not working.

if I change joinmode from outer to exists the filter works and return employees in department 022 

if i clear the filter it's return employees only have dimensions 

  • source258147 Profile Picture
    source258147 1,021 on at
    RE: I need help with outer join

    i solve the issue by adding my query after check parameter

    if(parameter)

    {

      do join

      filter data
    }

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 231,407 Most Valuable Professional on at
    RE: I need help with outer join

    I didn't meant anything like that, because you never mentioned such a requirement.

    But the solution should be easy - simply disable the data source if you don't want the filter:

    qbdsDimensions.enabled(parameter);

  • source258147 Profile Picture
    source258147 1,021 on at
    RE: I need help with outer join

    there are two secnario

    one --> if the user pass the value to the parameter and want to filter data depend on the parameter

    two --> if the user want to get all the employees with out any filter

    you mean 

    if(parameter)

     exist join dimensionTable

    else

     just use hcmemployment

    is that what you mean ?

  • Martin Dráb Profile Picture
    Martin Dráb 231,407 Most Valuable Professional on at
    RE: I need help with outer join

    Why do you want use an outer join instead of an exists join?

    If you want to return HcmEmployment records that has the given dimension value, an exists join is exactly what you need and the outer join is not.

  • source258147 Profile Picture
    source258147 1,021 on at
    RE: I need help with outer join

    Hello Will

    i try it but same result

  • source258147 Profile Picture
    source258147 1,021 on at
    RE: I need help with outer join

    Hello Ludwig

    this is my version details

    Installed product version : Microsoft Dynamics 365 for Finance and Operations (10)

    Installed platform version : Update24 (7.0.5179.35392)

  • source258147 Profile Picture
    source258147 1,021 on at
    RE: I need help with outer join

    Hello Martin

    i update my query as you mention but I get the same result

    i did debug on my code before i create the post and i don't figure out what is the problem with my code

    this is the result for qbdsEmployment.toString())

    SELECT * FROM HcmEmployment(HcmEmployment) 
OUTER JOIN * FROM DefaultDimensionView(DefaultDimensionView_1) 
ON HcmEmployment.DefaultDimension = DefaultDimensionView.DefaultDimension 
AND ((Name = N'Department')) AND ((DisplayValue>=N'022' AND DisplayValue<=N'022'))}	
Dynamics.AX.Application.QueryBuildDataSource

  • Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: I need help with outer join

    Hi,

    What AX version do you operate?

    Best regards,

    Ludwig

  • WillWU Profile Picture
    WillWU 22,352 on at
    RE: I need help with outer join

    Hi source258147,

    Please try to use class QueryFilter to filter the data.

    And have a look at this blog:

    https://community.dynamics.com/365/financeandoperations/b/daxmusings/posts/query-and-new-related-objects-in-ax-2012

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 231,407 Most Valuable Professional on at
    RE: I need help with outer join

    An obvious problem is that you have the wrong order of arguments of addLink(). The first argument should be the field from the parent, therefore remove your current addLink() and uncomment the correct one.

    qbdsDimensions.addLink(fieldNum(HcmEmployment,DefaultDimension), fieldNum(DefaultDimensionView, DefaultDimension));

    A useful technique is checking what query you've generated (e.g. by using the debugger, or calling qbdsEmployment.toString()). Observing the results tells you whether it works or not, but not where your X code failed to generate what you intended.

    By the way, firstOnly(true) doesn't work for joins.

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,407 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans