Training Snacks

Handling American Express Bills in Dynamics NAV

By Anton Modaresi

Last year, one of our clients wanted to start using an American Express (Amex) business credit card. Among other things, paying their vendor invoices using a credit card would both save them a month’s interest and consolidate the payments into a single bill.

We recognized how this opened an opportunity to streamline the process for accounts payable in NAV as well. Wouldn’t it be awesome if they could import the bill and automatically tie it to all of their purchase orders?

I instinctively saw the challenge. The Amex bill is itemized and can be hundreds, if not thousands of entries. But this is what we developers love to do! Automate processes that make everyone’s daily life a lot easier.

The Process

The goal is to import Amex charges and record invoices to the vendors. The invoices should be closed out with credit memos, since the vendor will not actually be paid as the amount is due to Amex. Additionally, purchase invoices must be created to Amex for the full amount of the bill and these invoices are to be paid by your company.

Here is a summary of the postings to the G/L for each of the steps:

1. Import and post PIs for individual vendors

Expense (Dr)
Accounts Payable (Cr)

2. Automatically create, post and apply credit memos for the invoices in Step 1

Accounts Payable (Dr)
AMEX GL A/C (Cr)

3. Create and post PI for AMEX

AMEX GL A/C (Dr)
Accounts Payable (Cr)

4. Pay AMEX

Accounts Payable (Dr)
Bank A/C (Cr)

A modification can be created to simplify the process of creating and posting invoices and credit memos.
Here’s how:

1. Download the Amex bill to Excel.

2. Import the Excel file into NAV’s Amex Staging Table, as below:

Date	Description	Card Member	Account #	Amount	Vendor No.	Vendor Name
1/1/14	American Airlines	John H. Smith	XXXX-XXXXXX-00001	200.00	10044	American Airlines
1/1/14	Best Western	John H. Smith	XXXX-XXXXXX-00001	500.15	10034	Best Western
1/3/14	7-11	John H. Smith	XXXX-XXXXXX-00001	2.14	99999	Miscellaneous Vendor

*Only vendor field is editable.

3. NAV will default the Vendor No. based on the description for previously memorized Descriptions. Otherwise, the user must select a Vendor. Whenever the user selects a Vendor, NAV will associate the Description with the Vendor and use this as the default for future imports.

4. A Miscellaneous Vendor may be set up and selected for minor charges which you do not want to create or set up a Vendor for. See 3rd row above for example of a small charge at a 7-11.

5. Once all lines have been associated to a Vendor, the user may press the Create Invoices button. This will create and post all necessary invoices and credit memos.

Selecting Vendors

There is an Amex Memorized Vendors table used by the system to default Vendors when importing Amex transactions. It is updated by NAV automatically whenever a user selects a Vendor for a transaction in the Amex Staging table. The user can also manually update this table directly, if desired.

This table will be added to the Accounts Payable menu:

Description (100)	Description (100)	Vendor Name (defaults)
7-11	99999	Miscellaneous Vendor
American Airlines	10044	American Airlines

When an Amex file is imported into the Amex Staging table, the system will compare the Description field in the Staging table to the Description field in the above table. If the descriptions match, NAV will default the associated Vendor.

Note: NAV will attempt to find an exact match between the Amex file and the Amex Memorized Vendors table. Failing that, it will compare the characters in the Description field in the above table and ignore any trailing characters in the import file.

For example:

Description (Amex import file)	Description (Amex Memorized Vendors table)	Result
American Airlines UTAH 1233	American Airlines UTAH 1233	Since there is an exact match, NAV can default the vendor.
American Airlines	American Airlines UTAH 1233	There is no exact match, so NAV attempts to find a Description in the memorized vendors table that starts with ‘American Airlines’ and finds ‘American Airlines UTAH 1233’ and therefore can default the vendor.
Amer. Airlines		There is no exact match or starting description that will enable NAV to find a match. No vendor is defaulted. The user must choose a vendor manually.

Read More

Disappearing Scrollbar

You may have noticed that in some pages, in the lines section the scrollbar has disappeared and there is no easy way to scroll to the right to see all the columns (see example below):

To make the scrollbar visible, you need to add the “freeze pane” after the first few columns and not towards the end of the list of visible columns. To check that, right-click on any column header and select Choose Columns…

---

 

Brought to You By

 

SCS Senior Training Director, Angie Prikian. Angie has been with SCS
for 9 years and spearheads our training drives for clients,
on-site or remotely.

 

 

 

 

Read More

Export/Import Data File

Do you miss the backup and restore function from the old NAV Classic client? It’s back!!!

With NAV 2015/ 2016, you can now export data/ objects/ companies from one database to a file with extension .navdata and then import it in another database.

Read More

Inventory Vs Non-Inventory Items

With NAV 2015/ 2016, you can now set up Items of type Service (i.e. Non-Inventory). A new option field named “Type” has been added to the Item table (27). The options for that field are Inventory and Service. The default value is Inventory. (Note that this field is not visible by default on the Item Card or List pages).

 

During data conversion, in addition to regular Inventory items, you can now set up Service Items by using Type = Service. A service item is an item that is purchased and/or sold, but has no cost-related postings to the GL. (These items are not to be confused with the service items that are set up under the Service Management module.)

 

A “service” item will be set up with a <blank> Inventory Posting Group. Any inventory related setups (e.g. Costing Method, Item Tracking Code, Item Category etc, will not be applicable and must be left blank)

 

A purchase transaction with a service item on the line will credit Accounts Payable G/L Account and debit the expense account that is mapped to the Purchase Account.

Read More