No-code: View only active trade agreements in Dynamics 365 Finance and Operations

From the community, there are a lot of questions about being able to achieve various scenarios in Dynamics 365 Finance and Operations, without coding. Any of those questions are a fair ask. Having no-code/low code solutions like Microsoft Power Apps in mind, you would like to prevent customizations if there is no need for them. In this post, I will explain how you can achieve the requirement of viewing only active sales agreements without any coding. Without coding? Well… That depends on the details of your requirement. In my thoughts of interpreting the question, I came very, very close to my original idea. I now have to choose between a very small customization or a manual step. Interesting to know the border between what is possible without coding and where you might consider a customization? Then continue the read.

Introduction

If you have a specific requirement, it would be recommended to check if it can be achieved in a different way. Even though all customizations are now extension-based, it still requires maintenance and regression testing when updating to a higher version of the standard application. Some requirements can be achieved with specific parameters, configuration, or personalization. If this is not possible, you can, many times, rely on extensions via the Power Platform or specific ISV solutions. ISV solutions fulfill industry-specific solutions or white spaces in the standard application.

Recently, a question was asked on the Microsoft Dynamics Community which got my interest to think of an option without coding. Here is a link to the question: Viewing, only Active, trade agreements – Dynamics 365 Finance Forum Community Forum.

The question is clear. Is it possible to hide not active trade agreements without coding? Talking about active trade agreements, this would imply a dynamic filter option based on today’s date. As an example, I created some trade agreements on a specific item in Dynamics 365 Finance and Operations.

The date at the time of preparing this blog is April 9, 2022. With help of the fields From date and To date, you can manage the active period of the trade agreements. It is possible to leave date fields unfilled. The records are an example where some agreements are active, some expired and I included an agreement being active in the future.

To be able to get the current active agreements, the From date should be before or on today or empty. In addition, the To date should be today, a date in the future or empty. Let’s see how you can translate this into an advanced filter and include some ranges which will be dynamic.

Advanced filtering

With help of calling pre-defined methods, you can set up dynamic ranges. The methods available are supporting e.g. the current user, and a current date which is defined in a class called SysQueryRangeUtil, or one of the classes extending this class. You can read more about the filtering options in the documentation: Advanced filtering and query syntax – Finance & Operations | Dynamics 365 | Microsoft Docs

Two predefined methods will help you with the dynamic query for the dates. The method is taking the current date.

• greaterThanDate(relative days)
• lessThanDate(relative days)

The relative days can be used to add or subtract days from the current date with positive or negative integer values. In this example you need it. When you would use the lessThanDate method without offset, it would return April 8, 2022. (The date of preparing the blog was April 9, 2022. This date is used in the examples to return dynamic ranges).

To get a range that includes April 9, we have to add one day. Then the range will return all dates before April 10, which includes April 9. In a similar way, you have to apply this logic on the greaterThanDate to first subtract one day.

Before you can apply the filters, you have to understand how Dynamics 365 handles empty dates or a maximum date. An empty date also referred to as the minimum date, is stored in the database with the value of ‘January 1, 1900’. The maximum date is not expiring and has a value of ‘December 31, 2154’. In this particular case, the trade agreements are stored with the empty date for both the fields which is pretty unusual to store a minimum date in the To date field. This means that the To date should be filtered with 01-01-1900 or greater than today or any future date.

When you have the View trade agreements form open, you can start the Advanced query or sort feature. On this form, set the next ranges (Click the image for a larger view):

As the method expression will ignore the separator value, you have to add a second row with the field To date. This will result in an OR statement and thus will show records that don’t have a value or are larger than the calculated date. After you entered the ranges, you can click the OK button to get the trade agreements filtered with active records only.

Saved views

Next, you can decide how to reuse this query. My first thought was to use the Saved views functionality, but you can also save the query itself for future references. The advantage of using Saved views is that you can switch the view directly on the View trade agreements form or even set the new view as default. Follow the next steps to save the filtering ranges as a saved view.

1. Click on the Standard view to activate the drop-down menu for saved views.
2. Click Save as… when you want to create a new saved view.
3. Provide a meaningful name. This will be directly visible to users.
4. Optionally, you can provide a description that would explain the purpose of the saved view. This text is visible for users as a tooltip.
5. When you want to open the View trade agreements form by default having the Active agreements view enabled, you can set it as the default view.
6. There is an option to define if the view will be available for all legal entities or only selected.
7. Click the Save button.

When you now close and reopen the form, the saved view can be used to filter the records.

Limitation

Unfortunately, there is a caveat here. As mentioned in the introduction, I was not fully able to get this requirement fully fulfilled using a configuration. When the saved view will save or use the query ranges, it will only use the first range for each field. So, the result in my scenario is, that after reopening the form, too less records are shown. All records without an end date were not included, so it was not showing all active agreements.

In this example, there are two reasons why it is not fully working:

• A range using a method is not allowing a separator with additional values
• Ranges with duplicate fields are ignored in the Saved views.

This can be solved by either changing the definition of active agreements and changing the query accordingly or reopening the advanced query and filter form again and reapplying the additional range. Another option would be to always specify an end date on trade agreements.

The three options were not acceptable to me, so I decided to look at an alternative. This alternative requires coding. There is an option to extend the class SysQueryRangeUtil. I created a new extension class and copied the original greaterThanDate into a method greaterThanDateOrEmpty. The new method will build a range where the date is greater than a particular date or where the date is ’01-01-1900′. Using this option, there is no need to create a second row with a filter range on the same field and this will be stored correctly with the saved views.

If you would like to achieve the same, you can copy the coding below and apply it in your environment.

[ExtensionOf(classStr(SysQueryRangeUtil))]
final class DynPed_SysQueryRangeUtil_Extension
{
    /// <summary>
    ///    Creates a query string for all dates that are after the specified date or where no date is filled.
    /// </summary>
    /// <param name="relativeDays">
    ///    The number of days, relative to the session date.
    /// </param>
    /// <returns>
    ///    A query string representation of all dates that are later than the specified date or where the date is empty.
    /// </returns>
    /// <remarks>
    ///    Use positive values for future dates and negative values for past dates.
    /// </remarks>
    [QueryRangeFunctionAttribute()]
    public static str greaterThanDateOrEmpty(int relativeDays = 0)
    {
        utcdatetime  currentDateTime;

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

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

}

When the extension class is successfully applied to your environment, the method is working similarly to the original method. Now the query can be configured as shown in the next screenshot.

Conclusion

It would always be beneficial to check if you can use the personalization and configuration options to prevent customizations. Some Power Platform features are included in particular Dynamics 365 F&O licenses. The Power Platform is a no-code/low-code platform that integrates with Dynamics 365. Microsoft is working on the convergence to bring Dynamics 365 and the Power Platform together with native integrations.

For sure, in a lot of scenarios, there is a need to extend the Dynamics 365 Finance and Operation features with a customization. In those scenarios, you can try to search for the least intrusive option. The example in this post could be solved with an extension of the Trade agreement forms itself. It would require custom x++ coding to manage the behavior of the form query. In that case, you have to think of the best ways for regression testing in case of installing updates from Microsoft. You have to verify if changes done by Microsoft would have an impact on your customization. Now, compare this with using a new extension method for the query option and the use of Saved Views. This would have a much lower risk of disruptions or rework in the future.

I do hope you liked this post and will add value for you in your daily work as a professional. If you have related questions or feedback, don’t hesitate to use the Comment feature below.


That’s all for now. Till next time!