Tech Tuesday - SmartConnect 2012 Scripting Updates

This Tuesday we're taking a quick look at the scripting windows in SmartConnect. There are a couple of improvements that have made scripting much easier and more useful in SmartConnect in the latest 2012 versions.

I am especially excited about these new features since a lot of my work in SmartConnect revolves around developing scripts and other tasks and these last couple updates have made the scripts much easier to read and also more secure.

The first thing you will notice is the text highlighting in the scripting and sql windows. There is a different highlighting method for VB, C# and SQL code so each language will have its own format in the windows.

As you can see from the image this VB code has all the comments color-coded, the keywords colored uniquely, and any strings displayed in another color. In the past, everything was just shown as plain black text which hurt the readability a bit.

The other piece I want to point out are the saved connections that can be used in scripts. I have mentioned this multiple times before, but the saved connections let us set up multiple types of connections in any script without displaying the critical bits of information (user names, passwords) in plaintext in the script.

This Fields list can be found in every scripting window just to the left of the coding window, and either of the Connections items can be expanded to see any saved connections you have set up already. The connections can then be used in the script. If you have questions on setting up the connections or using them in the script refer to my earlier tech article on creating saved connections.

Next week I will cover setting up templates in SmartConnect, which really deserve a video article just to themselves do to how powerful they are in speeding up your development time when working on multiple maps.

SmartView goes Stand Alone

SmartList has long been the best thing about Dynamics GP. A good and a bad thing about this is that you had to log into GP to access SmartList.  It was a good in that that's where you were if you already use GP all day.  It was a bad thing if you were one of the many people in an organization that did not have access to Dynamics GP.

"Making the right information available in the right format at the right time to the right people inside the organisation is vital to efficiency." I stole this quote from a BI consultancy website but it sums up the value of the information we collect in Dynamics GP.  

The release of our standalone SmartView this week makes delivering on the above statement possible for the average GP user. There is no doubt that all GP users would vote SmartList/SmartView as the most powerful tools for getting to the information you need. The problem was that if SmartList was the only option, the above quote would have read:

Making the right information available in the right format at the right time to the right people inside and outside the organisation is vital to efficiency . . . As long as they have a login to Dynamics GP.   And that does not really make sense.

The new stand alone SmartView fills this gap. Now everyone with a network connection is able to benefit from all the power of SmartView.  There has never been any upside to limiting the data shared across a company.  SmartView breaks down the walls between the haves (GP) and the have nots (GP) which does a world of good for productivity. 

SmartView standalone requires the core SmartView to be in place within GP. External licences are based on named users and will be available based on the following promotional price groupings through to Sept 1st 2012. 

1-10 external users: $1,000
10-30 external users: $2,000
30+ external users: $3,000  


For more information on the release of SmartView standalone please contact sales@eonesolutions.com

Tech Tuesday - Troubleshooting Scheduled Maps

Today we have another tip from our lead support technician on troubleshooting scheduled maps that won't run in SmartConnect. Scheduling maps is one of the most common tasks that our clients use, so these steps should help speed up the process of getting them up and running in the case where they don't work immediately:

Analyzing and troubleshooting why a map won't run scheduled.

99% of the time it's because the scheduling user does not have access to some resource used by the map. Either directories or pathnames or files that are not available to the user that is running the schedule. The best/easiest way to troubleshoot it is to go to the machine where the scheduler resides.

1. Go to Start - Run and type in Services.msc

2. In the services window, find the "eOne SmartConnect Service"

3. Right click on it and go to Properties. Then go to the Log On tab. Write down the User ID that is used there.

4. Log off the machine that is running the schedule, and log back on as the user you wrote down in step 3.

5. Open the map and preview the data source. If you cannot preview the data source, it means the user does not have access to the drive/directory/table the source data is coming from.

6. If the data source previews, run the map and see if you get an error. Most likely you will get some sort of error while manually running the map. Continue fixing issues as you find them until you can manually run the map as the user set to run the scheduling service.

7. Now log off the machine and setup the schedule to run again. If the map ran manually when you are logged into Windows as the scheduler user, it will most likely run via the scheduler at that point as well. 

David Youngquist

eOne Demo Server

Demo Environments
Having spent many years in presales being a demo man - I learned that the hardest thing is having a clean and reliable demo environment that supports everything that you need to show. With the complexity of today’s solutions it is actually impossible to have a real life environment with all components ready to go.

When it comes to SmartConnect there are so many moving parts and the demo will differ greatly form one client to another. It is simply too hard to have an environment with all the following components ready to demo: CRM, GP, Excel, Excel Templates, CRM templates, SalesForce.com, Valid XML , Scheduled maps with tasks, info path, outlook running for email alerts, etc etc etc.

So we have made it easy for you. All eOne partners have access to use my demo server. This is not something hung together and forgotten. This is my eOne demo server - the exact same one I do all my demo’s on. I take a full copy of my image and copy it to a back image which is open to you to use. So if I make my image better or upgrade to the new build then your demo environment is also updated. You can always be sure you have a working environment, that is one the latest code set and it works first time every time.

Key Demos ready to go:

1. CRM to GP bidirectional: Account to customers, Items to products, Pricing, sales Territories, payment terms etc. Orders from CRM to GP. Invoices form GP to CRM. It is all there with demo scripts ready for you to Demo.

2. Excel templates to GP: Enter a journal in Excel and it appears in GP. Same with AP, SOP, INV, PO etc

3. Excel template to CRM: Manage opportunities in Excel. See them, update them and then click a button to update them into CRM.

4. Speed: Run 30,000 customers into GP. Then run another map to delete them all!!

5. Extender data creating GP data via SmartConnect: If you are not demoing this now - you are selling GP short.

6. Filtered SmartList into CRM: Filter a SmartList to give you only items ready to go to CRM - and then push them right across. This is really very cool.

7. Real Time GP automation: Enter a new employee in payroll, and it creates a vendor for you.

and more and more and more.


How to Connect
As easy as a terminal server connection. This means you can connect from anywhere, anytime to a do a demo. First book a time with abbey to ensure you have sole access.

How to learn how to demo on this box:
Join one of my fortnightly “using the eOne demo machine” webinars. I will teach you everything there is to learn.

New World for Account Managers:
Account managers love to play dumb! They can spin a story but pretend a demo is way too hard!! The game is over. Next time you are out onsite and talking to someone about entering journals into excel - do not stop at talking. Show it to them. There is no surer way to lose an opportunity than saying I'll get back to you with a demo when I can schedule our presales person in - and 3 weeks later you come back for a demo interest is gone and you never make a sale.

So, jump on the financial controller’s machine. Enter the TS connection detail and you are demoing. Open a spread sheet from the desktop, fill in a few fields, click a button. Find the journal in GP. You have just made a sale instead of mucking about for 2 weeks and not making a sale.

So my call for every account manager - is call me and I will guaranteed - set you up to sell a whole pile of product and service this quarter.

New World for Consultants
Let’s face it, no one believes an account manager! But every customer believes the trusted consultant. Now you do not have time to boot up a VPC image every time you want to show a 2 minute demo. So leverage the eOne demo server. Jump on and show how cool the CRM to GP integration is and just 3-4 minutes. Close a sale and generate 2 weeks work for yourself. Too Easy.

So go back your boss tomorrow and negotiate a commission on product sales. Then every time you go onsite, before you leave, show off one little thing from SmartConnect and you will be making some good holiday money. It is really easy.

Tech Tuesday - SQL Timeouts

This week we have an article from our service delivery manager at eOne. He has discovered a tip to improve sql performance when using the query builder windows in SmartConnect:

We have had a few people that have had timeout issues when creating queries in our SmartConnect Query Builder window accessed by hitting the modify button when you have a SQL ODBC datasource. For most people the list of tables is quickly populated from whatever database that was specified. Others will see it sit there for a while and ultimately get a SQL timeout. What is actually running behind the scenes is a standard SQL Server procedure called sp_tables. Again, on most systems we see results returned very quickly if we run the following statement in SQL Server Management Studio:

 

     EXEC sp_tables

For people that are having a timeout issue like described above we can simply restart the SQL Server service and SQL Server will run that query very quickly and you shouldn’t have timeout issues anymore. Most likely this occurs because of the SQL Server running for a long period of time and many processes (ie Creating databases, tables, views, restoring databases, etc) have fragmented the internal index.

I hope this helps anyone who has experienced any timeouts in SmartConnect or even someone potentially using the sp_tables procedure for other purposes outside of SmartConnect.

Chris Dew

Tech Tuesday - Troubleshooting SmartConnect Logins

This week we have an article from our lead support technician at eOne. He has put together a couple quick steps that will resolve a commonly encountered message when launching SmartConnect:

If you receive the message “Unable to connect to database” when launching SmartConnect, there are two main causes for that.

1. The SmartConnect password is wrong on the machine(s)

or

2. The Windows user you are logged in does not have rights to access SmartConnect. 

To fix the first issue open up the Windows registry on each machine and navigate to the following location:

If you are on a 32 bit machine, you have to go to HKEY_LOCAL_MACHINE – Software –  eOneSolutions - SmartConnect

If you are on a 64 bit machine, you have to go to HKEY_LOCAL_MACHINE – Software – Wow6432Node – eOneSolutions - SmartConnect

Look at the key that says “ScSqlPassword”.  The password for the SmartConnect user should be there.  Also look in the “Connection String” key.  There is a section that says "Password=" followed by the same password.  It should be the same in both locations on both machines.  If it's different, make it the same on both. 

If you end up changing it on the machines, you will need to login to SQL Server Managemnt Studio and changing the password on the SQL user named SmartConnect.  Make it match what you set the password to in the registry of the workstations. 

If all that is good, then it's your Windows user.  Run the following statement in Query Analyzer

Select * from [SmartConnect]..[User]

You need to be logged in as a Windows user that is listed there in order to access SmartConnect. 

David Youngquist

How to explain what SmartConnect is?

How to explain SmartConnect

With most software it is very simple to explain the function that the software performs. SmartConnect is different to most software - in that it can be used for so many different things  - that a simple explantion is often not enough.

I have struggled over many years to nail down a clean description of SmartConnect that describes the full extent of its functionality. Using the terms 'integration' and 'automation' do not really get to the heart of the products functionality. The best solution has been to explain SmartConnect with the help of this image below.

The first thing to understand about SmartConnect is that it is based around Sources and Destinations. The Source being defined as 'where your data is is coming from' and the destination being defined as 'where you want the data to end up'.   This suddenly starts to sound like the old definition of integration. The important thing to note about the way SmartConnect is explained in the image below is that you are able to connect any source of data with any destination. This mean you can draw a line form any source to any destination.

So traditionally you would draw a line from Excel to Microsoft Dynamics GP. The example here might be importing a series of General Ledger journals from your retail system into the General Ledger.  OR you could draw a line from a CSV text fine into Microsoft Dynamics CRM to import a list of prospects received from a trade show. 

SmartConnect does all of this traditional type of integration better than most dedicated integration tools, but it then goes further. Looking at the image below you could draw a line from XML as the source to Excel as the destination.  Hmm - but that is not integrating data anywhere, is it?  This is an example of using the SmartConnect engine to take data form one format and pass it through the engine to return data in another format.

What if we drew a line from Microsoft Dynamics GP to Microsoft Dynamics GP?  Well that makes no sense - or does it? Within SmartConnect we have an engine that knows how to create any record in Dynamics GP as a destination - that's powerful. We can also read data form any of the listed data sources to pass through SmartConnect. So what if we read data from GP and use this data to create data in GP? Are you still following me?

One of the reasons I dislike the term integration is that it sets up in your mind the moving of data from one place to another.  We take a list of data form a csv file and move it into SalesForce.com. That's what the word integration conjours up in most of our minds. So lets go back to our GP to GP example.  If I define GP Sales Orders as my source of data, and restrict that source of data to only orders with a particular type of product - then only a small number of orders would be passed to the SmartConnect engine. Now for those orders I can choose any destination.  The important thing is that I can use this data not to just create another copy of this order somewhere else - but I can create any data I choose based upon the data in my datasource.

So - lets limit the sales order data source to only those that contain service or labour based inventory codes. If we sell professional services then there will always be a follow up action to schedule these services.  Using SmartConnect there are a whole range of things you might want to trigger if you sell people time and all of the following are achievable with SmartConnect:

1. Create a project in GP project accounting

2. Create a case in Field Service

3. Send an email to the consultant

4. Create an activity inside of Microsoft CRM for scheduling

SmartConnect would let you do all of these without writing any code or speaking to a programmer.

What I want you to understand here is that you can start at any source and map that data to any destination.  How clever you get with your solutions is entirely up to you. So when you are going out to inspire a customer or inspire your colleagues take the SmartConnect fact sheet that contains this image and you will be able to truly explain the breadth and depth of SmartConnect. 

Tech Tuesday - Saved Connections, Revisited

A few months ago we had an article that talked about using saved connections to make your scripts more secure and easier to setup when they involved database connections. Today I wan to focus more on why you should use them for your data sources too.

If you look at how you normally develop maps and then deploy them, one of the most common setups is doing all the development and testing work on one server and then moving everything to the live server when the testing is finished.

In the past, any of those connections you made in the data source for each map had to be updated individually. Now, we can setup a default connection and then use it on multiple maps so once the move is made to the live server all you need to do is update the saved connections and have the changes roll down to any of the maps that use that connection.

To setup a saved connection go to Setup -> Generic Defaults -> ODBC Data Source (or any of the other sources) in SmartConnect 2011. When the window opens, you can name and setup a connection to any ODBC source - in this case, my sql server.

Once you have the connection setup you can use it in any map data source that is connecting to the same location. In the screenshot below, I am setting up an ODBC data source and choosing the saved connection rather than setting up a new one.

When I later move my maps to a new server, I will go back to the saved connection and update that rather than each map individually which will save a lot of time.


Chris Hanson

SmartConnect 2012 - New User interface

SmartConnect 2012 will be released and available for download this week. We are really excited about this release and the 'ease of use' enhancements it delivers for all our SmartConnect implementers.

We have spent the last 2 years improving the functional aspects of SmartConnect making it broader and wider and deeper for our customers.  For this release we focused on the 'implementer' and the things that our implementation consultants were asking for to make working with SmartConnect even easier.

So functionally there are no surprises in this build - but there have been major changes to the look and feel. I am very sure that this will be a very popular and very stable build for all our customers and implementers.

Take a look at this 10 minute video that steps you through some of the key User interface changes.

For a WMV file please follow this link http://www.eonesolutions.com/content.aspx?page=SC+2012+New+User+Interface