Category Archives: Microsoft Dynamics GP

.NET development as it relates to the popular Microsoft ERP system called Microsoft Dynamics GP

How to find your GP Company ID

Often times when working with Dynamics GP you will come across something that is asking for a GP company ID.  This can either be an integration application, or a registration program for a cool addin, or a number of other pieces that need to connect to your data.  GP has three ways to identify a company: Company Name, Database Name and Company ID.  All of these luckily can be found in one table in a database.  By selecting out of the SY01500 table we can determine how to find your GP company ID.  Here is an example query:

How to find your GP Company ID

SELECT CMPNYNAM, INTERID, CMPANYID
FROM DYNAMICS.dbo.SY01500

how to find your gp company id

This will return back the Company Name as CMPNYNAM, the Database name as INTERID and the Company ID as CMPANYID.  The company ID will always be an integer, and the test company is usually -1.  The InterID is always a three letter word and potentially a number.  Example: DYN01, DYN02, etc.  The Company Name will be the value that you fill out during setup.  These tables are how to find your GP company ID and they will provide additional information about your company if you want to look further by selecting all columns.

While looking for how to find your GP company ID may seem somewhat difficult in GP it is typically best to look directly at SQL.  GP does not have a great method for looking up company information and you will likely still be asking yourself how to find your GP company ID.  Again, because this information is typically only required for an integraiton or special third party addins it is unlikely that GP will show you how to find your GP company ID anytime soon.

If you are looking for more information on how to find your GP company ID

Check out this excellent SQL posting from Victoria Yudin about how to find your GP company ID in SQL.

What’s New In Microsoft Dynamics GP 12 / GP 2013?

Almost any accounting system on the market has one major issue with it, the human interaction.  Regardless of how automated each system becomes there will always have to be someone who looks at the data and makes changes based on what happens in the real world.  Being that the real world is now involved in your accounting system it is destined to have some kind of error that needs to be corrected.  With the release of Microsoft Dynamics GP 2013 we should expect to see new advanced interaction with error handling and financial recovery.  Here are a few new features of Dynamics GP 2013 that will help keep your financial data secure:

  • Enhanced Reconciliation: At any given point in time there will likely be an account out of balance.  With the new reconciliation system we are going to see a filter based system to show us exactly what needs to be reviewed and when.  Easily setup conditions to review your GL, un-posted and posted batches and any other nitty gritty information that will flaw your perfect system.
  • Mass Fixed Asset Depreciation Reversal: Once your issues are identified it can be a nightmare to roll back your books to a specific point in time to correct it.  Mass Fixed Asset Depreciation Reversal is the process of rolling back your transactions to a point in time all at once instead of having to do it one by one.  In this transactional system your ability to control and correct errors is greatly increased.

Along with the enhanced data security, GP 12 is also moving towards more intuitive warehouse functionality.  Here are some highlights of processes that will be improved with the release of GP 2012:

  • Intuitive Serial/Lot Number Tracking: Drop ship items have always been a sore spot for entering tracking/serial numbers in GP.  When you drop ship an item and you are not tracking inventory you are guessing which item ships.  Seeing this as a flaw, Microsoft has corrected this and provides you the ability to enter what specific item shipped regardless of drop shipping or not.  Kudos, Microsoft!
  • Business Analyzer: A great addition to the already stellar dashboard is the Business Analyzer tool now available on the web!  This tool gives you access to business critical key performance indicators as well as offer you the ability to collaborate and take action all from one interface.

If you have any questions about GP 2013 or would like to talk about upgrades, please don’t hesitate to contact us at Integrated Systems Solutions for more information or call us direct at 714-634-4697 and mention this blog!

Primary Keys in SQL (Guid or Integer?)

Primary keys in SQL have come up quite a few times in my career so far. Let’s start by going back to the beginning with what a key really is and how it is used.

Each concept in programming typically starts with a record or a target. In the case of bank applications this would be an account, or sales would be a customer or an order. Regardless there is always some sort of unique object in coding that needs to be identified, tinkered with and some task completed. For a customer you would call them by their full name, Mike Calvert as an example. Unfortunately there are a lot of people named Mike Calvert in the world, so we need to learn to identify the specific Mike Calvert we are talking to.

From here a key was identified by using some sort of numbering scheme. Dynamics GP uses a limited alphanumeric scheme which you can use to assign a number such as MIKECALVERT01. This works until you run out of letters or you hit the maximum combinations and start coming up with one offs. The next step in the equation was to jump to auto incrementing integers, so Mike Calvert becomes 1, Mike Calvert 2 becomes 2, Jason Mills becomes 3, Howard Roberts becomes 4 etc. Everything was solved in the world because we now had an endless number that works forever. Moving into actual SQL syntax, you would have an issue with connecting up data links. Lets say you had to insert 50,000 records into this customer table, and you had a relationship table that referenced these people.

You now have to insert 50,000 records into the table, and then determine which of the 50,000 got assigned to a customer record because that record ID is generated when you insert it into the database. There are some tips and tricks around it but it is very messy. In a nut shell, if I wanted to find out what the ID was of Mike Calvert after I inserted 50,000 people into the database I would have to do a select to find Mike Calvert, and we run into the same issue we ran into before where there may be more than one Mike Calvert. At this point someone at Microsoft (thankfully) decided to put a Guid into play. Guid’s are by far the best key choice that I have come across as they provide three awesome things that keys need. The first and biggest requirement is being unique. Each Guid is unique no matter what table or database. If I put a customer record into a table with a Guid, I know that I can find that same customer record using that Guid from anywhere, without having to worry if that Guid will show up anywhere. The next big advantage is indexing. Indexing a guid is very efficient as they are the same length of characters, the same types of digits and are always unique. Finally, Guids can be generated prior to insert.

Generating a key prior to insert is a major advantage, as you can wrap up your 50,000 inserts into a single statement as well as the relationship. .NET provides a function called System.Guid.NewGuid that returns back a guid that you can use as a unique identifier in your inserts. Let us say that we generated our customer Mike Calvert and we created his guid. Any additional tables that required a relationship to Mike Calvert would already have this guid prior to inserting Mike Calvert, so I can create Mike Calvert and his sales histories and payment histories and insert it in one transaction instead of making multiple calls to fetch IDs. Fun stuff eh?

Pitfalls and Mistakes to avoid in Dynamics GP 2010

You have just entered in all of your manufacturing items into your system, and close down your GP client.  The next morning you come in and all of your items are being sent to the wrong assembly lines and a work stoppage has occurred.  Yikes!  Check out this document by Mark Polno about the Worst Mistakes You Can Make With Dynamics GP.

HOW TO: Setup Your First GP Company In GP 2010

Dynamics GP is built on a master database/child database structure, where you have a settings database and company databases.  When you install Dynamics, you are given the option to setup your company, or install at a later date.  Follow these steps below to create a new company at any point in time.

  1. Navigate to your Start Menu -> Microsoft Dynamics -> GP 2010 -> GP Utilities
    standard gp login screen
  2. Login as a System Administrator and select Create A Company as your option.  Press “Process”
    create a company option screen
  3. Enter your company specific details in the next screen, making sure to include the DB Name and Company name.
    company details entry screen
  4. Enter any specific SQL locations that you would like on the next screen.
    company gp specific data
  5. Press Finish and your company will be created.