SmartConnect 2010 Service Pack 1 – Data Validation
Thursday, August 19, 2010
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.
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.
