The post last week about posting Global general journals from one entity was part of a wider thought process for a client relating to working across regions (i.e. groups of legal entities) and globally (all legal entities in an environment).

Take an example, whereby a user needs to create a new fixed asset1, one for each of ten companies in the system which represent a region. At face value, D365 F&O struggles, with the only obvious approach being to login to each company and create the requisite asset.

Sure, we could use DMF, but that’s not practical for the everyday user. We could use an ISV, if we have endless budget for additional licensing and support costs.

If only we could add the Company field as a column, and clear any filter on it. Yes, if only that was possible…

(It’s not.)

Go on then, how?!

The oh-so-easy answer is the Excel Add-in. When you use the Open in Excel option, you’ll note the name of the relevant data entity with the current legal entity filter in brackets:

And as you’d expect, once the Excel sheet loads, you will see all the Fixed assets for the current company:

Now the clever bit…

If you click the Filter option, you’ll see that the Add-in has applied a filter based on the current company. Clear it!

Now, if you click Refresh, you’ll be able to see all fixed assets globally across the system, or at least, within the legal entities you have permission to access.

Do not try to add any fixed assets at this stage! You won’t be able to, because the Publish button won’t work.

You need to modify the design of the Excel template, to add the Company column – exactly what the main interface prevents us doing. Click on the pencil icon next the name of the data source (Fixed assets V2 entity). Find the field for Company and click Add, to move it to the Selected fields:

Once you click Update and Refresh the table, the Company column will be added and populated:

And bingo, now you can add fixed assets, to multiple companies simultaneously, to your heart’s content.

Any other tips?

A few other bonus tips to make your Excel Add-in experience a bit easier…

  1. You can save the Excel workbook you’ve just created and use it whenever you need to, i.e. you only need to follow these steps once!
  2. This applies if you’re working across multiple environments too, because if you click the gear icon at the top of the connector, you can change the URL of the environment you’re working in:
  1. If you ever have a clumsy moment and click the X to close the pane for the Microsoft Dynamics Office Add-in, don’t panic – there is no need to re-download or re-open the file! Simply go to Home > Add-ins and the Add-in is waiting patiently there to be re-opened:

Conclusion

Hopefully this post has highlighted some of the wider functionality of the Excel Add-in for D365 F&O that you weren’t previously aware of, as well as giving you a readier means of performing bulk updates across companies without DMF or ISVs.

Perhaps I should have called the article, “How to do more with the D365 Excel Add-in”, but given there’s still more to cover, I’ll save that title for another time!

  1. Of course, some data entities are more global-friendly than the likes of Fixed assets. Examples include Parties (in the Global address book) and Products (before they are made Released products in companies as required). ↩︎

Published by Mike Pearsall

Mike is a founding editor of AX7 - The D365 F&O Blog. He is a business and solution architect with experience of successfully implementing D365 F&O on both client and partner side, as well as strong knowledge of the wider D365 suite and Power Platform.

Leave a comment

Your email address will not be published. Required fields are marked *