Computed Column/Fields in Data Entity in Microsoft Dynamics 365 For Finance and Operations Without X++ code(only SQL Statement)

Hello Friends,

I am Vikas Vaish and I am back with a new topic of discussion i.e. Computed column in the data entity for D365 for Finance and Operation.

Scenario: 

    1) We have to add any system-generated fields to our data entities like "Created By", "Created Date Time", "RecId" etc. 

    2) We need to create a computed field that displays data from some other table that is not part of the Data Source in our current Data entity.

We can achieve the above requirement by creating computed columns and writing simple SQL statements (Without x++ code).

The SQL statement can be simple or complex as per our business requirements. We can even write SQL Case, Subqueries in our SQL statement. So let have a look at how we can proceed.

Solution Scenario 1: For this example let create one Data entity for the Customers (CustTable)

Here we can see CustTable as the main table for the Data entity. So when the system compiles this data entity, It creates a below query

Select T1.[ColumnName1], T1[ColumnName2] ..... from CUSTTABLE T1 Join DIMENSIONSETENTITY T2 ON T1.DEFAULTDIMENSION = T2.RECID

Here we can notice system is giving alias to each of our tables in data entity starting with T1

Now, All the computed columns which we will add will be part of this select statement. 

Now let us add a new column in the Fields section of the Data entity and give it a business name in this demo I will give the name "createdAt"

Create a new method with the below code.

private static server str createdAt()
    {
        return @"T1.CreatedDateTime";
    }

Add this method to the "DataEntityViewMethod" Property of the newly created fields as shown below.

Now you are ready to go. Regenerate staging table, DB Sync, and build the solution.

Solution Scenario 2:

Above we have discussed a simple scenario. Suppose we need to create a computed field to display data from some other table that is not part of our current data entity. 

For example, we need a field that will show the Balance of each customer. Here we need to get data from the CustTrans table which is not part of our current data entity.

To get this done we can write the below query.

private static server str Amount()

    {

        return @"

                    (

                        Select Sum(ct.AMOUNTCUR) from CUSTTRANS ct

                        where ct.ACCOUNTNUM = T1.ACCOUNTNUM

                        and ct.DATAAREAID = T1.DATAAREAID

                    )

                ";

    }

Create a new computed column and attach this method to the column as shown in the above image.

Optionally, you can also test these statement in SQL to check if it is working or not. You can do it in the below way. Past your SQL code in the above method in between the select statement as shown below

Hope this will give you an idea how we can play around with SQL statement in computed fields in Data entity.