Create computed column on view in D365FO using created date time column.

Dear All,

In this article will see how to create computed column in view. As we all know view are returning records from SQL from multiple tables, but some time we are getting requirement to create calculated column using the available column. Similarly, in below example as well we are going to create calculated column using createdDateTime field of PurchLine table.

We can achieve this requirement using display method, but it will not give you the filter and sorting options so let's learn and implement it together.

Requirement: Return a date difference between CreatedDateTime and today's date and show values to the form grid with filter and sorting should be available on return value column.

Solution: Create computed column on view, add view as a DataSource on form and add computed column field into grid.

Implementation: 

Step 1: 
Add new static method on view and add code according to your logic.

 
X++ Code for method -

Step 2: Add computed column on view.
Right click on view and select column according to your requirement. 

Step 3: Select method in View method property of new created column.

Step 4: Add DataSource column in form Grid. Assuming you are aware on form design so explaining this step-in details. Link 

Step 5: Build and synchronize your project.

Step 6: Verify SQL level changes: 
Filter views by name in AxDb and find how your column is created.

SQL view for this computed column.

Step Last: Validate changes on form, try to apply filters and sort using standard sorting functionality. 

That's It.

Happy Daxing.

How to apply custom range on AOT query in D365 Finance and Operations

Hi All,

Here in this blog will see how to apply custom range on AOT query.

Requirement: Add range on AOT query to get the records created before given days (createdDateTime - PriorDays)

Steps 1 - Add new field in the parameters. Please refer below screen shot.

Step 2 - Create new class and use attribute for range method. Refer below screen shot.

internal final class TestQueryRangeUtil

{

    

    [QueryRangeFunctionAttribute()]

    public static str greaterThanDateByParameter()

    {

        int relativeDays = SalesParameters::find().TestPriorDays;

        utcdatetime  currentDateTime;

        currentDateTime = DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::getSystemDateTime(),               DateTimeUtil::getUserPreferredTimeZone());

        return '> ' +              SysQueryRangeUtil::value(DateTimeUtil::date(DateTimeUtil::addDays(currentDateTime,relativeDays)));  

    }

}

Step 3:Create range on AOT query and add below given expression as value.

(TESTQueryRangeUtil::greaterThanDateByParameter())

Thats it. Happy Coding.