Skip to content
+1-888-319-3663

COMMUNITY FORUM

Conditional GP Rolling Column

alicia.smith@datacom.co.nz asked 8 years ago
I have an import into GP Receivables Transaction that contains both Credits and Sales/Invoices. I have set up 2 GP rolling columns to get the next invoice number, and the next credit note number: GETNEXTINVOICE, GETNEXTCREDIT.

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
Answers
Best Answer
Lorren answered 8 years ago
Alicia,

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
Daniel, Here is a sample VB.NET script that can be used as your starting point for calling SQL stored procedures and returning data within a calculated field. Depending on which table you are getting your next number from will determine the stored procedure to call. In the E1_SC_GetNextNumber, we have four stored procedures that can be called, E1_SC_GetNextBankCheckNumber, E1_SC_GetNextDepositNumber, E1_SC_GetNextBankDocumentNumber, E1_SC_GetNextBankReceiptNumber. You can call the right one depending on the document type.
 
‘————————————————————————————–
‘  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

If you would like to submit an answer or comment, please sign in to the eOne portal.