>> Microsoft Excel can be used to view and enter
data in Microsoft Dynamics
365 for Finance and Operations.
In this video, we'll show you how to
create a new Excel template and
then use that template to view and enter
data in a header and line pattern.
However, before we proceed,
you must make sure that the version of Excel that you're
using is at least 16.0.6868.2060.
If you have an older version,
you must update it.
For instructions, see
the "Troubleshoot the Office integration" topic
on docs.microsoft.com.
Start Excel and create a new blank workbook.
To enable the workbook to
communicate with Finance and Operations,
you must first install an add-in.
Click the "Insert" tab.
Click "Store", search for "Dynamics",
click "Add" next to Microsoft Dynamics Office Add-in.
The "Add-in" now appears on
the right side of the Excel window.
You must now add server information
to connect to Finance and Operations.
Excel has now established
a connection with Finance and Operations.
We'll now design a template for
viewing and entering purchase orders.
In the "Data Connector Pane",
click "Design" to turn on "Design" mode.
Click "Add Fields", select
the "Purchase Order" header table
so you can add the header fields.
Click "Next" to view the list of fields that you can add.
There are a few things that you must
understand before you add the fields.
First, you must add
all fields that make up the primary key.
These fields have a key symbol beside them.
Second, you must also
add the purchase order number field,
even if a number sequence will be used
to automatically number purchase orders.
The purchase order number field is required because
Excel must have all the information that's required,
in order to successfully create
a purchase order in Finance and Operations.
Even if you don't plan to
create purchase orders by using Excel,
it's still a good idea to add
the purchase order number field
from a functional perspective.
Third, because we're designing
the header part of the template,
you must lay out the fields in
a vertical manner not in a horizontal grid-like manner.
Therefore, you must choose a cell and the workbook for
the label of the field and
an adjacent cell for the value.
You'll do this for every field that must be added.
Let's look at how we can add a field.
Select cell "A1" for the label of the data area.
Select cell "B1" for the value of the data area ID field.
Repeat this process for
every other field that you want to add to the header.
After you finish adding fields,
you can complete the design
of the header for this template.
Click "Refresh" to fetch data
from Finance and Operations.
The workbook retrieves the first purchase order that was
created across all legal entities
in Finance and Operations.
Later in this video, we'll look at how
to bind templates to legal entities.
This is a good time to make sure that the formatting of
the purchase order number field is set to text.
If it isn't, you must change it to text.
We must now add support for purchase order lines,
in the data connector pane,
turn on design mode.
We must now add the purchase order lines table
as a child table to the header.
Click the "Double Plus" sign button next
to the purchase order header data source.
The data connector app automatically
selects the purchase order lines table.
Click "Next" to see the list of
fields that are available for lines.
Select the cell as
a starting point to lay out the grid for the lines.
As before, you must add the primary key fields,
because the fields will be laid
out in a grid like manner.
You just have to select a field.
After you finished, the
lines grid appears in the Workbook.
Click "Refresh" to fetch
the lines that correspond to the purchase order.
Save this template to your computer.
As the user who created this template,
you can continue to use the template to
view existing data and add new data.
However, you might also want to make
the template available to
other users in your organization.
Let's quickly see how we can do
this in Finance and Operations.
In Finance and Operations,
in the common module, open the document templates page.
This page lists all the Excel templates that are
available out of the box in Finance and Operations.
We allowed the template that you just
created to this list.
Click "New" to begin the process.
Click "Browse" to select and upload
the template that you save to your computer earlier.
By default, this template will be
available on the "Open an Office Menu"
on the purchase orders list page for
all legal entities and Finance and Operations.
However, if you want to make this template
available to a specific legal entity only,
you can select this specific company checkbox
and then select the legal entity.
Note, that if you want multiple
legal entities but not all of them.
To have access to this template
you can make copies of the template,
upload the copies individually,
and then link each copy to a different legal entity.
In this way, you can control
the scope of visibility for a template.
By default, the template is
available to all countries and regions.
Therefore, the template will always appear on the
"Open an Office Menu" on the purchase orders list page,
regardless of the country or region codes
that are enabled in Finance and Operations.
However, if you want to limit
this template to a specific country or region code,
you can associate it with a country or region by
selecting the specific country or region checkbox,
and then selecting the country or region.
If the template must be associated with
multiple countries or regions but not all of them,
follow the same process of making copies of the template.
By default, the template is
available to any user language.
Therefore, the template will always appear in the
"Open an Office Menu" on the purchase orders list page,
regardless of the language that's selected on
the user options page in Finance and Operations.
However, if you want to limit
this template to a specific language only,
you can associate it with a language code by
selecting the specific language checkbox,
and then selecting the language code.
If the template must be associated with
multiple languages but not all of them,
you can follow the same process
of making copies of the template.
After you've added the template
it appears on the list page.
Switch to the specific company that the template was
associated with and open the purchase orders list page.
On the "Open an Office Menu",
you'll see that the new template is available for use.
If you don't see the template
on the "Open an Office Menu",
make sure that your language setting and the country or
region codes that are enabled in Finance and Operations,
match the settings for the template.
This brings us to the end of this presentation.
We hope you found this information
useful. Thank you for watching.