Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: SmartConnect Custom Incrementing Fields


Today we are going to talk about ways of generating Custom incrementing fields in integration using SmartConnect. The methods will work for Dynamics GP integration, Dynamics CRM integration and also many other areas.

The first method we will talk about is simply using a built in fuction called a Custom Rolling Column. This can be added from the main mapping screen by clicking on Additional Columns-Columns-Custom Rolling Column.

In the figure below we can see that it is very simple to give the Column a name and then the next value to increment.

custom rolling column

It is not necessary to select the Global Variable checkbox but if you want to use this value in a script later on that option is available.

This option works great for Master Records (Customers, Vendors, Employees, etc.) that may not have a function to increment values. There are other times when you may want to call a stored procedure or lookup a value in a table directly to get the next value for one of your fields. If that is the case the next example may be a better method for you.

In this method it will involve creating a Calculated Field in SmartConnect to return the value. A Calculated Field can be added by clicking on Additional Columns-Columns-Calculated. This allows a user access to write a quick VB.NET or C# script. In the figure below, the example is calling a Stored Procedure:

calc

If you want to copy and modify the script below for you own use here is the code:

‘—————————————————————————————

If String.IsNullOrEmpty(GBL_E1_EXCEL_SOP_DOCTYPE ) Then

‘Define the SQL connection and open it

Dim myConn As New System.Data.SqlClient.SqlConnection( _SQLDEF_CONNECTIONSTRING )

Try

‘Declare the eOne stored procedure that will be called

Dim storedProc As String = “exec ” & _COMPINTERID & “.dbo.E1_SC_GetNextNumber 3,” & _DOCTYPE & “,'” & _SALESTYPE & “‘”

myConn.Open()

‘Declare the SQL command as the stored procedure

Dim myCmd As New SqlClient.SqlCommand(storedProc, myConn)

‘Define the SQL Reader and execute it

Dim sqlReader As System.Data.SqlClient.SqlDataReader = myCmd.ExecuteReader()

‘Read the line

sqlReader.Read()

GBL_E1_EXCEL_SOP_DOCTYPE = sqlReader(“Number”).ToString()

Catch ex As Exception

‘If there is nothing to read, make blank and cause it to error out

GBL_E1_EXCEL_SOP_DOCTYPE = “-1”

‘Close the connection

myConn.Close()

End Try

end if

‘At this point, the global constant should be set above if it was blank, else it remains the same throughout the map

‘ Messagebox.Show(GBL_E1_EXCEL_SOP_DOCTYPE)

return GBL_E1_EXCEL_SOP_DOCTYPE

‘—————————————————————————————

I hope you have been inspired to try a few new ways in how you configure maps.

3 Comments

  1. Angie on March 1, 2016 at 10:15 am

    This is my first time using Smart Connect. However, I have been able to successfully import most everything until the very end. Then, I receive an error stating,’ “COMPINTERID” is not declared. It may be inaccessible due to its protection level.” Please advise. It’s driving me batty on this implementation. THANKS!

    • Nick Waverek on March 1, 2016 at 10:17 am

      Hi Angie,

      Are you still hitting a roadblock? If so, I can get our support team to help out. It’s tough to tell what the issue is without a little more info.

  2. Ito on December 13, 2016 at 4:09 pm

    how can one include a user defined function in a script ? or can one create a class (user defined) and calling that function or procedure from that class in a script ?

    Thx

Leave a Comment





RECENT POSTS


Join Us in Chicago at the Zendesk Showcase!
Tech Tuesday: Popdock - Joining the tale of two States
Meet eOne Solutions at Directions EMEA 2023!
Connect with eOne Solutions at SuiteWorld 2023!
What is a Data Lake/Data Lake Management?

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release Salesforce Salesforce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.