Monday, October 25, 2010

AutoComplete Data for GP – it’s not really a Mystery

I have heard a lot of buzz lately about the AutoComplete fields and I thought I would take the opportunity to put it to rest. If I'm wrong about this, please correct me, but this is the truth as I know it.

The AutoComplete fields are not stored in the SQL database! They are stored in each user’s profile on each workstation they have used to log in to Dynamics GP.

For example, if I log in to workstation “A”, and then log in to Dynamics GP, an AutoCmpl.dat file will be created in my AppData folder on workstation “A”. If I then log in to workstation “B”, and log in to Dynamics GP, a different AutoCmpl.dat file will be created in my AppData folder on workstation “B”. A folder is created for each company database. Therefore, none of these files includes all of my AutoComplete data. Since the AutoComplete feature has a workstation component, you may have different AutoComplete fields pop up if you log in to Dynamics GP from multiple workstations.

On my Windows7 workstation, the AutoComplete fields for Fabrikam are stored here:

C:\Users\Leslie\AppData\Roaming\Microsoft Business Solutions\Microsoft Dynamics GP\TWO\AutoCmpl.dat

A corresponding AutoCmpl.idx file is in that same folder. The ‘AppData’ folder is a hidden folder, so you will need to modify your folder view options in order to see it.

Another point; I do not think all of the AutoComplete records are “loaded into memory” when the form loads. The data is simply stored in a file and is pulled from the file when the user asks for it. I think the “AutoComplete” feature has been around since version 8, it works by matching typed-in text character-by-character and displaying matches in a drop-down list.

It seems each user can store up to 99,999 entries per AutoComplete field, per company. Theoretically, I suppose you could get millions of records in that file, but just like other data, it is merely taking up a little hard drive space until it is requested. The default number of entries is set to 10,000 per field. I'm not sure why 10,000 was selected, but there must be a reason (or it just seemed like a good idea at the time!).

It has been suggested by many that each user lower the per-field maximum from 10,000 to around 300. Rather than changing the AutoComplete limits, I would think it more appropriate to select ‘Remove unused entries after x days’. If the user wants to remove an entry before that, simply right click on the errant entry and select ‘Remove from list’.

To satisfy my own curiosity, I connected an Excel spreadsheet to the AutoCmpl.dat file and imported the data. The data stored in the file looks like this.


If you open the Dex.dic file using Dexterity, you will find the AutoComplete table definition. The database type is forced to c-tree.

The .dat and .idx files are ctree Plus files. ctree Plus is an ISAM database from FairCom Corporation. Dynamics GP used to be available on the ctree platform. ctree files are still used for some of the temp tables in GP today. For more information about Temporary Tables, take a look at David’s article Working with temporary tables in Dexterity. As a Dexterity programmer you can define that a certain table will be created as a ctree file.

The Table Import Utility definitions are stored in ctree files (SY50000.dat and SY50100.dat). The On-Line Field Descriptions are stored in ctree files too, OLDF001.dat (for the tables) and OLFD002.dat (for the forms). If you have the ctree Plus ODBC driver, you can read these tables. If you want to learn more about using the ctree Plus ODBC drivers, read the article Using ODBC to read the On-Line Field Descriptions File.

Until next post!


Saturday, October 9, 2010

Dex.ini file has been updated!

I added a few more switches to the dex.ini file over the last few months so I have uploaded the latest and greatest to the share site. You can download the file from my 4shared site.
Have fun with them!

Friday, October 8, 2010

Come to the GPUG 2010 Summit!


Time is running out! If you haven’t already got your ticket to the 2010 GPUG Summit there is still time. If you still need some convincing, check out the ‘Top 6 Reasons to Attend. If you need to convince someone else that it is essential that YOU are sent, check out Why You Need to be at Summit-Tell your Boss!

I have been to the last three (the only three?) Summit’s and have vowed to go to them from now on. MVP Mark Polino and I will be delivering several separate sessions at the Summit – be sure to attend these sessions! If you want to come in a day early or stay an extra day, sign up for some of the optional training classes being offered.

In addition to all of the other valuable content, GPUG Summit 2010 will be featuring several Focus Areas. Among them is HR/Payroll, Reporting and Potpourri. In the HR/Payroll arena, I will be delivering three sessions, fellow MVP Mark Polino will be delivering a session in the Reporting area and Both MVP Mark Polino and I will each be delivering a session in the Potpourri area. Stop by the sessions and be sure to say ‘hello’!

HR/Payroll Special Interest Group Focus Area

Learn What You Can Now do with HR and Payroll Inside GP – delivered by MVP Leslie Vail
In this presentation session learn about the Dynamics GP functionality currently available in the Human Resources (HR) and Payroll areas. This session will feature a live demo of some of the new functionality brought to us by GP 2010.

Understanding the Latest Government Regulations Around HR and Payroll – delivered by MVP Leslie Vail and Landon Russell
There are a lot of recent regulatory changes in the HR and payroll areas lately. In this presentation learn how Dynamics GP can assist in meeting these regulations. Some of the key provisions of the Patient Protection and Affordable Care Act have already taken effect; come to this session for some guidance on what those provisions really mean to us as employers. Topics include the new W-2 reporting requirements as well as what it means to be a ‘grandfathered’ health benefit plan.

20 HR and Payroll Tips to Make Your Life Easier – delivered by MVP Leslie Vail
As a follow up to the presentation session on HR/Payroll and GP, attend this presentation session to see 20 HR/Payroll tips which will make your HR and Payroll department jumping for joy! Learn how you can set up employees faster, enter transactions faster, navigate the interface with more precision and pick up some tips on what to do ‘when things go wrong’. This will be a fun session.

Reporting Focus Area

Leveraging the Power of Extender – delivered by MVP Mark Polino
This presentation session will be about Extender. Learn more about this exciting Dynamics GP feature and how it can be used to enhance your GP user experience.

Potpourri Focus Area

This content area is, in my opinion, the most just pure fun! Be sure to catch the following two presentations:

What's Available for Free in the GP World – delivered by MVP Leslie Vail
Did you know there are plenty of free resources available to assist you and your company in its use of Dynamics GP. In this presentation session see some of these tools in action. You'll be surprised what all is out there for free!

All New! 50 Tips in 50 Minutes – delivered by MVP Mark Polino
Don't miss this always exciting session where 50 new tips for Dynamics GP will be shown. Leave this presentation with immediate take back information that can make your GP installation better! Mark always delivers a top notch performance. This session will keep you talking for weeks and looking forward to the next time you cat attend. So much information in so little time!

Until next post!