Computed columns in Dynamics 365 Finance and Operations explained

Computed Columns in Microsoft Dynamics 365 Finance and Operations offer a versatile way to dynamically calculate and display data without physically storing it in the database.

Computed columns are useful for tasks like formatting values, calculating totals, or deriving new data from existing fields without storing the computed values in the database.

Ready to dive into computed columns? This blog provides an overview, step-by-step guidance, and practical implementation tips for using computed columns in Dynamics 365 Finance and Operations.

What are computed columns?

Computed Columns are virtual fields that dynamically calculate values when queried. Unlike traditional fields, they are not physically stored in the database, making them ideal for scenarios where real-time calculations are needed without modifying database schema.

For example, if you need a field that shows the sum of two numbers, X and Y, you can simply use a computed column Z that calculates X+ Y dynamically.

Why use computed columns? Benefits you should know

The purpose of computed columns in Dynamics 365 Finance and Operations is to perform calculations and derive new data based on existing columns within a table. Here are some of it their benefits:

• Improved performance: They avoid unnecessary data storage while offering fast calculations.
• Dynamic filtering and sorting: Unlike display methods, computed columns support filtering and sorting within forms and views.
• Enhanced flexibility: They can be seamlessly used in data entities and views, enabling customized solutions.

Practical example: Using computed columns to display financial dimensions

Here’s a practical application where we demonstrate displaying financial dimensions as fields in a form grid using a view. These dimensions are fetched directly from tables via computed columns, ensuring both efficiency and high performance.

In this example, we focus on financial dimensions, but the same approach applies to ledger dimensions as well. The main difference lies in the source tables:

• Financial dimensions: Data is retrieved from the DimensionAttributeValueSet table.
• Ledger dimensions: Data is sourced from the DimensionAttributeValueCombination table.

While SQL contains all the necessary data within DimensionAttributeValueSet (or DimensionAttributeValueCombination), these tables are not directly accessible in Visual Studio. This is where computed columns come into play. Computed columns enable us to create a new column by writing a direct SQL query within a method, allowing us to retrieve and display the required fields seamlessly.

In this example, we’ll retrieve fields like BankValue, LegalEntityValue, and ProjectsValue and display them as computed columns in our view. This approach is both straightforward and optimized, ensuring excellent performance. Let’s walk you through the process step by step.

Step-by-step implementation of computed columns

Step 1: Create a view

1. Create a new view (e.g., CfzDimensionSetView) using DimensionAttributeValueSet as the data source.
2. Add the RecId field to the view and name it DefaultDimension. This RecId will correspond to the DefaultDimension field in the related table (e.g., BankAccountTrans).

Step 2: Define static methods

1. For each dimension value you want to display, create a static method in the view. For example, methods to retrieve BankValue, LegalEntityValue, and ProjectsValue.
2. Unlike the standard DimensionSetEntity entity, which generalizes dimensions, this approach is specific and avoids unnecessary complexity.

Step 3: Add computed columns

Create computed columns in the view and link them to the respective static methods.

Step 4: Establish relationships

1. Add a relation between BankAccountTrans and your view.

2. Add the view as a related data source in the form.

Step 5: Add computed columns

1. Add the computed columns to the grid in the form.
2. Compile the solution and synchronize the database. You will now see filterable and sortable fields in the grid.

Summing up

Computed Columns in Dynamics 365 Finance and Operations provide a powerful way to optimize solutions by enabling real-time calculations without altering the database schema. For functional consultants, technical consultants, and business intelligence professionals alike, mastering computed columns can bring efficiency and flexibility to your projects, making your implementations more robust and high-performing.

If you are looking to streamline your processes and maximize the potential of Dynamics 365 F&O for your business, we’re here to help. Contact us at marketing@confiz.com to learn how our expertise can support your journey toward smarter, more efficient solutions.