Applying custom filter to open excel template through X++ in Dynamics 365 F&O

In our recent project, we’ve developed a customized form that is rich with calculations. The data is retrieved and displayed on the form based on multiple checks. This form is part of an Excel Office Add-in that we’ve been working on.

Initially, when we opened the form using the Excel Office Add-in, it would fetch and display all the data by default. However, we received feedback from users who wanted to see only selected records in the grid.

This functionality worked fine if the parent data source had only one entry. But, when there were multiple entries in the parent data source, the default behavior was not sufficient.

To enhance the user experience, we decided to implement custom filters before exporting the data. This way, users can choose the records they want to see in the grid.

Here’s a code snippet that gives you an idea of how we applied custom filters.

 

<pre>

using Microsoft.Dynamics.Platform.Integration.Office;

[Form]

public class JPPurchTransferBudgets extends FormRun implements OfficeIMenuCustomizer, OfficeITemplateCustomExporter

{

    /// <summary>

    /// Customizes the options used to populate the Office Menu with the sales order entities.

    /// </summary>

    /// <param name = "_menuOptions">The menu options to be customized.</param>

    public void customizeMenuOptions(OfficeMenuOptions _menuOptions)

    {

        //Required by interface not important to this scenario

    }

    /// <summary>

    /// Update Tmplete setting.

    /// </summary>

    /// <param name = "_menuItem"> Menu item.</param>

    /// <param name = "_settingsManager"> Setting manager.</param>

    void updateTemplateSettings(OfficeTemplateExportMenuItem _menuItem, Microsoft.Dynamics.Platform.Integration.Office.SettingsManager _settingsManager)

    {

        //Required by interface not important to this scenario

    }

    /// <summary>

    /// Adds filters on exported records.

    /// </summary>

    /// <param name = "_menuItem"> Menu item name.</param>

    /// <returns> Map of filters.</returns>

    Map getInitialTemplateFilters(OfficeTemplateExportMenuItem _menuItem)

    {

        Map filtersToApply = new Map(Types::String, Types::Class);

        JPProjSelected    projSelected;

        if (_menuItem.dataEntityName() == tableStr(JPPurchTransferBudgetEntity))

        {

            var lineEntityName = tableStr(JPPurchTransferBudgetEntity);

            // Create lines filter

            ExportToExcelFilterTreeBuilder lineFilterBuilder = new ExportToExcelFilterTreeBuilder(lineEntityName);

            select firstonly projSelected;

            if (projSelected && !this.parmProjId())

            {

                var lineFilter = lineFilterBuilder.and(

                                                lineFilterBuilder.areEqual(fieldStr(JPPurchTransferBudgetEntity, FiscalYearName), JPPurchTransferBudget.FiscalYearName),

                    lineFilterBuilder.areNotEqual(fieldStr(JPPurchTransferBudgetEntity, SelectedProjId), SysQuery::valueEmptyString()));

                filtersToApply.insert(lineEntityName, lineFilter);

            }

            else if (!this.parmProjId())

            {

                var lineFilter = lineFilterBuilder.and(

                                                lineFilterBuilder.areEqual(fieldStr(JPPurchTransferBudgetEntity, FiscalYearName), JPPurchTransferBudget.FiscalYearName),

                    lineFilterBuilder.areEqual(fieldStr(JPPurchTransferBudgetEntity, DataAreaId), JPPurchTransferBudget.DataAreaId));

                filtersToApply.insert(lineEntityName, lineFilter);

            }

            if (this.parmProjId())

            {

                var lineFilter = lineFilterBuilder.or(

                                                lineFilterBuilder.areEqual(fieldStr(JPPurchTransferBudgetEntity, ProjId), this.parmProjId()),

                    lineFilterBuilder.areEqual(fieldStr(JPPurchTransferBudgetEntity, ParentId), this.parmProjId()));

                filtersToApply.insert(lineEntityName, lineFilter);

            }

        }

        return filtersToApply;

    }

</pre>

Enhancing User Experience with Custom Filters in Excel Office Add-in