Thursday, September 3, 2015

Musings on some oddities of software purchasing habits

By Steve Endow

In addition to developing Dynamics GP integrations and customizations, I sell a few add-on solutions for Dynamics GP, such as my AP Payment & Apply Import, and Envisage Software's Post Master Enterprise.

Over the last 6 years selling software, I've noticed that people seem to behave differently when purchasing software versus consulting services.

Here are a few observations I've made.

1. Patience:  When people are shopping for, evaluating, testing, and purchasing software (versus consulting services), they seem to be more impatient.  If I am unable to respond to an email or inquiry the same day, I've noticed that many people get impatient and send additional messages or web site inquiries.  "I emailed you yesterday but haven't received any response!".  I try my best to respond promptly, usually within an hour, but sometimes I'm sick, out of the office, travelling, or actually buried deep in code and can't respond the same day.  In general, it seems that people who are working with me on consulting projects are much more patient than the software prospects and customers.  I guess this can be attributed to the prevalence of online shopping for just about everything, and Amazon's same-day and next-day shipping have further heightened our expectations for immediate delivery of products.

2. Trial license key versus final license key:  I always provide trial license keys to customers to allow them to fully test the software before they purchase.  Usually this works out fine, and the customer is able to use the software with their trial license key while they process a payment.  They then receive their final license key before the trial expires, and they have uninterrupted use of the software.  But, somewhat related to point #1 above, there are occasionally customers (and sometimes partners) who are surprisingly eager to get the final license key.  Even though they have 20 days left on the trial, they will suddenly ask to pay for the software ASAP and get the final key ASAP.  I don't mind processing the payment quickly, but these requests puzzle me.  I can only assume that there is some psychological component about a trial vs. final license key that causes this?

3. Credit cards:  Since I started Precipio Services 8 years ago, I haven't had a single customer ask to pay me for my consulting services with a credit card.  Zero.  And I've only had one partner pay me via ACH.  But when it comes to software, a majority of the purchases are paid by credit card.  I have some customers that have purchased both services and software from me--they pay for my services with a check in the mail, but they want to purchase the software with a credit card.  It seems there is a different psychology about how people pay for things vs. services, or perhaps how people purchase software.

I just pulled some payment history, and see that almost 30% pay by check, 7% by ACH, and the rest with a credit card.  Breaking things down a bit further, of customers who purchased the software directly, only about 7% paid with a credit card.  Partners were the exact opposite--only 7% paid with a check--the rest used a credit card.  So that's interesting--GP partners are the primary drivers of the credit card purchases.  But based on my experience, they never pay for consulting services with a credit card.

As a result of these behaviors or trends, I've had to adapt my processes and systems.  I now respond to all of the software related inquiries to get them out of the way first, and then I have to use the remaining time to get my consulting work done.  This is often a challenge and makes it harder to plan my consulting work since the software inquiries and support requests can vary so dramatically from day to day.

I started accepting credit cards in 2010 to accommodate all of the requests, and this year I finally added a payment page to my web site so that customers can pay online without having to fill out a form or call me to process the transaction.  Accepting payments on my web site has been a big hit--customers can submit the payment in under a minute and receive their final license key shortly after. While by no means revolutionary, it seems to be somewhat progressive for the Dynamics GP marketplace.

Anyway, just some observations that I thought were interesting.

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

Unable to run SQL query on GP data with dates - Conversion of a varchar to a datetime error

By Steve Endow

An interesting Dynamics GP query question came up on Experts Exchange.  The user was asking how to select transactions for a given date range from the PM30300 table.  Pretty straightforward--I recommended this query as a start:

SELECT * FROM TWO..PM30300 WHERE DOCDATE BETWEEN '2017-01-01' AND '2017-03-31'

The user tried it, but said that he received the following error:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Puzzling.  I asked him to run this sp_help statement to verify that the GP PM30300 table did have the correct datetime data type for the DOCDATE field.

EXEC sp_help PM30300

The table looked fine and the DOCDATE field was set as a datetime data type.

DOCDATE datetime

Very odd.  So the table looks okay.  The query looks okay.  And I assumed the data in the DOCDATE field was okay.

So why would a query filtering dates give a data type error?

I looked at the error again.  Conversion of a varchar to a date time.  When we use a date filter of '2017-01-01', that is a string, and SQL Server is implicitly converting that to a datetime data type.

So that means that for some reason, when the user sent in the value of '2017-01-01', SQL Server failed to convert it to a datetime.  But that date format obviously works for me, so why wasn't it working for him?

Enter the mess called regional settings.  Start by running this statement to view the SQL User Options settings.


In the results, look at the language and deateformat values.

My settings showed a language of us_english, and a dateformat of mdy.  So with this setting, SQL Server is able to apparently implicitly convert the '2015-07-01' date value to the mdy date format.

But then I ran this statement against the sa login:


This change only takes effect the next time you connect, so you have to close your query and open a new query.  When I do that, here is what I see.

In addition to changing the language, the dateformat changes.

It is possible to change the dateformat value directly, but that change will only persist for the active connection. Once the connection is closed and recreated, the setting will default back to the user options value.

So now that I have set my language to British, which has a dateformat of dmy, what happens when I run my simple query with a date filter?

There ya go.

So it would seem that the user has a language setting other than us_english for their SQL Server login, and that language in turn as a dateformat other than mdy.

The simple fix would be to just run this statement for whatever login is having the issue:


This sets the language to us_english and the dateformat to mdy.  Once that setting is changed and you reconnect to SQL, you should be able to query with a date format of '2017-01-01'.

The potential downside is that there may be other applications that rely on the language value, and dateformat, that may break if you change to us_english.  If you are unable to change the default language setting, you have two options.

You could potentially change the date format you can use in your queries:

SELECT * FROM TWO..PM30300 WHERE DOCDATE BETWEEN '15-01-2017' AND '31-03-2017'

The problem with this is if you have users with different language settings.  If a us_english user tries to run this query, it will fail with the same 'conversion of varchar' error.

Another option is to explicitly cast your date values to datetime:

SELECT * FROM TWO01..PM30300 WHERE DOCDATE BETWEEN CAST('15-01-2017' AS datetime) AND CAST('31-03-2017' AS datetime)

But in my test, even this does not work for users that have different language and dateformat settings.

As a last resort, it looks like this option would work:


This statement would have to be run before every query, as it only persists during the connection.  But it ensures that you are using a known date format for all queries.

This is the first time I've run into this, but since most of my customers are in the US, it isn't too surprising.  

Consultants in other countries may run into this regularly.

Now if only the rest of the world could see the error of their ways and finally start using mdy, inches, ounces, pounds, miles per hour, etc.

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, August 26, 2015

Revenue Accruals in Project Accounting

The conversation about revenue recognition in project accounting for Microsoft Dynamics GP often goes like this...

"How do you do revenue recognition?"

"Oh, we don't.  Just hit revenue when we bill the expenses and fees."

"Oh, okay, so no need for revenue recognition?"

"No.  But we need to accrue at month end for unbilled."

Or, sometimes, it devolves in a conversation of how to use the revenue recognition module to handle the accrual.  Which is neither fun, nor simple. Particularly because GP revenue recognition
effectively calculates one of three ways...

1. Based on contract/project/cost category progress (either by cost, or by hours)
2. When the project is complete
3. Over time when dealing with a service fee (based on duration of service fee)

But many folks don't realize that GP Project Accounting has built in functionality to deal with accruing unbilled revenue (and therefore the offset, unbilled AR as well).

In this case, you would use a Time and Materials Project.  But the difference is that you use a When Performed accounting method.  The When Performed account method, generates a credit to Unbilled Project Revenue and debit to Unbilled Accounts Receivable when a cost is posted to the project.  The amounts posted to these accounts are determined based on the budgeted profit type and project amount/percent.  Then when the expenses are billed, the amounts move from unbilled to actual revenue and AR.  At any point, the unbilled AR and unbilled revenue represent your accrual.

Of course, this works great for expenses, but what about fees?  And the need to accrue them?  You can use this same logic, but instead of using a fee - we use a zero cost miscellaneous log.  So you set up the miscellaneous log cost category with no cost, a quantity equal to the total amount of fee (if known), a profit type of billing rate, and a billing rate of $1.  Yes, $1. So then, at month end, you can enter a miscellaneous log with zero cost, where the quantity is equal to the amount of the fee you want to accrue.  It will then calculate the accrued revenue and accrued AR for the GL posting for you.  This is particularly helpful when the accrual of the fee is not predetermined, but rather calculated based on criteria outside of the system (project milestones, etc). 

The neat part of handling the fees as zero cost miscellaneous logs is that it them goes in to the WIP queue for billing at the amount you want to bill.

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.

Saturday, August 22, 2015

Using the Dynamics GP PM00400 table for Payables Imports

By Steve Endow

I'm developing two different custom Dynamics GP Payables eConnect integrations for two different customers.  One of the shared requirements is to validate the data for each Payables transaction before it is imported.

The import will confirm among other things, that the vendor exists in GP, the GL distribution accounts are valid, the amounts are valid, and lastly, that the transaction does not already exist in GP.  So if Invoice 12345 for vendor ACME is already present in GP, the import should flag the record as a duplicate and skip it.

To check for a duplicate PM transaction, you could check the PM transaction tables directly.  While definitely possible, the downside to this approach is that an invoice or credit memo may be in one of three different tables:  Work, Open, and History (PM10000, PM20000, and PM30200).

All three of the tables could be queried with a single SQL statement, but there is an alternative.  Instead of querying those three tables, you can query the PM00400 "PM Key Master" table instead.

PM00400 should contain every voucher number and vendor document number process by the Payables module, so you should be able to query it to determine if a transaction already exists, and the table should tell you the document status (work, open, or history).

Above is a sample transaction, with each row being a snapshot of the data in PM00400 as the invoice moved from work to open to history.  Note that the CDSTATUS field changes from 1 to 2 to 3, and the TRXSOURCE value is populated once the transaction is posted.

So PM00400 can be a handy option to check whether a Payables transaction already exists in GP, and a quick way to verify the status of the document.

The Receivables module has a similar "RM Key" table, RM00401.

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, August 12, 2015

Adding a Visual Studio snippet for a C# "public string" property

By Steve Endow

When I am developing in Visual Studio and adding properties to a class, by far the most common property I add is a string type property.  When developing for GP, over 90% of my properties are strings.

While I have been using the "prop" snippet to automatically type out most of the property declaration, when you have a few dozen properties, it gets tedious and repetitive having to press specify "string" over and over.

Typing "prop" and pressing tab twice will automatically create a property line ("automatically implemented property").

But, the default data type is "int", so I usually have to press TAB and then type "string", then press TAB again to name the property.

So my typing laziness finally overcame my research laziness and I actually looked up how to create a custom snippet in Visual Studio.  It's pretty easy.  Should have done it years ago.

The location of the snippets may vary by Visual Studio version and other factors, but on my machine, the snippet files are located at:

C:\Program Files (x86)\Microsoft Visual Studio 12.0\VC#\Snippets\1033\Visual C#

I located the "prop.snippet" file, copied it to my desktop, and edited it.

Here is the original prop.snippet.  (the spaces are added to the XML tags so they don't get stripped out by Blogger)

< ?xml version="1.0" encoding="utf-8" ? >
< CodeSnippets  xmlns="" >
< CodeSnippet Format="1.0.0" >
< Header>
< Title >prop< /Title >
< Shortcut > prop < /Shortcut >
< Description >Code snippet for an automatically implemented property
Language Version: C# 3.0 or higher< /Description >
< Author > Microsoft Corporation < /Author >
< SnippetTypes >
< SnippetType >Expansion< /SnippetType >
< /SnippetTypes >
< /Header >
< Snippet >
< Declarations >
< Literal >
< ID >type< /ID >
< ToolTip >Property type
< Default >int< /Default >
< /Literal >
< Literal >
< ID >property< /ID >
< ToolTip >Property name< /ToolTip >
MyProperty< /Default >
< /Literal >
< /Declarations >
< Code Language="csharp" >< ![ CDATA [public $type$ $property$ { get; set; }$end$]] >
< /Code  >
< /Snippet >
< /CodeSnippet >
< /CodeSnippets >

I edited the highlighted items, and removed the lines in red.

< ?xml version="1.0" encoding="utf-8" ? >
< CodeSnippets  xmlns="" >
< CodeSnippet Format="1.0.0" >
< Header >
< Title >props< /Title >
< Shortcut >props< /Shortcut >
< Description >Code snippet for an automatically implemented string property
Language Version: C# 3.0 or higher< /Description >
< Author >Steve Endow< /Author >
< SnippetTypes >
< SnippetType >Expansion< /SnippetType >
< /SnippetTypes >
< /Header >
< Snippet >
< Declarations >
< Literal >
< ID >property< /ID >
< ToolTip >Property name< /ToolTip >
< Default >MyProperty< /Default >
< /Literal >
< /Declarations >
< Code Language="csharp" >< ![CDATA[public string $property$ { get; set; }$end$]] >
< /Code >
< /Snippet >
< /CodeSnippet >
< /CodeSnippets >

Once you have your new snippet, you can go to Tools -> Code Snippets Manager -> Import.  One recommendation when you import--only select one category.  For example, either use only My Snippets, or select only C#, etc., otherwise Intellisense will detect multiple snippets with the same name.

With my new "props" snippet imported, I save a press of the TAB key and don't have to type "string" every time.  The word "string" is now hard coded in the output and it jumps straight to the property name.

If you code properties, I recommend looking into customizing your snippets.  And I'm sure there are tons of other cool things you are do with snippets, but for now, my typing laziness is content.

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 name limitation with GPConnNet and VS Tools

By Steve Endow

I previously wrote about a situation where the Dynamics GP GPConnNet library is unable to connect to a SQL Server instance if a port number must be specified.

This week I encountered a new limitation with GPConnNet and VS Tools.  A customer has been successfully using a Dynamics GP AddIn for several years, and they are now upgrading to GP 2015.  When they tried to test my AddIn on their GP 2015 test server, they received this error.

The error message says GP login failed.  But since this is a VS Tools AddIn that runs inside of GP after a user has logged in, that message doesn't make much sense.  We know that the username and password are correct.  Very odd.

We then noticed that the server name was incomplete.  The final "T" in the name was missing, and the value displayed is exactly 15 characters--more than a coincidence.  So it looks like the 16 character server name is being truncated to 15 characters, and that is likely the cause of the problem.

But wait!  If the server name is being truncated, then the server name would be incorrect.  And when the AddIn attempted to connect to that non-existent server to authenticate, the connection attempt would fail, right?  The error message would be different for a connection failure.

So back to the original error message.  It says "GP login failed", not "failed to connect" or something similar.  So this would seem to tell us that the connection was successful, but that the login subsequently failed.

What in the world?

But it gets better.

If the customer logs in to Dynamics GP using the 'sa' login, the AddIn works and does not give the "GP login failed" message.

So the sa account works, but GP users don't work.  What does that tell us?  In theory, it is a confirmation that the AddIn connection process is working, but that there is something about the GP logins that is failing.

So why would sa work, but not a GP user login?

My guess is Dynamics GP password encryption.

When you create a new user in Dynamics GP, it "encrypts" the password before sending it to SQL Server.  This prevents a user from connecting directly to the SQL Server.

My guess is that GPConnNet uses the SQL Server name in the "encryption" process, but it is truncating the server name at 15 characters for some reason, and that is the cause of this issue.  Presumably Dynamics GP does not do this, since my client is able to login to GP just fine.

So how do you work around this issue?

The best option is to make sure that your SQL Server instance names are no more than 15 characters.

The only other option I was able to come up with was to have the client create a shorter SQL Server Alias.  I then had to hard-code that shorter alias name in my AddIn.  Once I hard coded the shorter alias for the server name, the AddIn worked fine.

Why hard code, you ask?

Well, VS Tools uses the Dynamics GP Backup / Restore form in order to get the name of the SQL Server.  Even if the Dynamics GP ODBC DSN is set to use a short alias name, the Backup / Restore window will return the actual SQL Server name.  So even after the Alias was setup and the GP ODBC DSN was using it, my AddIn was still receiving a SQL Server name of MCCGP15DB01-TEST, and the login would still fail.  Fortunately, they only have this issue with their Test database server--their GP 2015 production SQL Server has a shorter name.

So, like I said, just make sure your SQL Server instance names are 15 characters or less if you are using GPConnNet.

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 22, 2015

Dynamics GP VS Tools Reference Quirk: Microsoft.Dexterity.Bridge

By Steve Endow

I'm developing a Dynamics GP VS Tools AddIn and noticed an odd behavior.

I was trying to access the Dynamics Globals object properties to get the current company ID and system database name and store it in my Model object.

Controller.Instance.Model.GPCompanyDatabase = Dynamics.Globals.IntercompanyId;
Controller.Instance.Model.GPSystemDatabase = Dynamics.Globals.SystemDatabaseName;

I have these two lines in another project, so I copied them to my current project.

Intellisense recognized Dynamics.Globals and let me choose the two properties, but I was getting an error about type conversion to a string.

Since I have used these exact lines previously, I suspected something wasn't right with my current project.

I had a reference to Application.Dynamics, and I had this using statement:

using Microsoft.Dexterity.Applications;

Since Dynamics.Globals was being picked up by Intellisense, it seemed like my references were okay, but obviously something wasn't quite right.

Another odd thing I noticed was that if I typed a period after SystemDatabaseName or IntercompanyId, I wasn't getting an Intellisense pop up of options.

So something was wrong--clearly Visual Studio wasn't able to determine the data types for those properties.  I was able to use String.Convert to bypass the error, but it bugged me.  It seemed like there was some type of issue with my Application.Dynamics reference.

After checking my other code and trying various things, I finally stumbled across the solution.

I needed to add a reference to Microsoft.Dexterity.Bridge.

Once I added the Bridge reference, Intellisense stopped complaining about the type conversion, and I was able to get Intellisense values for SystemDatabaseName and IntercompanyId.

Only after looking at it again today did I realize that a big clue was staring right at me.

The error was indicating that it was a Dexterity Bridge data type, but I didn't think to look at that detail, and probably only in hindsight was this clue helpful.  But it explains why Bridge is required, and now I know to reference both libraries!

Happy bug hunting!

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