Left Join link in Datasouce

Finance and Operations/AX Developer Forum
1

Hi

Can anyone know how to link datasouce with similar in left join, below is my sql script and i don’t know how to do this in form datasource join…

select VendTable.AccountNum,

VendTable.Name,

AAA.PartyId

from VendTable

left join (select DirPartyAddressRelationship.partyId,

DirPartyAddressRelationship.recId

from DirPartyAddressRelationship

where DirPartyAddressRelationship.isPrimary = 1) AAA

on AAA.PartyId = VendTable.PartyId

expedted result in Grid are :

AccountNum Name PartyId
000001_046 First Vendor 000000003_172
000002_046 Second record 000000010_172
000003_046 Third Vendor

NULL

Please advise :slight_smile: thanks in advance.

2

use outer join

3

First of all, get rid of the sub-query. As I understand, this is the X++ query you’ll want to design as a form data source:

select AccountNum, Name from vendTable
    outer join PartyId, RecId from dirPartyAddressRelationship
    where dirPartyAddressRelationship.PartyId == vendTable.PartyId
       && dirPartyAddressRelationship.IsPrimary== NoYes::Yes;

Then look at How to: Join Data Sources for a Form.

4

Thanks martin you give me idea how to to it thanks a lot…

5

Thanks Kranthi