X++ code using SysComputedColumn with a complex sql query in D365FO

Hi,

In this post we will view the code use a complex SQL query with joins on a view using SysComputedColumn in D365FO.

Step 1: Created a view DAXPurchAgreementDetails(namely) and added tables PurchAgreementHeader and PurchTable as its datasources. Included fields PurchNumberSequence, PurchId, InvoiceAccount , POTotalLineAmount and POTotalInvoiceAmount.

Step 2: Created a method "getPOInvAmount" to display the sum of invoice amounts for a purchase order.

    public static str getPOInvAmount()
    {
        str sqlQuery = strfmt('select isnull(sum(VendInvoiceJour.InvoiceAmount),0.0) from'
                             ' VendInvoiceJour join VendInvoicePurchLink on VendInvoicePurchLink.PurchId = VendInvoiceJour.PurchId'
                             ' and VendInvoicePurchLink.InvoiceId = VendInvoiceJour.InvoiceId'
                             ' and VendInvoicePurchLink.InternalInvoiceId = VendInvoiceJour.InternalInvoiceId'
                             ' and VendInvoicePurchLink.InvoiceDate = VendInvoiceJour.InvoiceDate'
                             ' join PurchTable on PurchTable.PurchId = VendInvoiceJour.PurchId'
                             ' where VendInvoiceJour.PurchId = %1'
                             ' group by VendInvoiceJour.PurchId',
                            SysComputedColumn::returnField(viewstr(DAXPurchAgreementDetails),
                                                           identifierStr(PurchTable),
                                                           fieldStr(PurchTable, PurchId))
                            );
 
        return 	sqlQuery;
    }

Step 3:   Assigned view method "getPOInvAmount" to the field "POTotalInvoiceAmount".

Step 4: Build the solution. In the table browser able to see the total invoice amount value for purchase orders.

Output:

Thanks,

Chaitanya Golla