Thursday, December 22, 2011

Troubleshooting SmartConnect Data Issues

Basic Techniques of Troubleshooting SmartConnect Map errors

Today is Tech Christmasand we are going to review couple of ways to troubleshoot a SmartConnect map that is generating errors when it is run. I will start with simple errors and simple troubleshooting techniques.

In this example I have a sample map with sample data which will import vendors into Dynamics GP 2010. After mapping all the fields that I need to populate, I ran the map.

In the progress screen we can see that only 8 out of 10 records were successfully integrated into GP. So what is wrong with 2 other records?

Lets click on the error message and see what is the reason for this issue.

Ok, we can see a standard eConnect error message. Description of the error states that vendor name is empty.  I don’t remember having blank vendor name fields in my sample data. I clicked on Fix button at bottom right corner of progress window to have a closer look at the records that failed.

Oh! SmartConnect was right! As usual! I can see that 2 records do have blank fields in VENDNAME column. I can fix this issue “on fly” by manually typing in Vendor names into these empty fields:

Once I finished correcting the vendor names - I want these 2 records to join their mates in the GP company database. I click on the Run button. This will rerun the original map for just these records displayed in the SmartConnect process Errors window – but for the updated data. Now all my vendors were successfully integrated in to GP.

Let’s throw in a twist. What if I don’t want to import them? I just want SmartConnect to skip records with blank vendor names as they are invalid, without throwing errors. My favorite Integration tool can do it easily! Let me show you how.

I open SmartConnect Mapping window and click on Restrictions button. In restrictions window I expand Source Column section to display all my source columns as I’m going to use one of them as a variable in my script. I want to restrict SmartConnect from trying to import records that do not match my criteria.

In this example the restriction will be in English as follows:

If Vendor Name field is empty
skip the record
Try to import the record.

I use VB.NET to write my scripts, but SmartConnect knows C#.NET as well.

On the screenshot in my script I used VendName with _ prefix as it is required by SmartConnect in order to recognize it as a variable. The next time you’ll run the map, SmartConnect will automatically skip these records.

To demonstrate another way of troubleshooting errors I will use a simple map that imports customers into GP.  When I try to run the map I get the following error:

The error message gives me a direction in which I should start troubleshooting the map.

My dates that I’m trying to import are in incorrect format. It would be very useful to see the data(xml document) that SmartConnect generates and sends to GP through eConnect. SmartConnect can help us troubleshooting this issue and show the content of xml document.

On the Map Setup screen, I will change the destination to Microsoft Dynamics GP – File and enter a file path for files that SmartConnect will generate in the future.

I ran the map and opened a directory that I specified. Don’t be surprised to find heaps of xml files as the amount of documents will match the amount of records of your data source.  When I opened one of xml files, I discovered that CREDIT CARD EXIRATION DATE (CCRDXPDT) has an invalid date format.

Exactly what I thought! This xml file was extremely helpful. Now I know which field is problematic (remember that you can have multiple date fields mapped and it will make troubleshooting more difficult that’s why it is important to see which one is wrong one), I also can see the actual (invalid) format so it will be easy to write a script that will convert the date to a proper one.

But don’t forget it is SmartConnect we are working with. It does all the work for you and you don’t need to write a script to convert the dates. SmartConnect still can’t read your thoughts, but believe me, we are working on it.

To fix this issue we need to open a Mapping window.

  1. Go to Additional columns and click on Date Calculation and add a column.
  2. Enter Name of the field.
  3. Select a Source column that contains the date, you need to convert.
  4. Select the Source Format, the format of your source date. In my case it was day/month/short year.
  5. In the last drop down box select predefined date format called “GP Date Format” this is the format GP can understand.

* You can also define your own date formats, by clicking on Manage Formats button.

6. Now click on OK and map the Date Calculation column (I called it DATECALC) to Credit Card Expiration Date in the destination.

All done! Run the map. SmartConnect will convert the date to the correct format.

In this article I reviewed basic troubleshooting techniques which will make it easier to make integrations to GP. In my future articles will review advanced troubleshooting techniques which are required when you work with complex integrations.

Please, leave your feedback. Let me know if it was useful and what would you like to see in my next Tech Tuesday articles.  Remember, SmartConnect makes life easier!

Alex Sugak

QA Team Leader

Wednesday, December 21, 2011

SmartConnect SP

SmartConnect keeps getting better and stronger.

On the 20th Dec we released SP2 of SmartConnect 2011. This is a significant release with a mixture of fixes and features that make SmartConnect even more robust and functional. SP 2  rolls up previous hot fixes as well as additional features and bug fixes into this one build. We recommend all our clients upgrade to SmartConnect SP2 at their next opportunity. 

SmartConnect 2011 SP 2 Release Information and Upgrade Instructions

Release Information

1. Dynamics GP
Ability to define a single user with which to connect to Microsoft Dynamics GP. This means that a single AD user may be used for connections to GP rather than requiring each SmartConnect user to be set up individually within MSSQL security.

2. Dynamics CRM 2011
Added support for the update of entity record owners.

3. MSSQL Change Data Sources
Added support for schemas other than the default schema.

Bug Fixes
1. Extender
Fixed the bug where SmartConnect was truncating key fields for Extender windows and detail windows.

2. Importing Templates
Imports that use eOne Excel templates will now work if no TWO database is present on the Dynamics install.

3. Email SMTP ports
Now allows selection of any port number.

4. MSSQL Change Data Source
Now handles column and table names where the name is a reserved word correctly.

5. SmartConnect does not detect eConnect
SmartConnect will now detect eConnect correctly where eConnect is installed after SmartConnect.

6. Dashes are no longer allowed in default connection names
This was causing issues in some calculation and scripting fields and has been disabled.

7. SQL Command tasks and SQL Command Templates
Insert variable now works correctly.

8. Scheduler
 Fixed issue where a schedule recreated itself if it was running when it was deleted.

Wednesday, December 14, 2011

Tech Tuesday: SmartView and SQL

The built in Visual filter is really powerful with many more options that you would have in traditional SmartList. But there will be times that even with these filter options you can not get the exact data filters you need.

If you have a complex query that you can't enter with the built-in visual filter editor in SmartView, you can enter a SQL script to construct complex queries. Simply select Text as the Filter Editor Mode in the Options menu.

Now when you open the Filter Editor, the SQL entry interface is displayed. This interface has intellisense enabled to suggest field names and operators. For all those familiar with SQL you can construct whatever filter criteria you require. If you are not familiar with SQL syntax than go and knock on the door of the IT department and ask for a guru OR contact the eOne consulting team who will be able to assist (at a small charge).

Monday, December 12, 2011

Tech Tuesday: Copying Extender Nodes

Hey guys, I'm back in the Tech Tuesday mix and I'm starting off our video articles for this series on how to copy extender nodes into your needed maps in SmartConnect.

I've got the video below, make sure you flip it over to 720p for the best viewing - I promise that it will be full 1080p quality in the future.

Sunday, December 11, 2011

GP resellers. Stand and make Revenue.

GP resellers do not go into business for fun. Microsoft is not in the ERP business for fun. ISV do not write software for fun. Customers do not buy ERP software for fun.  All of the above mentioned organizations do what they do to provide a return on investment for their owners. Fact.

Now that does not mean we do not enjoy what we do, that we do not go out of our way to look after our customers who also become our friends. We build relationships,  pay salaries, design solutions, travel the world and make businesses run better - but underlying all that is a goal to generate income for business owners (and an income for all of those involved). We all want to run and be involved in successful businesses that generate revenue and return.

Recently, Microsoft made significant changes to its partner program. The reason they did so is that they were of the opinion that these changes would align with their strategy to run a good business that generates revenue. You may or may not agree with their decisions and changes. Each of the businesses involved within their model need to make decisions regarding how they will react to the changes in a way that is in their own best interest of running a good and successful organisation (which in turn makes revenue) .

I had feedback from one reselling partner that went like this "With the increased targets we have to hit with Microsoft - we will no longer be able to sell your ISV solutions (even though they are much better for our customers). We will be forced to sell only what is on the Microsoft price list or our revenue will not hit the targets." 

I believe there to be a fatal flaw in this thought process. A reseller who is not hitting the required targets right now needs to change something - I agree with that.  They need to change something that will align with their business goals  - which deep down is to 'run a successful business to generate a return' - as we discussed above. So is a decision to knowingly sell an inferior or mediocre solutions going to help you reach your goals? 

I have long been of the opinion that resellers must 'amaze' their customers. A reseller that does anything less is opening the door for a competitor who does 'amaze' their customers.   If you are not Amazing with GP - then rest assured that over time somebody will 'amaze' with NetSuite, or Sage, or .... (I think you get the picture).

I would like to see all GP resellers go out there and smash the Microsoft targets - as would Microsoft!  No reseller will do that by repeating the things they do right now? You do not get different results form repeating the same thing. Resellers must change something - they must try and grow and better.  10,15 and 20 years ago there was nothing more dynamic and exciting than a growing GP practice. These startups had amazing energy, tried new marketing activity, tested new technology daily and took risks to grow their business.

The reseller channel needs this energy again - and if Microsoft changing reseller targets helps achieve this then I applaud Microsoft. Back in those early days there was little or no recurring revenue in annual maintenance - you had to make new business. Now - it seems the same people 15 years on are only interested in protecting their recurring revenue rather than being hungry for new revenue. To all business owners  - if you are not hungry for new revenue then go out and employ people who are - and empower them.

Microsoft owe resellers nothing - and we are not victims of change or being squeezed out. Microsoft want an active and energetic reseller channel - lets give them one by doing our very best. Lets do it by selling only the very best solutions and selling more than the opposition.

Microsoft is not a company that will invest in a stagnant market - that is not what they will do. So if you are seeking great innovation and investment in GP from Microsoft then the only way to get that is to sell more and more GP. That is what they are looking for in changing the partner programs.  They are looking to motivate the channel and deal with growing organisations that want to get on board for the ride. If Microsoft see you as a hanger on that is milking a revenue stream then your days a numbered. Get active, get creative, and sell only world class solutions that amaze.  Funnily enough through this process I suspect you will also generate more revenue and greater returns to investors.  

Tuesday, December 06, 2011

Cut and Paste to save Waste

SmartView Tip fo the Day

The very best and fastest way to get data out of SmartView is to cut and paste. That good old fashioned method of using CTRL C and CTRL V (or right click - copy and right click - paste  for those that prefer).

There are a bunch of advantages to the cut and paste method.
1. It is instant. You can cut and paste 100,000 rows of data in just 2 seconds.
2. Column headings are pasted automatically
3. You can select only highlighted rows for export
4. The rows you select need not be consecutive

You will find that once you start the cut and paste method this will soon become the only way you push data out to Excel.  Another limitation overcome bu cut and paste is that the regular Excel export is limited to 1,000,000 cells. If you wish to export more than this number ot excel in a single run then there are two options for you.
1. Export to CSV and open in Excel
2. Cut and Paste as per above.

As they say in the classics "Cut and Paste to save time that others waste".

Sunday, December 04, 2011

Tech Tuesday is back and not just on Tuesdays.

Scripting and database connections.

The ability to create and run scripts within SmartConnect when processing maps is a great asset to the SmartConnect community. However it can raise issues about password security.

When a script requires connection to a database, and other users have access to the map setup screen, it is a simple exercise to open the map, open the script and read the database login details from the script window.

An additional issue is what happens if the database credentials change, and I need to update the script? You would need to remember everywhere you used those credentials in a script, then find and update the script. You have probably thought that surely there is a better way to both manage connection credentials, and hide restricted information?

Well now there is.

As of SmartConnect ( for those connecting to GP 10) all default connections are available for use as variables within the scripting windows. What does that mean for those users maintaining and creating scripts? Well firstly it means setting up and maintaining database connection details in a single place. If the database connection required is to a MSSQL or GP database, or to an ODBC or OLEDB compliant database the connection can be setup up under default connections. (Setup / Generic Defaults / then MSSQL, ODBC or OLEDB data source defaults, or Setup / Dynamics GP Defaults / Dynamics GP Query for GP connections). These connections can then not only be used in the relevant data source windows, but can also be used in scripting.

When opening the scripting task on a SmartConnect map, there is now a Default Connections node in the tree on the left. Expanding the node will give you the different types of connections that have been defined, and expanding the type will give a list of each connection of the selected type. The connection can then be dragged into the script window and used in scripting. How is it used? Let’s look at an example.

Originally your script would look something like this:
Dim conString as String = “DataSource=MYSERVER;Initial Catalog=MYDATABASE;User=UserName;Password=UserPassword;”

The new script would look something like this:
Dim conString as String = _SQLDEF_MYDATABASE

You will have noticed that using the default connection also removes all credential information from the script. It can no longer be read directly from the scripting window. You can also use the same default connection in other scripts, but only update the single default connection if something changes. With SmartConnect, Yes you can have easy script access, with easy database connection maintenance, while still maintaining security around connection credentials.