Using SmartConnect Customization to capture Dynamics GP Fixed Asset ID with GP Rolling Column

Recently, I had a client that needed to integrate fixed assets from an Excel spreadsheet into Dynamics using SmartConnect. When building the integration, I encountered an issue with using a GP Rolling Column for the Fixed Asset ID; SmartConnect does not include the native capability to capture the next Fixed Asset ID with their GP Rolling Columns. I was able to customize SmartConnect in order to provide this capability of finding the next Fixed Asset ID and updating the GP table where this ID is stored.

When SmartConnect is added to Dynamics GP, there are some stored procedures that are added to the GP database and one of them is to get what is referred to as “next” numbers. This is how the GP Rolling columns in SmartConnect know what the next number is, whether it is the next journal entry number, payment number, etc. Since there is no stored procedure for SmartConnect to fetch the next Fixed Asset ID number, I first created a stored procedure that would get the next number from the appropriate Fixed Assets table, store it as a parameter, and then update the table with a new “next” number.

CREATE PROCEDURE [dbo].[E1_Get_Next_Fixed_Asset_ID_Number]

-- Add the parameters for the stored procedure here

@DOC_ID CHAR(15), -- DocumentType from MsGpRollingColumn table

@FixedAssetID CHAR(15) = null OUTPUT

AS

-- =============================================

-- Author:

-- Create date:       <9/6/2017>

-- Description:

-- =============================================

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

DECLARE @Loop int = 0

WHILE @Loop < 1000

BEGIN

SELECT @FixedAssetID = NXTASSETID FROM FA40201 WHERE ASSETCLASSID=@DOC_ID

UPDATE FA40201 SET NXTASSETID = dbo.E1_fnIncrementDecrement(NXTASSETID) WHERE ASSETCLASSID=@DOC_ID

--make sure the asset id doesn't already exist

IF EXISTS(SELECT 1 FROM FA00100 WHERE ASSETID=@FixedAssetID)

BEGIN

SELECT @Loop= @Loop + 1

SELECT @FixedAssetID='Err'

END

ELSE

BEGIN

SET @Loop=1000

END

END

SELECT @FixedAssetID=@FixedAssetID

END

I then modified the Get Next Number SmartConnect stored procedure
to enable it to call the new Get Next Fixed Asset ID Number stored
procedure that I just wrote by adding the code below:

ALTER Procedure [dbo].[E1_SC_GetNextNumber]

@TYPE smallint,

@DOC_TYPE smallint,

@DOC_ID char(15),

@INC_DEC  tinyint = 1

as

DECLARE @I_tInc_Dec tinyint

DECLARE @O_iErrorState int

SET @I_tInc_Dec = @INC_DEC

.

.

.

-- FIXED ASSETS

IF @TYPE = 9

BEGIN

DECLARE @FixedAssetID CHAR(15)

EXEC E1_Get_Next_Fixed_Asset_ID_Number @DOC_ID, @FixedAssetID OUTPUT

SELECT @FixedAssetID AS Number

END

I then customized tables in the SmartConnect database to enable it to get the next Fixed Assets ID and update the GP Fixed Asset tables.

There are two SmartConnect tables involved with the use of the GP Rolling Column:

• MsGPRollingSeries
• MsGPRollingDocumentId

First, a row must be inserted into MsGPRollingSeries to add the fixed assets series ID:

My client has many types of fixed assets and uses a different set of Fixed Asset IDs for each type. In order to make sure that the next Fixed Asset ID of the right type of fixed asset is selected and updated, I needed to insert a row for each type into the SmartConnect MSGPRollingDocumentId table. I created rows for MED EQUIP, AUTOMOBILES, CAP LEASE ASSET, and several others. Each row included a SQL command including the designated type in order to both fetch and update the correct fixed asset by type:

Example of ExecuteCommand created:

exec {CompanyId}.dbo. E1_SC_GetNextNumber {seriesId},{documentType},'{documentId}'

Once I finished modifying both the GP and SmartConnect databases, I was then ready to create my Fixed Asset integration.

I specified my Excel data source:

Then, I created the destination mapping:

And when I create my GP Rolling Column, I now have the ability to select the Fixed Assets series:

And that GP Rolling Column can now be used in my integration map:

When you have great tools like Dynamics GP and SmartConnect, customization can make them work even better together!

If you are having difficulty getting started and would like some additional help please contact FMT Consultants by filling out the form below.