Two outer joined tables in a report without duplicates from both sides in Dynamics 365 Finance and Operations

I cannot find a particular term for this type of join; so I called it 'hanging left outer join'.

Let's state the problem.

We have two tables:

- LedgerJournalTrans (trans) with a field mgcProjId, which is a reference to ProjTable.

- mgcWarrantsDetails (details), which may have 0..N records for certain records from ProjTable.

Examples of data in both may look like the following:

If we apply standard outer join, the output will be as follows

But we need to print all details records by 'hanging' them against the same projId, like this

So, no duplicates from both tables must be present in the merged table.

 

This is how you can achieve it. 

/// <summary>

    /// Process report data.

    /// </summary>

    public void processReport()

    {

        Query                   query;

        QueryRun                qr;

        mgcWarrantsDetails      details;

        LedgerJournalTrans      trans;

        ProjId                  prevProjId;

        // merge trans and detail even if details are empty

        void insertWithTrans()

        {

            mgcProjWarrantDetailTmp.clear();

            mgcProjWarrantDetailTmp.RefRecId            = trans.RecId;

            mgcProjWarrantDetailTmp.JournalNum          = trans.JournalNum;

            mgcProjWarrantDetailTmp.TransDate           = trans.TransDate;

            mgcProjWarrantDetailTmp.Voucher             = trans.Voucher;

            mgcProjWarrantDetailTmp.ProjId              = trans.mgcProjId;

            mgcProjWarrantDetailTmp.WarrantDetailId     = details.WarrantDetailId;

            mgcProjWarrantDetailTmp.WarrantsWithPrice   = details.WarrantsWithPrice;

 

            mgcProjWarrantDetailTmp.insert();

        }

        // merge empty trans and detail; it will be 'a hanging' detail

        void insertWarrants()

        {

            mgcProjWarrantDetailTmp.clear();

            mgcProjWarrantDetailTmp.ProjId              = details.ProjId;

            mgcProjWarrantDetailTmp.WarrantDetailId     = details.WarrantDetailId;

            mgcProjWarrantDetailTmp.WarrantsWithPrice   = details.WarrantsWithPrice;

 

            mgcProjWarrantDetailTmp.insert();

        }

        // process warrants for the previous projId

        // 'hanging' details on the right side of the report

        void processRestOfWarrants()

        {

            // if there are still some warrants we found for the previous projId

            if (details)

            {

                // as we do not have enough rows from trans with the same previous projId,

                // let's add them with empty part from trans side

                next details;

                while(details)

                {

                    insertWarrants();

                    next details;

                }

            }

        }

 

        // Get the query from the runtime using a dynamic query.

        query = this.parmQuery();

        qr = new QueryRun(query);

        // we suppose that trans sorted by projId

        // each projId may have 0..N of warrants (details)

        while(qr.next())

        {         

            // get next transaction

            trans =  qr.get(tablenum(LedgerJournalTrans)); 

            // projId changes

            if(prevProjId != trans.mgcProjId)

            {

                // if there are still some warrants we found for the previous projId

                processRestOfWarrants();

                // get the first warrant for the new projId

                select details

                    where details.ProjId == trans.mgcProjId;

                // keep the new as a previous for the next iteration

                prevProjId = trans.mgcProjId;

            }

            // if this is a trans with the same projId, then we can merge it with the next warrant

            else if (details)

            {

                next details;

            }

            // merge trans with warrants

            insertWithTrans();

        }

        // if there are still some warrants we found for the previous projId

        processRestOfWarrants();

    }

 

Raw outer joined data

 

Ascending ProjId

Descending ProjId