Dynamics NAV Excel Template - Purchase Journal
SmartConnect 2017+ (Office 2016+)
Dynamics NAV Excel Template - Purchase Journal
Integrate directly into Dynamics NAV using our pre-configured Excel templates!
The templates make data entry fast and easy without sacrificing power or flexibility. Each template has a simple interface can be set up quickly and used as-is or customized to fit your exact integration needs.
Right out of the box the templates get you moving quickly, starting with four tabs visible: a README tab, a setup/connections tab, a main entry tab, and a map run log tab. Drop-down boxes speed up data entry on the main entry tab. Un-hiding tabs and using buttons on the ribbon give more control should you need to customize – nearly every setting and customization is saved in the workbook instead of locked away in code.
With many settings exposed, the templates are incredibly flexible and ready to be customized in many ways. The main interface allows adding in additional fields to the heading or lines. Additional Dynamics NAV pages can be added for creating additional lookups. Error messages directly from NAV are in XML and can be cryptic, but cleaner and clearer error messages can be added to help with troubleshooting.
This specific workbook and map are part of a collection of 15 templates: 7 Dynamics 365 templates and 8 Dynamics NAV templates. There is a workbook that integrates into:
- General Journals
- Item Journals
- Purchase Journals
- Sales Journals
- Purchase Orders
- Sales Orders
- Transfer Orders (Only into NAV, no D365)
- Sales Invoice
- Setting up Excel security (Office 2016):
- In Excel click on the File tab and select Options
- On the Trust Center Settings window select Macro Settings
- ‘Disable all macros with notification’ or ‘Enable all macros’ can be selected
- In the Trust Center Settings window select External Content
- ‘Enable all Data Connections’ or ‘Prompt user about Data Connections’
- Please note if ‘Disable all macros with notification’ or ‘Prompt user about Data Connections’ is selected the user must choose whether to enable this functionality or set up trusted locations and documents.
- Open internet explorer
- Select Tools -> Internet Options
- Click on the Security tab
- If accessing from the Local intranet follow steps 6 and 7
- If accessing from the internet, select Trusted Sites from the zone and select Sites. Add the URL of the SmartConnect Web Service and click add. Click on close and follow steps 6 and 7
- Click on Custom Level and scroll down to User Authentication -> Logon
- Select Automatic logon only in Intranet zone
- The table below gives the list of published web service pages and their ID's
- Importing the Map
- In SmartConnect go to Maintenance -> Import
- Click the Select Files button and select the map file "NAV Excel Template Maps.sce". Make sure "NAV_PURCHASE_JOURNAL" is checked, but other maps can be imported as well
- Ensure the map shows up in the import list and that the checkbox is marked and click Import
- Setting up the Map
- In SmartConnect, select the Map ID 'NAV_PURCHASE_JOURNAL' from the list of Maps, and click edit from the toolbar.
- In the Settings sub tab, browse to the location of the "Nav Purchase Journal.xlsm" workbook. NOTE: The workbook contains macros and is of the '.xlsm' format, so select this format from the drop-down menu
- Change the Sheet name to SCValues if it is not already selected
- Map Security
- The user that was used to configure the SmartConnect web service must have access to run the imported map. To do this go to Setup in SmartConnect, select Security and follow the main SmartConnect manual for setting up SmartConnect security
- Setting up the Template
- Select the SmartConnect Config sheet inside of the template:
- When the cell for 'NAV Company Name' or 'Full Computer Name' are selected, an input box will appear prompting to change that field. If the field is changed through the input box, all of the Power Queries will be updated to use the new value entered.
- The gray cells in the 'External Connection Settings' section define how the workbook connects to the SmartConnect web service and the NAV server.
- The screenshot below has the URL-encoded company name highlighted, as well as an object ID and service name. The object ID and service name are required when publishing a page.
- Select the SmartConnect Config sheet inside of the template:
- The Map field does not have to change as this is the ID of the map that was designed to work with the template. The map should be set up in SmartConnect.
- The SmartConnect Web Service needs to be set to the URL of the web service ( i.e. http://eone-2017:5557/SmartConnect.svc).
- The SmartConnectConfig sheet can be hidden once it is setup so that it isn’t accidentally modified.
- How it Works
- This template does not do any validation within the workbook before attempting to send the workbook to the SmartConnect web service because of the many different types of validation that can occur. Instead, all error messages returned from NAV will be returned directly from SmartConnect and saved to the "Map Run Log" tab.
- When creating transaction lines, the entire table of entries will be sent, so no lines can be skipped and no blank rows can be left in the table.
- Creating a Journal Entry
- Fill out the table. Note that not every field will be filled out for each row. The rules on what fields should be filled out are the same as the NAV web interface.
- If a batch type has defaults set up, we may not be able to over-write these. This is a limitation of the NAV Odata web service and not Excel or SmartConnect.
- The error message returned is unstructured XML and can be hard to read as-is. The 'Map Run Responses on the 'Excel Model Config' tab translates the raw XML message into a cleaner dialog message.
- NOTE: To modify the message returned from running a map, see the description of the "Map Run Responses" table below.
The ribbon provides additional controls to use the model and to customize the main entry tab. The templates have a ribbon tab called either 'NAV Templates' or '365BE Templates' depending on the type of template. The image below shows a screenshot from the NAV Sales Journal template, but the buttons are all labelled the same way and have the same functionality in all templates.
Below are the button descriptions:
- Refreshes the table to be sent to the SmartConnect web service, sends the table to the web service, then returns with an error/success message.
- Refreshes all the data tables that have a PowerQuery associated with the data source. If this is the first time using the template, you may need to enter credentials.
- When connecting to D365 Financials use basic authentication and the web service access key as the password (as shown in picture).
- When connecting to NAV, use windows authentication.
Refresh Output Dataset
- Refreshes the table to be sent to the SmartConnect web service. To view exactly what is being sent, unhide the 'SCValues' tab and click this button.
- This adds a new header field to every line on the table that gets sent to the web service. Clicking the button will ask what the name of the header should be and what cell you want to be pulled.
- The example below shows how to add a column called 'CREATE_DATE' and previewing the SCValues tab.
After creating the new column, click the 'Refresh Output Dataset' and select the 'SCValues' tab (it may be hidden by default). We see the 'CREATE_DATE' column added with 12/5/2017 copied as values for every row in the table.
- This lets you choose the column name that gets sent to the web service. To change a column name, select a column in the workbook and click the button.
- The example below shows changing the 'Account Type' column to still say 'Account Type' on the worksheet but to actually send 'MY_ACCOUNT_TYPE' to the web service. The table to the right shows the SCValues tab with the new column header.
Add Key Field
- A key field means that the field needs to be populated before the row will be sent to the webservice. With a column in the table selected, clicking the 'Add Key Field' button will toggle between the column being a key field or not.
- Below shows adding a key field to Account Number and previewing the results on the SCValues tab.
- A multi-lookup is a cell that can have a value or a formula. This is useful if a field can be selected in two different ways (usually by an ID number or by name). For example, an account can be selected by choosing the account number or the account name. If you choose an account number, the account name cell is populated with a lookup formula then immediately pasted as value after the lookup occurs. If you choose account name, a lookup formula populates the account number then immediately pasted as value.
- Let's show adding a vendor account number/vendor account name lookup to the heading at the top for demonstration purposes. The two yellow boxes have a drop-down added that is linked to vendor number and vendor name columns of the vendor field. If you choose a vendor name, the vendor number can be looked up using an INDEX/MATCH formula like shown below.
- To make the vendor number formula a multi-lookup in cell G2, select cell G2 and click the 'Add Multi-Lookup' button. It will first ask for the 'trigger cell' that, when changed, will cause the formula to evaluate. The vendor name is the trigger cell in this example, so choose G3.
- Now we can choose a vendor number using the drop down, or we can choose a vendor name from that drop down and the vendor number will be populated for us.
- Notice how if you choose the vendor number, the vendor name DOES NOT update. To make them both work, add a lookup formula to G3, click 'Add Multi-Lookup' and choose G2 as the trigger cell. The screenshot below shows the formula entered.
- After adding multi-lookups to both cells, we can use either drop-down and the other cell will be populated. This example shows how to create a multi-lookup for a header, but the process is the same for adding a multi-lookup in the table.
Excel Model Configurations
Excel Model Config tab
The 'Excel Model Config' tab has a bunch of tables that control how our model runs. The list below explains some of tables that appear on that tab. Note that this tab is hidden by default and does not ever need to be edited manually, but can be useful for troubleshooting.
Header Field Table - This table contains all of the fields that are copied down for each row that is sent into SmartConnect. The 'Label' is what the column header is labelled as, and the 'Value' column is a live formula linked to the main entry tab.
Mapping Table - The mapping table tells what gets sent into SmartConnect. As of now, only 1 map can be linked to a workbook at a time, so this table should not be modified.
Map Run Responses - This table gives a list of possible messages that are returned when a map is run. The XML that gets returned by default can be hard to read, so this table gives a cleaned up version of the XML response. If the XML returned contains text from the "Contains" column, the message box returned will use the title from the "Title" column and the message body from the "Response" column. More responses can be added and existing responses can be modified or deleted. If no match is found, the raw XML is returned. The 'Map Run Log' tab contains a table that gives the raw XML returned for every run in case you need to go back and see what was actually returned.
Data Table Field Names - This table translates the descriptive names on the main entry tab to more appropriate names before being sent into SmartConnect. The "Column Name" is the column heading from the main entry tab, and the "Map Field Name" shows what is printed on the SCValues tab.
Multi-Lookup Trigger Table Ranges - This table contains the list of multi-lookups that are live on the model. A "multi-lookup" is a pair of cells or columns that allow a user to enter a value in one and default the value of the other. For example, if an entry has accounts, you may want to search for an account by the account number or by the account name. With a multi-lookup, you choose what to select and the other is defaulted in. Two types of multi-lookups are for headers or for columns within a table. If a cell is changed with a cell address from the "Trigger Address" column or from a table column named in the "Trigger Column Name", the corresponding Target cell will get populated with the "Formula" temporarily. Once the formula is refreshed to populate the field, it will be pasted as a value to remove the formula. Most multi-lookups have two entries in this table to allow either one of the fields to be entered (and the other defaulted in). This table can be modified, but errors may be difficult to trace as the formula is deleted immediately.