Thursday, July 24, 2014

C# is great, but can be very annoying

By Steve Endow

I just had a call to review an error that was occurring with an integration that I had developed.

The integration was adding records to a Dynamics GP ISV solution and while importing a large data file, it reported 6 errors.  The ISV solution simply returned the generic .NET / SQL error indicating that a "string or binary data would be truncated."

The ISV has almost no documentation on their API, and the error message didn't tell us which field was causing the problem, so we reviewed the records that were getting the error, and saw that the customer names were very long.  We assumed that a long company name was the problem.  Looking at the ISV tables, I found that it only allowed 50 characters for the company name, unlike GP, which allows 65 characters.

Fine, so now that we found the likely issue, I had to modify my import to truncate the company name at 50 characters.

I opened my C# code and quickly added Substring(0, 50) to the company name string, and tested that change, wondering if it would work.  C# developers will probably guess what happened.

"Index and length must refer to a location within the string"

Lovely.  Okay, so off to Google to search for "C# truncate string".  That search led me to this StackOverflow discussion of the topic, where I saw, thankfully, that I wasn't the only one with this question.  Although I found a few solutions to the problem, what was very disappointing was that there was even a discussion of the topic and that there were multiple solutions to the problem.

Seriously?  A modern business programming language can't just truncate a string?  We have to measure the length of the string and make sure that we send in a valid length value?  That's like selling someone a Corvette and then telling them they have to adjust the timing of the engine themselves if they want to go over 50 mph.  Why in the world haven't they provided a means of natively truncating a string?

As one post on the StackOverflow thread points out, the Visual Basic Left function provides such functionality without throwing an error, so why does C# insist on making developers across the world all write their own quasi-functions to perform such rudimentary tasks?

How about the C# "Right" string function?  Oh, you mean the one that doesn't exist?  Ya, that one, where you have to create your own quasi-function, or yet again reference VisualBasic to perform a simple, obvious task.

It's just silly.  A purist says something like what was mentioned on the StackOverflow thread:  "That's probably why the member function doesn't exist -- it doesn't follow the semantics of the datatype."  Seriously, that's the explanation or justification of why this modern language is wearing polyester bell bottoms?  The semantics of the datatype?  As in the string data type, where people actually need to truncate strings?

Don't get me wrong, I really like C# and now prefer it very much over VB, but when I come across annoying gaps in functionality like this, it just makes me shake my head.


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



Have you registered for reIMAGINE 2014 yet?

By Steve Endow

Is there any spot on the planet more inviting, more bucolic, more exciting, or more of a global destination than Fargo, North Dakota in November?

Nope.

On November 9-12, 2014, the world will be watching as Dynamic Partner Connections hosts the reIMAGINE 2014 conference at the posh Holiday Inn Fargo, the premier resort and spa for Fargo travelers.

There will be dozens of sessions, jam packed with Dynamics GP goodness, Microsoft campus tours, and even a cocktail reception.  Did I mention that the Holiday Inn has an indoor pirate ship water park?  Seriously, no joke.  Where else can you experience an indoor pirate ship?  Imagine the stories you'll be able to tell when you get back from the conference.

My fun story is that at the GP Tech Airlift conference in Fargo two years ago, there were TWO different fake bomb threats.  Yes, in Fargo.  One was at Fargo's world famous Hector International Airport on the day before the conference, which prevented anyone from entering the airport and stopped all departing flights.  Fortunately, they were letting people leave the airport, but the catch was that there were no rental cars, since nobody else could come back to the airport to return their rental cars!  Luckily, I was getting a ride from a clever colleague who bribed the woman at the rental car counter, who then snuck us the keys for the last car on the lot--a top of the line minivan (aka the Fargo party bus).  The second bomb threat was at North Dakota State University on the day after the conference.  Fargo police blocked off a perimeter around the entire University for several hours, so we had to postpone our visit and come back after lunch.  Even then, all of the doors were locked for another hour, so we had to hang out for a while until they got the all clear and let us go shop in the bookstore.  Seriously, when was the last time you got to experience TWO bomb threats in one week?  Didn't I mention "excitement"?

And you'll only be able to tell those captivating stories if you attend this exciting launch of the new dedicated Dynamics GP partner conference.  There will be presentations for sales, marketing, consulting, and developer roles, so there will be something for everybody.

So register now at the reIMAGINE2014 web site.  Then book your flight to scenic Fargo and make that hotel reservation.

I hope to see you there!

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



Product Recommendation: SQL Prompt by Red Gate Software

By Steve Endow

If you have been using SQL Server Management Studio for several years, you probably know that its Intellisense feature sometimes doesn't work.  I've had it work well on some machines, intermittently on other machines, and on some servers, it just doesn't work at all no matter what I try.  Even when it does work, it is sometimes slow and not always helpful.

While talking to the famous Victoria Yudin, the developer of GP Reports Viewer, she showed me a handy tool that she uses called SQL Prompt, from the well known company Red Gate Software.

Victoria showed me how it provided fast, reliable, and very full featured Intellisense within SQL Management Studio.  Here's an example.


It is very responsive, so as you type each character, it immediately displays and filters its results.  And in addition to showing you the proposed object names for the word you are currently typing, it also displays additional related information--so in the example above, in addition to listing the RM tables, it displays the fields in RM20201 with their data types.

It also supports "snippets", which are shortcuts that are automatically converted into larger SQL statements with a press of the Tab key.  In this example, if I type "ssf" and press Tab, it converts it to "SELECT * FROM" and then displays a list of tables.


This morning, when I was working on the eConnect AP apply script, I just typed "EXEC taRMApply" and it automatically wrote the parameters for the stored procedure.


That "auto code" feature right there saved me several minutes of tedious typing or copying and pasting of the parameters, and the resulting formatting is clean and easy to work with.

After trying it for just a few days, I'm a believer.  But there are two small downsides.  First, it isn't free--the license is currently $369.  While this seems like a very reasonable price for such a powerful and refined tool, I can understand if that is a little expensive for a typical GP consultant or VAR.

The second issue is one that I face with several specialized tools that I use:  Once you get used to using it and relying on it, you will be frustrated if you have to work on a server that doesn't have it, such as on a client's SQL Server.  I use the UltraEdit text editor, and it drives me nuts when I don't have it on a machine and have to clean up several thousand rows of data.

But aside from those two small caveats, it looks like a great utility.

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


Importing Dynamics GP AR Apply records using SQL

By Steve Endow

I was working with a customer this morning who had recently imported and posted 2,800 AR cash receipts.  Only after posting the cash receipts did they realize that the payment applications for those cash receipts did not import.

Since Integration Manager doesn't allow you to import just payment applications, we had to come up with a way to import the 2,800 AR payment applications.

A few years ago, I developed an eConnect integration that imported AR cash receipts and automatically applied them to open invoices.  But since the client already had the payment application data, that was overkill--we just needed a way to import the applications without any additional fancy logic.

Rather than deal with a .NET application, I figured we could just use the eConnect RM Apply stored procedure--taRMApply.  If we imported the AR payment application data into a SQL staging table, a SQL script could loop through the apply records and call the taRMApply procedure for each.  Sounded easy, but I was afraid there would be a catch somewhere.

So I created an initial SQL script that would read the apply data from the staging table into a cursor, loop through that cursor, and call taRMApply.  It turned out to be surprisingly simple and clean.  And I was able to easily update each record of the staging table to indicate whether the apply was successful, and if not, record the error from eConnect.

Here is the SQL Script that I created, which I have also shared up on my OneDrive in case  you want to download it instead.

The script assumes you have a staging table with a unique row ID, the necessary apply from and apply to values, and an imported flag field and an importstatus field.  You can adjust the table names, fields, and field names to suit your needs.

One last note--the ErrorString output from the taRMApply procedure is going to be an error number.  You can lookup that number in the DYNAMICS..taErrorCode table to get the related text error message.

You will want to test this yourself, and make sure to perform an initial test on a Test database first, but it appears to have worked well.  It took maybe a minute or or two so to apply the 2,800 payments based on an initial test, so it appears to be fairly fast.

To my pleasant surprise, the process went smoothly, and other than a small error in the script that we quickly fixed, it worked well.

If you find any errors in the sample script below, or have any suggestions for improving it, please let me know.


/*
7/24/2014
Steve Endow, Precipio Services
Read RM payment application data from a staging table and apply payments to open invoices

sp_help RM20201
sp_help taRMApply
*/

DECLARE @RowID INT
DECLARE @ApplyFrom VARCHAR(21)
DECLARE @ApplyTo VARCHAR(21)
DECLARE @ApplyAmount NUMERIC(19, 5)
DECLARE @ApplyFromType INT
DECLARE @ApplyToType INT
DECLARE @ApplyDate DATETIME
DECLARE @ErrorState INT
DECLARE @ErrorString VARCHAR(255)

SELECT @ApplyDate = '2014-07-24'

--Get data from staging table
DECLARE ApplyCursor CURSOR FOR
SELECT RowID, ApplyFrom, ApplyTo, ApplyAmount, ApplyFromType, ApplyToType FROM staging WHERE imported = 0

OPEN ApplyCursor

--Retrieve first record from cursor
FETCH NEXT FROM ApplyCursor INTO @RowID, @ApplyFrom, @ApplyTo, @ApplyAmount, @ApplyFromType, @ApplyToType

WHILE @@FETCH_STATUS = 0
BEGIN

       --Perform apply
       EXEC dbo.taRMApply
              @I_vAPTODCNM = @ApplyTo, -- char(21)
              @I_vAPFRDCNM = @ApplyFrom, -- char(21)
              @I_vAPPTOAMT = @ApplyAmount, -- numeric
              @I_vAPFRDCTY = @ApplyFromType, -- int
              @I_vAPTODCTY = @ApplyToType, -- int
              @I_vDISTKNAM = 0, -- numeric
              @I_vWROFAMNT = 0, -- numeric
              @I_vAPPLYDATE = @ApplyDate, -- datetime
              @I_vGLPOSTDT = @ApplyDate, -- datetime
              @I_vUSRDEFND1 = '', -- char(50)
              @I_vUSRDEFND2 = '', -- char(50)
              @I_vUSRDEFND3 = '', -- char(50)
              @I_vUSRDEFND4 = '', -- varchar(8000)
              @I_vUSRDEFND5 = '', -- varchar(8000)
              @O_iErrorState = @ErrorState OUTPUT, -- int
              @oErrString = @ErrorString OUTPUT -- varchar(255)

       --Check for success
       IF (@ErrorState = 0)
              BEGIN
                     --If apply was successful
                     UPDATE staging SET imported = 1, importstatus = '' WHERE RowID = @RowID
              END
       ELSE
              BEGIN
                     --If apply failed
                     UPDATE staging SET imported = 0, importstatus = @ErrorString WHERE RowID = @RowID
              END

       FETCH NEXT FROM ApplyCursor INTO @RowID, @ApplyFrom, @ApplyTo, @ApplyAmount, @ApplyFromType, @ApplyToType

END

CLOSE ApplyCursor
DEALLOCATE ApplyCursor

 

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 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.