SSRS X++ contact query

Question

I'm trying to put together multiple queries in a SSRS report for some data validation. At a glance I would like to display some address/contact info, project info, customer info, supplier info and resource info. I've started on address/contact info but seem to have gotten a bit stuck...

I'm getting a invalid field combination error for the logisticselectronic address tables, and also I'm trying to return the locator field twice based on different joins.

I know how to do this in SQL which is easy using left joins and aliased fields however I'm a beginner in X++ and not sure how to do this.

public class DataValidation extends SRSReportDataProviderBase
{

TmpAddresses TmpAddresses;
TmpProjects  TmpProjects;
TmpSuppliers TmpSuppliers;
TmpCustomers TmpCustomers;
TmpResources TmpResources;

[SRSReportDataSetAttribute('TmpAddresses')]
public TmpAddresses getAddresses()
{
    select TmpAddresses;
    return TmpAddresses;
}

public void processReport()
{

    Query                   q;
    QueryRun                qr;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;

    // Declare a table buffer
    DirPartyTable               dirPartyTable;
    LogisticsLocation           logisticsLocation;
    LogisticsPostalAddress      logisticsPostalAddress;
    LogisticsElectronicAddress  logisticsElectronicAddress;

    q = new Query();

    qbds = q.addDataSource(tableNum(DirPartyTable));
    qbr = qbds.addRange(fieldNum(DirPartyTable, Name));
    qbr.value('!""');

    qbds = qbds.addDataSource(tableNum(LogisticsPostalAddress));
    qbds.addLink(fieldNum(DirPartyTable, PrimaryAddressLocation),fieldNum(LogisticsPostalAddress, Location));
    qbds.joinMode(JoinMode::OuterJoin);

    qbds = qbds.addDataSource(tableNum(LogisticsElectronicAddress));
    qbds.addLink(fieldNum(DirPartyTable, PrimaryContactEmail),fieldNum(LogisticsElectronicAddress, RecId));
    qbds.joinMode(JoinMode::OuterJoin);

    qbds = qbds.addDataSource(tableNum(LogisticsElectronicAddress));
    qbds.addLink(fieldNum(DirPartyTable, PrimaryContactPhone),fieldNum(LogisticsElectronicAddress, RecId));
    qbds.joinMode(JoinMode::OuterJoin);

    qbds = qbds.addDataSource(tableNum(LogisticsLocation));
    qbds.relations(true);
    qbds.joinMode(JoinMode::OuterJoin);


    qr = new QueryRun(q);
    while(qr.next())
    {

        dirPartyTable = qr.get(tableNum(DirPartyTable));
        logisticsLocation = qr.get(tableNum(LogisticsLocation));
        logisticsPostalAddress = qr.get(tableNum(LogisticsPostalAddress));
        logisticsElectronicAddress = qr.get(tableNum(LogisticsElectronicAddress));

        TmpAddresses.clear();
        TmpAddresses.Name = dirPartyTable.Name;
        TmpAddresses.Address = logisticsPostalAddress.Address;
        TmpAddresses.Description = logisticsLocation.Description;
        TmpAddresses.Email = logisticsElectronicAddress.Locator;
        TmpAddresses.Phone = logisticsElectronicAddress.Locator;
        TmpAddresses.insert();
    }
}

}

Answer 1

Your child datasources should all refer to the DirParty:

qbdsPrimary = q.addDataSource(tableNum(DirPartyTable));
qbr = qbdsPrimary.addRange(fieldNum(DirPartyTable, Name));
qbr.value('!""');

qbds = qbdsPrimary.addDataSource(tableNum(LogisticsPostalAddress));
qbds.addLink(fieldNum(DirPartyTable, PrimaryAddressLocation),fieldNum(LogisticsPostalAddress, Location));
qbds.joinMode(JoinMode::OuterJoin);

qbds = qbdsPrimary.addDataSource(tableNum(LogisticsElectronicAddress));
qbds.addLink(fieldNum(DirPartyTable, PrimaryContactEmail),fieldNum(LogisticsElectronicAddress, RecId));
qbds.joinMode(JoinMode::OuterJoin);

qbds = qbdsPrimary.addDataSource(tableNum(LogisticsElectronicAddress));
qbds.addLink(fieldNum(DirPartyTable, PrimaryContactPhone),fieldNum(LogisticsElectronicAddress, RecId));
qbds.joinMode(JoinMode::OuterJoin);

qbds = qbdsPrimary.addDataSource(tableNum(LogisticsLocation));
qbds.relations(true);
qbds.joinMode(JoinMode::OuterJoin);

// Verify the query
info(qbdsPrimary.toString());