EONE BLOG

Tech Tuesday: Line Sequence Numbering


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!!


Interested in learning more about SmartConnect? Feel free to reach us at sales@eonesolutions.com!

1 Comment

  1. Natalie Matsie on August 16, 2016 at 3:15 am

    Hi,
    I have created these calculations/restrictions successfully and been using them for sometime now.

    However, I have a document whereby there is only one line item.

    It works if I add a second line item, but not if I just have the single line item.

    Not sure if this is something to do with the sequence number.

    Many thanks.

    Natalie.

Leave a Comment





RECENT POSTS


Employee Spotlight: Mary Charuhas, Partner Development Manager
Join us for the Q1 Partner All Hands Call!
SmartConnect Office Hours: Executing Maps Remotely
Partnering with eOne Solutions & Getting Started
2020 Q1 Promotions

POPULAR POSTS


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

CATEGORIES

ARCHIVES

open all | close all

TAGS

Business Central CRM Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight Events Excel Excel Report Builder Extender Extender Enterprise Flexicoder GP GPUG integration Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Office Relationships partners Popdock release Reporting SalesForce SalesForce.com scripting SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Office Hours SmartList SmartList Builder SmartList Designer SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.