Document Template for Uploading Purchase Order Lines

 

1.   Install the Excel Add-in in Excel

Go to Insert Tab àGet Add-ins àSearch Microsoft Dynamics Office Add-in à Click Add

Then Fill the server information (URL – of Dynamics Environment)

 

2.   Now Let’s understand the meaning of below option:

S. No.	Option	Use
1	New	>> To create new record (like new purchase order, sales order, general journal etc) >> Previous data will be override
2	Refresh	>> By refreshing, Data can be exported to the template from Dynamics 365 >> By default it shows the first record available in the data source
3	Publish	>> After making changes in the data in template (fill the data), then we publish the data. It updates the values in the Dynamics. >> There is option that to refresh the data after publish
4	Filter	>> To Filter the data, there are many options like we get during creation of Advance rules criteria Here are the examples: >> Select the datasource (tables) where filter is to be applied >> Add a filter field (equal, not equal, less than etc) >> Clear filter
5	Design	>> For adding Datasource (New table or New field) See more:

 

3.   Create Template from Scratch

Warm-up by making a simple template for Creating new purchase order and upload multiple lines

Steps:

1)      Click on Design Button

 

2)      Click Add Table

            

3)      Select the Table (Purchase order header) and click next

4)      Click Add Fields

5)      Select the required fields from Available fields

a.       Click on Add Label (Will add only the name of field in the excel)

b.       Click on Add value (Will add the value field which will be retrieved once we use the refresh button)

c.       Then click “Done”

Note:

                                 i.            Field having primary key must be in the selected fields.

                               ii.            Add Label or Add Values button should be press after selected the excel cell where the label and its values should be reflected.

Extract of Excel sheet:

Use the refresh button (shown earlier) to retrieve the data.

6)      After creation of header of Purchase order there needs to add the datasource (table) for Purchase order lines. So let’s start

Select the Purchase order lines and then click next.

7)      Select the required fields from Available fields and click on Add button (under Selected fields) or simply Double Click.

8)      After adding required field. Select the excel cell where the data is to be added. Click Done.

Extract of Excel sheet: After refreshing

        

9)      Now we have a sample data exported for Purchase order header and purchase order lines. Save a copy of this file. Then start doing further experiment.

10)   To Create new purchase order, first check the next number sequence of Purchase order in the Dynamics 365. (e. g PO-WM-000020462)

11)   Click on new. Click Yes to the warning (clear the data source)

12)   Fill the data with new purchase order number and Line number should be unique. (We can arrange Columns as per our requirement. 

13)   Click on Publish button.

 

14)   Now we can verify the purchase order from Dynamics 365 and confirm the purchase order. 

4.   Add the Template in Dynamics 365

Template is created but from where to Access it in the dynamics??

Okay!!

a)       Go to Common > Common > Office integration > Document templates.

b)      Click New.

c)       Upload the file by browsing the saved template. Here template can be shown to specific legal entity; in case, template need to be made visible in selected companies, then separate templates will be made and make company specific.

d)      Click OK.

e)      Now same can be verified from the All purchases order form. Go to Accounts payable > Purchase orders > All purchase orders.

f)        Click Open in Excel.

g)       Click Download. Then same process can be followed for creating new purchase order (steps explained above)

That's it for the blog, will see you in next blog.

Thanks for reading!

       By Neeraj Kumar