Importing Applied Credit Memos from Dynamics GP into Fidesic

Overview

When you pay vendors through Fidesic, you often need credit memos to be reflected against the right invoices. Dynamics GP stores which credit memos were applied to which invoices, but that information isn't exposed in a single out-of-the-box report you can hand to Fidesic.

This article shows you how to:

1. Create a SQL view that lists each applied credit memo alongside the invoice it was applied to.
2. Turn that view into a SmartList you can open any time in GP.
3. Export it to Excel and use Fidesic's Apply Credits import to apply those credits to the matching invoices in Fidesic.

The SQL view step requires someone comfortable working in SQL Server Management Studio (typically a GP administrator or your GP partner). Once the view and SmartList exist, the day-to-day export and import is a simple, repeatable process for your AP team.

---

Why this is needed

In Dynamics GP, a credit document (credit memo or return) is always applied from the credit to an invoice. GP keeps that apply relationship in its payables tables, but the standard payables reports are built around invoices and payments — applied credits don't surface as their own list with the invoice they offset.

The view below is built the other way around: it starts from the credit memo and resolves the invoice each credit was applied to, which is exactly the pairing Fidesic's Apply Credits import needs.

---

Step 1 — Create the SQL view

Run the following in SQL Server Management Studio against your GP company database (the company database, not the system/DYNAMICS database). If you run multiple companies, run it in each company database you want to use this in.

view_Fidesic_Credit_Apply.sql

The grant select ... to DYNGRP line at the end is required — it's what allows GP (and SmartList) to read the view.

---

Step 2 — Create the SmartList

You can use either SmartList Designer (built into GP) or SmartList Builder (the eOne add-on). Use whichever you have.

Using SmartList Designer

1. Open SmartList (Microsoft Dynamics GP ▸ SmartList).
2. Click New to open SmartList Designer.
3. Give the SmartList a Name (for example, "Applied Credits") and choose a Series to file it under (Purchasing is a logical choice).
4. In the Database View list on the left, scroll to Views, expand it, and select view_Fidesic_Credit_Apply. (It may take a moment to load the first time.)
5. Check the columns you want to include, then click OK to create the SmartList.

Using SmartList Builder

1. Go to Microsoft Dynamics GP ▸ Tools ▸ SmartList Builder ▸ Security ▸ SQL Table Security, choose Views at the top, select your company database, and check view_Fidesic_Credit_Apply.
2. Go to Microsoft Dynamics GP ▸ Tools ▸ SmartList Builder ▸ SmartList Builder.
3. Enter a SmartList ID and Name, and choose a Product and Series.
4. Click the + next to Tables, choose SQL Server Table, check Use Company Database, select Views, and pick view_Fidesic_Credit_Apply.
5. Add at least one Key Field (use Credit_Number), then add the columns you want and Save.

---

Step 3 — Match the column names to the import (recommended)

Fidesic's Apply Credits import looks for these column headers:

Fidesic header	Use this SmartList column
Credit Number	Credit_Number
Vendor ID	Vendor_ID
Credit Date	Credit_Date
Credit Amount	Credit_Amount
Document Number	Document_Number (the invoice the credit applies to)

To make the export drop in with no editing, rename each column's Display Name in your SmartList to match the Fidesic header exactly (for example, set the Display Name of Credit_Number to "Credit Number"). The Display Name becomes the header row when you export to Excel.

Extra columns (Vendor Name, Invoice Date, Invoice Amount, etc.) are fine to leave in — the import ignores columns it doesn't need, and they make it easier to eyeball the data before importing.

---

Step 4 — Export to Excel

1. Open your SmartList and let it load the rows.
2. Click the Excel button in the SmartList toolbar. This opens the data in Excel.
3. Save As an .xlsx (or .csv) file somewhere you can find it.

Before importing, remove any rows that aren't applied to an invoice — a credit with a blank Document Number has nothing to apply to and should not be included in the file. You can filter these out in Excel, or restrict your SmartList to applied credits only.

---

Step 5 — Import with Apply Credits

1. In Fidesic, go to Import Files.
2. In Choose a type of file to import, select Apply Credits.
3. Click Choose File, select your saved spreadsheet, and run the import.

The import looks for a sheet named Fidesic; if there isn't one, it uses the first sheet, so a single-sheet export works fine.

Test with a small file first. Before importing a large batch, export just a handful of credits and import those. Open one of the affected invoices in Fidesic afterward and confirm the credit reduced the balance by the expected amount. Once you've confirmed it behaves the way you expect, run the full file with confidence.

---

Notes and tips

• One row per applied invoice. If a single credit memo was applied across several invoices, it appears as several rows — one per invoice. This is correct: each row tells Fidesic how much of the credit to apply to that specific invoice.
• Open, unapplied credits. Credits that haven't been applied to anything in GP will show with a blank Document Number. Leave these out of the Apply Credits file.
• Multiple companies. If you run more than one GP company, create the view in each company database (Step 1). With SmartList Builder's "Use Company Database" option, one SmartList can serve all companies; with SmartList Designer, build the list in each company.
• Need help? Creating the SQL view requires SQL Server access. If you're not comfortable with that step, contact Fidesic support or your GP partner and we can help you get it set up.