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:
- Import and post PIs for individual vendors
Expense (Dr)
Accounts Payable (Cr)
- Automatically create, post and apply credit memos for the invoices in Step 1
Accounts Payable (Dr)
AMEX GL A/C (Cr)
- Create and post PI for AMEX
AMEX GL A/C (Dr)
Accounts Payable (Cr)
- 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 |
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 |
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. |