A Dynamics GP client has a situation where they may receive multiple payments from a customer with the same "check" number on different days. So on Monday November 2, they might receive check "12669", and on Wednesday November 4, they might receive another payment marked with the same check number "12669".
I don't know the background of the source data, but it's coming in electronically (such as from a lock box, bank web site, or other cash management system), and it is being imported using SmartConnect.
If you attempt to import a customer check number that already exists in GP, SmartConnect will spew this eConnect error at you.
"Duplicate check number for this customer already exists in RM00401"
So, in the Dynamics GP import tool that doesn't require any programming, you'll need to do some programming if you want to work past this error.
First, open the Mapping window and click on Additional Columns. Then add a new Calculated column.
I called mine the overly long but self explanatory "UNIQUECUSTOMERCHECKNUMBER".
In the calculated column, paste in this script. I would have written it in C#, but the client's other SmartConnect integrations have some VB, so I went with VB.
This is my inaugural SmartConnect script, so excuse me if I'm doing anything wrong or sloppy. And I'm sure there are at least 3 different ways to do this in SmartConnect, so if you have a better way, post a comment and let me know.
As a side note, one reason why I hate "scripting" in any tool is that it is typically a euphemism for "Here's an empty dumb window with no Intellisense or feedback about the code you are writing". It's a glorified version of Notepad. It makes coding horribly tedious compared to Visual Studio, as modern IDEs no longer require you to memorize every excruciating detail about the language syntax. So if you also find such scripting windows painful, just write the script in Visual Studio first, and then paste it into SmartConnect.
Dim connString As String
Dim commandString As String
Dim recordCount As Integer
Dim dateString As String
connString =
_SQLDEF_GP2013
Dim conn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connString)
conn.Open()
commandString = "SELECT COUNT(*) AS
RecordCount FROM RM00401 WHERE CUSTNMBR = '"
& _CUSTOMERID & "' AND RMDTYPAL = 9
AND CHEKNMBR = '" & _CHECK_CARDNO & "'"
Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(commandString,
conn)
cmd.CommandType = System.Data.CommandType.Text
recordCount
= Convert.ToInt32(cmd.ExecuteScalar())
dateString =
Microsoft.VisualBasic.Strings.Right("0" & System.DateTime.Now.Month, 2) & Microsoft.VisualBasic.Strings.Right("0" &
System.DateTime.Now.Day,
2) & Microsoft.VisualBasic.Strings.Right(System.DateTime.Now.Year, 2)
If (recordCount > 0) Then
Return _CHECK_CARDNO &
dateString
Else
Return _CHECK_CARDNO
End If
This script queries the RM00401 table to count any cash receipt records for the customer and given check number. If it finds any, it appends the date (MMDDYY) to the end of the check number value and returns it back to the Check Number field in the Mapping.
Please note that I've highlighted two variables above. The first one, _SQLDEF_GP2013, is the SmartConnect default connection string for my server. You will want to change that variable to point to your server.
In the Edit Calculation window, expand Default Connections -> Generic -> MSSQL Default to find your connection string variable.
The second variable I've highlighted, "_CHECK_CARDNO" is the name of the check number field in my particular source data file. Yours will likely be different, so you'll need to change that to match your check number field.
I suspect that this situation of having duplicate customer check numbers is probably unusual, so you may not need to use the script for this specific scenario, but the script design would be useful for other situations where other duplicate document numbers may need to be imported. You would just need to change the SQL query to work for your particular document type.
Happy coding.
Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics
GP Certified IT Professional in Los Angeles. He is the owner of Precipio
Services, which provides Dynamics GP integrations, customizations, and
automation solutions.
No comments:
Post a Comment