Customize the Dynamics 365 User Interface with Extension and Event Handler

Open in Excel experiences in Microsoft Dynamics facilitate transaction input in several places in Dynamics 365. Out-of-the-box, the Open in Excel templates are designed with the Main Account Dimension only, as it is the only Financial Dimension common to all customers.

In most cases, however, customers employ either native Dynamics 365 or custom Financial Dimensions that must also be input into Dynamics transactions.

Before we start, is your team looking for a Dynamics 365 managed services partner? For information on our 24/7 support contact us.

Open in Excel templates must, therefore, be customized to include Financial Dimensions other than Main Account. This customization must be partly done in development and partly in Dynamics 365. To date, Microsoft has provided very helpful information about the development part, but not so much about template modification in Dynamics 365.

Open in Excel In Dynamics 365

Open the native Dynamics 365 Open in Excel template from the location of the desired update. In this example, we will be using the one for General Journals.

Click the Open Lines in Excel button on the ribbon, select the correct Template Name, then click OK.

When the following pop-up appears, click the Open button.

An Excel file will open. Click the Enable Editing button to make changes to the document.

Depending on the security enabled, such as that from some Single Sign On software, you may have to sign in with your Dynamics credentials again to activate the Data Connector on the right side of the Excel document. After the Data Connector appears, click the Design button.

Click the Pencil for the LedgerJournalLine Data Source.

The following will appear.

Select desired Available Fields from the list and add to the Selected Fields section using the Add button. You may select more than one Available Field at a time to add to the Selected Fields area by holding down the Control button while clicking your selections.

Field values in the Selected Fields section may be ordered as needed by selecting them and using the up or down arrows to move them to the desired location(s).

When all field values are in the correct order, click the Update button.

The following pop-up will appear.

Click Yes on the pop-up, then click Done on the Data Connector Design box.

After you return to the Data Connector home page, click the Refresh button.

The new fields you added will appear as new columns in the Excel template. If you would like to rename the field values, you may do so in the cell.

After redesign of the template is complete, save the file using the original template name (LedgerJournalLineEntryTemplate in this example). Then, in Dynamics, go to Common > Common > Office Integration > Document Templates.

Click the New button from the ribbon. The below will appear on the right side of the screen.

Click the Browse button, locate the file to upload, and click the Open button.

The file information will automatically transfer into the necessary fields in the Upload Template pop up. Click the OK button.

Dynamics 365 will ask that you confirm your wish to replace the existing template file with the new one. Click Yes.

The next time you access the General Journal Open in Excel experience, the revised template will appear.

Though our sample has been specific to General Journals, the same steps apply to customization of other Open in Excel experiences in Dynamics 365. Some of those are the Budget Register Entry, Fixed Asset Journal Lines, and Vendor Invoice Journal templates. Template modifications made are globally effective in Dynamics; changes will appear across Legal Entities.

For questions on Dynamics 365, please contact Hitachi Solutions today.