Dynamics GP Macros to Import Data without Integration Manager

There are a number of ways to import data into Dynamics GP. Most commonly known are Integration Manager and eConnect. Another, less-known method, is the ability to upload data by creating a macro. For a day-to-day GP user, without Integration Manager, a macro can often be a good option. Below are the few simple steps needed to create a Dynamics GP macro. In this example, we will be updating shipping method for a group of vendors that are not in the same vendor class.
 
The first thing we’ll want to do is create a source file in Excel format. At minimum, we will need two columns: “Vendor ID” and “Shipping Method.” Source File in Excel
Then we will record a macro. A Dynamics GP macro can be initiated from virtually any GP window. From within the desired window, select: Tools > Macro > Record (or press Alt +F8.)
Dynamics GP Macros to Import Data 
You will be prompted to choose the location where you’d like to save the macro. Choose the desktop or any other location that you can easily access. As soon as you click “Save,” the macro will begin recording.
Location to Save Macro  
For our example, we will be in the “Vendor Maintenance” card. We will enter a Vendor ID and use the mouse to select the “Shipping Method” field. Enter the desired Shipping Method and click “Save.”
Vendor Maintenance Card
 
Stop recording the Dynamics GP macro, by navigating to: Tools > Macro > Stop Record (or press Alt + F8.) Navigate to the location where the macro was saved, right-click, and choose to open with Word. (If Word is not an option, you can also start by opening word, choose to “Open” a file, look for all file types, and choose the macro.)  Macro in Word
From within Word, click on the “Mailings” tab and choose “Start Mail Merge” and “Step by Step Mail Merge Wizard.” Step By Step Mail Merge Wizard  Select “Next: Starting Document”

 Macro in Word

Choose “Select Recipients” and “Browse”         

 Macro Data Import

Mail Merge- Browse

Select the Excel spreadsheet with the data we will import

 Select Excel Spreedsheet with the data

Validate the tab in the spreadsheet with the data and make sure the checkbox for “First row of data contains column headers” is marked.

Validate the Spreadsheet

 The data from the Excel file should appear. Click OK.

Dynamics GP Macros to Import Data

Click “Insert Merge Field” button and override the “Vendor ID” and “Shipping Method.” Make sure not to override the quotation marks. It should look like this:

 Insert Merge Fields

Click “Finish and Merge” and “Edit Individual Letters.” Select “All” and click “OK.”

Finish and Merge

 Merge “All” and click “OK”

Merge All

 Save the document as a “plain text” document and click “OK” when “File Conversion” window appears

 File Conversion

File Conversion

Locate the text file you just saved. You will want to see the .txt extension at the end of the file. (If you don’t see it, click the “Start” button on your computer, click “Documents” and “Organize.” Click on “Folder and search options,” click on “View” tab, and unmark the box for “Hide extensions for known file types.”) Right-click on the text file and choose “Rename.” Change the extension from .txt to .mac

Change extention from .txt to .mac

Open the vendor card, click Alt + F8, and choose the macro to play. Let the macro play to completion. Do NOT do anything else on your computer while the macro is running or it will interrupt the process.

 This article was written by Zandra Montes, Dynamics GP Application Consultant for Tridea Partners. Tridea is a leading Microsoft Dynamics GP provider.

Dynamics ERP for Craft Beer Production

Craft brewers have a number of system requirements that are typical of many manufacturers looking to deploy an ERP system: material planning, cradle to grave lot traceability, bar-codes for materials handling, production scheduling, etc. However, there are many business processes that are very unique to this burgeoning industry. As breweries begin to grow in size and sophistication, they encounter an increasing need to start managing and controlling their unique processes within the ERP system, as opposed to relying on manual and home-grown systems.

With eight years of experience working in this arena, Tridea has partnered with our clients to solve a number of these unique challenges by utilizing tools available within the Dynamics ERP software systems.  Below are a few examples of requirements that craft brewers may face, along with the solutions to those challenges. 

Tracking Barrel Aged Beer

Small batches of special beer creations are often aged in barrels, which may be stored in a warehouse for extended periods of time and even transferred to different locations prior to being packaged and made available for sale. As the size of a “barrel program” grows, it can be challenging to keep track of:

1) What beer is currently stored in which types of barrels?

2) Where the barrels are located?

3) What types of beer have passed through a given barrel in the past? (thus establishing a “flavor profile”)

With Dynamics AX, we have used the concept of “License Plate Numbers” (LPNs) to assign a unique ID to each of these barrels. We can then track the beer that is assigned to an LPN, track the location and movements of the LPN, and also see a history of how many times a barrel has been used and what types of beers were stored in the barrel.

 

Blending of Batches in a bright tank (“brite tank”)

Bright tanks are where clarified beer is stored after it has been filtered and before it gets bottled or placed in a keg / cask.  It is not uncommon for a quantity of beer from one production batch to remain in a bright tank when a new production batch is added to the tank. As a result, two separate batches of beer that would ordinarily be tracked with two distinct lot numbers, have now effectively become blended into a single “combined” batch, which is then packaged and sold to customers. We have developed an automated routine to merge these batch numbers to reflect the blending of the beer in the bright tank, allowing for a more accurate and true lot traceability.

 

Batch Processing Resulting in the Production of Both Filtered Beer and Unfiltered Beer

In some cases, during the course of producing a filtered beer, a quantity of unfiltered beer is taken out of the tank prior to filtering. Sometimes, this might even be a last-minute decision that is made after the production order has been started. This unfiltered beer might then be barrel-aged or have special ingredients added to it in order to produce a batch of a special limited offering product. Because the process manufacturing functionality within Dynamics AX allows for the creation of co-products on a single production order, we can easily use one production order to produce both the unfiltered and filtered beer, and accurately track them as two distinct products.

 

Integration to Brewing Systems

Because brewers need to swiftly make decisions about which brew systems, fermentation vessels, and bright tanks to use, it is important for them to interact directly with the ERP system if we are to maintain timely and accurate information about the production process. Leveraging MS Dynamics’ strong integration tools, we have been able to build integration points that can take direct feeds from the brewing system (example: quantities of malt that flow into the brew system from a silo can feed directly to the ERP production order, quantities of filtered beer flowing through a filter outlet into a bright tank are fed directly to the production order). We have also built touch-points allowing for brewers to manually record transactions in the brewery floor systems that then get integrated to the ERP system.

 

This article was written by Matthew Boese, Partner at Tridea Partners, a Gold Certified Microsoft Dynamics Partner serving Southern California.

Connector Error Between GP Sales Invoice to CRM Invoice

During a Dynamics GP Sales Invoice to Dynamics CRM Invoice sync using the Connector for Microsoft Dynamics, it failed on a number of records with the error “A record level exception has occurred for record 00011111. Error text: Business object not found”. The error details mention checking the TaxScheduleKey, the order/line price list or the customer’s billing address.

Connector Error Between GP Sales Invoice to CRM Invoice

While trying to re-sync the addresses, they failed stating it was unable to retrieve the customer address with a certain id. I cleared the More Addresses from the CRM Account and tried clearing out the Address 1 and Address 2 information in order to have a fresh sync. But when trying to clear out the Address Integration key fields, CRM failed saving the record.

In order to get around this problem, you can create a new Account and merge it with the problem Account. Select the newly created account as the master and then select all the fields from the old record except the address information.

Microsoft Dynamics Connector Error

With the new Account, you can first run a Connector integration to add the Address Integration keys and then a full Customer to Account sync.

With the addresses correct, the invoices successfully sync to CRM.

Written by Derek Finlinson, Dynamics CRM Technical Consultant for Tridea Partners. Tridea Partners is a Southern California based Dynamics CRM partner.

How to Address a Functionality Gap in Dynamics GP

When you have a functionality gap between what Microsoft Dynamics GP offers and what you need the software to do, you will need to pick one of three ways to address the issue. The first is to adjust your procedures such that standard Dynamics GP functionality can address the requirement. This “work-around” approach is the least amount of out of pocket money upfront, and is a good approach as long as it doesn’t cause procedural in-efficiencies that can come at a price tag over time. The second option is to explore having a customization written to address the functional gap. Dynamics GP Customizations can be pricey but are justified if the added functionality is essential to the software ultimately meeting the company’s needs.  The third option for addressing a functionality gap is to look at what are called 3rd party products. These products are designed by independent firms, and exist specifically to enhance the Dynamics GP offering. 3rd party products most of the time will have the same look and feel as your version of GP, are upgraded in line with Microsoft’s scheduled upgrades of GP, and are usually priced affordably.

On a recent project we were faced with 2 functional gaps that we addressed with 2 different 3rd party products. The customer wanted to account for all 20 of their companies in one Dynamics GP database. Putting the companies under one database allows them to share vendor and customer accounts, centralize cash disbursements, and stay in one database file instead of constantly logging in and out of the different databases all day long. One requirement they had was the need to book transactions between companies that would result in automatic intercompany postings. This was a challenge with all companies being in one database. Dynamics GP has a module called Interfund that came close to meeting their needs, but ultimately fell short. We ended up deploying Nolan Business Solutions 3rd party product called Intercompany Postings. It comes with functionality that addresses what they call “Intra-company” postings when the companies are in the same database. This module gave our customer the functionality they needed to get the correct intercompany entries booked automatically.

The second challenge we faced was that their customers could buy from the different companies in Dynamics GP, and they wanted GP to book all debits and credits for the sale to that locations general ledger accounts (sales, accounts receivable, inventory, and cost of sales). Setting up Inventory Sites for each company, and assigning the appropriate company code to the site, gave them all the right general ledger postings for sales, cost of sales, and inventory, but not accounts receivable. The accounts receivable account didn’t default to the correct company code. We deployed a 3rd party product from KTL Solutions called SOP/POP Advanced Distribution that allowed us to default the correct company code to the accounts receivable account.

The products from Nolan and KTL Solutions mentioned above were reasonably priced, easy to deploy, and met our customer’s business requirements.

 

This post was written by Andrew Warner, Solutions Consultant at Tridea Partners, a CA, UT, CO Dynamics GP Partner 

System Considerations Before Your Life Sciences Company Goes Commercial

There is no question there are many system considerations when it comes to preparing your company to go commercial – each of which will depend on the business model you have and what business processes will be maintained in-house versus what is outsourced.  Such considerations might depend on whether manufacturing and fulfillment would be internal or through both a contract manufacturer and 3PL.  Other considerations include the following:

  • System of Record: Do you want your ERP software system to be the ‘system of record’ where you’ll be able to defend full product traceability in this system for FDA purposes.  Or, will this traceabilty be paper-based?
  • 3PL Integration / Contract Manufacturer Integration: Is your 3rd party logistics provider able to provide data integrations communications of product that was shipped.  How about providing your manufacturer with visibility into sales demand, and you with visibility into finished product or product costing?
  • Serial / Lot Controlled Items: Unless you’ve passed off all inventory responsibility to a contract manufacturing and 3PL, you’ll need full product lot or serial traceability and a means for managing this added element of effort.
  • CRM Requirements: Do you have plans for a CRM system, or the ability for the sales team to easily manage leads, opportunities and prospective client communications and follow ups so that new business doesn’t fall through the cracks?
  • Quality Management: Once sales of product occurs, it important to have strict controls over the inbound product quality and quality control through the manufacturing process.  Only specialized systems can manage these quality considerations.
  • Material Planning: Making sure the right products are available for the forecasted sales activity can become very important for some companies, which means they need to put an MRP and Sales Forecasting system in place to balance supply and demand.
  • Online Ordering / eCommerce: Today’s customer requires much more flexibility into how and when orders are placed for your product in the market.  However, online ordering comes with many complex challenges especially when dealing with FDA controlled products and operations. 
  • Expense Management: Once there is a sales team selling product, there are regulations in place for how they communicate with potential clients and how they can spend money on them.  One of those requirements is outlined in the ‘Sunshine Act’.  Make sure the systems help you managed towards these regulations.

 

If you would like to discuss these system considerations in more detail, or would like an assessment done of your current system, please contact Tridea Partners: sales@trideapartners.com.

This post was written by Andy Collins, Partner at Tridea Partners. Tridea Partners is a leading consulting provider of Microsoft Dynamics AX, Dynamics GP, and Microsoft Dynamics CRM software applications and professional services for the life sciences and health care industry.