D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

Custom 'Open in Excel' template drops lines grid

  • 1.  Custom 'Open in Excel' template drops lines grid

    Posted Apr 01, 2019 11:14 AM
    I'm using this link as a guide on how to create a document template for the purchase order:  https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/office-integration/office-integration-edit-excel

    Although we are only interested in loading the lines to a newly created purchase order header, I have to have the header on the template because the main datasource on the template must be the main datasource on the form, in my case form: PurchTable.
    I just can't get it to work. When I click on the Open in Excel and choose my template, then it shows the header but the lines are missing. It also just shows the first record in the PurchTable or then the last one that was pulled in when I refreshed the template.

    I looked at the video mentioned in the Microsoft doc link. The guy said the version of Excel should be  at least 16.0.6868.2060.

    I looked at the Excel version on my Cloud Dev box and it is 16.0.4738.1000 64-bit. so definitely older than what I should be working with. The Excel doesn't have a "Check for updates" button. So I don't know who is responsible to update the Excel version in my Cloud Dev environment. It is one of the pay-as-you-go environments. I assume Microsoft.

    When I was using the Excel on the Dev box, I used Office integration > Excel workbook designer and then choose to create a blank workbook. This is so that it can automatically connect me to the correct data connector add-in.

    When I do the following step that is for changes to an existing template: "Clear the data from the template before upload by clicking Options (gear icon), expand the Data Connector section, then click the Clear binding data button", then I do not get the error "The cached data was in an invalid format and could not be loaded" when opening the template in Excel using the 'Open in Excel' on the PurchTable form. However Excel loaded without the lines grid. If I do not clear the data before uploading the template into Document Templates.

    I assumed it is because of not having the correct Excel version on my Dev box.

    So I went to the Excel on my desktop that has the version 16.0.10730.20264 32-bit.

    I clicked on Insert > Get Add-ins and selected 'Microsoft Dynamics Office Add-in'. For the server URL I gave it my cloud Dev server URL and it was able to retrieve the metadata.

    I created my template:

    I then copied this template to my Dev box and uploaded it into the Documents templates. The List in Open in Office menu is checked.

    I then go to All purchase orders in the accounts payable menu. My template is showing in the Open in Excel menu. I clicked on it, choose download and open.

    As you can see it is not showing the lines. It is not even showing the correct purchase order. It is showing the first order in the system instead of the order I was in when I clicked on the button. When clicking on the Enabling edit button I get this same error as with the version on my Dev box when I didn't clear the data.:

    An error occurred while reading the data cache Details: The cached data was in an invalid format and could not be loaded.

    The workbook didn't include the "cache"-sheet when I created it using the Excel on my desktop. When using the older version on my Dev-box I got the cache worksheet and it mentioned that it will not be generated in newer versions. SO I assumed it is because of the version it was created in that I do not have it.

    Creating the workbook with the older version excel that gives me the cache-worksheet, at least if I clear the binding data, I do not get this error. However it behaves the same in that it doesn't show the grid for the lines.  When I click on Design again on this workbook that was opened with clicking on the template in 'Open in excel', I noticed that my PurchaseorderlinesV2 data entity is missing. So why will it drop it?

    In the design I added the PurchaseOrderLinesV2 data entity again by clicking on the ++-sign next to the main datasource. And it was added. When I clicked on refresh, it then pulled in the correct order with its line I was on when I chose the template in 'Open in excel'.

    I added a line and published it and it did write it back to my order successfully.

    Seeing that it was able to write back to D365FO into the correct PO. I saved this specific workbook with a different name, deleted my template under Document templates, and uploaded this new template. At the bottom it shows that the root data entity is PurchPurchaseOrderHeaderV2Entity. I gave the template a different name to make sure it is picking up the latest template.

    I chose this latest template under Open in Excel and download and open and the lines grid is missing again. Why is it dropping the lines data entity?

    I got again the cache data error. At least it is showing the correct order in the header but could be just because that is what was in the template with the last time I clicked on Design

    I even uploaded this template into Production to make sure it is not the Excel on Dev that is the problem but it behaves the same in production.

    The example in the Microsoft Docs in the link I provided specifically show how to do it for a purchase order header and lines. So it should work.

    What is making it even more weird is that when I go back to the template under Organization Administration > Setup > Office Integration >  Document Templates and choose to download my template as if I want to modify it, it opens the workbook with the lines grid populated with data together with the header and the PurchaseOrderLineV2 datasource showing in the design.

    So what am I missing? Is it the versions of Excel I'm working with? 

    Retha Lambrechts
    The Master's Craft Flooring Company
    West Plains MO

  • 2.  RE: Custom 'Open in Excel' template drops lines grid

    Posted Apr 09, 2019 11:41 AM
    The problem in the end was that there is a bug in version 7.3 and the fix for it is in V10.

    Retha Lambrechts
    The Master's Craft Flooring Company
    West Plains MO


If you've found this thread useful, dive deeper into User Group community content by role