Site icon Microsoft Business Applications

Excel integration (filter records with form value)



Scenario: We want to filter records in excel adding based on form value.

Standard D365 example is the General journal form.

Solution:

  1. Create a data entity for the data source
  2. Create an excel file and add it as a resource in Dynamics365
  3. Creating a class that will extend DocuTemplateRegistrationBaseBelow is an example of that
    class AG_IARRequestProcessTemplate extends DocuTemplateRegistrationBase
    {
        private const DocuTemplateName ExcelTemplateName    = resourceStr(AG_IARExcelTemplate);
    
        public void registerTemplates()
        {
            this.addTemplate(
                    OfficeAppApplicationType::Excel,
                    ExcelTemplateName,
                    ExcelTemplateName,
                    literalStr("Excel template for IAR  Process"),
                    literalStr("IAR process - Excel template"),
                    NoYes::No,
                    NoYes::No,
                    NoYes::Yes);
       		}
    
    }
  4. On your form where you want to add this excel and filter you can implement below interfaces
    (officeIMenuCustomizer,OfficeITemplateCustomExporter) and implement customize menu options method on AG_IARProcess

Public class AG_IARProcess extends FormRun implements officeIMenuCustomizer,OfficeITemplateCustomExporter

public void customizeMenuOptions(OfficeMenuOptions _menuOptions)
{
ListIterator dataEntityIterator = new       ListIterator(_menuOptions.dataEntityOptions());
        while (dataEntityIterator.more())
        {
            dataEntityIterator.delete();
        }
        	
DocuTemplate docuTemplate = DocuTemplate::findTemplate(OfficeAppApplicationType::Excel, resourceStr(AG_IARExcelTemplate));
        if (docuTemplate)
        {
OfficeTemplateExportMenuItem menuItem = OfficeTemplateExportMenuItem:constructWithDocuTemplate(docuTemplate, docuTemplate.TemplateID);

            _menuOptions.customMenuItems().addEnd(menuItem);
        }
    }
}

Implement the below method as well getInitialTemplateFilters
In this method, you can pass your filters

public Map getInitialTemplateFilters(OfficeTemplateExportMenuItem _menuItem)
{
     AG_IARequestDetailsTable   iarTable = AG_IARequestDetailsTable_ds.cursor();
     const str templateName = resourceStr(QTQ_IARExcelTemplate);
     DocuTemplate template = DocuTemplate::findTemplate(OfficeAppApplicationType::Excel, templateName);
     str iARequestDetailsTableEntity = tableStr(AG_IARequestDetailsTableEntity);  
      if (template && template.TemplateID == templateName)
       {
          ExportToExcelFilterTreeBuilder filterBuilder = new ExportToExcelFilterTreeBuilder(iARequestDetailsTableEntity);
          var filter = filterBuilder.areEqual(fieldStr(AG_IARequestDetailsTableEntity, CaseId), detailBase.CaseId);
          filtersToApply.insert(iARequestDetailsTableEntity, filter);
       }
}
Exit mobile version