Open in Excel with filter

 I've found that the open in Excel functionality was lacking in certain aspects.


Technically for forms not implementing the office add-in excel interface they retrieve data with only possible filters being company filter and current record filter. A customer of ours wanted to open Project item requirements already filtered with lookup on specific columns. As it is not possible to implement interfaces on form extensions, so another solution was needed. 


Requirements:

- Open in excel data pre-filtered on project id

Pros:

- Programmatically control filters and columns

Cons:

- Does not use Office templates

In this example I will be using the entity ProjSalesItemRequirementEntity. 


Prepare your entity

It is basic out of the box without anything in the AutoReport field group. 

First task is to add all key filds and wanted columns. The key fields enables you to publish back to D365, without them you cannot publish. 

The key fields are DataAreaId and ProjectTransactionId, the other fields are your own preferences and requirements. 



Event handler class

As the form ProjSalesItemReq does not implement the needed interface, and we cannot add interfaces in extensions. We need to latch onto the the event FormEventType::Initializing for the form and add a eventhandler to run a certain method when pressed. 

See below for complete code. 

Result


Now when I press the Open in Microsoft office, a new menu item is available.



The excel file has the filters applied:

The publish button is available without the need to add key fields.





Code:


using Microsoft.Dynamics.Platform.Integration.Office;

public static class InspProjSalesItemReq_Form_EventHandlers
{

    public static const str CustomExportLinesToExcelOptionId        = 'ProjectItemRequirementLines';        

    [FormEventHandler(formStr(ProjSalesItemReq), FormEventType::Initializing)]
    public static void ExportToExcel_DataEntityCustom_OnInitializing(xFormRun sender, FormEventArgs e)
    {
        FormRun formRun = sender as FormRun;

        if (formRun)
        {
            OfficeFormRunHelper officeHelper = formRun.officeHelper();

            if (officeHelper)
            {
                officeHelper.OfficeMenuInitialized += eventhandler(InspProjSalesItemReq_Form_EventHandlers::officeMenuInitializingHandler);
            }
        }
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name = "_formRun"></param>
    /// <param name = "_eventArgs"></param>
    private static void officeMenuInitializingHandler(FormRun _formRun, OfficeMenuEventArgs _eventArgs)
    {       
        // Modify the OfficeMenuOptions available on the OfficeMenuEventArgs.menuOptions() as necessary.
        SalesLine       localSalesLine = _formRun.dataSource(tableStr(SalesLine)).cursor();
        ListEnumerator  listEnumerator = _eventArgs.menuOptions().dataEntityOptions().getEnumerator();

        OfficeMenuOptions           menuOptions = _eventArgs.menuOptions();
        OfficeMenuDataEntityOptions entityOptions = menuOptions.getOptionsForEntity(tableStr(ProjSalesItemRequirementEntity));

        if (!entityOptions)
        {
            // The entity options were not included. Add them.
            entityOptions = OfficeMenuDataEntityOptions::construct(tableStr(ProjSalesItemRequirementEntity));
            menuOptions.dataEntityOptions().addEnd(entityOptions);
        }

        if(true)
        {
            OfficeGeneratedExportMenuItem menuItem = OfficeGeneratedExportMenuItem::construct(
                                                        tableStr(ProjSalesItemRequirementEntity)
                                                    ,   InspProjSalesItemReq_Form_EventHandlers::CustomExportLinesToExcelOptionId);

            menuItem.displayName(strFmt(    "Project requirements for project %2 (%1)", 
                                        ,   curExt()
                                        ,   localSalesLine.ProjId));

            menuItem.getDataEntityContext +=  eventhandler(InspProjSalesItemReq_Form_EventHandlers::getDataEntityContextHandler);
            menuOptions.customMenuItems().addEnd(menuItem);

            if(false)
            {
                ListEnumerator listCustomMenuItems = menuOptions.customMenuItems().getEnumerator();
                    
                while(listCustomMenuItems.movenext())
                {
                    var currentCustomMenuItem = listCustomMenuItems.current();

                    currentCustomMenuItem = currentCustomMenuItem;
                }
            }
        }
             
        //while(listEnumerator.moveNext())
        if(false)
        {
            while(listEnumerator.moveNext())
            {
                OfficeGeneratedExportMenuItem menuItem;

                int iIndex = 1;

                menuItem = menuOptions.allMenuItems().value(iIndex);
                menuItem.getDataEntityContext +=  eventhandler(InspProjSalesItemReq_Form_EventHandlers::getDataEntityContextHandler);            
            }
        }               
    }

    /// <summary>
    /// Adds context from entity and filter
    /// </summary>
    /// <param name = "_menuItem"></param>
    /// <param name = "_formRun"></param>
    /// <param name = "_dataEntityContext"></param>
    private static void getDataEntityContextHandler(OfficeGeneratedExportMenuItem _menuItem, FormRun _formRun, ExportToExcelDataEntityContext _dataEntityContext)
    {        
        ExportToExcelDataEntityContext eexeContext;

        SalesLine       localSalesLine = _formRun.dataSource(tableStr(SalesLine)).cursor();     

        if(_menuItem.id() == InspProjSalesItemReq_Form_EventHandlers::CustomExportLinesToExcelOptionId)
        {            
            _dataEntityContext.addEntityDefault(tableStr(ProjSalesItemRequirementEntity));            
            
            ListEnumerator enumerator = _dataEntityContext.entities().getEnumerator();            
            
            while (enumerator.moveNext())
            {
                ExportToExcelDataEntityInfo entity = enumerator.current();

                if (entity.entityName() == tableStr(ProjSalesItemRequirementEntity))
                {
                    ExportToExcelFilterTreeBuilder filterBuilder = new ExportToExcelFilterTreeBuilder(tableStr(ProjSalesItemRequirementEntity));                    

                    FilterNode filterProjId         = filterBuilder.areEqual(fieldStr(ProjSalesItemRequirementEntity, ProjectId), localSalesLine.ProjId);
                    FilterNode filterCompanyId      = filterBuilder.areEqual(fieldStr(ProjSalesItemRequirementEntity, DataAreaId), curExt());

                    entity.filter(filterBuilder.and(filterProjId, filterCompanyId));                    
                }
            }
        }
    }

}



Comments

Post a Comment

Popular posts from this blog

Call a simple Logic app from X++

Image
Logic apps can be called manually from D365FO with code I've made a basic Logic App that receive a short JSON. 1. Get JSON schema Get your JSON sample and go here and paste it in  https://www.liquid-technologies.com/online-json-to-schema-converter . Press Generate Schema and you have your schema in the box below. Example JSON : {   "sku": "875828",   "externalMaterialReference": "400267" } Example JSON Schema { "$schema" : "http://json-schema.org/draft-04/schema#" , "type" : "object" , "properties" : { "sku" : { "type" : "string" }, "externalMaterialReference" : { "type" : "string" } }, "required" : [ "sku" , "externalMaterialReference" ] } 2. Create Logic App In the Azure Portal or in your Visual Studio, cre
Read more

SysOperationFramework with use of Query

Image
 The SysOperation framework is very good and effective, but unforutnatly a bit complicated to get in to and do as you want.  I had one case in which a simple table should be updated, the job was to be able to set in batch. Also it had to use a query so that the user could easily set up the ranges as he pleased. Note that I will use the same query for updating the table which I mentioned. So the same query is used all the way through ending in using Quer::updateRecordset For this you need: Data contract class Controller class Service class Simple query oject Action Menu item Main menu where to make the menu item available. 1. Data contract class Note the simple query highlighted in bright yellow .  [DataContractAttribute     , SysOperationGroupAttribute('Parameters',  "Parameters", '1')] internal final class CustAutoCancelUpdateDataContract {     str             packedQuery;     MCRAutoCancel   autoCancel;     public Query getQuery()     {         return new Qu
Read more

Retail Attributes on sales lines

Image
Recently I've dived into Retail. There will be some posts about retail and the problems I've faced with solutions. First off will be the Retail Attributes, they can be problematic here is a data base diagram illustrating a simplyfied view, assuming the value is of type text. And here is the select statement for listing all sales lines with their attributes.         SalesTable salesTable;         SalesLine salesLine;         RetailSalesTableInstanceValue instanceValue;         EcoResAttribute attribute;         EcoResAttributeValue attributeValue;         EcoResTextValue      textValue;     while select salesId                  from  salesLine             join SalesId                   from salesTable                 where salesTable.SalesId == salesLine.SalesId             join SalesId, RecID                 from instanceValue                 where    instanceValue.SalesId == salesLine.SalesId                 &&  
Read more