eConnect will import data with leading spaces: Why should you care?

By Steve Endow 

 One fine day, you joyfully work on a simple Dynamics GP customer query.  You just want to look up the record for a customer.

SELECT * FROM RM00101 WHERE CUSTNMBR = 'WEB001'

Couldn't get much simpler.

But when you run the query, you get zero results.

Hmm, that's odd.  You open GP to verify the customer exists.

Yup, there it is in the correct company database.  You double check your query and make sure you are querying the correct database in SQL Server Management Studio--and everything looks okay.

So, what is going on?  How can the customer exist in GP, but not show up in a simple SQL query?

Look a little closer at the customer record in GP.

Well, there's the problem right there.  Do you see it?  

When entering data directly into Dynamics GP, if you try and type a space as the first character in any field, nothing happens.  You just can't enter a field value that starts with a space in the GP user interface.

But I have discovered that eConnect doesn't have such a restriction, and it will dutifully import data values that begin with a space.  It doesn't seem like a big deal, until you try and query that data.

In SQL, these two queries are different:

SELECT * FROM RM00101 WHERE CUSTNMBR = 'WEB001'

SELECT * FROM RM00101 WHERE CUSTNMBR = ' WEB001'

The leading space on the customer ID makes all the difference.  With the first query, I get no results.  With the second query I find the record that has a leading space on the customer ID.

Honestly, I don't know that I actually realized this distinction previously--it just isn't something I have had to try, use, or deal with.  Trimming white space is such second nature for a developer that I can't remember ever thinking about it.  It seems obvious in retrospect, but I think it's just one of those incredibly elementary assumptions that becomes invisible to you after so many years of development. 

When you open the Customer lookup window, you can see that a leading space also affects sorting.

I made up this specific example to demonstrate the issue with eConnect and GP.  In all of my integrations and code, I habitually use a Trim() command on all strings to trim leading and trailing white space, so I was actually quite surprised that eConnect doesn't trim leading spaces.

But this topic came up because of a similar leading space issue showed up on a customer integration this week, and I was quite surprised.

I was surprised because having done software development for about 20 years now, I can't recall encountering this issue before.  While I may have encountered data that had a leading space, my code always trimmed leading and trailing spaces, so a leading space never resulted in a problem.

But in the case of my customer, they had populated a custom table with a few rows that had a leading space.  That leading space prevented a SQL WHERE clause from working, preventing records from being retrieved and imported.

My integration retrieved invoice " ABC123" (with leading space) from the invoice header table in SQL.  It then trimmed the invoice number and queried the invoice line table for all lines related to invoice "ABC123".  As you can guess, the line query retrieved 0 records.

The client and I spent several minutes reviewing the integration and the data trying to figure out why one invoice wouldn't import.  I eventually noticed that the data looked slightly shifted in the SQL query results grid.

It's subtle, but if you have other records above and below, it is much easier to spot.

Once we discovered the leading space in the invoice data, the client removed the spaces and the integration worked fine.

Paradoxically, the issue was caused because my integration code was trimming the invoice number.  Instead of using the actual " ABC123" value from the invoice header table, the trimmed version of "ABC123" caused the problem.  But that turned out to be fortunate, since I now know eConnect would have imported the value with a space, which would have certainly caused headaches in GP later on.

So, the lessons are:

1. Leading spaces in data can be problematic in GP (or just about any database or application)

2. The GP application doesn't allow leading spaces, but eConnect will import them

3. Always trim leading and trailing white space for all of your imported data

Keep learning!

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