How to Copy and Paste into Microsoft Dynamics GP

Advtrex’s Titanium GP brings the ease of copying and pasting into Microsoft Dynamics GP.

Imagine a user wanted to copy and paste 50 lines of data from an Excel worksheet into a General Journal Entry in Dynamics GP. Normally, this would require using Microsoft importing tools that take a heavy amount of time depending on the size of the data files. With Titanium GP, a user can easily and immediately copy and paste import detail lines.

1. Highlight data in your computer file
2. Right click to copy or hold down “CTRL+C”
3. In GP, hit “Paste”.

Voila! The data is immediately pasted from Excel or Word into GP.
To make the process even faster, use keyboard shortcuts to copy/paste:

• To paste in Dynamics GP, hit “CTRL+T”
• To view in Grid form that allows the user to copy lines, hit “CTRL+G”

Pictured below is the grid view (CTRL+G).

How to Copy and Paste into Microsoft Dynamics GP 2013

When you copy/paste from Excel or Word to GP, the order of the columns is flexible.  As long as the columns titles match the respective fields in the GP window, the function will recognize the proper fields to copy/paste data.

Notice below that the Debit, Credit and Reference columns have been switched.

Titanium GP

Nonetheless, we are still able to successfully paste the data into the GP window.

Additionally, this tool allows copying and pasting from GP into an Excel or Word document. To save a GP document (or export it) to Excel, open a document in GP and use the menu command to “Save to Excel.” You will then be prompted to indicate the path and name to save the file.

The COPY/PASTE functionality can be used with the following windows:

• Journal Entries

• SOP Entry

• Purchase Orders

• Receivables Transaction

• Inventory Transaction

• Inventory Transfer

• Inventory Kits

Contact us at Tridea Partners to learn more about this tool.

Consistency Check Tool in Dynamics AX 2012

Tables in Dynamics AX 2012 are divided into different categories: Main tables such as Customers, Ledger Accounts, Vendors, Items, etc, Transaction headers such as Sales headers, Purchase order headers, etc, and transaction details such as Sales lines, PO lines, etc. These tables are all related to each other through keys. For example when a sales order is created for customer A123, the customer, sales header and sales line are all linked to each other.

Occasionally one or more of these transaction records can be abandoned, meaning the parent data was deleted but the transaction still exists. We call these orphans because they exist in the database but are not connected to any other record. While these orphans by themselves cause no problem, they take up space in the database and given enough time, a lot of these orphaned records can cause performance issues.

Dynamics AX 2012 has a tool to spot and remove these orphans. It’s called the Consistency Check tool. It basically runs through the entire database and locates transaction records that do not have any parent record associated with them. The Consistency check tool is located under System Administration > Periodic > Database > Consistency Check

AX Consistency check

When running this tool, you will first be presented with a series of options such as which modules you would like to check for orphans in, what to do with orphans found (check or fix) and a starting date to perform the search on (if you run this tool every 90 days you can select a day just 90 days back in order to speed up the search process).

Dynamics AX Consistency check

The tool will take an hour or more to run, be sure to run this after hours.

Dynamics AX

AX does a great job of keeping track of transaction data and removing automatically when any parent data is removed, but, as with any ERP, different scenarios may come up in which records are left orphaned. Using the Consistency Check tool on a regular basis is a great way to clear these records and keep your system running smoothly.

Can you recover a deleted record in Dynamics CRM 2011?

Unfortunately, the answer is no, unless you restore the database from a backup which is not really practical for a single deleted record.  However, you may be able to recover some data if you have auditing turned on and follow the steps in this Microsoft blog below.

You can also use a free tool to recover data (again if auditing is turned on).

If record deletion is a problem:  One solution is to remove “delete” privileges from most users and encourage users to deactivate records.  This way the record can still be reactivated.  If space is an issue, you can create Bulk deletion jobs that delete inactive/deactivated records every X days.

View relationship between Sub-Ledger and GL in Dynamics GP

How many times do you wish that there was report that would show you the link between a sub-ledger and the GL? Here is a little trick that will help you see some of this sub-ledger information through a GL Smartlist. It can especially come in handy when reconciling your Accrued Purchases account as there is not a routine to help you through this process.

Here are the steps:

Go to Microsoft Dynamics GP > Smartlist. Expand the Financial Folder and then Expand the Account Transactions folder.

Click on the default Smartlist indicated by an asterisk.

Sub-Ledger and General Ledger Smartlist

Click on the Columns button and search for the word Originating. This will bring you to fields such as Originating Master ID, Originating Master Name, Originating Document Number and so on.

These will be the originating Master Record and document information that generated each Journal Entry. The Originating Master ID will be either the Customer ID or Vendor ID depending on which module the Journal Entry originated in. When reconciling your Accrued Purchases account for instance, you can restrict the list to the Accrued Purchases GL Account and then export this data to Excel.

Then take your Credits minus your Debits to get the net changes and subtotal by Originating Master ID to get your Vendor received not invoiced totals.

This list can then be compared to the Rec’d/Not Invoiced Report subtotaled by Vendor. By doing this, you will easily be able to determine what vendor or document is causing issues and more easily resolve the discrepancy.

Sub-Ledger and GL in GP