Part 16 - Action Methods and Computed Columns

What are the differences between an Action Method and a Computed Column? Let's take a look.

Data entities have a few options for getting data to present in a specific way. One way is to use a virtual field with postLoad(). However, we can also use computed column as well as action methods. Both have their results exposed via OData but computed columns also present in the DMF when exporting (plus importing with some extra code).  

What's a computed column?

Documentation on this can be found here. In simple terms, it is a field that is calculated whenever the data entity is executed. This is done by the view for the data entity and not with X++. The location where this type of column is populated, on the SQL server rather than the AOS, is an important distinction. This means that SQL sever is absorbing the workload rather than the AOS. This can be advantageous for many reasons but it also comes with some drawbacks. Whatever you're looking to calculate has to be done via T-SQL which can be clunky, cumbersome, and depending on the requirement, not even possible.

How to make a computed column?

Making a computed column is fairly easy. First, you have to make a method to do the work. Below is an example that will be placed a Customer related data entity. This will calculate the amount invoiced for a customer for the last calendar year. Look at this article for another example.

    public static server str salesLastYearMST()
    {
        return strFmt('SELECT Isnull(Sum(InvoiceAmountMST), 0)   ' + 
                      'FROM   CustInvoiceJour          ' +
                      'WHERE  DataAreaId = \'%1\'     ' +
                      'AND partition = %2      ' +
                      'AND OrderAccount = \'%3\'     ' +
                      'AND Datepart(yyyy, invoicedate) = Datepart(yyyy, Dateadd(year, -1, Getdate()))',
                      SysComputedColumn::returnField(tableStr(AAXCustCustomerV3Entity), identifierStr(CustTable), fieldStr(CustTable, DataAreaId)),
                      SysComputedColumn::returnField(tableStr(AAXCustCustomerV3Entity), identifierStr(CustTable), fieldStr(CustTable, Partition)),
                      SysComputedColumn::returnField(tableStr(AAXCustCustomerV3Entity), identifierStr(CustTable), fieldStr(CustTable, InvoiceAccount)));
    }

Next, on the data entity, add an upmapped real type field called "SalesLastYearMST", assign the extended data type of AmountMST and set the value for "DataEntityView Method" to be "SalesLastYearMST". I suggest creating your SQL statement in SSMS first that integrating it into a method. Once synced, you can see it in the view for the entity in SQL.

What is an action method?

You can find the documentation for Actions Methods here. An action method is a mechanism to expose methods on a data entity for OData consumption. These methods will contain code, typically X++, and invoking the action method executes the code. The code can do just about anything you could do with code anywhere else inside Finance and Operations. You could also call C# if  you'd like. Using action methods on an OData Entity, you could do anything you want. You could post a packing slip for a sales order, fetch a PDF of a report for a given Sales Invoice, just about whatever you could want to do. Action Methods have 2 basic type: Static and Instance.

How to make a static action method?

Making an action method is similar to making any other type method. It just required a specific method decorator; SysODataAction("<MethodName>", <true for instance>). Below is the action method version of the computed field method from above:

    [SysODataAction("ODataSalesLastYearMST", true)]
    public AmountMST ODataSalesLastYearMST()
    {
        CustInvoiceJour CustInvoiceJour;

        select sum(InvoiceAmountMST)
            from CustInvoiceJour
            where CustInvoiceJour.OrderAccount == this.InvoiceAccount
               && year(CustInvoiceJour.InvoiceDate) ==  year(prevYr(systemDateGet()));

        return(CustInvoiceJour.InvoiceAmountMST);

    }

This would present on a scoped record on an entity, like so when using Connected Service in C#:

context.AAXCustomersV3.FirstOrDefault().ODataSalesLastYearMST().GetValue();

How to Make an Instance Action Method?

To make an instance method, it's similar to an instance method except the section value passed into SysODataAction() should be false; SysODataAction("<MethodName>", <false for static>). Below is an example of a static OData Action Method

    [SysODataAction("ODataCustSalesLastYearMST", false)]
    public AmountMST ODataCustSalesLastYearMST(CustAccount _CustAccount)
    {
        CustInvoiceJour CustInvoiceJour;

        select sum(InvoiceAmountMST)
            from CustInvoiceJour
            where CustInvoiceJour.OrderAccount == _CustAccount
               && year(CustInvoiceJour.InvoiceDate) ==  year(prevYr(systemDateGet()));

        return(CustInvoiceJour.InvoiceAmountMST);

    }

This would present on the public collection for the entity and not on a scoped record in the collection when using Connected Service:

context.AAXCustomersV3.ODataCustSalesLastYearMST("US-001").GetValue();

Main Differences

There are some primary difference between an action method and a computed column.

Computed columns are:

  • calculated by SQL server as close to the data as possible so they are the most performant typically*.
  • calculated for all rows in the result of a data entity. If you data entity doesn't use the computed fields, you're wasting CPU time calculating something you aren't using. This is SQL CPU rather than AOS CPU.
  • limited by the fact that you are essentially writing a SQL Sub-Select in a view. Some more complex workloads may be difficult or impossible to do with a computed column.
  • best for reads but with some custom code can be used for updates or inserts if you'd like.
  • Good to compliment a view of your data when in small doses. If you use lots of computed columns, performance may decrease substantially. 
  • Best when added as a compliment view or data entity so the base entity is still performance and if you require additional computation on your fields, you can join in the compliment data entity or view into your forms, reports, whatever else if and when needed.

Action Methods are:

  • A great addition for an OData Entity as it helps enable complex integration scenarios
  • something that must be called explicitly in order to get the result. This means that we have no performance decrease when adding OData action methods but we must call and wait for the result when using OData.
  • typically used for OData but can be re-purposed in X++ when working with a data entity that has action methods.

To illustrate the differences around workflow and consumption, please review the chart below. Computed columns are always there and ready for consumption once scoped with no extra work to get the data. OData Action Methods require extra work, included a full roundtrip from client to AOS to Database and all way back with the database hit being assumed as its highly likely you'll need data for your action method.

Code for this article can be found here