In a previous post I discussed using Global Variables to sum amounts for detail lines. This method can be used for additional purposes such as incrementing sequence line numbers. To accomplish this correctly and quickly we need to understand how SmartConnect processes calculated fields. Basically SmartConnect runs through the calculations for each destination node for a document (the key fields) and then sends that document. Then it moves to the next source record and processes again. This blog article will walk through that process.
I'll use the example of creating inventory item transfers that include items with Serial Numbers. We need to calculate the sequence number for the serial number line since we don't have it available in our source file and we are letting eConnect default the next line sequence number.
Our data source has standard inventory item information. ItemCode, Serial Number, Quantity, Current Location, New Location and Transaction Date.
We will set our key field to the Transaction Date since we want to have one Transfer Document per date in our file.
Here are the Microsoft Dynamics GP Destination nodes we will map for this transfer integration. We will Create Inventory Transfer header, Add Line Items and Assign Serial Numbers to those line items.
Processing
SmartConnect will take the data source and process each Key Field set, or data grouping, as one document, running through the Assign Serial Number node, Add Line Item Node and then the Create Inventory Transfer Node doing all of the calculations in each node consecutively. The next step is to move on to the Pre Document tasks before sending the data to the destination. After the document process has returned from the destination, the post document tasks are processed.
Data
In this dataset, I have only one transaction date so it will run through this set once.
In this next dataset, SmartConnect will run each process twice since we have two different transaction dates.
The reason for all of the above information is to show how to calculate values correctly within Smart Connect.
Setup
In this example, I need to make sure the Serial Number line will have the same line item sequence as the inventory transaction line where they belong. If we don't, we will get errors like the Line Item Quantity does not match the Serial Number Quantity.
First, we set up two Global Variables using the Global Variables option from the Maintenance menu. We will call one GBL_TRANSFERLINESEQUENCE and the other GBL_SERIALNUMBERSEQUENCE.
In the Assign Serial Number node, we will use the Restriction script to determine the next Transaction Line Sequence number, by checking to see if our Item Number is different. If it is a new Item Number, we increment our line by 16384.
If _ITEMCODE <> GBL_CURRENTITEMNUMBER then
GBL_SERIALNUMBERSEQUENCE += 16384
end if
GBL_CURRENTITEMNUMBER = _ITEMCODE
' Only map this line if there is a serial number
If _SN > "" Then
Return true
Else
Return false
End If
Assign Serial Number Node
In the Assign Serial Number node, we will create a calculated column called NEXTSERIALLOTSEQUENCE to return the current line sequence number that is calculated in the restriction script using the code below. (This is the VB.NET version). This will give us the Sequence number from the transaction line.
Dim mysequencenumber as integer
mysequencenumber = GBL_SERIALNUMBERSEQUENCE
Return mysequencenumber
Then we will map this to the Line Sequence Number column in our destination.
Add Line Item Node
We can now move on to mapping the line which requires creating a calculated field to increment the Line Sequence, just as we did in the Assign Serial Number line, however we will increment our other global variable.
Create a calculated column called NEXTLINESEQUENCE that increments the GBL_TRANSFERLINESEQUENCE global variable for each item in our group. This assumes we have grouped our data for this node by the Key Fields and then Item Number to create a new line for each item in our data set.
Use this code for the calculated column
dim mysequencenumber as integer
mysequencenumber = GBL_TRANSFERLINESEQUENCE
GBL_TRANSFERLINESEQUENCE = GBL_TRANSFERLINESEQUENCE + 16384
return mysequencenumber
Then map this calculated field to the Line Item Sequence.
Create Inventory transfer Node
The Create Inventory Transfer node does not require any special settings and can be mapped as follows.
Final Step
As a final step we need to reset the global variables to make sure the calculations are correct for the next document. We will create a Script Task that runs before the document which can be misleading however. This really means the task is executing before it is sent to the destination not before the data is gathered for processing. What we are really doing is clearing the variables before the next document begins processing.
Here is the code we will use for this Pre-Document Task
GBL_TRANSFERLINESEQUENCE = 16384
GBL_CURRENTITEMNUMBER = String.Empty
return true
Here is the screen showing the Document Task
Summary
To summarize, SmartConnect executes each node separately for a given document, defined by the key fields of the map, before sending the document to the destination. The order the nodes are processed is the order they appear in the destination.
Happy Integrating!!
