Tuesday, September 22, 2015

Project In One Company, Not All Companies

So, back in the day, Project Accounting had its own purchase order window.  And we all moaned and complained about this.  And we rejoiced when it was combined in to the standard purchase order window.  But little did we appreciate the complexities that this would bring. 

These complexities are illuminated when you try to use Project Accounting in one company but not all companies in your installation. This post focuses on the issues you will encounter in non-project accounting companies. Once Project Accounting is registered, you will begin receiving messages when accessing the Purchase Order Entry window, like 'Purchase Order Processing Setup Information is Missing or Damaged'.  Ugh.  And then, once you resolve that, every vendor you select when entering a purchase order will be greeted with 'Project Accounting information for this vendor does not exist. Do you want to add the vendor's project information?'.  Double ugh.  And keep in mind, these errors are encountered in companies where users DO NOT have access to the alternate project accounting windows.

On a side note, I am not exploring the Dynamics.Set file hack (having a separate non-Project Set file) in this post, as I generally am not a fan of separate Set files due to the potential for confusion and issues.  But to each their own :)

So, what can you do...

For the first message related to Purchase Order Processing Setup, you will need to (at least temporarily) grant access to the alternate Purchase Order Processing Setup window for Project Accounting (Setup-System-Alternate/Modified Forms and Reports ID).  Once you have access, go to Setup-Purchasing-Purchase Order Processing.  Click the Project button, click OK, and close the Purchase Order Processing Setup window.  At this point, even if you remove access to the alternate setup window, the first error messsage regarding setup information will be resolved.

But the vendor message will persist.  And yes, as I mention above, you can have users simply say NO and continue on with the entry.  But the pop up is indeed annoying.  So you have a few options...

First, you have to address all existing vendors at the time of registration of project accounting.  To do this, you can check out the script here that will do it automatically:

http://kbase.dyntools.com/home/project-accounting/project-accounting-information-for-this-vendor-does-not-exist)

Then, you need to decide how you want to handle new vendors added to GP.  This is where the options come in to play.

Option A- Grant access to the alternate Vendor Maintenance window for Project Accounting.  This would be the ONLY alternate project accounting window that the users will need to access.  This way, when they save a new vendor, it will automatically save the project info as well (which will prevent the prompt to add project info when entering a purchase order for the vendor).

Option B- If Option A is not possible (for example, if you have another product that has an alternate Vendor Maintenance window as well that users need to access), then you can either schedule the script to create the project info to run every evening or look at some VBA code to populate the project info even when the alternate window is not used.

Please feel free to other options/workarounds you have found as well to avoid these headaches!



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.

Tuesday, September 15, 2015

Microsoft Dynamics GP 2016 should go meatless

By Steve Endow

Roving Dynamics GP reporter Tim Wappat travelled to London today to get a sneak peek at a very early version of Dynamics GP 2016.  He posted an article on his blog:

http://www.timwappat.info/post/2015/09/15/GP2016-looking-sweet-in-HTML5

One of the interesting side notes that he mentions is that the "hamburger icon", or "hamburger menu" was being used, for the time being, in the pre-release GP 2016 web client.


The demo that Tim saw was obviously pre-pre-pre-Release, and everything is subject to change, but if Microsoft is even considering using the hamburger menu, I beseech them to reconsider.

I admit that I didn't know the name of that three-bar icon until a few weeks ago--which is when I read this very convincing article on why the hamburger menu is a horrible UI element.

http://deep.design/the-hamburger-menu/

The article is very convincing in its explanation of why the hamburger menu is detrimental to application navigation, and then it goes on to cite numerous examples and statistics to support its assertion.

Here is another article with the same conclusion and more stats:

https://redbooth.com/blog/hamburger-menu-iphone-app

After reading about the design deficiencies, I now notice how annoying the hamburger menu is on my iPhone apps--I am looking for settings and options and features, but can't find them--until I realize there is an innocuous little three line icon in the corner that I have to click to expand a menu.  It's a great way to hide features so that your users never use them.

While there may be some situations where limited use of the hamburger menu may make sense--rarely used settings or infrequently used windows, it definitely should be used very sparingly.  But based on the articles above, it should be replaced with some other menu design.

Save the cows.  Get rid of the hamburger.


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



Turn off all Dynamics GP posting reports in the Fabrikam test company

By Steve Endow


Update: Victoria Yudin correctly noted that there are times when posting reports are helpful or required, perhaps for training or identifying errors.  Absolutely--posting reports are definitely necessary in certain situations, even in Fabrikam.  I chose to turn off the posting reports in my test company because I am constantly doing repetitive testing of batch posting for my projects.  I know that my data is fine, the transactions are fine, and everything posts fine, so in my situation, I don't need the posting reports or the report dialogs, and I turn everything off to avoid having to click Cancel constantly.


It's pretty sad when you know how many times you have to click the cancel button on the report dialog boxes after you post a certain Dynamics GP batch.

Finally, my laziness wanting to avoid constantly clicking a Cancel button finally overtook my laziness to disable all posting reports, and I finally spent 10 seconds to create the script to turn off all posting reports in Fabrikam.

UPDATE TWO..SY02200 SET PRNTJRNL = 0


There.  Done.

I now need to make it a standard part of the GP installation process after I create the Fabrikam company.

Batches and posting reports...two anachronisms of the ERP world that GP will probably never abandon...

Ask a NetSuite consultant about their "batches" and "posting reports" if you want to see some puzzled reactions.

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



eConnect error: hexadecimal value 0x00, is an invalid character

By Steve Endow

A client emailed me regarding the following error they were receiving with a Dynamics GP 2010 eConnect SOP Invoice integration.

eConnectException: '.', hexadecimal value 0x00, is an invalid character. Line 5, position -248.

Based on the error message, it appeared that there was some type of invalid character in the CSV source data file.  But when we looked at the CSV file in a text editor, everything looked fine.

So I ran the integration in debug mode in Visual Studio, and when the eConnect insert failed, I checked the XML data that was submitted.  The results were interesting.

Trans Number:         205112482
Borrower:             THOMAS JEFFERSON
                      VIRGINIA COLONIES
Title Company:        & #x0 ;
Commitment Number:    & #x0 ;
File Name:            & #x0 ;
Lien Type:            FIRST
Loan Type:            CONVENTIONAL

(I've had to add spaces to the hex string so that Blogger doesn't strip them out)

The text above is a long comment string that is being inserted with the SOP invoice header.  It seems that there is an odd hex character in three of the field values.

I had to look up that hex character, and learned that it is the hex value of the null character.  Apparently eConnect is not a fan of the null character, so that is causing the error.


This is all that I see when I open the file in UltraEdit and view in text mode.  Looks fine.

But if I view the file in the UltraEdit HEX mode, I see this.


Notice the "00" values in the data on the left, and the period in the representation on the right.  That's our problem.

So how do we deal with these rogue null characters?  Ideally, the source data file would be corrected so that they are not inserted in the file in the first place.  But in case they do show up in the data file again, it's fairly easy to remove them using Regular Expressions.

So I added this simple function to strip the null character from the field value.

public static string RemoveNull(string input, string replaceWith)
{
    string output = Regex.Replace(input, "\x00", replaceWith);
    return output;

}

Once I applied the RemoveNull function to the invoice comment text, the invoices imported fine and the error went away.

I've never encountered the "null" character before, but fortunately it was a pretty easy fix.

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








Monday, September 14, 2015

Design Specifications- What, How, Yet, How Much?

I know we have all been there.  You (or your team member) create a fabulous customization, report, or interface.  Or, at least you think it’s fabulous.  But once it’s delivered to the client, the issues begin…

  • -          It doesn’t work the way it we thought it would
  • -          We need this additional work for it to meet our needs
  • -          We thought we already explained this

And the list goes on and on and on.  I had a webinar last week for GPUG about creating specifications for customizations, reports, and interfaces, and I thought I would share some of that content here as well.  I continually am reminded of the importance of the spec process, it seems like every week.  And if I let it slide, either because I think it’s not a big deal or the client is resistant to committing to a spec document for whatever reason, the universe almost always course corrects me at some point in the project (e.g., budget overrun, client dissatisfaction, etc).

So we all know the reasons to create specs, right?
  • -          Reduce risk
  • -          Ensure clear communication and expectations
  • -          Accurately scope work
  • -          Create a better outcome in terms of design and client response

But what are the key components of a successful spec?  Now, I completely understand that this list will vary based on the client, the developer, and the specifics of the customization/report/interface being addressed.  This list simply serves as a starting point checklist of the normal “must-haves”:
  • -          WHAT is the spec for? A functional/plain language description of the problem to be solved.   Visual flowcharts/mockups as necessary to make the point.  Include any caveats/assumptions/risks to be clear up front.
  • -          HOW do you plan to address the need? The technologies/tools/methods to be used, including prerequisites for deployment.   At a minimum a high level technical design, but could be more detailed including fields, calculations, and needed logic.  Whether it is high-level or detailed depends on what you need to be able to accurately scope it out, and how involved the project may be.  If it is a large project, the initial spec may have a high  level technical design with a statement that a full technical design is included in the estimate and may change. On smaller projects, a full technical design may not take much time and allow you to better scope/estimate the project.
  • -          YET, what might be the hurdles? Always note any exceptions, assumptions, or outstanding questions that might impact the estimate and/or complexity of the project. It’s okay to have outstanding items, as long as they are documented and ultimately addressed in the project.
  • -          HOW MUCH will this cost?  And don’t just focus on development costs, make sure you include project management, design, testing (unit, function, process, and supporting end user acceptance testing) in your estimate.

I love getting all of this down on paper, I just do.  And it is great when a client fully commits to the process, and we can make sure we are all on the same page before work starts.  But, as with anything in this line of work, you can’t make the perfect the enemy of the adequate at times.  So although a spec is important, don’t let it become the bat with which you bully others (either your team members or the client).  Try to remember the intention of the spec, and approach it with good faith.   Things may change as the project proceeds, and the client may grow in their understanding of their own needs.  Of course, assess the impact of these changes on your budget and timeline, but also know that a minor change with no measurable impact can be addressed without a lot of hub-bub (assuming that the necessary decision makers are aware).

Naturally, everything above is just my opinion.  I would love to hear from you all regarding your “must-have’s” for successful spec documents, and your challenges (and successes) with applying these guidelines to projects.


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.


Friday, September 11, 2015

Dynamics GP Server field is blank due to changes in Dex.ini file

By Steve Endow

A customer was having an issue with her "Server" field being blank when she launched Dynamics GP.


I previously wrote about a similar situation where a space at the beginning of the DSN name could cause this issue.  But in the case of this client, the DSN does not have any spaces at the beginning of the DSN name.

Yesterday I had a call with the customer and we resolved the issue by launching GP, selecting the Dynamics GP 2013 "Server" value, logging in to GP, and then closing GP.  The next time she launched GP, the Server field had a default value.  Problem solved.

But she emailed me again today, saying that the problem came back--the Server field was blank again.

I asked her to email me her Dex.ini file.  When I reviewed the file, I found this:

SQLLastDataSource=DYNAMICS GP 2013

There was a value for the last data source, but it was in all caps, which seemed odd.

I opened the Dex.ini on my GP server and changed the SQLLastDataSource to all caps and launched GP.  And here is what I saw.


Sure enough, that was the cause of the problem.

So it seems that in addition to the GP login window not liking spaces, the Server field value that is read from the Dex.ini is case sensitive.  If the window doesn't find an exact match in the list of DSNs, including capitalization, it will leave the Server field blank.

Okay, so I can understand that.

But what is weird is that the customer's Dex.ini file is having an all caps value being saved to the SQLLastDataSource setting.  That seems really strange.

There is only one DSN setup on that particular customer machine, and only "Dynamics GP 2013" shows up in the Server drop down list.  So that would seem to indicate that another machine is using the Dex.ini file and writing the all caps DSN name to the file.

So does that mean that some other machine is actually launching GP from a network share?  Or is there some other way that another GP machine can utilize a shared Dex.ini?  I wouldn't think so, but apparently it is happening somehow.  Very odd.

I've asked the customer and their partner to try and track down the problem.

UPDATE: The client informed me that rather than determine the cause of the Dex,ini changes, they have just changed the DSN on the machine to be all caps to match the mystery value that is being written to the Dex.ini.  So I guess I'll never know the cause...


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





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.

DBCC USEROPTIONS

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:

ALTER LOGIN [sa] WITH DEFAULT_LANGUAGE = [British];

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:

ALTER LOGIN [sa] WITH DEFAULT_LANGUAGE = [us_english];

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:

SET DATEFORMAT mdy

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.


9/10/2015 UPDATE: David Musgrave offered some sage wisdom in the comments. He explained that if you use the date format of YYYYMMDD in SQL queries, you avoid these issues.  Sure enough, this query works regardless of the Default Language setting.

SELECT * FROM TWO..PM30300 WHERE DOCDATE BETWEEN '20170115' AND '20170331'



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