AX / D365FO – Add a subquery computed column in a Data Entity #d365fo #ax #msdyn365fo

I want to add a computed column in a data entity. This computed column is not a simple calculated field but the result of a subquery.
This query retrieve a field from a table (VendPackingSlipVersion) which is linked to the main Data Entity Data source table (VendPackingSlipTrans).

The relation bewteen Main Table and the child table is 1:N, so I want to retrieve only the first record of the child table.

These steps show how to do that.

Create Data Entity method

Open data entity and add the new method getInternalPackingSlipId()

Create :

• DataEntityName class to instantiate your Data Entity (Al0VendPackingSlipTransEntity)
• The string variable VendPackingSlipJour which will contain the data entity field which will be the join relation key for the child Table
• The string variable Qry which will contain your subquery syntax

public static server str getInternalPackingSlipId()
{
    DataEntityName  dataEntityName= tablestr(Al0VendPackingSlipTransEntity);
    str             VendPackingSlipJour;
    str             qry;

.........
}

Now :

• retrieve the current Data Entity field which will be used a relation key for the child table
• write the subquery syntax using StrFmt to add the data entity key field into it
• return the Subquery

VendPackingSlipJour = SysComputedColumn::returnField(DataEntityName, identifierstr(VendPackingSlipTrans), fieldstr(VendPackingSlipTrans, VendPackingSlipJour));

qry = strFmt('select top 1 VendPackingSlipVersion.InternalPackingSlipId from VendPackingSlipVersion where VendPackingSlipVersion.VendPackingSlipJour = %1 order by VendPackingSlipVersion.VersionDateTime desc', VendPackingSlipJour);

return   qry;

This is the final Method() code

public class Al0VendPackingSlipTransEntity extends common
{
    public static server str getInternalPackingSlipId()
    {
        DataEntityName  dataEntityName= tablestr(Al0VendPackingSlipTransEntity);
        str             VendPackingSlipJour;
        str             qry;

        VendPackingSlipJour = SysComputedColumn::returnField(DataEntityName, identifierstr(VendPackingSlipTrans), fieldstr(VendPackingSlipTrans, VendPackingSlipJour));

        qry = strFmt('select top 1 VendPackingSlipVersion.InternalPackingSlipId from VendPackingSlipVersion' +
            ' where VendPackingSlipVersion.VendPackingSlipJour = %1' + 
            ' order by VendPackingSlipVersion.VersionDateTime desc', VendPackingSlipJour);

        return   qry;
    }
}

Create Computed column field

Now that we created the method we must create a Computed column field (String unmapped field)

Add your newly created method “getInternalPackingSlipId” into the “DataEntity Method” property

Add the staging table into your project and Regenerate it

Build your project

…and syncronize the database….

Data Entity is a SQL Server View so if you open the definition in SSMS your View will appear like below

CREATE VIEW [dbo].[AL0VENDPACKINGSLIPTRANSENTITY] AS SELECT T1.DELIVERYDATE AS DELIVERYDATE, T1.INVENTDATE AS INVENTDATE, T1.INVENTTRANSID AS INVENTTRANSID, T1.ITEMID AS ITEMID, T1.LINENUM 
AS LINENUM, T1.ORDERED AS ORDERED, T1.ORIGPURCHID AS ORIGPURCHID, T1.PACKINGSLIPID AS PACKINGSLIPID, T1.PURCHASELINEEXPECTEDDELIVERYDATE AS PURCHASELINEEXPECTEDDELIVERYDATE, T1.PURCHASELINELINENUMBER 
AS PURCHASELINELINENUMBER, T1.QTY AS QTY, T1.REMAIN AS REMAIN, T1.VALUEMST AS VALUEMST, T1.RECVERSION AS RECVERSION, T1.DATAAREAID AS DATAAREAID, T1.PARTITION AS PARTITION, T1.RECID AS RECID, 
(CAST ((select top 1 VendPackingSlipVersion.InternalPackingSlipId 
			from VendPackingSlipVersion 
			where VendPackingSlipVersion.VendPackingSlipJour = T1.VENDPACKINGSLIPJOUR 
			order by VendPackingSlipVersion.VersionDateTime desc) AS NVARCHAR(100))) AS ALINTERNALPRODUCTRECEIPTNUMBER 
FROM VENDPACKINGSLIPTRANS T1

Refresh Data Entity

Now refresh the data Entity going in System Administration>Data Management

Framework parameters

Click on Entity settings>Refresh entity list.

Wait several minutes to see the refreshed data entity