Conditional GP Rolling Column
alicia.smith@datacom.co.nz asked 8 years ago
How can I map the Document Number to one of the GP Rolling columns based on whether my Amount field is negative? I have tried using a calculation:
If _INVAMT < 0 then
Return _GETNEXTCREDIT
Else
Return _GETNEXTINVOICE
End If
But this does not seem to work.
Any help is appreciated, thanks.
Alicia
Lorren answered 8 years ago
You cannot use a rolling column in that manner as the calculated column is not able to read the rolling column data.
To accomplish what you need, it would require writing a script/calculated field that handles the rolling column process manually versus creating a GP Rolling Column. You can execute the E1_SC_GetNextNumber procedure to return the desired value in your calculation.
Lorren
dfeenstra@manercpa.com replied 8 years ago Alicia,
One option would be to run 2 maps and filter the data source on the first to be just the Invoices and filter the second one to just the credits. Each map would have it's own rolling column.
The other option, as Loren mentioned, would be to bypass the rolling column function and get the next number directly from GP with calculated field. You could either run the E1_SC_GetNextNumber stored procedure or, for AR transactions, you could run the taGetPaymentNumber stored procedure. You would pass in a 1 for Invoice or a 7 for a Credit Memo and it would return the next number in the sequence for each document type.
Dave
Daniel replied 5 years ago How do you get it t run a stored procedure and how do you know which stored procedure to run?
Is there a list?
I’m running into the same issue, multiple bank transactions and no way to distinguish the GP rolling column with the transaction type, although there really should be a way….
Lorren Zemke Staff replied 5 years ago Daniel, In the GP Rolling Column type, you can specify the document type from your source file, so it will call the correct stored procedure based on the value you pass in. This article tells you which value is required to be passed into the Document Type field depending on the Series and Document Type you are using. http://www.eonesolutions.com/blog-post/next-dynamics-gp-document-number-smartconnect/
Lorren Zemke Staff answered 5 years ago
‘————————————————————————————–
‘ Executing a stored procedure within a calculated field to return a single data element
‘ This script uses a Data Reader to loop through the rows to get the value of the last row
‘
‘ History:
‘—————————————————————————————
‘
‘———————————– CONFIGURATION ————————————-
Dim mapInterface As Integer = GBL_MANUAL
‘—————————————————————————————
Try
‘******************************************************************
‘ This section is used to set the connection string to execute the stored procedure
‘
‘ Requires setting up MSSQL Default servers from the Maintenance tab
‘
‘Declare the connection string based on the configuration variables
‘_SQLDEF_MYSQL is the MSSQL default connection string created in the SmartConnect Maintenance->MSSQL Defaults section.
Dim conString As New String(_SQLDEF_MYSQL)
‘ Set connection string based on the company defined at the destination
‘ to match the company connection string
‘
‘*******************************************************************
‘Create the SQL connection and open it
Dim myConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(conString)
myConn.Open()
‘Define the query command to be run
Dim sqlCode As String = “DECLARE @NextNumber CHAR(31) ” & _
“DECLARE @O_iErrorState INT ” & _
” SELECT @NextNumber = /* Code for getting data from either setup or transaction table */ ” & _
” exec ivNumber_Inc_Dec 1, @NextNumber output, @O_iErrorState output ” & _
” /* Code to update Setup table if applicable */ ” & _
“SELECT @NextNumber “
‘Declare the SQL command as the query string
Dim myCmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(sqlCode, myConn)
myCmd.CommandType = System.Data.CommandType.Text
‘——————————————————————————-
‘ Add SQL Stored Procedure parameters here
‘ myCmd.Parameters.AddWithValue(“@ParameterName”, _ParameterValue)
‘——————————————————————————-
‘Define the SQL Reader and execute it
Dim sqlReader As System.Data.SqlClient.SqlDataReader = myCmd.ExecuteReader()
Dim result as string
Try
‘Read the line
if sqlReader.HasRows then
while sqlReader.Read()
result = sqlReader.GetValue(0)
‘ Add Logic as needed
end while
end if
Return result
Catch ex As Exception
If (mapInterface = 1) Then
System.Windows.Forms.MessageBox.Show(ex.ToString())
End If
Return String.Empty
Finally
sqlReader.Close()
myConn.Close()
End Try
Catch ex As Exception
If (mapInterface = 1) Then
System.Windows.Forms.MessageBox.Show(ex.ToString())
End If
Return String.Empty
End Try