GP to BC Migrations: SQL Script to Check for Problematic GP Data Conditions With FAQs!

Hello All!

The very first thing I do when troubleshooting a GP to Business Central Online migration case, is to have users run a SQL Script to find problematic GP data conditions that may result in a failed migration. As such, I would like to share with you the SQL script we in Support have created over time to accomplish that. This is followed by a FAQ section where I'll share our most commonly asked GP to BC Migration Questions and Answers. This will be a living blog, meaning if we find a new problematic data condition and/or see more commonly asked questions, we'll add them to this blog. Enjoy!

To find potential problematic data conditions in Dynamics GP, use the Migration Assessment Tool to better understand how your business can transition to the cloud.

IMPORTANT: If you find you need to make changes to the GP setups and/or data, ALWAYS make those changes in a Test Company with a copy of live data, or in a test environment. Here’s a link to an article that talks about how to make a GP Test company with a copy of live data:

Set up a test company - Dynamics GP | Microsoft Learn

Do not change/fix the data in a GP production company that is still actively used. Why? Some of the data conditions that will need to be addressed are simply setups that must be in place in GP for the migration to be successful. This doesn’t mean the existing setups are ‘wrong’ as far as GP is concerned, and we don’t want the changes you make to detrimentally affect processing in GP. Here are a couple of examples:

• All Default Posting Accounts must be in place for the Financial, Sales, Purchasing, and Inventory series in GP (Microsoft Dynamics GP >> Tools >> Setup >> Posting >> Posting Accounts). *GP does not require these accounts to exist. However, to migrate the data successfully – they must exist. There may be valid business reasons for users not having these accounts in place in production and assigning them has the potential to change which account defaults into various transactions in GP.
• All accounts must have the ‘Allow Account Entry’ box marked (Cards >> Financial >> Account). *This is not an invalid setting in GP, but BC requires this box to be marked. There are valid business reasons for users ‘not’ to accounts assigned to ‘Allow Account Entry’ in production and changing that will allow users to manually key the account onto transactions.

The script may return results associated with the bolded sections below. Read the details for each to know how best to proceed:

• There is only one data condition in this script for which one result must be returned, and that is the script to verify there is a ‘main’ account segment defined:

• Unbalanced or Duplicate Journal Entries: This is a damaged data condition in GP that needs to be fixed before you migrate. If you are unsure how to fix these, you should open a case with the Support Team for assistance. 
• Missing default posting accounts: Valid accounts must be assigned via Microsoft Dynamics GP >> Tools >> Setup >> Posting >> Posting Accounts for all accounts in the Financial, Sales, Purchasing, and Inventory series. Failure to do this will likely result in a failed migration and unbalanced journals in the BC company being migrated 'into'.
• Blank Account Segments: Each unique Account Segment in GP becomes a ‘Dimension’ in Business Central, and Business Central cannot create a ‘blank’ dimension. As such, having a blank segment will cause the migration to fail. Use the Account Modifier PSTL (Professional Services Tools Library) in GP to modify the account to ensure no segment is blank.
• Invalid Period Dates in Fiscal Period Setup: All records with invalid Period Dates must be fixed prior to migrating (or removed in a test company with a copy of live data).
• Incompatible Payment Terms: Fix or remove the problem payment terms from GP via Microsoft Dynamics GP >> Tools >> Setup >> Company >> Payment Terms. Incompatible payment terms will cause the migration to fail.
• Inventoried Items with more than 20 Characters: BC allows up to 20 characters for the Item No., and GP allows for more than that. As such, BC truncates Item No. at 20 characters. If the first 20 characters of two or more items is the ‘same’ in GP, they are considered duplicates in BC (which is not allowed). This will cause the migration to fail. As such, use PSTL in GP to change item numbers appropriately to avoid duplicates during the migration.
• Letters in Customer or Vendor Phone Numbers: Business Central does not allow letters in phone number fields. All letters must be removed from all phone number fields prior to migrating or the migration may fail.
• More than 2 Decimal Places on Transactions: This is not a complete deal breaker and will not stop the amounts from migrating to BC. However, it will stop the auto-posting process (due to rounding to 2 decimals) and likely result in unbalanced batches (off by penny(ies)) in BC that must be fixed before they can be posted (and result in a Failed Status in the Cloud Migration management window). Things to think about: Do you ‘use’ more than 2 decimal places in GP? If you aren’t sure, check the currency setups (Microsoft Dynamics GP >> Tools >> Setup >> System >> Currency). Are any of the currencies set to use more than 2 decimals? Are you planning to use more than 2 decimal places for currencies in Business Central? If you DO plan to use more than 2 decimals in BC, then change the decimal place settings in General Ledger Setup for the company you’re migrating ‘into’ after the shell company has been created, and before you click on ‘Run Migration Now’ to ensure the company will allow for the extra decimals.
• Inactive Item on Open Purchase Order: Remove the inactive item from the open purchase order in GP prior to migrating. Neither ERP allows you to add an inactive item to purchase order.
• Open Purchase Order in Originating Currency: These purchase orders need to be deleted or closed in GP prior to migrating. There is a known quality issue around this that will cause the migration to fail.
• Missing Accounts on Customer, Vendor, and/or Inventory Classes: If you are migrating classes, all posting accounts associated with each class must be populated prior to migration.

Frequently Asked Questions - Migrating from GP to BC:

Question 1: What exactly will the Cloud Migration Tool migrate from GP to Business Central Online?

Answer 1: Here’s a link to documentation that describes exactly what the Cloud Migration Tool will move from GP to Business Central Online: https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/migrate-dynamics-gp#dynamics-gp-data

We are always in the process of adding to what the Cloud Migration Tool can do. The documentation above gets updated every time a new feature is added. As such, continue to check periodically to verify what has changed.

If there is a module and/or GP data you’d like to see added to the tool, please enter a Product Suggestion for that here: Ideas (dynamics.com)

Question 2: Which GP data should I migrate from GP to Business Central Online?

Answer 2: The answer to this varies from business to business. Think about which modules you use on a regular basis. These are likely the modules you’ll want to migrate from GP to BC Online.

Think about whether the data in those modules are in good standing (this is where the script comes into play):

• Prior to running the script, we recommend you take steps you’d normally take in GP to ensure data integrity such as running Check Links on the series in question and/or Reconciling amounts associated with the series in question.
• If there are transactions sitting in WORK that you want migrated to BC, post them in GP first. Unposted transactions won’t be migrated.
• Are data results returned for this module when you run the script associated with this blog? If so, the problem data should be fixed prior to migrating.

Think about how much data is coming into play, along with what processes you use and don’t use when determining what to migrate. Here are some examples:

• If you use Bank Reconciliation in GP but do not reconcile your bank accounts (checkbooks) in GP, then it might not make sense to migrate all outstanding unreconciled Bank Transactions. A better option may be to migrate only Bank Account Master data. That way, all checkbooks are still migrated, but you don’t have a ton of unwanted outstanding transactions taking up unneeded space waiting to be reconciled Business Central.
• If you are migrating Items and have a lot of history, consider removing sold receipt layers prior to migrating.

Question 3: I am not comfortable with SQL. Is there another way for me to find potentially problematic GP data conditions prior to migrating?

Answer 3: Yes, there is a free Migration Assessment Tool offered here: Migration assessment (bcmigrationassessments.com)

Question 4: Where can I learn more about how to accomplish my normal GP processes in Business Central Online?

Answer 4: Here is a link to some great videos that compare and contrast the most common processes between GP and Business Central Online: 

Compare Work in Dynamics GP to Business Central (contains videos) - Business Central | Microsoft Learn

Question 5: My Retained Earnings and Profit and Loss account balances don't match what I see in GP.  Why and how can I fix this?

Answer 5: All Fiscal Periods (Accounting Periods in BC) are migrated as OPEN years in Business Central. As such, all migrated General Ledger transactions are posted to an 'Open' year in Business Central, even when the corresponding year in GP is closed (or historical). After migrating from GP, in Business Central users must close all appropriate Fiscal Years (Process >> Close Year from the Accounting Periods page) and process the 'Close Income Statement' for those years to match what's already been done in GP. Once this is done, the Retained Earnings and Profit and Loss Account balances should match between the two ERP's.

Question 6: Is it possible to migrate multiple GP companies into one company in Business Central Online?

Answer 6: No, it is not possible to migrate multiple GP companies into a single BC company. Upon completion of the Cloud Migration Wizard (where you choose which companies you want to migrate): Business Central creates a 'shell' company(ies) with the same company name in Business Central. It is this new shell company that the data for the corresponding GP company will migrate 'into'.

Question 7: Are there translation options for GP Segments that become Dimensions in Business Central? For example, we'd like the ability to combine multiple GP Segments into one BC Dimension, and/or we'd like to rename a GP Segment to have a different Dimension value in BC.

Answer 7: No, the Cloud Migration Tool does not include functionality to combine multiple GP Segments into one BC Dimension. Nor does the tool allow users to map GP Segment XX to be called something different in Business Central. The Cloud Migration Tool will do the following when it comes to GP Segments:

• The 'Main' Segment in GP will become the Account Number in Business Central.
• Users have the ability to assign two Global Dimensions in Business Central (via the GP Company Migration Configuration window) to correspond to additional existing GP Segments (but users cannot rename them to be called something else).
• If there are more than 3 Segments in GP: Business Central will create Dimensions in to represent the remaining GP Segments.

Users may want to consider using PSTL in GP to make desired Chart of Account changes prior to migrating to Business Central. 

I hope you find this information helpful! Don't forget to check out our Landing Page for GP to BC Migrations!!