Top Microsoft Office Integration Features in Dynamics 365 F&O

When Microsoft Office is integrated into Microsoft Dynamics 365 Finance & Operations (D365 F&O), you’ll benefit from many features that will help you speed up business processes, improve your time management, reduce duplication of work and more.  

Read this OneByte article to learn the most useful features that come with an integration and how to take advantage of them for your team and organization. Check out this previous OneByte for an in-depth article on how to configure a Microsoft Office integration. 

 

Features we’ll explore: 

1. Document Templates – Quickly create and customize templates within D365 F&O, ensuring consistent and efficient document generation. 
2. Custom ‘Open in Office’ Menu – Personalize the ‘Open in Office’ menu for precise control over data entities, optimizing reporting and analysis. 
3. Excel Add-In with Data Lookups – Use Excel’s built-in data lookups, such as date pickers and Enum lists, for faster and more accurate data entry. 
4. Custom Real-Time Lookups – Build custom lookups to access real-time data from external services, keeping your data up to date. 

These features streamline workflows, save time, and boost overall efficiency. 

 

Document templates

Document templates can be added from the Document templates page. System-generated templates can also be added by interfaces or event handlers. Several fields that are associated with each document template record control the behavior of that template on the Open in Office menu. 

Field	Description
Root data entity	The root data entity of the template. The root data entity is used to determine which pages the template can be included on.
List in Office menu	If this field is selected, the template will be included on the Open in Office menu on applicable pages. (The applicable pages depend on the root data entity).
Apply record filter	If this field is selected, the data will be filtered, based on the record that is currently selected on the page.
Apply company filter	If this field is selected, the data will be filtered, based on the current company.

 

 

• For Excel templates that are included on the Open in Office menu, columns and fields will be trimmed from the workbook, based on the configuration keys that are applied to the system and the applicable country/region context.  

• If a configuration key is associated with a column or field in the workbook, the column or field will be removed if the configuration key is disabled. If a set of country/region codes is associated with the column or field in the workbook, the column or field will be removed if the country/region code isn’t in scope. 

 

Open in office menu customization

• There are several methods for customizing the content that appears on the Open in Office menu on a given page. For example, you can customize the content statically through metadata properties on model element and code attributes. However, customization via code gives you the finest level of control. 

• In code, you can either implement one or more interfaces on a page, or use extensions and event subscriptions. The following sections describe the customization scenarios that are most often used. 

• If you must modify a page that you own, interfaces are the most appropriate customization method, because they give access to all private members of the page and allow for deeper customization. You can apply the following interfaces to the code for a page. 

Interface	Description
OfficeIMenuCustomizer	Use this interface to modify the set of data entities that is considered for a page and add custom menu items.
OfficeIGeneratedWorkbookCustomExporter	Use this interface to do a custom export of a workbook that is generated at run time.
OfficeITemplateCustomExporter	Use this interface to do a custom export that is based on a Document Template record.

 

• To define what is actually exported, use an ExportToExcelDataEntityContext() method available in OfficeIGeneratedWorkbookCustomExporter. 

• Various filters can be applied on the data based on current company or current record. 

• To explicitly add menu item of a different entity, we can also use customizeMenuOptions(OfficeMenuOptions _menuOptions), available in OfficeIMenuCustomizer Interface. 

•  To add a menu item that corresponds to a Document Template record, use an OfficeTemplateExportMenuItem Interface. 

• If you must modify a page that you don’t own, you should avoid using interfaces, because that approach will require over-layering. Instead, you should do the customization through extensions and event subscriptions. To use this approach, implement an extension class that subscribes to the OnIntializing event of the page that you’re customizing. 

• To explicitly add a menu item, you must add it to the OfficeMenuOptions.customMenuItems() list. To add a menu item that corresponds to a workbook that is generated at run time, use an OfficeGeneratedExportMenuItem. 

• To define what is actually exported, use an ExportToExcelDataEntityContext. The method for specifying the ExportToExcelDataEntityContext depends on whether you’re using interfaces or extensions and event subscriptions to customize the Open in Office menu. 

• Integration scenarios require that some data entities be publicly available via the OData Service. However, it isn’t always appropriate that these data entities appear on the Open in Office menu by default. In this scenario, you can add the OfficeMenuOmit code attribute to the entity declaration. 

 

Lookups in Excel

To facilitate data entry, the Excel App provides lookups and data assistance. Date fields provide a date picker, enumeration (enum) fields provide an enum list, and relationships provide a relationship lookup. 

1. Go to Fleet Management > Rentals > Rental.
2. Select Open in Microsoft Office > Open in Excel > Fleet Management Rentals. 
3. Open the workbook that is generated. 
4. Select Enable editing to enable the Excel Data Connector App to load and read in data. 
5. Select a Driver’s license value. Note that a relationship lookup now appears in the Excel App and shows a list of customers. Because relationship lookups are in their first generation, no filtering or sorting is currently supported. 
6. Select another customer in the lookup and note that the Driver’s license value changes. Because this field is part of the key, click the original Driver’s license value to reset it. Note that the Driver’s license, first name, and Last name fields form a multi-part key, but the Excel App doesn’t immediately change all parts of the multi-part key. 
7. Select a Start date value. Note that a date picker now appears in the Excel App. 
8. Select another date to change the Start date value. 
9. Select Design and edit the FleetRental data source by adding the Status field as a column in the table binding. 
10. When you’ve finished adding the Status column, click a Status value. Note that an enum list now appears in the Excel App. 
11. While focus is on the Status column, move up and down the list of rentals to see how quickly the enum list changes to reflect the current value. The whole enum list is shown, so that the user can quickly see all the available values. 
12. Select a different Status value to see how an enum value can be changed by using a single click. 

 

Create a custom lookup 

A custom lookup can be created to show data options when an enum or relationship isn’t sufficient. The main use case is when data must be retrieved from an external service and presented in real time. 

1. Start Visual Studio by opening the previously created project where the model is set to Fleet Management or create a new project. 
2. Open the designer view for FMCustomerEntity. 
3. Right-click Methods, and then click New Method. 
4. Add the lookup_Country code from the following example. 

 

Code sample: 

  public class FMCustomerEntity extends common 

{ 

  [SysODataAction(“FMCustomerEntityCountryCustomLookup”, false), //Name in $metadata 

  SysODataCollection(“_fields”, Types::String), //Types in context 

  SysODataFieldLookup(fieldStr(FMCustomerEntity, Country))] //Name of field 

  public static str lookup_Country(Array _fields) 

{ 

OfficeAppCustomLookupListResult result = new OfficeAppCustomLookupListResult(); 

 

result.items().value(1, “US”); 

result.items().value(2, “AU”); 

result.items().value(3, “FR”); 

result.items().value(4, “GR”); 

result.items().value(5, “NZ”); 

 

return result.serialize(); 

} 

  } 

1. Save the code. If you’re asked whether you want to overwrite the existing code or save is as a new file, click Overwrite. 
2. Build the solution (press Ctrl+Shift+B). 
3. Verify that the change was successful. In the browser, go to Fleet Management > Customers > Customer. 
4. Select Open in Microsoft Office > Open in Excel > Fleet Management Customers. 
5. Open the workbook that is generated. 
6. Select a Country/region value. 
7. Change the Country/region value for a customer and publish the change. 

 

Did you know about these features? Utilize them to improve your business processes and workflow! 

 

About the expert

Vishakha Shamnani, Technical Consultant, OnActuate Vishakha is experienced in development and customization for a variety of functionalities in Microsoft Dynamics 365 F&O and customizing Power BI and SSRS reporting and F&O integrations. Vishakha is currently certified in MB-500: Microsoft Dynamics 365: Finance and Operations Apps Developer and AZ-900: Microsoft Azure Fundamentals.

 

The “OneByte” blog series are technical how-to articles by OnActuate’s expert consultants covering Microsoft Business Applications (Microsoft Dynamics, Power Platform) and related technologies. Have a topic you’d like to learn more about? Email us at info@onactuate.com!