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

Attaching a .pdf File to a Payables Transaction

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.

I hope this helps!

Until next time,

Leslie

Dynamics GP 2010 Cookbook At Last!

Congratulations to fellow MVP Mark Polino on the release of his fabulous book that is available for sale right now!

http://msdynamicsgp.blogspot.com/2010/07/microsoft-dynamics-gp-2010-cookbook-is.html

Everyone who works with Dynamics GP should add this to their library. Thank you Mark for the great effort and diligence that I know it took to bring this to fruition!

Leslie

Dynamics GP Table Names

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.

Table Number	Description
00000	Master Tables
10000	Work Tables
20000	Open Tables
30000	History Tables
40000	Setup Tables
50000	Temp Tables
60000	Relation Tables
70000	Report Options Tables
80000	Posting Journal Reprint Tables

One of my earlier posts explains the difference between ‘work, open, and history’ http://dynamicsconfessions.blogspot.com/2009/11/moving-from-work-to-open-to-history.html.

Some information on the remaining tables follows.

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/

Have fun with it! Until next post!

Leslie

Congratulations MVPs!

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.

Congratulations again, you guys are the best!

The list:

Victoria Yudin- Dynamics GP- Design (The expert on helping us ‘find the data’. Definitely the ‘go to’ person for table information and cool SQL queries)

Charles L. Allen- Dynamics GP (Another ‘old timer’ like me, we need to get together and share stories!)

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)

Leslie Vail- Dynamics GP- Training (That’s me!)

Mark Polino- Dynamics GP- Systems Administration (Buy his fabulous ‘Great Plains Recipe Book)! Don’t miss any of his ‘50 tips’ sessions! Mark, you are as funny as you are informative. You can make accounting software ‘fun’!)

Monzer Osama AL Shaikh- Dynamics GP- Engineering (I need to meet you! It looks like you know just about everything!)

Frank Hamelly- Dynamics GP- Systems Administration (don’t miss his tip 4 today! I finally got to meet you Convergence. You are every bit as fun in person as you are on the forums! You can always help me without making me feel dumb!)

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.

Ian Stewart- Dynamics GP (So often our savior on the forums. Where’s your picture?!)

Good luck to you all this year! And keep up the good work, the community would never be the same if any of you left.

Kind regards,

Leslie

You NEED this book by Mark Polino!

This is an exciting year for all Dynamics GP partners and users!

The Microsoft Dynamics GP 2010 Cookbook is available for preorder.

This is a fabulous book covering so many tips and tricks that you will want it in your personal collection. Imagine, 400 pages of tips with step-by-step instructions on how you can take advantage of these unique topics.

My hat (pink with LED lights) is off to you Mark, great job!

Until next post!

Leslie

Change your Fiscal Period Names!

Fiscal periods should not be named Period 1, Period 2, Period 3, etc. These are the default names assigned by GP when new years are created. These names are editable and are easily changed just by typing into the field. Recording a macro to change the period names can make the job much easier and can be used year after year.

Access this window in the Administration Area Page in the Setup Content Pane, using the Fiscal Periods item in the Company menu.

The default period names

Better period names

These period names will appear in reports, range selections, and even FRx!

Until next post!

Leslie

Change your Account Segment Names

Account segments should have meaningful names. They should not be named ‘Segment1’, ‘Segment2’, etc. Turn them into relevant names using the Account Format Setup Window.

How it is now

So many look similar to this:

 

The ‘Name’ field is an editable field. Type the proper name of the segment and many of the reports and views will have meaningful names.

How it can look

This change will cascade down to all of your range descriptions and inquiry screens throughout the system!

Until next post!

Leslie

Taming the Accounts Lookup Window

Some features exist in Microsoft Dynamics GP that have been there ‘forever’, but are not given much attention. This post explores the ‘Include in Lookup’ option on the Account Maintenance window.

What’s the big deal?

The big deal is that when new accounts are created the default selections are most likely not the appropriate settings for your accounts, yet these selections are often left ‘as is’. Take a look at the familiar Account Maintenance window below.

Focus on the ‘Include in Lookup’ box, notice that all of the items are highlighted. I do not think this is the best setting for most accounts.

Restrict the accounts displayed

If you are entering a sales transaction, do you really want to see ALL of the accounts when you hit the lookup button on the distribution window? I don’t think so. For the accounts you do NOT want to display in the initial lookup window, simply remove that series from the ‘Include in Lookup’ option.

Here’s how it’s done

Open the Account Maintenance window. Select an account that should not be displayed in the Sales lookup window. Hold down the CTRL key and click on the Sales item to remove the highlight. 

Now, whenever the account lookup is selected in any sales series module, this account will not appear in the list. By using the View menu you can select to see all of the accounts, but this is an effective method to limit the initial choices presented.

There are several methods to limit the items that appear in a lookup window, but this one is often overlooked.

Until next post!

Leslie

Converting Security to v 10 – it’s OK!

Hi all,

I’ve heard a lot of buzz about converting security from earlier versions (8 or 9) to version 10. Most of what I have heard is “don’t do it”. I disagree. Do it, but understand the ramifications.

Why Convert?

The first (and only) ramification I can speak to is that you can get up and running more quickly by converting your current security than by completely re-doing your security using the new v 10 concept. In my world, if my clients had to re-do their security they would possibly never convert. Converting security to the new paradigm for a site with many users and many companies is an onerous task; and that’s putting it in a good light.

Making it Easier

Before you upgrade, you need to ‘skinny up’ your security such that you end up with the minimum number of tasks after the conversion.

For example, if you have 8 users and 6 companies, and each user has the same security for each of the companies he/she has access to, then after conversion each user has a task and role and Alternate/Modified Report ID for each company. Wow!  Don’t do that.

This recommendation came to me from Sheila Jefferson-Ross, an experienced (read over 15 years) consultant near Sacramento, California.

Before conversion, remove access from each company for each user who has the same security for each company. Whew, that’s another one where reading it again won’t help. Let’s look at an example.

Bob has access to three companies. His security is exactly the same for each company. If the conversion is done ‘as is’, then after conversion Bob will have a task for each of the three companies. Each of these tasks will roll into a role for each company. Multiply this for each user, and you end up with more identical tasks than anyone wants to manage.

In order to minimize the number of Tasks/Roles/Alternates, simply remove Bob’s access from two of the three companies before conversion. After conversion, grant him access to the other companies and copy the security from the first company to the other two.

You will end up with a manageable number of roles until the security is re-worked to embrace the v 10 model. Go ahead and convert! Version 10 has so many enhancements on v 9; don’t let the new security model slow you down!

Until next post!

Leslie

Making the Account Number SMALLER

We all know that we can increase the number of characters in an account segment up to the maximum number allowed in the Account Framework. However, if we need to REDUCE the number of characters, all of the documentation tells us we cannot.

Once upon a time there was not an option to display an expanded account width. Therefore, if you wanted to use an alphabetical character instead of a number, the alphabetical character would not be displayed if it was wider than a number. The only way around this limitation was to make the segment an extra character long.

After the ‘display width’ option was introduced, it was desirable to remove the extra character(s) from the account segment. All questions to Microsoft (then Great Plains) tech support said ‘NO’ you can’t do that.

Well, I’m here to tell you that you can!

Making it Smaller: Setting it up

First, you need to make sure that you do not have any characters in the position(s) that you want to reduce. For example, if you want to reduce a segment from 5 characters to 4 characters, then each of your accounts must not be more than 4 characters long in the segment you want to reduce.

If I want segment two to go from 5 characters to 4 characters, your accounts must include a blank character for position 5 of that segment.

Before account size reduction:
555-6588 –000

After account size reduction:
555-6588-000

Notice that the second segment includes 4 characters and then a ‘blank’. If your accounts are not in this structure, then you might want to invest in the Professional Services Tools Library’s Account Modifier/Combiner tool in order to align your accounts to the necessary structure.

Making it Smaller: Executing the change

Once your accounts are in the specified format, simply run the System Reconcile Utility (Microsoft Dynamics GP >> Tools >> Utilities >> Reconcile) against the Account Format Setup table.

The length of the account segment will be reduced from 5 characters to 4.

And they said it couldn’t be done . . .

Until next post!

Leslie

SmartList .ini switch Problems

What’s the switch?

SmartlistEnhancedExcelExport=TRUE

What’s the problem?

A client of mine was gracious enough to do some testing with this switch and discovered a couple of notable issues.

1. Leading zeros are stripped from string fields when they are exported to Excel.
2. The last digit of a string field is changed from a ‘2’ to a ‘0’. For instance:

Without Enhanced switch:

6055762161047502

Same record with Enhanced=TRUE

6055762161047500

I am not sure if it matters that there were two zeros in a row, but for now the switch is not usable for these types of fields. Using only numeric fields it is wonderful, but for string fields it seems to have issues. Like Patrick reminded us, it’s ‘undocumented’ for a reason.

Until next post!

Leslie

Organize Your QuickLinks Section

Thanks to Landon Russell I learned a tip today that I think will ‘tame’ the QuickLinks content pane on our GP 10 (and 2010) Home Pages.

Landon’s Quick Links section contained dividers much like this:



I thought it was fabulous to have dividers and asked him how he did it. He explained how he created those dividers and I thought it was genius – and can be used in other places throughout our GP system. But for now, let’s deal with the Home Page.

To create a divider line you need to add a web page shortcut to your QuickLinks pane. In the Name field on the Add Web Page box you should put in whatever you want to appear as the divider. For our example I will use a string of hyphen characters (-----). For the address, select either http:// or https://, it does not matter which. In the name field to the right of the address simply type in a hyphen.



The hyphen is really just a placeholder, you will not get any error messages because it will really not try to launch any web page.

The new line will be added to the bottom of the Quick Links pane and can be moved up and down to accommodate your needs.

It’s a wonderful thing, thank YOU Landon Russell!

Until next post!

Leslie

Boolean Logic to the Rescue

We all have a couple of phrases we say all of the time which are probably despised by our friends because they have heard them so often. One of mine is:

“Don’t make this hard”

On a recent post by Richard Whaley on the Dynamics GP Newsgroup, he suggested adding up the values in a field to see if the field was populated. If the resulting value was greater than zero, the field was populated.

With that in mind, let’s explore the world of Boolean logic. I like it, no gray areas, no fancy nested conditional statements, it’s either true or false. Pretty easy.

True or False?

Often we find ourselves building IF statements that go through multiple logical tests. We string together a bunch of AND, OR, and NOT functions in order to test our data and arrive at a conclusion. Throughout this complex statement, all we are doing, really, is making up a group of True or False computations. Think about it, each step of our equation is either true or false. If it is False it is 0 and if it resolves to True it is 1. Such is the logic behind the ‘IF, THEN, ELSE’ statement. So before you reinvent the wheel with some monster conditional structure, consider breaking it down into a logic statement and see if you can get to your answer more quickly and more elegantly.

Before I go into the example, write down these cornerstones for Boolean logic problems:

Cornerstones for Logic

FALSE is Zero (0)

Add (+) if you want ‘OR’

Multiply (*) if you want ‘AND’

Anything else is TRUE (1)

An Example in Logic

Scenario: A 5% bonus is paid to salespeople who have sales over $50,000 so long as their gross profit percentage is greater than 30%. Oh, and it is always paid to ERIN J. because she has a special relationship with Aaron Fitz. Your job is to figure the bonus amount for each sale.

OK, let’s take this apart. The bonus is paid IF:

(Sales > $50,000 AND GP > 30%) OR Salesperson ID = ERIN J.

Programmatically that would look something like this:

IF(OR(AND (Sales > 50,000, GP > .3),
Salesperson = “ERIN J.”),
.05 * Sales, 0)

If we could create a calculation to solve this logical test, we could check the value of the resulting number. If the number is 0, the answer is FALSE. If it is anything else, the answer is TRUE. So if the answer is TRUE, then the bonus is paid, otherwise it is not.

Using the cornerstones we set out above, let’s solve this problem with Boolean logic.

Sales > 50,000 AND GP > 30% would turn into:
Sales > 50,000 * GP > .3
The result of this calculation is 0 if either of the components is not true. The result is 1 if both components are true.

If that was all of our criteria, then we could just multiply this formula by the bonus calculation and we would have our answer. The whole formula would look like this:

Bonus = (Sales > 50,000) * (GP > .3) * .05 * Sales

We have another wrinkle that we need to factor in, however. ERIN J. always gets the bonus, just because she is ERIN J. So now we have a big fat OR clause in our logic problem.

Our cornerstones say that an OR clause should be converted to an addition operation. On its face we might rewrite the formula to this:

Bonus = ((Sales > 50,000) * (GP > .3)) + (Salesperson = ERIN J.)

We need to be careful here because if ERIN J. had a sale that qualified for a bonus in its own right, then the result of our formula would be 2. ERIN J. may like this outcome, but the stockholders would not. In order to avoid this, we need to convert the result to TRUE  or FALSE, or 0 or 1.

The restated formula would look like this:

Bonus = If((Sales > 50,000) * (GP > .3) + (Salesperson = ERIN J.) 1, 0) * Sales * .05

Breaking it down into its components, you can do this easily even in Report Writer. Boolean logic turns it into 3 conditional fields and 3 straight calculated fields.

• Calc 1, conditional = if Sales > 50,000 then 1
• Calc 2, conditional = if GP > .3 then 1
• Calc 3, calculated = Calc 1 * Calc 2, this will be 1 or 0
• Calc 4, conditional = if Salesperson = ERIN J. then 1
• Calc 5, calculated = Calc 3 * Calc 4 this will be 1 or 0
• Calc 6, calculated = Sales * Calc 5 * .05 =The Bonus Amount

If you try and convert your IF statements into Boolean logic, you may find that they are much easier to understand and faster to write.

Don’t make this hard!

Until next post,

Leslie

Speeding up SmartList Exports

Put SmartList on Hyperdrive! There is a dex.ini switch that has just been disclosed. Patrick has posted a great article about how he found it and how it works at the Developing for Dynamics blog. The article explains why SmartList exports are so slow and how to speed them up. Check out his article here:

http://blogs.msdn.com/developingfordynamicsgp/archive/2010/05/14/smartlist-exports-slowly-to-excel-part-1.aspx

The switch is:

SmartlistEnhancedExcelExport=TRUE

It's a wonderful piece of information, thank you Patrick.

Until next post,

Leslie

Allocating Tips in Payroll – How and Why?

This posting steps through the tip allocation computation used by the Payroll module in Dynamics GP. Some questions have come up lately on the forums; rather than posting a lengthy answer, I thought it would be better to cover it in more detail in my blog.

What is it? -- The IRS has set a minimum amount that it expects waitstaff should have included in income for tips. The tip allocation calculation function merely adds an amount to taxable income; it does NOT result in an extra amount going into the paycheck.

The amount includable in income for an employee who is directly tipped is the greater of (a) the amount they reported to their employer or (b) their allocable share of the minimum amount established by the IRS (for 2009 it was 8%).

An indirectly tipped employee does not receive allocated tips. Generally, indirectly tipped employees receive their tips from other tipped employees rather than directly from the customers. Positions such as cooks, bussers, hostesses, barbacks, and dishwashers are typically indirectly tipped employees. Of course, an employee could receive both direct and indirect tips, if so they will be included in the allocation computation for their direct tips.

The Dynamics GP instructions provide the following explanation about allocated tips:

Microsoft Dynamics GP uses the Gross
Receipts method that is calculated per pay
run. The allocated tips amount equals the
difference between tips received and
employee gross receipts divided by
company gross receipts, multiplied by the
tip allocation rate, provided the difference is
more. Made to each directly tipped
employee.

Reading it again won’t help. I think an example is in order.

Our Example -- Let’s say our company, named Tailspin Pizza (“Tailspin”), has three employees who receive tips. The employees are tipped both directly and indirectly

During a payroll period, gross receipts at Tailspin were $15,500. Total tips reported by employees for this period were $980. Directly tipped employees reported $430 and indirectly tipped employees reported $550. Based on the IRS guidelines, the employees’ total tips should have been 8% of $15,500, or $1,240. Our job is to properly calculate and allocate the $260 shortfall.

Dynamics GP can do it!

How to set it up in Payroll -- Payroll Options need to be set telling the system that you want to use the tip allocation feature. To do this:

1. Navigate to the Payroll Setup window

Microsoft Dynamics GP>>Tools>>Setup>>Payroll>>Payroll

2. Select the Options button on the lower right-hand corner of the Payroll Setup window.

3. Mark the Calculate Tip Allocation checkbox in the Options section of the Payroll Setup Options window and set the Tip Allocation Rate to 8.00% (or whatever applicable percentage).

The allocation rate is determined by the IRS. Unless a lower amount was negotiated with the IRS, the rate was 8.00% for 2009.

How to set up Pay Codes --The appropriate pay codes need to be set up in order to include tips in a payroll run. Additionally, we need to be able to indicate whether the tips were received directly or indirectly. For the purposes of the tip allocation computation it does not matter whether the tips were ‘charged’ or ‘reported’. We’ll set up the following 3 Pay Codes to use in our example:

Pay Code	Pay Type	Tips Received
CTIPI	Charged Tips	Indirectly
CTIPD	Charged Tips	Directly
RTIPD	Reported Tips	Directly

For the payroll period, Tailspin has total gross receipts of $15,500. Total gross receipts are calculated by adding together the individual employee receipts entered on the payroll transaction. The employee’s receipts are entered on the Payroll Transaction Entry window with the ‘show’ details open.

The Calculation -- Total gross receipts were $15,500, total reported tips were $980. Tips received directly were reported as $430. Tips received indirectly were reported as $550.

Directly Tipped Employees	Gross Receipts for payroll period	Direct Tips Reported
ACKE	3,500	30
BARB	5,500	100
BARR	6,500	300
Totals	15,500	430

1. $15,500(gross receipts) x .08 = $1,240

2. $1,240 - $550 (tips reported by indirectly tipped workers) = $690

The amount used to calculate each directly tipped employee’s share of 8% of the gross is $690.

3. Calculate each directly tipped employee’s share of 8% of gross receipts.

Directly Tipped Employees	Directly Tipped Share of 8% of the gross	(Times) Gross Receipts Ratio	Employee’s share of 8% of the gross
ACKE	690	3,500/15,500=	156
BARB	690	5,500/15,500=	245
BARR	690	6,500/15,500=	289
		Total	690

4. Calculate the shortfall in reported directly received tips

Directly Tipped Employees	Employee’s share of 8% of the gross	(Minus) Tips Reported	Employee shortfall (use 0 if no shortfall)
ACKE	156	30	126
BARB	245	100	145
BARR	289	300	-
Total	690	430	271

5. Calculate the allocable share of the total tip shortfall. $1,240(total gross receipts x 8%) less $980 (total tips reported, includes direct and indirect) = $260(amount to be allocated among employees who had a shortfall).

Shortfall Employees	Allocable Amount	(Times) Shortfall ratio	Amount of allocation
ACKE	260	126/271=.46484	120.86
BARB	260	145/271=.53516	139.14
BARR	260	0/271=.00000	0
Total			260.00

6. Since employee BARR did not have a shortfall, there is no allocated amount to employee BARR.

Leave it to the IRS to dream up this ‘simple’ allocation calculation. Fortunately Dynamics GP does it for us!

Until next post!

Leslie

DEXVBA.ini Switches - Part 1

In keeping with my ‘.ini’ switch theme I would like to keep track of entries we can make to the DEXVBA.ini file. I currently have only two. I hope you will comment here if you have any others and I can add them to the list. I have titled this ‘Part 1’ in hopes that I will get additional settings so that I may post a ‘Part 2’.

The DEXVBA.ini file is not created by default. You will need to open Notepad (or similar text editor) and create it. The file should be created in the root Windows folder, not in the GP folder.

Step 1. Create a file named DEXVBA.ini in the root Windows folder.

Step 2. Add the following line to the top of the file: [General]

Step 3. Add the selected .ini setting beneath [General].

INI Setting	Description
LogObjects=TRUE	This will create a text file that will include all of the objects in a VBA project. The text file will be the same name as the product dictionary with a ‘.txt’ extension.
NoUnresolvedDialog=TRUE	This will suppress the following error message when you launch Dynamics GP. “The product_name.vba project references some objects that cannot be found. These objects are listed in the file: C:\Program Files\Microsoft Business Solutions\GP\ product_name.txt” The warning will be suppressed for all VBA projects loaded. It doesn’t solve the problem regarding missing objects, but it suppresses the message.

Until next post!

Leslie

GP 2010 on 64-bit Windows 7 with i3 Processor

I have more to report on my attempted installation of Dexterity 2010.

Thanks to Vaidy for the guidance, I did the following:
I ran "dw20sharedia64.msi" from the following path:
GP2010 Main Folder -> Tools -> Dex -> Watson -> dw20sharedia64.msi

I got this error:

I then ran “dw20sharedamd64.msi'” from that same folder and was greeted with nothing noteable. I didn't expect anything because this is not an AMD processor, but you never know.

I then ran the “dw20shared.msi” and received this message:

It looked promising, but then nothing else happened.

I then run the setup.exe file for Dex and got this again:

I restarted the machine between attempts.

The test machine's processor is an i3. The i3 was released January 8th, 2010. Since the first error message tells me the processor isn't supported, I am thinking the new processor may be the issue.

I do not have another machine to use for the beta - has anyone tried to install Dex 2010 on a similar hardware configuration to mine?

The machine is 64-bit, running Windows 7 Professional, with an i3 processor.

Until next post!

Leslie

12 inches of Snow in Dallas!

How is this related to Dynamics GP? It isn’t, but it was a record setting day! The snow fell on Thursday, Feb 11, 2010. It is all melted now, but it was amazing.

The city shut down, schools closed, electricity went out, etc. I grew up in Michigan, if they had closed the schools because of cold weather and snow, I would still be in high school.

The pictures are from my neighborhood. We lost a lot of trees, that was a shame.

Until next post!

Leslie

Tools Available from Professional Services

Lately there have been several posts to the various newsgroups regarding functionality that is not part of Dynamics GP ‘out-of-the-box’.

I received a list from GP Professional Services of tools available to partners for resale. Prices are subject to change, and the versions supported have been based on the popularity of the request. Some of the tools are my favorite price, free! The most expensive one is $1,500, most are $500 or $750.

I have struggled with so many of the issues that these tools have solved so I thought I would put the list out for all. I asked Microsoft first and they said it was OK to post it.

Name	Description	Versions	Price
AutoDim	Utility allows you to launch Great Plains and run one or more integrations or integration groups and have Great Plains exit upon completion of the integrations. This product also supports integrating into several different companies.	7.0, 7.5, 8.0, 9.0, 10.0	500
Customer PO Number Check (this is a very nice tool, it prevents some orders from being duplicated)	This tool checks the Customer PO Number for duplicates as data is entered into the field on both the SOP Entry window and the SOP Customer Detail Entry window.	7.5, 8.0, 9.0, 10.0	500
Doc Date Verify	When a document date is entered in Payables Transaction Entry or Receivables Transaction Entry, a check will be performed to determine if the document date is in a fiscal period. If the date does not fall within a valid fiscal period, a message will be issued and the user will not be allowed to continue until the date is in a valid period. This customization will also do a validation if the period is closed.	7, 8.0, 9.0,10.0	FREE
Grant All Access Script	Grants all users access to all projects.	Not version specific	FREE
Master Record Macros	Macros to populate the master records when Project Accounting has been implemented after Vendors, Customers, or Employees have been created.	Not version specific	FREE
Payroll Local Tax Tool	This utility allows specific local taxes for an employee to be sheltered for TSA style deductions while allowing other local taxes for the same employee to not be sheltered.  Each deduction in the system can specify which local taxes should be sheltered, and employees can also be specifically excluded from this process.	7.0, 7.5, 8.0, 9.0, 10.0	500
PO Returns (this prevents you from paying for an item that has been returned)	When invoicing a receipt in POP, this customization will check the previous quantity invoiced and the quantity returned for the line item. The maximum quantity allowed to be invoiced is based the following formula: Quantity Shipped minus (Previous Quantity Invoiced plus Quantity Returned). This customization will not allow invoicing of more than this amount.	7.5, 8.0, 9.0, 10.0	FREE
POP Cost Defaulter (a life saver if you don’t want your PO cost to float around)	This utility allows the user to default in the Standard Cost or the Current Cost within the Purchase Order Entry window instead of the vendor specific Last Invoice Cost.	7.0, 7.5, 8.0, 9.0, 10.0	FREE
RM Auto Apply Utility	This application will allow the user to Mass Apply posted RM Credit Documents to posted RM Debit Documents.  The user can select a range of Customer, Debit Document Dates, Credit Document Dates, and a Credit Document Type restriction if desired. The credit documents will apply to the debit documents in the order specified in Receivables Management Setup window which is the same order that Dynamics Apply Sales Documents Auto Apply button uses.	7.0, 7.5, 8.0, 9.0, 10.0	750
Shipment Notification (YES! No more delaying customer billing!)	Will allow drop ship sales orders to be transferred to invoice prior to invoicing the purchase order	7.x, 8.0, 9.0, 10.0	750
SOP Lot Number Overrides	Use this tool if you use Lot Number processing and run into situations where you sell Lot Numbers before they are entered into the system through POP or BOM.  This utility will create a temporary Lot Number receipt during the Sales Transaction Entry process and manage the Lot Number quantity accurately. After the Lot Number is received through POP, BOM or IV, the temporary receipt is removed and the actual lot receipt is updated to reflect that a portion of it has already been sold.	7.0,7.5,8.0, 9.0, 10.0	1,500
SOP Partial Kit Transfers	This utility supports partial transfers of Kit items from Orders to Invoices and will maintain the Previously Invoiced quantity for the overall kit item and all of its components. This utility does not currently support partial transfers to back-order documents.	7.0,7.5,8.0, 9.0, 10.0	1,500
SOP to POP Line Reordering (the PO doc will now be in the same order as the SOP doc.)	This utility will reorder the line items of Purchase Orders generated by the SOP to POP transfer process to be in the same order as they originally were on the Sales Order document(s).  The preview report will still show an alphabetical list, but on the actual Purchase Order document the line items will maintain their correct sequence. This is critical for companies who rely on matching up PO printouts to Packing Lists or Picking Tickets in SOP.	7.0, 7.5, 8.0, 9.0, 10.0	500
Default AddItem for POP/SOP	Application that defaults the Add Item option to be marked in Sales Transaction Entry, Purchase Order Entry, POP Receivings Entry and Purchasing Invoice Entry.	8.0, 9.0, 10.0	FREE
AutoPost	COM dll that can be called from IM or an outside application to post some types of Dynamics Batches.  Currently the application supports posting of: Sales Order Processing Batches GL Transaction Entry Batches IV Transaction Entry Batches RM Cash Receipts Batches Because this application uses the Continuum API, the Great Plains client must be running and a user logged into a company for this application to work.  The posting process would then use that user & company to post the desired batch.	8.0, 9.0	500
Benefit Modifier Tool	Allows users to change the Benefit Code	7.5,8.0,9.0	750
Deduction Modifier Tool	Allows users to change Deduction Codes	7.0,7.5,8.0,9.0	750
Credit Limit Override for eOrder	Allows the function of overriding credit limits for customers that have no credit or have reached their credit limit during the creation of an Order. This feature is not available on the base package of eOrder or eCommerce.	7.0, 7.5, 8.0	750
Customer specific Catalogs for eOrder	Provides ability to setup multiple catalogs and assign different catalogs to different customers.	7.0, 7.5, 8.0	750
Email confirmation for eOrder	Sends an email to the shopper after the order has been placed with the order details to the email address entered on the Internet Information page from the page off the Customer’s main address record for eOrder.	7.0, 7.5, 8.0	750
Email used as login for eOrder	Changes the login from Customer ID to the email address entered on the Internet Information page from the page off the Customer’s main address record.	7.0, 7.5	750
Extended Pricing eOrder / eCommerce	Allows extended pricing for the eOrder and eCommerce module.	7.0, 7.5	1,500
Historical Order Inquiry for eOrder	Provides the capability of viewing historical Order information within eOrder.	7.0, 7.5	750
Email Invoices	Allows users to email invoices to Customers. No more hassles mailing invoices to customers, simply specify their email address and send the invoice electronically.	7.0, 7.5, 8.0, 9.0	750
Employee Business Card with Image	The Employee Business Card with Image web part will display the image of the employee.  Employee Business Card with Image web part adds an image to the Employee Business Card web part found in the Business Portal at Company – Directory.  Business Portal administrators simply name their *.jpg file according to the Employee ID and copy the file to a specified directory.	8.0, 9.0	FREE
IV Post To GL	Allows IV Transaction Batches to look at the checkbox in Setup>Posting>Posting to determine if the Post To GL checkbox should be marked or unmarked for IV batches.	6.0,7.0,7.5,8.0,9.0	FREE
Multi-Build Payroll Tool	Allows multiple people to build /calculate / print / post payroll batches at the same time	8.0, 9.0	1,000
Pay Code Modifier Tool	Allows users to change Pay Codes	7.0,7.5,8.0,9.0	750
Payables Management Checks (Decimal Place Tool)	There is an option under Setup | System | Currency to change the decimal places. Once you move them up (example 2 to 4) it affects all modules checks and you are not able to back it down. This tool will print the checks out with two decimal places for Payables Management and Bank Reconciliation checks.	7.0,7.5,8.0,9.0	FREE
Payroll Checks (Decimal Place Tool)	There is an option under Setup | System | Currency to change the decimal places. Once you move them up (example 2 to 4) it affects all modules checks and you are not able to back it down. This tool will print checks with two decimal places only for Payroll checks and Direct Deposit.	7.0,7.5,8.0,9.0	FREE
Payroll Customized 941 Report	Prints the 941 by date range and  / or employee	7.0, 7.5, 8.0, 9.0	500
Payroll Detailed Activity Tracking	Each time a change is made within HR and Payroll to an employee a script can be processed to see the activity.	7.5, 8.0, 9.0	750
Payroll Pay Code History Edit	Allows users to change WC, FUTA and SUTA within the Pay Code History window	7.0, 7.5, 8.0, 9.0	500
PM Checks Default (remember this feature?)	Default Print PM Checks to check and not alignment form	6.0,7.0,7.5,8.0,9.0	FREE
Print Check Default (and its cousin in payroll)	For payroll only (not used with Direct Deposit)  to default into print the check within the Print Checks window instead of Printing the alignment first	7.0, 7.5	FREE
PM EFT User Defined Setup Utility	Allows users that require slight variations to the EFT file to format in order for their banks to accept the files.	8.0, 9.0	FREE
POP Over Receipt Tolerance	Utility that allows a receipt tolerance percentage for quantities.  A setup window allows the user to enter a tolerance percentage, that percentage is then used when a Purchase Order is received.  The highest quantity that can be received is the original quantity ordered plus the percentage amount.  For example, a Purchase Order is entered for a quantity of 10.  A tolerance percentage of 20% is entered.  The highest quantity allowed to be received would be 12.	7.0, 7.5, 8.0, 9.0	750
Security Troubleshooting Utility	This utility assist in troubleshooting security issues which have occurred in Great Plains.  Utility lists each window/report resource ID then trace these references to the security table before the permissions to the resources ID is validated by Great Plains, aiding in which resource is causing a security issue in Great Plains.  Does not function with Small Business Manager.	7.0, 7.5, 8.0, 9.0	FREE
SOP Default Site per Line	This customization changes the site that is defaulted on line items.  Instead of pulling the site from the Default Site on the SOP Entry window, if there is a default site assigned to the item in Cards>Inventory>Qtys/Sites, then that site will default for that line item  If there is no default site id for the item, then the customization will default the first site that is assigned to the item.	7.0, 7.5, 8.0, 9.0	FREE
SOP Returns Account Default	This customization will default the sales order returns account when a return is created from SOP Entry under Extras>Create Return.  As the SALES distribution type is created for the return, the customization will check the Sales Order Returns account from the customer card.  If the account is not empty, then all SALES type accounts will default with the account number in the Sales Order Returns slot from the customer.	7.0, 7.5, 8.0, 9.0	FREE
SOP Sort Line Items	This utility allows the user to define custom sorting options to display line items in Sales Order Processing.  For example, a sort could be defined by Item Number and then the currently selected SOP Document would display the line items in Item Number order instead of the standard by order entered.  This utility does not physically reorder the records in the underlying tables; it only displays them differently in the Sales Order Processing window.  This application also does not affect the way that sales documents print. (SQL database only)	7.0, 7.5, 8.0, 9.0	500
Update User Date Utility (otherwise the user date will be off and so will all of the default batch dates)	This application automatically updates the User Date in Great Plains to the next date at midnight.  The application will run in the background as long as Great Plains is open.  This tool can be useful when Great Plains is left open on a machine overnight running integrations or other processes.  (date disabled with dex.ini switch)	7.0, 7.5, 8.0, 9.0	FREE

GP 2010

I installed the beta version of GP 2010 on my 64-bit Windows 7 pro machine running SQL 2008. It installed without a hitch. I installed the SDK with no problems. I could not get Dexterity installed. I received a simple error message:



So that was a little disappointing but I still plan on taking GP2010 through the upgrade cycle of one of my smaller clients and see what I can discover. The 'new' FRx is also part of my plan - conversion wizard and all. I'm doing all of this on a clean machine dedicated to the beta test of GP2010 and the Office Beta.

Good news! I hooked up the old c-tree ODBC driver and can easily read the .dat files now.

This should be a fun process, especially since I have a machine just for testing.

Until next post :)

Leslie

System Preferences Option GP 10

Setting up a new installation recently I wanted to prevent both the 'Metrics' and 'Outlook' sections of a new user's home page from loading. I've read solutions about running a SQL statement against the Users Master (SY01400) in the Dynamics database, but I wanted it to prevent it from happening in the first place rather than going back and changing it after the users had been set up.

The solution is found in the System Preferences window. To navigate to this window select the Administration button on the Navigation Pane to open the Administration Area Page. In the Setup content pane of the Area Page expand the System menu. Near the bottom of the System list is 'System Preferences'. This screen shot is pretty small, but it shows the general area.



In the lower half of the System Preferences window there are three check boxes. Your options are 'Load To Do: Reminders', 'Load Microsoft Office Outlook' and 'Load Metrics'. Unmarking a checkbox will prevent the selected area from loading on a new user's home page. Note that the 'Load To Do: Reminders' will only prevent the 'Reminders' content to be suppressed, the 'Tasks' content will still display. The section heading will still appear for the unmarked content. But the content will be empty. The System Preferences window is illustrated below:



A new Home Page with each of the checkmarks unmarked looks like this:



Until next post!

Leslie Vail

DEXTERITY class May 10th in Orlando

I am happy to announce that the Dexterity I class is on the schedule for the week of May 10th, 2010 in Orlando, FL.

This class is a lot of fun and will be especially helpful for VBTools programmers. It will teach you just enough Dexterity to make your integrations easier. Officially, it's the Dexterity Basics class which assumes the student has not done any work with Dexterity, but is familiar with programming in general.

At the end of the 5-day class you will know how to create Forms and Windows, write scripts and procedures, and create triggers that interact with the GP business logic. During class you will create a functioning 3rd party integrating add-on.

Most of all, you will understand how it all works in that block box we call Dexterity dictionaries. You never know, you may like it so much you'll become a full-time Dexterity programmer! We cover a lot of information in 5 days.

The more people we have in the class, the more fun it is; and it is a fun class.

The classroom in Orlando, Florida is one of the most posh training centers in the country. For more information send an e-mail to Roxanna Alveraz at ralvarez@ibgnet.com. Or, go their website at www.ibgnet.com. There you can see all of their class offerings.

I'm not part of the IBG staff, I'm an independent instructor willing to go anywhere and teach anything I can. I would be delighted to come to your office and teach it for your staff. If you want classroom training, IBG is your best bet.

Other classes available include:

Installation and Configuration
Foundation
General Ledger
Payables Management
Receivables Management
Bank Reconciliation
Fixed Assets
Inventory Management
Purchase Order Processing
Sales order Processing
Payroll with or without HR
Modifier with VBA
Report Writer
SQL Server Reporting Services
SmartList Builder
Excel Report Builder
Extender
Integrated Excel Reports
Learning the Dynamics GP Data Model

Until next post!

Leslie

Contact:
cell: 972-814-8550

.net 3.5 sp 1 Year End tax forms OK!

Hello friends,

I'm happy to report that after all of the issues that came up during the SP 4 year end update and .net 3.5 sp 1, the Forms W-2 and 1099 printed perfectly.

We did have a problem with the Reports dictionary, but that was resolved by creating a new reports dictionary and importing the reports from the old dictionary using the Import function within Report Writer. The GL trial balance report was DOA and had to be redone. Most of the previous customizations, which were mostly calculated fields, could be copied from the package files created as backups. At least all of the SOP and POP reports came through the update error free.

So, at the end of the day each of the workstations were using build 324 of both the Dex.dic and Dynamics.exe files. From now on I'm going to jump right to this configuration. I will test it again on 24 workstations over the next week or two.

I'll let you know what happens.

Until next post!

Kind regards,

Leslie Vail