Friday, June 4, 2010

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

Monday, May 17, 2010

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

Friday, May 14, 2010

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