Using ODBC to read the On-Line Field Descriptions File

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.

The c-tree ODBC Driver

GP used to sell a read-only ODBC driver for c-tree files. I think it was about $300 per workstation. They do not sell it anymore, but you can get it from FairCom http://www.faircom.com/ace/ctpodbc_information_t.php.

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.

This is what my faircom.db looks like:

CN40102 C:\OTREE\DATA\CN40102.dat
OLFD_Tables C:\OTREE\DATA\OLFD001.dat
OLFD_Windows C:\OTREE\DATA\OLFD002.dat
IUHeader C:\OTREE\DATA\SY50000.dat
IUDetail C:\OTREE\DATA\SY50100.dat

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.

http://www.4shared.com/account/file/d6x1cNPR/Table_and_Window_Names.html

I try to put fairly original material on this blog, I hope you benefit from it!

Until next Post!

Leslie

Dex.ini switches now available to download

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

https://app.box.com/s/xkgae2rz1vil1ap4o579

Until next post!
Leslie

The GP Recipe Book is at last for sale! Amazon.com

This is the book! Written by MVP Mark Polino, who endured the technical editing provided by me and fellow MVP Frank Hamelly,  how can it miss?! It’s available right now!

If you won’t take my word for it, read a chapter for yourself at :

https://www.packtpub.com/sites/default/files/0424-chapter-2-organizing-dynamics-gp.pdf

then buy one for yourself at https://www.packtpub.com/microsoft-dynamics-gp-2010-cookbook/book

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!

Until next post!

Leslie

You Can’t Deny security for some Windows!

How you can tell which windows

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.

http://www.4shared.com/file/d6x1cNPR/Table_and_Window_Names.html

Until Next Post!

Leslie

Goodbye CEIP Program!

This little post addresses the question:

How do I get rid of this annoying reminder at start-up?

Well, here’s how you do it.

First, click on it to open the item.

 

Next, Zoom on the ‘Name’ prompt at the bottom.

Answer ‘No’ and then click ‘OK’

 

Change the Status to ‘Completed’ and then hit the ‘Delete’ button.

Refresh your Home Page and it will be gone forever!

Of course, if you DO want to participate in the CEIP program then you would answer ‘Yes’. The rest of the steps would still be the same!

I hope this helps someone.

Until next Post!

Leslie