This presentation is the property of its rightful owner.
Sponsored Links
1 / 80

Welcome! PowerPoint PPT Presentation

  • Uploaded on
  • Presentation posted in: General

Welcome!. Microsoft Dynamics GP User Group (GPUG ® ) DENVER, CO Microsoft Technology Center Regional Chapter Meeting FEBRUARY 26, 2013. Regional Chapters. Agenda. The Importance of GPUG Regional Chapters. Video recording by Pam Misialek of Microsoft. Name Company Role in Company

Download Presentation


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript



Microsoft Dynamics GP User Group (GPUG®)


Microsoft Technology CenterRegional Chapter MeetingFEBRUARY 26, 2013

Regional Chapters



Video recording by pam misialek of microsoft

The Importance of GPUG Regional Chapters

Video recording by Pam Misialek of Microsoft

Welcome introductions



Role in Company

How long have you personally used GP?

Favorite Place to Find GP Answers?

Welcome & Introductions

Who is gpug

Who is GPUG

  • Founded in 2007

  • Officially recognized by Microsoft

  • The largest, independent group for users of Microsoft Dynamics GP

  • 3,700+ company members


“Membership in GPUG provides a direct line to the most comprehensive and experienced group of Dynamics GP professionals available .”

  • Jim Noblitt, Director of Application ServicesL-1 Identity Solutions

Gpug membership

GPUG Membership

Basic Membership Includes:

  • eNewsletter

  • Level 100 Webinars

  • Paid access to Regional Chapter Meetings & Events

  • Partner Showcase Webinars

  • Academy Learning

  • Share My Code

Premium Membership Includes:

  • Basic level benefits, PLUS:

  • Unlimited Workshops, Webinars & Roundtables

  • FREE access to Regional Chapter Meetings & Events

  • Special Interest Groups

  • Microsoft Conduit Meetings

  • Discounts on Summit (annual conf)

  • Free & Discounted Academy Courses

A single GPUG Membership includes benefits for your entire organization.


Member showcase dairy specialists

Member Showcase – Dairy Specialists

Who we are what we do


Dairy Specialists is what is known as a Dairy Services Company. We have four main departments that work together for the Installation, Design, Service, Supply Delivery of over 70% of the Dairies in Northern Colorado, Wyoming, Western Nebraska, and the Western Slope of Colorado.

History of dairy specialists

Founded in 1970 (Sorensen Surge).

1977-Installed the first fully automated milking system in Colorado. (Hertzke – Greeley)

1989-First fully loaded Double-24 parlor in Colorado. (Duo Dairy – Loveland)

1991-Merged with Progressive Dairy Systems to become Dairy Specialists.

1994-First “Subway” Milking Parlor. (Chapin – Weldona, CO.)

History of Dairy specialists

History of dairy specialists1

1997- Expansion into Western Nebraska

1998- Expansion onto Western Slope

2007- Built the largest single parlor in the World to date. A double 80.

2012- Completed a revolutionary 80 Stall Rotary in Kersey, Colorado

2013- Adding IT Services to the list of services we provide to Dairies.

History of Dairy specialists

Dynamics and dairy specialists

We currently have 49 employees spanning in two main offices. Administrative, Sales, Design, Install, Service, and Routes.

Great Plains users since November ‘99.

20 user license.

Current version GP 2010 R2 w/SQL Server Standard 2008.

Dynamics and dairy specialists

Modules utilized

Integration Manager (Distribution, Financials)

Payroll/Direct Deposit


Bank Rec

Inventory Control

Payables Mgmt.

Receivables Mgmt.

Smart List Builder

Purchase Order Processing

SOP with Advanced Invoicing

Modules utilized

Partners and isv s

Partners and isv’s

Favorite modules why

Inventory Module/Process Improvement

SSRS Reports

Excel Refreshable Reports

Smart List

Favorite modules/why?


We have somewhat of a manufacturing process in place and normal human error that throws our Inventory value out of balance with GL.

To figure out where we are out of balance I use a combination of Smart Lists, Reports, and Excel to fix. Let me demonstrate.


Check balances


Check balances

Check balances1

Reports-Inventory—Activity-Historical IV Trial Balance

Check balances

Check balances2

Reports-Inventory—Activity-Historical IV Trial Balance

Check balances

Generate smartlists and export

  • Under Financial, Account Transactions. Build one called DailyIVTransactions

    -Key fields Journal Entry , TRX Date, Credit Amount, Debit Amount. Parameters are Account Number and TRX Date.

  • Under Inventory, Inventory Trial Balance Detail. Build one called DailyReportBasedonGLPostDate.

    -Key fields Journal Entry Item Number, Extended Cost . Parameters is the GL Posting Date.


Pivot table fun once in spreadsheet

On Daily IV Transactions Add ‘Transaction Amount’ Field. (=Debit Amount-Credit Amount)

Pivot Table with Journal Entry as Row Label and Sum of Transaction Amount as Value.

Pivot table fun once in spreadsheet

Pivot table fun once in spreadsheet1

On HITB Spreadsheet Pivot Table with Journal Entry as Row Label and Sum of Extended Cost as Value.

Pivot table fun once in spreadsheet

Make a reconcile sheet

Copy and paste both columns from the Daily IV Transactions Pivot and “Sum of Extended” Cost from the HITB Spreadsheet. Then make a column to calculate any differences you may have.

Make a reconcile sheet

Where do i turn for answers






Where do I turn for answers?




Member Showcase

Regional Chapters

Who we are and what we do

Global provider of metro optical access solutions. Our products enable carriers and enterprises to cost effectively add capacity to their existing optical networks, so that they deliver the high-bandwidth applications and services that their customers demand without investing in additional fiber cabling.

Corporate office is in Centennial Colorado – Executives, GSS, Accounting, and Order Management

Research and Development , Manufacturing, Distribution, and Purchasing is in Fremont California

Subsidiary in Stuttgart Germany – Sales and GSS

Sales presence throughout US and office in the UK

45 employees worldwide.

Who We Are and What We Do


  • 2004 - Original Sorrento Networks was acquired by Zhone Technologies.

  • 2008 - Zhone Technologies sold optical assets acquired from the original Sorrento Networks and a new Sorrento Networks was created in January.


Implementation of gp

  • Used Oracle for Manufacturing, Purchasing, and Sales Order Processing in California.

  • Used QuickBooks for Accounting in Colorado.

  • Decision to implement GP

    • Convert all data to GP – legacy data from Oracle would not be available.

  • Citrix

    • No local installations

    • 11 users – 1 Germany, 6 Colorado, 4 California

Implementation of GP

Implementation of gp1

  • Began using GP 10 May 2008

    • Sales, Purchasing, and Financial

  • Field Service June 2008

    • RMA and Contract Administration

  • Completed Implementation July 2008

    • Manufacturing

  • Upgraded to GP 2010 R2 May 2011

    • Installed Collection Management

    • Installed Expense/Revenue Deferral

    • Extender

  • Planning to upgrade to GP 2013 mid to late 2013

Implementation of GP


Multicurrency Management

General Ledger

Sales Order Processing

Receivables Management

Purchase Order Processing

Payables Management


Inventory Control

Returns Management (RMA)

Contract Administration

Expense/Revenue Deferral

Bank reconciliation


SmartList Builder


Collections Management

Professional Services Tools Library


Partner isv customizations

  • Partner

    • McGladrey

  • ISV

    • Avalara – AvaTax

      • Sales Tax calculation and reports

  • Customizations – minimal

    • Duplicate PO number in SOP

    • Email notifications

    • Tracking and charges from UPS to SOP


My role

  • Staff Accountant

    • Accounts Receivable

    • Accounts Payable

    • Month End – reconciliations and reporting

  • System Administrator

    • Setting up new users

    • Setting up Reports

    • Training/Help

    • Research

  • Server Related issues – IT or Partner

My Role

Mail merge macro

  • Needed to transfer large quantity of inventory items between locations

  • Tried Table Import

  • Posted to CustomerSource for help

    • Directed to David Musgrave’s Blog article

      • How to Use Word Mail Merge and Macros to Import Data

        • Uses example of entering in Prospects

Mail Merge Macro

Mail merge macro1

  • Technical knowledge of tables is not needed

  • Provides ability to enter or update large number of records quickly and without data entry mistakes

  • Records need to be identical

    • Will not work if unexpected dialog box appears or if different actions are required

Mail Merge Macro

Mail merge macro2

Set up an Excel file with records to be updated or entered.

Create a template macro

Create a Word Mail Merge document

Run the Macro in GP

Mail Merge Macro

Mail merge macro3

  • Example – updating Salesperson ID

    • Use SmartList to export data

    • Insert column for new Salesperson ID

    • Save to use in Mail Merge

Mail Merge Macro

Mail merge macro4

  • Create Macro Template

    • Open Customer Maintenance Window – Cards>Sales>Customers

    • Tools>Macro>Record

Mail Merge Macro

Mail merge macro5

  • Change the path if needed (Save in)

  • Name the macro template and Save

Mail Merge Macro

Mail merge macro6

  • Enter first line of data as normal and Save.

  • Tools>Macro>Stop Record

    • Delete data entered from Excel spreadsheet if needed.

Mail Merge Macro

Mail merge macro7

  • Launch Word

  • Open Macro template – may need to change Files of type to “All Files” and select Open

Mail Merge Macro

Mail merge macro8

Change CheckActiveWin to ActivateWindow

Mail Merge Macro

Mail merge macro9

Use the Step by Step Mail Merge Wizard

Mail Merge Macro

Mail merge macro10

  • Select the document type as Letters and click Next: Starting Document

  • Select the starting document as Use the current document and click Next: Select Recipients.

  • Select recipients as Use an existing list and click Browse and locate your data file.

    • Check that Word has read your data file correctly on the Mail Merge Recipients window, click OK.

  • Click Next: Write your Letter to get to the Write your letter stage.

Mail Merge Macro

Mail merge macro11

Look for the TypeTo Macro commands and replace the text between the single quotes with merge fields. Use Insert Merge Field from the Office Ribbon.

Mail Merge Macro

Mail merge macro12

Look for the TypeTo Macro commands and replace the text between the single quotes with merge fields. Use Insert Merge Field from the Office Ribbon.

Mail Merge Macro

Mail merge macro13

  • Click Next: Preview your Letters

  • Click Next: Complete the merge.

  • Click Edit individual letters….

    • Merge to New Document Window

      • All and Click OK

Mail Merge Macro

Mail merge macro14

Word has created new multipage document

Save As

Give name to document and location (Save in)

Change Save As Type to Plain Text

Mail Merge Macro

Mail merge macro15

Click Save

File Conversion window will open

Text encoding: Windows


Mail Merge Macro

Mail merge macro16

  • Close the template and the new .txt document

  • Use File Explorer to change the extension from .txt to .mac

    • Right click, Rename, type over .txt to change to .mac

  • Click Yes for whether you are sure you want to rename the file

Mail Merge Macro

Mail merge macro17

Open Customer Maintenance window in GP

Set cursor in first field


Mail Merge Macro

Mail merge macro18

Select macro

Click Open

Mail Merge Macro

Mail merge macro19

Macro is now running – you will see records being pulled up.

Once macro is finished, dialog box appears with run time.

Mail Merge Macro

Mail merge macro20

  • Scrolling windows are slightly different.

    • Record macro from record where window starts to scroll.

  • May need to segregate data if some records are different – decimal places

  • Cannot leave GP window while running

  • Used for:

    • Setting up and updating Inventory Items

    • Setting up BOMs and Item Engineering Data

    • Assigning and updating Standard Costs

    • Assigning and updating Vendor Item Numbers

    • Updating Salesperson ids

    • Assigning and Unassigning Site ids from Item Numbers

    • Updating Return Costs

    • Inventory Transfers between site ids and Bin Transfers

Mail Merge Macro


Word Up!

Presented by

Patty Taylor


Authored by Frank Hamelly, Microsoft MVP

Regional Chapters


GP 2010 allows you to quickly and easily e-mail single documents, batches of documents, or multiple documents from a list.

Some of the supported documents that can be used and modified using the new ms word templates

Some of the supported documents that can be used and modified using the new MS Word templates:

Okay so how do we set this up

Okay, so how do we set this up?

Microsoft Dynamics GP >> Tools >> Setup >> Company >> Email Settings


Click on the Sales Setup button to select the documents you want to enable for Customers


Multiple Customers can be easily enabled for e-mail from Customers Navigation List.


Setup e-mail addresses for Customers or Vendors via the Internet Information window.


E-mail address entered in the Customer Internet Information window defaults on the transaction. If you need to change an E-mail address on the fly, use the Email Detail Entry window.


You can immediately e-mail this document to the Customer by looking it up from the Inquiry window and using the ‘Send Document in E-mail’ button.


You can select and send E-mail for multiple documents and Customers, and use filters to narrow the results in the Navigation List window.


Using MS Word Templates

Word templates offer some nice advantages over Report Writer forms, such as enhanced graphics capabilities.


The Word Add-in is required for all users who print the MS Word templates


The Add-in provides access to GP fields from within Word. Once installed, you will find a “Field List” icon on the Developer tab. This enables you to add fields from tables used by Report Writer into the Word template.

If you don’t see the Developer tab in Word, enable it in Word Options>> Popular>>Show Developer tab in the Ribbon.

Navigation to add Developer tab may vary depending on the version of MS Office (must be at least Office 2007)


It’s recommended to enable only the documents for which you want to print Word templates. Disable the rest.


Once a template is assigned to a report, make sure to use the ‘Assign’ button to set the report as ‘Default’ for the company.

Some helpful tips on setting up these features in gp 2010

  • Word templates are based on Report Writer reports. If you need additional data in your templates, you still need to modify the R/W report so the additional information is included on the Modified report. Also, make sure security is granted to the Alternate Modified Forms and the Report ID has been changed to use the Modified report so the new information can be used on the Word template.

  • On the client workstation, check Add/Remove Programs and verify that the following two items are installed:

    • * Microsoft Dynamics GP Add-in for Microsoft Word

    • * Open XML SDK 2.0 for Microsoft Office (this should be dated 4/1/10 or later) – Ideally this should be installed initially with GP.

      Note: These two items are required to ensure MS Word templates can be modified and E-mailed.

Some helpful tips on setting up these features in GP 2010


A user printing and sending E-mails using the Word template should be a Local Administrator on the workstation.

If using MS Office 2010 and sending documents as attachments using the PDF format and ALL of the format options available (DOCX, HTML, PDF and XPS) are not marked in the Company E-mail Settings window, E-mails will not be created and sent. The user will get the error “You must have the Microsoft Save As PDF or XPS Add-In for 2007 Microsoft Office installed to send documents”. All format options should be enabled to resolve this issue.

Wrap up

Thanks for joining us!

Some helpful links:

  • Adding the Developer tab in Microsoft Word after installing the Microsoft Dynamics GP Add-in for Microsoft Word – Video

  • Adding the Developer tab in Microsoft Word after installing the Microsoft Dynamics GP Add-in for Microsoft Word - Video

  • Adding Fields to Word Templates

  • Performance issue when printing Word Templates

  • Word Templates not printing to the screen on a Citrix Environment

  • Using Word Templates in Dynamics GP




End announcements

  • Meeting slides on www.gpug.com by the end of the week

  • Mark your calendars for the next meeting

    • Tuesday May 21

    • Accolade Publications is providing another book for a door prize

    • Who’d like to host and/or do a Member Showcase presentation?

    • Ideas for Q2 meeting?

  • http://www.gpug.com/member-resources/regional-chapters/gpugcolorado/

  • Like us on Facebook

    • https://www.facebook.com/GpugColoradoRegionalChapter

  • Microsoft Convergence – March 18-21

  • GPUG Summit – October 21-25

  • Join GPUG to be sure you are included on the invitation list for upcoming events

    • http://www.gpug.com/join-gpug

    • Already a member? Update your Profile!

  • Evaluations

  • Door prize giveaway

End Announcements

  • Login