Tuesday, August 24, 2010

SmartConnect 2010 Service Pack 1 - Templates

SmartConnect Templates are designed to make your life easier. In SmartConnect 2010 SP1 you will find there are many new template options.

We have provided for a number of out of the box templates, we let you build your own template's and we let you import templates written by others. Now I am not referring to prebuilt maps here, but prebuilt components that can be used within a map.

Email Templates: You have always been able to trigger an email to be sent after a map has completed. Now we have a series of prebuilt templates emails you can choose from. Further to that you can build your own templates that you use on this installation and then on any other installations you work on. The standard templates include:
- A simple map completion email
- A simple map failure email
- A success email containing a list of created records
- A failure email containing a list of all records that failed

Script Templates: Many of the most commonly used calculated fields, restrictions and tasks are now available in template form. These are configurable templates containing variables that are automatically found and replaced for you with the data in your map. You can then attach any of these template actions to any map you create.

SQL Tasks Templates: This is a new function in SP1 that has also been templated. So any of your favorite SQL scripts will be prebuilt for you and all you need to do is tell them where you data sits and it will do all the hard work for you.


All the templates added to SP1 are there to make the life of a consultant easier. Faster SmartConnect roll outs, less errors, no coding and more comprehensive solutions for you customers. These template are another great ease of use feature for SmartConnect 2010.

Friday, August 20, 2010

SmartConnect 2010 Service Pack 1 - Data Validation

The great thing about SmartConnect is that all the business rules of Dynamics GP and CRM are applied during the integration. This means that SmartConnect checks to ensure that journals balance, customers exist, inventory has quantity etc.

BUT, what if you want to do some validation before the integration runs. The new feature for SQL validation data checks in Service Pack 1 comes to your rescue.

Lets look at a recent customer scenario: A large customer needed to import a series of monthly General Ledger journals containing 85,000+ lines. (Extreme I know). The problem was that the chart of accounts was ever expanding to cater for new locations for a rapidly growing company. When they ran the integration it would run for 20 minutes (which was pretty good) and then come back saying that one of your GL accounts in the file, does not exist in GP! This created 2 problems - firstly the journal did not integrate as it no longer balanced due to a missing account, and also you did not know which on of hte 85,000 accounts was the missing link. (In the current smartconnct you can get around this by writing some custom scripting).

With SP1 the way to handle this and many similar scenarios is to run a pre map data check. You can configure your map to go and lookup any existing data field, and compare it with the records in a column of your source data. In this way you can ask a map to check that each account exists, before you begin the integration. If missing accounts are identified you will receive a full list of missing accounts and the integration will stop.

The great part is there is no code required to do any number of pre map data validation checks. This is a great feature and one more step towards ensuring every consultant can configure complex integrations without the need to every speak with a developer.

Wednesday, August 11, 2010

SmartConnect 2010 Service Pack 1 - SQL Lookups

SQL look ups make the hard bits easier!

Customers don't always have (in fact rarely) a perfectly formatted source file with all the data you might need. For example you tell a client that for the billing integration you need the customer and their billing address ID. But they do not have the billing address ID in their data.

A SQL Lookup column will look into the SQL Database at run time and return a value based on data that you pass in. For example you could have a customer record in GP, and use this to look up the default address ID for that customer, and then put that address ID into your map to go on a sales order. Another example is if you are bringing in a payment via SmartConnect but need to link the payment to a PO. You can use the SQL lookup to find the voucher number in GP (based uopn a PO number lookup) to which the payment need to be matched.

It is one of those little features that you are going to use all the time, to expand on what you can offer your customer - all without writing a single line of code.

Saturday, August 07, 2010

SmartConnect 2010 Service Pack 1 - SmartConnect Tasks

SmartConnect tasks are a really powerful part of SmartConnect that enables SC to handle the most complex of pre and post integration requirements. In SP1 we making this a whole lot easier for most (non developer) consultants to use.

Below is a list of the changes coming for tasks:

Task Window
The first feature is a new task management window that allows you to see all tasks linked with a map in a single tree view. A new tasks window will be created that allows users to see and create all task types from the same area. A tree view will be displayed on the left containing the following:
• All tasks
• Map pre tasks
• Map post success tasks
• Map post failure tasks
• Document pre tasks
• Document post success tasks
• Document post failure tasks
• Global variables

Each of the functions in the tree view (except global variables) display the tasks created beneath them. Selecting a task type will display all tasks within that node, selecting a task node will display the actual task selected. Users click a ‘New Task’ button which will allows them to create new tasks.
This new screen allows users to see and update all tasks from a single window and also removes the complexity in creating and managing tasks.

Global Variables
Global variables that are used within tasks will be divided into two groups, variables that may be used by client to store and return values (user variables), and variables that cannot be updated or manipulated by users but may be returned for reporting purposes (system variables).
The list of system variables is:
• Map id
• Map description
• Current tenant
• Now (date/time)
• Current user
• Record counts
o Total documents
o Successful documents
o Failed documents
• Rolling columns
• Last error
The list of user variables is:
• Administrator address
• Bcc address
• Cc address
• File name
• File path
• From address
• Message
• To address
• User variables 1 - 10

Our more technical consultants have been requesting user defined Global variables for some time, so the addition of 10 user definable variables will make writing those complex tasks much simpler.

Selecting variables
there is a new drop down list within the task setup areas that allows users to select variables for output in emails and other tasks. Variables selected in drop downs will enter in the applicable field which will then be re-populated with the appropriate value at run-time. This then means that users do not need to know the name of variables to be able to include them in tasks.

Templates
To make adding tasks to your maps even easier we have added a number of standard templates that can be linked to you map. Templates have been provided for both email and scripting tasks.

Many of the scripts that were available as part of our script library within the eOne knowledge base have been included within the temlaptes. This means that the most common scripting tasks can be added via drop down option. In addition a number of predefined emails tasks to notify users on the success failure of the integration have also been included.


Move/Date and time flags for Copy File Tasks
An additional check box ‘Append date time’ has been added to the move/copy file tasks. When selected this option will automatically append the date time to the target file name before moving/copying the file.

SQL Tasks
Two new tasks will be added to the task type list. Run a SQL statement and SQL validation. many of the consultants we work with are comfortable running SQL scripts but less comfortable including this within VB.net or c# code - so this is another way to empower consultants.

SQL Validation
This task may only be selected as a map or document pre task and will allow users to validate data before processing. Have you ever wanted to run through your source data and make sure that all the Gl accounts already exist in GP? There is nothing worse than running in 50,000 lines in a journal only to find that one account does not exist.

Wednesday, August 04, 2010

SmartConnect 2010 Service Pack 1 - Dates

Over the next few weeks I am going to highlight some of the product features that are coming in Septembers release of SP1 for SmartConnect 2010.

SP1 is focussed on making the little things easier for our implementers. There are no massive ground breaking additions, but we have incorporated the feedback from the consultants that are building great solutions, into this build.

It is a pity that all countries and all software have not adopted a standard date format to make integration activity easy. We know that dates is one area where consultants have been tripped up as between CRM, GP, Excel, CSV and the massive variety of source systems you work with - getting the date set correctly can be a challenge.

Coming in SP1:
System date format
Users will be able to select a system wide ‘expected’ date format. All fields identified as date will be assumed to be in this format unless otherwise specified. A dropdown will be available on the current SmartConnect setup screen allowing users to select from the following date formats:
• dd/mm/yyyy
• dd/mm/yy
• mm/dd/yyyy
• mm/dd/yyyy
• yy/mm/dd
• yyyy/mm/dd
• yy/dd/mm
• yyyy/dd/mm

Columns defined as date
Currently columns that contain date are displayed as string columns in the node mapping window. This will be changed so that users can see which columns are dates and which are not. The processing of dates for the GP connector will also be changed so that if a column is defined as a date the system will automatically cast the target column as a date when processing. If the cast fails then the user will be notified that the document has failed due to that date processing. When creating new nodes from Node Maintenance the type must be set to the value from the stored procedure.

DateTime additional column
An additional column type will be added to the GP connector node mapping window. It will allow users to select a source column, determine the date format of that column, and return a GP date. This will allow users to set/read date formats that are different from the standard date processing within GP/SmartConnect. These source date formats can be user dfined to ensure there is nothing you can not do.

If the date you need to work with is not handled by all the above enhancements - then we will still have the traditional calculated field functionality so you can write your own formula for handling unique dates.