Wednesday, July 16, 2014

Dynamics GP Web Services does not support importing sales taxes for SOP Sales Orders

By Steve Endow

I just spoke with a customer who is trying to use Dynamics GP Web Services to import SOP Sales Orders.

He needs to import header level taxes for the sales orders, but was unable to do so--he would get an error about the amount being incorrect.  He had read a forum post indicating that Web Services did not support sales taxes, so he didn't know if he was doing something wrong with his import, or if it was a limitation of Web Services.

I recommended that he contact support, as that would be the quickest way to either determine that sales taxes were not supported, or find the issue with his import.

He contacted MS support and confirmed that Dynamics GP Web Services does not support the import of sales taxes for Sales Orders.  Apparently this is true for GP 10, 2010 and 2013.  He was told that Web Services does support the import of sales taxes for SOP Invoices, but not for SOP Sales Orders.

I'm assuming there is some reason for this, but it seems baffling.  eConnect supports the import of sales taxes for orders, so I don't understand why web services wouldn't have similar support for taxes on orders.  And why Invoices are supported but Orders are not is similarly puzzling.

I guess that's one more reason why I won't be using Web Services.  I'm just not a fan.

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



Tuesday, July 15, 2014

Oldie But Goody- Users When Logging in to Test Companies

This is a pretty common issue, but I thought it was worthwhile to post a summary of the issue and the (relatively) easy fix.

First, the scenario...
  1. You set up users in GP (Admin Page-Setup-User)
  2. You don't want users accidentally testing in the live company, so you limit their access to the Test company only (Admin Page-Setup-User Access)
  3. This works great until you refresh the Test database with a copy of the Live database
  4. Now the users can't access the Test company, and receive a variety of errors on the SY_Current_Activity table
This is due to the fact that the users have access to the test DB per the GP setup, but the database itself does not have the users attached to it (once restored from Live).  If you attempt to remedy this in GP through Admin Page-Setup-User Access, you will usually also receive errors if you attempt to unmark/remark access to the Test company.

So, it's a relatively easy three part fix...(as always test this out, have a backup, yadda yadda yadda)
  1. In SQL Server Management Studio, first expand the Test company database, then expand Security, then expand Logins.  Make sure the user in question is NOT listed. If they are, delete the user.
  2. Next in SQL Server Management Studio, expand the overall Security folder, and expand Logins.  Right-click on the affected user and choose Properties.  Then click on the User Mapping page.  Unmark the access to the Test database in the upper part of the User Mapping window.
  3. Log in to GP, and navigate to Admin Page-Setup-User Access.  Select the affected user, and remark the access to the Test company
Now, to avoid this in the first place...
  1. Give users access to both the live and test versions of companies (not just the test versions), Admin Page-Setup-User Access. 
  2. Control their ability to enter in to the Live company by not giving them a security role, Admin Page-Setup-User Security.
Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing consultant 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.

Project Types in Dynamics GP- Not Always What They Seem

My entry point to Dynamics GP, about 14 years ago, was Project Accounting.  It was the first I learned, and I lived the lessons of how hard it is change course once you have something set up.  In my career as a consultant, I have witness many a Project Accounting implementation gone awry.  And in my naive younger days I would judge the prior consultants, thinking how they must not have understood how Project works.  But over time, I have come to understand that the issue often is a matter of definitions and communication.

When I sit down and ask a client to describe their projects, they will often use terms like "time and materials" or "fixed price" or "cost plus".  And it can be tempting in those moments to immediately correlate that to the GP terms.  I mean, they are identical, right?  GP has Fixed Price, Cost Plus, and Time and Materials projects.  But, hold up...

When clients/users describe their projects, they often are talking in terms of how they bill the project (and the corresponding contract terms).  While in GP, the difference in project types is more about revenue recognition and how billing amounts are calculated.  So it is important to dive deeper in to the project type discussion to avoid setting up unnecessarily complicated projects. 

Let's break it down by the attributes of each project type, starting with most complicated to least (in my humble opinion)...

Cost Plus
  • Fee plus allowable (billable) expenses
  • Can include project fee (lump amount), retainer fee (amount paid in advance) or retention (amount withheld from billing)
  • Billing calculated as percent complete (Forecaster vs Actual) based on profit type of budget items
  • Revenue Recognition calculated per accounting method (Based on percent complete, or when project completed).  Will not recognize when billed.
Fixed Price
  • Fixed fee only
  • Can include project fee (lump amount), retainer fee (amount paid in advance) or retention (amount withheld from billing)
  • Billing calculated as percent complete (Forecaster vs Actual) based on profit type of budget items
  • Revenue Recognition calculated per accounting method (Based on percent complete, or when project completed). Will not recognize when billed.
Time and Materials
  • Can include both fees allowable (billable) expenses
  • Can include project fee (lump amount), retainer fee (amount paid in advance) or service fee (fee amount recognized over time)
  • Billing is calculated based on profit type of budget items and scheduled fee dates (not percent complete)
  • Revenue is recognized on expenses and project fees based on the accounting method, either when the expense is posted or when it is billed (not percent complete)
  • Service fees are the only feature of Time and Materials projects to require revenue recognition, and they recognize based on duration (you enter a start and end date for the fee)

As you read through the list, I want you to note specifically the impact of revenue recognition and billing on the project type.  If you do NOT recognize revenue on a percent complete, if you do it when you bill...then your projects may be Time and Materials by GP standards.  If you bill specific amounts at specific times, rather than progress bill based on percent complete, then your projects may be Time and Materials by GP standards.  Perhaps you need to recognize revenue over time, not based on budget -vs- actual, then you may be a Service Fee on Time and Materials in GP.

The lesson here is to not take the terms at face value, and to make sure you understand the impact of the choice in GP (not just what you call it internally, or per the contract terms).

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing consultant 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.

Friday, July 11, 2014

Help!!! My Dynamics GP SQL Server is out of disk space!!!

By Steve Endow

ZOMG!  Your SQL Server only has 8 MEGABYTES of disk space left!!!  What do you do???


Obviously, there are many reasons for a full disk--maybe there are extra database backups, or a bunch of large files that are consuming disk space.

But, if you have considered those obvious suspects and already dealt with them, and your SQL Server data disk is still full, what do you do?

I just had a call with a customer who had 8 MB of free space on a 250 GB drive.  The drive was dedicated to SQL Server and only had databases on it.  Obviously 250 GB is not very large by today's standards, and you could argue that a larger disk would help, but in the short term, we had to deal with the full disk, and as you'll see, 250 GB is plenty of space for this customer's data.

There are a few ways to diagnose the problem, but I like to start with a fantastic tool called WinDirStat.  It scans one or more drives, lists files and folders based on how much space they consume, and also provides a visual representation of the files consuming the most space on the drive.


For this customer, we knew that the 250 GB drive only had SQL Server databases on it, but WinDirStat showed us how much space each mdf and ldf file was consuming.

We saw that there were three databases where the the log file was 19.6 GB, but the database was only 18 GB.  It turns out that one of those databases was the production company database, and the other two were test databases--copies of the production database.  So a backup of production was restored into two test companies, resulting in three copies of the nearly 20 GB SQL log file.

We also saw that the tempdb database was 44 GB!  See my post about Dynamics GP and tempdb if you want to learn more about tempdb.  As we now know, simply restarting the SQL Server service will recreate the tempdb, which would save over 40GB of disk space.

But what about those large log files?  Well, when a SQL log file is larger than the Dynamics GP company database, or even when it grows to several gigabytes, I typically bet that the log file is mostly empty and doesn't need to be that large (for applications other than Dynamics GP, this may not be the case).

Transaction logs are used to record database activity that occurs between SQL Server backups.  As the activity occurs, the transaction log will typically grow automatically as needed.  This is fine.  But sometimes there is a ton of activity--say the customer imports thousands of historical transactions--which causes the log file to grow abnormally large.

When the next SQL Server backup occurs, the log file will be cleared out, but by default, the log file will not shrink.  So the log file may be 20 GB, but only 10 MB of that file may actually be used--it's essentially empty, but it still consumes 20 GB of disk space.

So how can you tell?  There are probably a few better ways, but I like to go into the SQL Server Management Studio Shrink Files window.  Right click on the database, select Tasks -> Shrink -> Files.


When the Shrink File window opens, change the File type to Log.


Notice that the log file in this screen shot is 20 GB, but 99% empty.  This was for a training copy of the Dynamics GP company database.

After clicking OK in this window, SQL Server shrank the log file down to 20 MB, instantly freeing up 20 GB of disk space.  It took less than a second.  While I don't believe that the test database log shrink operation in this particular case had a performance impact, I would recommend that any similar operations on a production database be performed after hours, just in case.

The client repeated this process on another test GP database, and another 20 GB was freed up, producing a total of 40 GB of free disk space.  When they have a chance to shrink the log file for the production company database after hours, that total will increase to 60 GB of free disk space.

And once they restart SQL Server and tempdb is recreated, that should free up another 40 GB, giving them 100 GB of free disk space.

So from 8 MB to 100 GB of free disk space in just a few minutes without having to delete a single file.  That 250 GB hard drive is now looking pretty spacious.

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


Wednesday, July 2, 2014

GP 2013 R2 changes to User Setup window

By Steve Endow

I installed GP 2013 R2 last week and am slowly encountering a few changes and new features.

One change that jumped out at me was the redesigned User Setup window.

GP 2013 SP2:



GP 2013 R2:


Obviously the R2 window has the rather "prominent" ribbon at the top, but aside from that global change in R2, the User Setup window has a few interesting additions.

User Type:  Full vs. Limited.  A Limited user is apparently "restricted to inquiries and reports" only.  And if the user is a member of the POWERUSER role, you cannot change the user from Full to Limited.  As such, you cannot change the sa or DYNSA logins from Full to Limited.

Web client user only:  This allows you to specify that the user is for the web client only, and will use their Windows account to login rather than a SQL login.

There are now two tabs on the window--one for SQL Server Account info, and the other to specify a Windows Account for web client users.

Last, there is a Home Page Role field.  The help file explains:
Assign the user to a user role. The user role determines the content that is displayed on a user’s home page by default.
On my install, that field is disabled, so I don't know if I am missing some configuration options, or if that is a future feature.


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



Unlocking a SQL Server login without resetting the password

By Steve Endow

I had a call with a consultant who was having trouble unlocking a SQL Server login.  While trying to login to GP Utilities with the sa login, he used the wrong password and ended up locking out sa.

Not a big deal, but when he went into SQL Sesrver Management Studio to unlock the sa account, he received this message:


"Reset password for the login while unlocking"

I never use the SQL password policy option on my development SQL servers, so I never lock out accounts, but apparently SQL Server requires that you reset the password in order to unlock the account.

A quick search returned this helpful workaround on MSSQLTips.com:  If you uncheck the Enforce password policy option and save the login, it will disable the account lockout.  He tried unchecking the Enforce password policy checkbox, and was able to login to GP Utilities just fine.  He then went back and re-checked the Enforce password policy box.

I don't understand why the password policy option and account lockout option are linked like that, but I learned something new today.


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



SQL Server WHERE clause wildcards

By Steve Endow

This morning I was working on a SQL query used to create to create new Dynamics GP customer IDs.

The customer IDs are created by using the first 6 characters of the customer name, followed by 3 numbers.  For example:

ACMETO001 - Acme Tools
ACMETO002 - Acme Tooling, Inc.

To query these customer IDs, I was using this query:

SELECT CUSTNMBR FROM RM00101 WHERE CUSTNMBR LIKE 'ACMETO%'

In this example, the query would return two records, so I knew that the next customer ID needed to be "ACMETO003".

This appeared to work fine.

Except this morning, the client found that they had a customer name with less than 6 characters, in which case this query did not work properly.

ACME001 - Acme

If I use the same query, it would look like:

SELECT CUSTNMBR FROM RM00101 WHERE CUSTNMBR LIKE 'ACME%'

Well, the problem is that this query will pick up the ACMETO001 and ACMETO002 IDs as well, and my new customer ID routine would improperly think that this is the next ID:

ACME003

This is not correct--it should be ACME001.

So how do we query all "ACME" customer IDs without including "ACMETO" customer IDs?

I had a general idea, but I hadn't used it in quite a while.  After searching for regular expression options and wildcards, I was reminded that the SQL LIKE clause supports basic regex-like wildcards.

This MSDN page discusses the SQL like clause and its wildcard options.  If you use brackets [ ], you can specify characters that you want to search for, or that you do not want to search for.  Additionally, you can use the underscore character to represent any character.

In my case, I came up with this:

SELECT CUSTNMBR FROM RM00101 WHERE CUSTNMBR LIKE 'ACME[0-9][0-9][0-9]%'

Since I know that the "ACME" customer ID will always have 3 digits on the end, I'm able to use the [0-9] wildcard three times to represent those three digits.  So the query will now pick up all ACME customer IDs, but will not pick up any ACMETO customer IDs.

The % at the end is probably not really necessary anymore, but I left it in the statement for now.

Now go and impress your friends and neighbors with this wonderful SQL trick!


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

You can also find him on Google+ and Twitter