Pages

Saturday, July 24, 2010

Here’s the scenario. You have set up a Pilot environment, loaded all of your setups and master files, tweaked the settings, tweaked the master file setups, have your security perfect, entered some test transactions and are ready to go live. Well, you do not want to leave the test transactions in there, but you want everything else.

Where’s the Tool?

In my world, this scenario is a common event. I saw a question on the forum asking if there was some sort of tool that would strip out all of the transactions and leave us with a clean company to take to production. I was hoping there was.

Richard Whaley answered the question succinctly - “Nope”. While I do not have a tool, I do have a ‘starter list’ of things the go-live team performs to prepare the company for production. Some of the things are redundant, but it works and I haven’t spent the time to be precise.

What are the Clearing Steps?

As many of you have learned, there are many more things to change then just clearing out the transaction files. I posted a list I used in a recent implementation for download. It isn’t all encompassing – but it will get you started.

Monday, July 19, 2010

This post describes how to read the On-Line Field Description Tables (OLFD001.dat & OLFD002.dat) using the c-tree ODBC driver. It is the tool I have used forever to put the table and window names in a spreadsheet for easier access.

FairCom c-tree Plus

Dynamics GP v 7.5 and prior supported three databases. Pervasive SQL 2000 (Btrieve), FairCom’s c-tree Plus and MS-SQL. Dexterity still supports these formats. If you search the GP folder, you will find a few tables with the extensions .dat and .idx. These are c-tree tables. Although there are others, I’d like to focus on the On-Line Field Description tables. When you navigate to Microsoft Dynamics GP > Tools > Resource Descriptions > Tables, you are reading those files. While you can, of course, access these tables using Dexterity, you can also read them with an ODBC driver.

If you still have your CDs from version 6 of Great Plains (September, 2000), the c-tree drivers are located on CD 2 in \ODBC\Ctree. You must still pay FairCom for the drivers, they are not shareware, nor were they included with your purchase of GP. Although they are 10 years old (at least), I have been using them on Windows 7 Pro on a 64-bit machine with no problems (and yes, I paid for them).

Instructions on how to use these drivers are scarce, so I thought I would tell you how I use them.

Setting up the c-tree ODBC

After installation, you will have a folder named OTREE of off your root (if you installed to the default location). Inside that folder is a file named faircom.db. This is a text file that documents the path of the .dat files to the driver. It’s a simple format. Name the table something, space, and then put in the full path to the .dat file.

The configuration of the driver itself (in Control Panel) looks like this on my machine. Notice it is a 32-Bit driver:

As you can see, you can name the .db file anything you wish, so long as you tell the system where it is located in the Data Dictionary field.

From there it is a simple process of using the Microsoft Query wizard to create a connection between Excel and the .dat files. You can even create an .odc file! You can do this from any connection you make, not just FairCom.

Generating the OLFD files

‘Out-of-the-box’, the OLFD files do not exist. In order to generate them and populate them, you need to open each product under the Resources menu. If you add any new products, you will likewise need to populate the tables by opening that product under the Resources menu. Recently, I was told that these tables were not automatically re-populated when resources are added to existing dictionaries, so you should go through this process each time you do an upgrade or install a service pack to any product you are using. This is only necessary if new tables were added, or existing tables deleted.

Download the Excel file

Here is a link to the most recent spreadsheet I created using this method. This is current as of build 1411. If anyone has a product they would like to see in an Excel spreadsheet, just e-mail me the .cnk file (or a link) and I will be happy to create another spreadsheet for you and post it.

Wednesday, July 14, 2010

I finally figured out how to post items for download – the blogs will never be the same! The second thing I would like to share with you is my list of .ini switches for the Dex.ini file. This is a 12 page document spanning 17 years of my career as a GP consultant. I’m looking for that 13th page! Anybody that sees something to add or that needs correction please let me know. My goal is to keep this a reliable list!
Have fun with it

It is a fabulous manuscript – you will NOT be disappointed. If you liked my Confessions book, you will love this one. It takes Confessions and adds all of the missing information, like examples and explanations.

I think this is available at Amazon.com as well.

Let’s make Mark proud by each getting a book! Of course, I think he should sign the book for each of us!

Monday, July 12, 2010

If you open the window with Modifier (or Dexterity) you will notice the Window Title value is ~internal~. The System Setup Checklist is one of those windows.

Windows with this title are not available as a security object that can be added to a task.

Can you change it?

Modifier – Changing the Title in Modifier doesn’t work. The window is not available in the Alternate/Modified list.

VBA – You could make the window invisible. I’m sure there are other things a skilled VBA programmer can come up with.

SQL – Adding the window to the SY10700 table doesn’t help. It still does not show up as a valid operation. It is listed on the Security Task Setup report however.

Dexterity –

Creating an Alternate window doesn’t work.

Hacking the Dynamics.dic, well of course that works, but not recommended.

Get a List of the ~internal~ windows

Follow the link below – I created an Excel Spreadsheet called ‘Table and Window Names’ that will show the window & table resources listed in the Window Descriptions and Table Descriptions in Dynamics GP.

Recently I was asked how to attach a .pdf file to a payables transaction. Since pictures are so much easier to follow than words, I am posting the steps necessary to accomplish the task.

First, select the Note icon next to the voucher number.

When the Note window opens. There is a little paperclip icon to the left of the Attach button. This is what you need in order to attach files. If the paperclip icon is not present, there is a problem with the OLEPath = setting in your dex.ini file.

Once you select the paperclip icon, you will be presented with the OLE Container window. On this window select Edit and then Insert New Object.

On the Insert Object window, select the ‘Create from File’ radio button, browse to select your .pdf file and select the ‘Display As Icon’ check box. The ‘Display As Icon’ check box is not necessary, but it makes the Container window look more orderly.

After Selecting ‘OK’ the OLE container will contain an Icon for Adobe.

Select File Exit, save when prompted and then hit the Attach button when you return to the Notes window. After it’s close, you can open the note back up and there will be a little piece of paper in the paperclip.

If you click on the paperclip icon, the OLE Container will open again.

Click on the Adobe icon and Acrobat should open your .pdf file.

There should be no degradation in the quality of the .pdf file as a result of inserting it as an OLE object.

Tuesday, July 6, 2010

A client recently told me they were very confused by the table naming conventions for Dynamics GP. I’m so used to those legacy table names that I don’t appreciate what a wonder it must be for a new user trying to write reports.

In fact, any class I train that involves pulling data from tables (Extender, SmartList Builder, Excel ReportBuilder, ListBuilder, Report Writer, SSRS, Crystal Reports, etc.) sooner or later results in the question “How do we know which table to use”. This query spawned the on-line class I will be doing for GPUG Thursday http://www.gpug.com/events/GPAcademyGPData070810 entitled “Finding the GP Data you Need”

What do the table names mean?

There is actually a very good naming convention for Dynamics GP data tables. This is only a convention, however. It is followed by the GP programmers pretty diligently, but not so much by some 3rd party programmers. Here’s the basics, the first 2 or 3 characters will indicate the module name, the numbers indicate the type of table.

The modules (prefix)

Some of the more popular module abbreviations are in the table below.

Prefix

Module

GL

General Ledger

AF

Advanced Financial Analysis

PM

Payables Management

RM

Receivables Management

SOP

Sales Order Processing

POP

Purchase Order Processing

IV

Inventory

IVC

Invoicing (NOT SOP)

UPR

US Payroll

CM

Cash Management (Bank Rec)

LK

Linked Transactions

ME

EFT

PA

Project Accounting

FA

Fixed Assets

AA

Analytical Accounting

DTA

Multi-dimensional Analysis

SY

System or Company

AHR

Advanced HR

HR

Human Resources

BM

Bill of Materials

DD

Direct Deposit

EXT

Extender

MC

Multicurrency

SVC

Field Service

ASI

SmartList Favorites

ERB

Excel Report Builder

EXT

Extender

SLB

SmartList Builder

WDC

Field-Level Security

The Table Types

After the prefix, the number indicates the table type. Knowing these numbers will help you zero in on the correct table. The table below sets out the numbering convention used by the Dynamics GP programmers.

Master Tables are mostly what you find under the ‘Cards’ area. These are your Customers, Vendors, Inventory Items, GL Accounts and the like. For instance, the information you see on the Customer Maintenance window is stored in the RM Customer MSTR or RM00101.

Setup Tables include choices you have made to initiate a module. For instance, the information entered on the Payables Management Setup window are stored in the PM Setup File or PM40100.

Temp Tables are tables that are used temporarily by the system and the records in those tables can normally be deleted without issue. For instance the Net Profit Temporary table or AF50000 is used by the Advanced Financial Analysis module to hold the Net Profit amount that will be used on one of the statements. Once the statement has been printed, the number is irrelevant.

Relation or Cross Reference Tables are tables that are used to store information that spans more than one module. For instance the SOP/POPLink table (SOP60100) holds the information about POP documents linked to SOP documents. Another example is the Sales Customer Item Cross Reference (SOP60300). This table reflects the information as to how customer item numbers relate to regular item numbers. There are not that many tables in the 60000 range.

Report Options Tables contain all of the information you enter in any of the report options windows. The image below comes from the Payables Trial Balance Report Options window (PM70500).

Posting Journal Reprint Tables contain all of the information you need to reprint your posting journals. So don’t feel like you MUST print all of those reports. They are here waiting for you should you ever need to reprint them.

So now you know. If you were looking for a posted inventory transactions, you would start with the IV20000 table and go from there.

Victoria Yudin has some great information on the popular tables from each module. If you are creating reports, it is information you can use. http://victoriayudin.com/gp-tables/

Friday, July 2, 2010

I would like to offer my hardiest congratulations to the July MVP awardees! I am very humbled yet thrilled to be included with such a group of talented people.

You each are very generous with your help and benefit all consultants, resellers and users alike. Your blogs are the standard for information about Dynamics and Dexterity. Before looking on PartnerSource, I check with you first.

Andrew Anatol Karasev- Dynamics GP- Development (Great job! How about learning Spanish? There are so many people that need your talent in Mexico and Latin America. Gosh, after English, French, Latvian and Russian it should be a breeze! Thank you for all of your valuable contributions to the community)

Mariano Gomez- Dynamics GP- Development (Talented in so many ways, your blog is an absolute must for developers. Thank you Mariano for all the help you have given me over the years. This man is very funny in person, and sets the new standard for Convergence and Tech Conference presentations). We expect to enjoy your sessions for years to come.

About Me

Leslie's biggest achievement is being on the OFFICIAL 100 Most Famous, Awesome and Totally Influential Dynamics People list since its inception.
Leslie Vail is a CPA and has been working as a Dynamics GP consultant for 20 years. She began with version 1.0 in 1993. She has been a Microsoft Most Valuable Professional (MVP) since 2007.
She presents at many partner and customer technical conferences and conducts training classes throughout North America.
Leslie is recognized throughout the industry for her product expertise and contributions to the Dynamics community. She is the principal of ASCI, Inc., a consulting firm located in Dallas, TX.
As a Microsoft Certified Trainer (MCT) her training expertise spans the entire Dynamics GP product line. She is an experienced trainer and teaches classes in Dexterity, Financials, Distribution, HR/Payroll, Integration Manager, Modifier with VBA, FRx, SQL Server Reporting Services, Report Writer, Crystal Reports, SmartList Builder, Integrated Excel Reports, Extender and System Manager.
She serves as a Subject Matter Expert (SME) for the Assessments and Certification Exams (ACE) Team. She is a member of the US MCT Advisory Council.