There are three main parts to the webinar (plus the references):
The stored procedure will call the eConnect stored procedure which gets the next number and increments the stored value. This code was written so I could easily call it from VBA in Integration Manager for an integration which needed to insert some data into a custom table. This allowed me to get the voucher number up front and use it in the VBA.
-- drop stored proc if it exists IF OBJECT_ID(N'usp_AZRCRV_GetNextPMVoucherNumber', N'P') IS NOT NULL DROP PROCEDURE usp_AZRCRV_GetNextPMVoucherNumber
GO -- create stored proc CREATE PROCEDURE usp_AZRCRV_GetNextPMVoucherNumber AS /*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
DECLARE @return_value AS INT DECLARE @O_vCNTRLNUM AS VARCHAR(17) DECLARE @I_sCNTRLTYP AS TINYINT = 0
DECLARE @O_iErrorState AS INT
EXEC @return_value = taGetPMNextVoucherNumber
@I_sCNTRLTYP = @I_sCNTRLTYP
,@O_iErrorState = @O_iErrorState OUTPUT ,@O_vCNTRLNUM = @O_vCNTRLNUM OUTPUT SELECT @O_vCNTRLNUM AS VCHRNMBR
GO -- grant execute permission on stored proc to DYNGRP GRANT EXECUTE ON usp_AZRCRV_GetNextPMVoucherNumber TO DYNGRP
The stored procedure can be executed using this command:
-- execute stored proc EXEC usp_AZRCRV_GetNextPMVoucherNumber
The vendor has an existing purchase order. Choose Continue to post or save. Choose Go To to view a purchasing navigation list. Choose Cancel to return to the window...
Find Integration Manager on the Windows Start menu and right click and select Run as Administrator. When it starts, click the Register Now button:
To install it, launch the Setup utility and, under the Additional Products heading, select Integration Manager:
Integration Manager has two adaptors available for integrating data into Microsoft Dynamics GP.
The first adaptor is the Microsoft Dynamics GP one which requires you to have the Dynamics GP client open and logged into the company into which you want to integrate data.
The second adaptor is the eConnect one; this requires eConnect to be installed, although it doesn’t need the Incomging/Outgoing queues installed within eConnect which is different to the earlier post on installing eConnect; it also does not require Dynamics GP installed.
Over the last few posts in this series, I have shown how to create a simple General Journal integration.
The process is slightly more involved than creating a similar integration, but the huge advantage is being able to schedule the integration to automatically run and select all files from a folder.
With Integration Manager, to allow multiple people to use the same integration you had to point it at a shared drive letter which all users had in common (and often not all did, due to being in different locations).
In addition, SmartConnect can also process the Excel spreadsheet directly, without requiring the user to save the active page down as a CSV; something which Excel really doesn’t want you to do.
The other main benefit of SmartConnect is that if there is an eConnect node available, it can integrate that type of record into Microsoft Dynamics GP, whereas Integration Manager could only use the ones available via the standard or eConnect adapters.
eOne Solutions also have a product called Node Builder which allows additional eConnect nodes to easily be created without the need for a developer. I’ll be taking a look at NodeBuilder in a future series.
The prerequisites for creating a new integration map are quite straight forward:
- Decide on the type of integration to be created.
- Decide how the integration should work.
- Create the source data template.
The integration I am creating as part of this series is one of the easiest which can be created; it is a standard General journal integration. I always create this map after implementing SmartConnect (and before starting to use SmartConnect, Integration Manager) as it is a quick and easy one to create and which will be useful to all clients.
The most useful way of creating this integration for clients, is to create an integration which polls a folder every few minutes on a schedule and which reads data from an Excel spreadsheet. This approach allows one integration to be set up and usuable by all users; as it is a folder being polled, the files dropped into the folder can have any name and, as long as the format matches the template, SmartConnect will attempt to integrate the data.
In the past when using Integration Manager, the source file would be a CSV or TXT file as Integration Manager couldn’t easily handle Excel spreadsheets; SmartConnect has no such limitation, so I am going to use the Excel version of the Integration Manager template:
With the above decided and in place, the next post, in this series, will start to cover the creation of the integration map in SmartConnect.
For providing clients with integrations, we’ve typically used Integration Manager, which is part of the Customisation Pack. Having recently taken a look at SmartConnect from eOne Solutions, I rapidly came to the conclusion that while Integrating Manager is easy to use and integrations are relatively simple to create, it lacks a lot of the features of SmartConnect.
|Easy to create integrations|
|Schedule Integrations to run automatically|
|Use Excel XLSX as a data source|
|Connect to almost any datasource|
|Integrate to any eConnect node|
|Integrate with custom eConnect nodes|
|Extend integrations programatically||VBA||VB.NET or C#|
|Chain integrations to run one after the other|
|Schedule export of data to other systems|
|Robust when integrating large datasets|
SmartConnect provides an alternative to Integration Manager, which is part of the Microsoft Dynamics GP Customisation Pack. While Integration Manager allows users to create and run integrations on an ad hoc basis, SmartConnect provides much more functionality, such as the ability to schedule integrations, poll a folder to load all files within and load data from many different data sources including Excel spreadsheets, transform data and provides a web service which can be called from any application.
There are three types of integration which can be created in SmartConnect:
- Bulk Data Sources – Bulk data sources are configured to look at fixed data locations (such as a particular file or folder). The SmartConnect Scheduler can be used to define a schedule on which bulk data source integrations should be run.
- Change Data Sources – A change data source will track all data changes since the map last ran successfully; based on the schedule, it will execute a SmartConnect map to integrate the data.
- Real Time Data Sources – Data is integrated in real time from any Microsoft Dynamics GP window or direct from Dynamics CRM to any destination.