X++ code using SysComputedColumn with a SQL query in D365FO.

Hi,

In this post we will view the code use a simple SQL query 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 and POTotalLineAmount.

Step 2: Created a method "getPOAmount" to display the sum of line amounts of purch lines for a purchase order.

public class DAXPurchAgreementDetails extends common
{
	public static str getPOAmount()
    {
		str sqlQuery = strfmt('select isnull(sum(PurchLine.LineAmount),0.0) from'
                             ' PurchLine join PurchTable on PurchTable.PurchId = PurchLine.PurchId'
                             ' where PurchLine.PurchId = %1',
                            SysComputedColumn::returnField(viewstr(DAXPurchAgreementDetails), 
                                                           identifierStr(PurchTable),
														   fieldStr(PurchTable, PurchId))
							);
 
        return 	sqlQuery;
    }

}

Step 3: Assigned view method "getPOAmount" to the field "POTotalLineAmount"

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

Purchase orders:

Output:

Thanks,

Chaitanya Golla