Oddity with decimal behavior on custom Modifier currency fields

By Steve Endow

This is another one of those incredibly obscure quirks that I seem to have a talent for finding.  It isn't a huge deal, but for a heavy GP user who does a lot of data entry, I can see it being very annoying.

So my customer wants to add several custom fields to the Item Maintenance Options window.  A few of those fields need to be numeric, with 2 decimal places, to store a measurement, such as 8.25 inches.  (I don't recall why they didn't choose to use Extender instead of custom fields.)

So I added the fields, got the code working to read and save the custom fields, and then tested the fields.  While testing, I noticed something odd.

If you enter data into a 2 decimal currency field in Dynamics GP, when you type the first number, it will appear in the "cents" location--the second number after the decimal.  As you type additional digits, the numbers will append on the right, and field will fill to the left.

But, after you type one or more digits, you have the option of typing a period, or decimal.  When you do that, the digit(s) already in the field will jump to the left of the decimal, and the cursor moves to the position just right of the cursor, allowing you to type the numbers after the decimal.  Basically the decimal is always displayed in a fixed position, and you end up typing around it.

It's an unusual behavior that I don't think I've seen in any other application.  It's a bit tedious at first, but once you get used to it, it seems to work, and theoretically can allow you to save a keystroke by not typing the period.

While testing the custom fields I had added, I noticed that one field was behaving "normally"--I could type a decimal, and the numbers would jump to the left of the decimal.  But in other fields, when I typed a decimal, nothing would happen.

Here is a demonstration of the behavior: (animated gif)

When I type 1 in the Cubic field and then press period, the 1 jumps to the left of the decimal.  But when I type 9 in the Length field, and then press period, nothing happens.

The fields are the same type and use the same format, so this behavior is puzzling.  After trying several different formats, thinking that might be the cause, I tried one last change.

The only difference between the Cubic field and the Length field is the Keyable Length property.  Cubic allows 6 characters, while Length allows 5.

So I tried changing the Length field to have a Keyable Length of 6, just like the Cubic field.  Once I did that, the decimal behavior started working, and it behaved just like the Cubic field.

I tried several different Formats and that didn't help, so I don't think that is the cause.

Anyway, one more obscure and strange tale to add to the tome of Dynamics GP.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Create multiple SQL Server tempdb files to improve performance

By Steve Endow

While researching some performance issues at a Dynamics GP customer, I noticed that the SQL Server tempdb had been setup with four separate files.  I hadn't seen that before, so I looked it up, and apparently it's a thing.  I would have thought I would have come across this info before, but I'm pretty sure it's been a very, very long time since I looked into tempdb optimization.

https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

• Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for anyaffinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.
• Make each data file the same size; this allows for optimal proportional-fill performance.
• Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
• Put the tempdb database on disks that differ from those that are used by user databases.

But, this guy offers a slight counterpoint to the simplified guidelines above.

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/


His points get pretty technical, so I don't follow all of the details.  But it seems that multiple tempdb files are generally a good idea, so you may consider monitoring performance before going nuts and creating 8 tempdb files.

And yes, the fact that I'm reading this stuff, with interest, after 8pm on a Thursday evening does concern me as well.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

SQL Server 2014 TempDB performance changes

By Steve Endow

This post is for hard core SQL Server geeks...

I was just searching for something, and as often happens, 5 clicks later I have completely forgotten what I was searching for, but I stumbled across this obscure gem.

https://blogs.msdn.microsoft.com/psssql/2014/04/09/sql-server-2014-tempdb-hidden-performance-gem/

It discusses a change to how SQL Server 2014 handles writes to the tempdb database.  Apparently this change can result in a dramatic performance improvement for certain short operations involving tempdb.

Here is the article where I found it, discussing a performance load test of SQL 2012 vs. SQL 2014 to measure how the feature affects performance.

https://www.brentozar.com/archive/2014/04/memory-ssd-tempdb-temp-table-sql-2014/

This blog post indicates that some of the performance improvements have been back ported to SQL 2012 SP1 CU10.

Enjoy!

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Enable scripting of table indexes in SQL Server Management Studio

By Steve Endow

If you create custom tables for Dynamics GP customizations or have situations where you need to backup and recreate tables via SQL script, you've probably used the "Script Table" feature in SQL Server Management Studio.

But if you have ever scripted a table, you have probably noticed that by default, SQL Server Management Studio does not include table indexes.  This is a pretty big omission.

You can change this behavior in Management Studio Options, but I always forget where to find the setting and end up spending 5 minutes every time trying to find the setting.

So, documenting the location for posterity, it is under Tools -> Options -> SQL Server Object Explorer -> Scripting.

Set Script Indexes to True.

While you are at it, you may want to also enable "Check for object existence" and "Script permissions" as well.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

The complexities of reproducing Dynamics GP timesheet calculations

By Steve Endow

Many years ago, I developed a small product for Dynamics GP called PA Timesheet Recalc.  It solves a slightly obscure problem for companies that use Dynamics GP Project Accounting Timesheets for salaried employees.

I regret developing it for a few reasons, but the biggest reason is that supporting a Dynamics GP Project Accounting customization is a pain.  It seems that every single customer uses Project Accounting slightly differently, so I've had to make several adjustments and a few customizations to support the oddities of customers' PA processes.

On top of that general challenge, I ended up with a customization that modifies a pretty complex set of data.  The Project Accounting Timesheet Entry window might look pretty straightforward, but it's like a mathematical circus behind the scenes.

Just one of the many complexities is rounding.  Yup, rounding.

You would think that rounding numbers is pretty cut and dry, stuff you learn in elementary school.  But if you've been developing for very long, you should know that there are several types of rounding, and the sequence of rounding affects calculations.

Here's a simple example.

Let's say that Jane's Unit Cost is $29.45, and her overhead is 17%.

If Jane works 2.5 hours, what is the total cost?

Well, that's pretty simple:  29.45 x 2.5 x 1.17 = $86.14

If that was your answer, you'd be wrong.

The total cost, from Dynamics GP's perspective, is $86.15.

How is this possible?  Let's break it down.

29.45 x 2.5 = 73.625

73.625 x 1.17 = 84.14125

So using this math, we would round the result to $84.14

But that's not how GP calculates it.  GP performs an interim rounding step.

29.45 x 2.5 = 73.625  ==> Rounded to 73.63

73.63 x 1.17 = 84.1471  ==> Rounded to 84.15

The rounding performed after the first calculation results in a difference of $0.01.

While this difference is not a lot of money, it is the difference between being "right" and "wrong" from GP's perspective.  And if you have larger numbers, the difference can be more than one penny.  And an insidious part of this problem is that you could have 50 timesheet lines that calculate fine, because their values don't happen to trigger this rounding issue.

But if you have hundreds of PA Timesheets with dozens of lines, you will get a pile of differences.

So if you ever have to recalculate Dynamics GP data, be careful and double check.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Learn Something New Every Day- Detail Payroll Activity Tracking Tool

Today I had a client ask for a tool that my coworker, Charles Allen, had blogged about years ago.  The Detail Payroll Activity Tracking Tool is available from MS Dynamics Support, and can be used to create a table in your SQL database that records changes to a variety of payroll related tables.

Normally, when folks need change tracking, we refer them to Rockton's Auditor tool.  It is easy to use, and allows users to set up specific audits based on their needs and then report on the changes in SmartList.

https://www.rocktonsoftware.com/gp/overview/products/auditor

But if you are looking for a no-cost/simple tool for payroll specifically, this might fit your needs.

Check out his post, as the tool is still available:

https://community.dynamics.com/gp/b/gpinsights/archive/2012/08/15/tracking-payroll-master-record-changes

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Small mistakes can cause big headaches

By Steve Endow

Several months ago I developed a GP customization.  It worked fine, the partner deployed it, and then it was tested.  So I thought.

Today, I got a message saying that the customization works for the sa login, but not for a standard GP login.  A standard GP user will get an error that the save failed.

That's a great start, as it tells us quite a bit.  If it works for sa, but doesn't work for a GP user, then there's a 99% chance it's a SQL permission issue.

So I send the partner several GRANT statements to ensure that DYNGRP has access to the custom tables and custom stored procedures.  The partner runs those scripts, but the error persists.

I do a test on my development server, and sure enough, I can reproduce the problem.  It works fine for sa, but doesn't work for a test GP user.

Clearly I made a classic developer mistake.  It worked just fine with Administrator and sa, but obviously I didn't test with a standard user.

So I trace the SQL that is being called and see that it's a call to a stored procedure.  When I run the proc as sa, it runs fine.  But when I run it as a test GP user, I get this error.

(0 row(s) affected)
Msg 15151, Level 16, State 1, Procedure zDP_MEMBIL10000SD_1, Line 34
Cannot find the object 'zDP_MEMBIL10000SD_1', because it does not exist or you do not have permission.

I confirm the procedure does exist.  And I confirm that DYNGRP does have permission.  I also confirm that the custom tables and procs are under the dbo schema, as I've had that issue before.

So everything looks fine.  Objects look fine.  Permissions look fine.

So I then script out the stored procedure from the TWO database to see if it is calling some other object that may be causing the issue.  And after reviewing it, I finally see the problem.

So do you see the problem?

Notice the GRANT statement at the bottom.  It was scripted as part of the stored procedure.  The GO keyword was missing after the last END statement, before the GRANT statement.  So the GRANT statement became part of the proc.

The sa user can run the GRANT statement, but the standard GP user cannot.

Once I updated the script to add a GO before and after the GRANT, the permission issue went away and the customization worked fine.

So a single missing word in a stored procedure just took an hour to research and resolve.

Fun times.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Can't install SQL Server 2014 or .NET 3.5 on Server 2012 R2 with Updates

By Steve Endow

So, like many things in life, what started out as a simple project has turned into a circuitous pain.

I created a new Hyper-V VM so that I could install the shiny new GP 2016.

Easy.  Peasy.  Lemony.  Squeezee.  No problem.

The install of "Server 2012 R2 with Updates" from the MSDN ISO went swimmingly and was done before I knew it.  I got the server configured the way I wanted and RDP'd into it.  Perfecto.

Then I tried to install SQL Server 2014.  Nope.  Can't do that.  You, good sir, need .NET 3.5.  Oh goodie.

So I tried to install .NET 3.5 through the Windows Server Add Roles and Features Wizard.

But it complained:

The source files could not be found. 
Use the "Source" option to specify the location of the files that are required to restore the feature.

After trying a few things to work around the problem, I found that it is a bug that was introduced in a Server 2012 update.

See this KB for more info:

https://support.microsoft.com/en-us/kb/3005628

After downloading a mysterious EXE file and running it, you will receive absolutely zero feedback, status, or messages indicating whether it installed or worked properly.

I tried to install .NET 3.5 again, but no joy.  So I rebooted the server and tried again. It still gave me the same error, but I clicked Next and it appeared to have installed .NET 3.5 successfully.

I then tried to install SQL Server 2014 again, but the install froze up.  After yet another server reboot, the SQL 2014 Setup finally behaved itself.

Ugh.  All of this just to get ready to install GP 2016...

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Variable Fees and Project Accounting

Project Accounting allows you to configure fees on a project by project basis, by entering the fees in Cards..Project..Project..Fees. Unfortunately, these fees are bound by a couple different constraints when you go to bill:

1.  Only the amount entered on the card can be billed

2.  The default amount when you bill will be based on how the fee was scheduled on the card

So it requires a fair amount of foresight, and isn't the friendliest method if your fees may vary over each billing cycle.  Additionally, recording fees in this way does not allow you to accrue the fee before billing (unless you use a service fee and/or a fixed price project).

Here  is an alternate method I  use when a client wants to use Time and Materials projects due to their simplicity, but still needs fees to accrue and be variable.

First, you set up a cost category (Cards...Project..Cost Category) with a few specific settings:

1.  The unit cost is zero, profit type is Billing Rate, and the billing rate is $1.00

2.  Populate the Unbilled Accounts Receivable  and Unbilled Project Revenue accounts, as well as the normal AR and Revenue accounts



Then you can assign to a project budget as usual. If the project is set up with a When Performed accounting method, the fee will accrue when entered (that comes next).  If the project accounting method is When Billed, the entry will only influence what is available to be billed.

So to enter the fee, you record a Miscellaneous Log. Enter the amount of the fee to be charged as the quantity on the Miscellaneous Log.  Note that no cost is calculated or posted.  In the screenshot below, the project is set up as When Performed, so note the accrued revenues at the bottom of the entry:

The accrued revenues are posted using the Unbilled AR and Unbilled Project Revenue accounts.  If the project is When Billed, then no accrual occurs but the calculated billing amount (Quantity X $1) will be available to be billed by default in Billing Entry.

Happy project accounting!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.