Home
 

Document last modified: December 06, 2019

Introduction

Areas of the EDI Import/Export process have been opened to modification via scripts. Visual Basic (VB) is the only scripting language currently allowed. For exports, the EDI Solution determines the value(s) to be sent as it normally would. This value(s) is then passed to the script and the value returned by the script placed in the EDI output instead. For Imports, the element values are passed to the script and allowed to be modified before processing.

Notes:

  • Scripting requires the Cadacus Solutions for Syspro Support system to be installed on the server. Use the Downloads tab to download the latest version.
  • Some servers will not allow the scripting to work under client/server at the server. When this occurs set the option to run the scripts at the client and install the support system on the client.

Warning: Scripts must be carefully tested as the EDI system does no validation on the returned information.

Enabling scripting

In the EDI System Setup, select the "Files & Paths" tab. Check the "Enable Scripting" box and enter a directory on the SYSPRO Application server where the scripts will be stored.

Do not use a path with spaces in the directory names.  If the path does include a space, use the "short" name for the directory with the space in it (i.e. "progra~1" instead of "Program Files").

To temporarily disable scripting, uncheck the "Enable Scripting" box.

Writing a script

Any text editor can be used to write a script and any file naming convention may be used with the exception of spaces-- do not use spaces in the script file name. Generally VB Scripts should use the ".vbs" extension, but this is not required. Notes:

  • Do not place any VBScript commands outside of a function in the script. These commands will execute upon the initialization of the script and may cause problems. Do not attempt to write anything to the screen or ask any questions of the user as these will not work and may cause the EDI system to halt.
  • There can be multiple functions in the same script file.
  • The scripts consists of one or more functions.
  • For more information on VBScript visit Microsoft's VBScript explanation.

Functions

There are several type of scripts within the EDI Solution.


Export date or reference modification scripts:

These VBScript functions must accept exactly one string input and must return a string.

Example: Function that would add "00" to the beginning of everything.

Function AddZeros(Information)
  AddZeros = "00" & Information
End Function

Note: Date and reference scripts only operate during exports.  


Import product qualifier modify scripts:

These VBScript functions must accept exactly two string inputs and must return a string.

Example: Function that would add "X" to the end of the product information

Function AddAnX(Qualifier,Information)
  AddAnX =   Information & "X"
End Function

Import product qualifier check scripts:

These VBScript functions must accept exactly two string inputs and must return either a 1 or a 0. The function of these scripts is to determine if a product line should be processed (return a 0) or skipped (return a 1).  

Example: Function that would skip an item if the "AA" qualifier is found.

Function Product_Qualifier_Test(Qualifier,Information)
  if Qualifier = "AA" then
    Product_Qualifier_Test = 1
  else
    Product_Qualifier_Test = 0
  end if
End Function

Segment scripts:

These functions in the script file do not accept any input parameters and do not return any information directly from the function.  All information is passed via the variables noted below.

For each segment in the import or export, the script file will be checked for a function name using the the segment name as the function name.

Each element of the segment is provided in an array called "Element" referenced from the VBScript object "EDI". To access the first element reference EDI.Element(1) etc.

To modify an element set the proper array element to a new value (I.E. EDI.Element(2) = "NEW" would cause element 2 in the output segment to be "NEW").

For segments with sub-elements, the sub-elements must be accounted for when accessing the EDI.Element information.  For example if a segment as an element, then an element with 3 sub-elements and then another element.  Access to the 3rd element is via EDI.Element(5) not EDI.Element(3).

Script Variables:

There are additional values available to the VBScript via the EDI object.

  • EDI.Partner = Current trading partner (read only)
  • EDI.Document = Current document (read only)
  • EDI.Branch = Sets SYSPRO branch for a sales order (inbound only)
  • EDI.GeographicArea = Sets sales order header geographic area (inbound only)
  • EDI.Warehouse = Sets the warehouse to use for the order (inbound only)
  • EDI.Version = Gets the current version of the scripting DLL (read only)
  • EDI.ReleaseDate = Gets the current release date of scripting DLL (read only)
  • EDI.Segment = Value of current segment
  • EDI.ConnectionString = Sets or Returns the connection string - No need to call, the connection string will be created automatically
  • EDI.Company = Value of the SYSPRO company
  • EDI.Server = Value of the SQL server
  • EDI.VersionString = Version() - Returns a string of the current script library version
  • EDI.ReleaseDateString = ReleaseDate() - Returns a string of the current script library release date
  • EDI.ShipAddress1 = Sets ship to address line 1 to value provided (inbound only)
  • EDI.ShipAddress2 = Sets ship to address line 2 to value provided (inbound only)
  • EDI.ShipAddress3 = Sets ship to address line 3 to value provided (inbound only)
  • EDI.ShipAddress3Loc = Sets ship to address line 3 locality to value provided (inbound only)
  • EDI.ShipAddress4 = Sets ship to address line 4 to value provided (inbound only)
  • EDI.ShipAddress5 = Sets ship to address line 5 to value provided (inbound only)
  • EDI.PostalCode = Sets ship to address postal code to value provided (inbound only)
  • EDI.ShipToFlag = Instructs system to either replace the ship to address with the information provided or merge the address information. Valid values are EDI.ShipToReplace and EDI.ShipToMerge. (inbound only)

SQL-related Functions:

  • EDI.SQLUsername = SQL user name (write only)
  • EDI.SQLPassword = SQL password (write only)
  • EDI.Database = SYSPRO SQL database
  • EDI.ConnectionString = BuildConnectionString(Username,Password) - Builds and returns a SQL connection string; note: the connection string is also saved for any SQL calls or Call BuildConnectionString(Username,Password)
  • Object = SQLExecuteScalar(Command) - Executes the SQL command and returns the first value
  • Object = SQLExecuteNonQuery(Command) - Executes the SQL command and returns the number of records processed

Logging Functions:

  • EDI.SQLError = The last exception thrown by any SQL call
  • EDI.Log = Enable debug logging within the VB Script (EDI.Log = True to turn on, EDI.Log = False to turn off)
  • EDI.LogFile = Name of the log file (Default = "EDI.Scripting.CCYYMMDD" in the SYSPRO BASE\CadacusSFS directory)
  • EDI.LogInfo(<Info>) = Writes the line <Info> to the logfile
  • EDI.LogFileOptions = Options to modify default logging details
  • EDI.Overwrite = LogFileOption to overwrite log file if it exists (append is default)

More Logging Functions

  • call LogInfo(Message) - Writes the message to the log file if the "Log" variable is true

Special Functions:

There are also special functions available via the EDI Object.

  • EDI.Value(<ID>) = Stores or retrieves a value to be used later. The value of <ID> can be alphanumeric or numeric and is stored as an object.
  • EDI.sValue(<ID>) = Same as Value, but specifically stores and retrieve a string.
  • EDI.iValue(<ID>) = Same as Value, but specifically stores and retrieve an integer value.
  • EDI.dValue(<ID>) = Same as Value, but specifically stores and retrieve a decimal value.
  • EDI.RequestCFF(<Name>,<Type>) = Requests the import/export retrieve the value of the CFF indicated.
    Note:  Put the command "Call" in front of this function for VB to parse it properly;
    also, <Name> and <Type> are strings and will generally need quotes around them; 
    for example,

call EDI.RequestCFF("ORD","BIL001")

  Use EDI.CFF to retrieve the value. This function is generally placed in an "Initialize" function.

  • EDI.CFF(<name>,<type>) = Value of CFF setup using the EDI.RequestCFF function.

The following functions allow for writing messages to the import/export report. 

  • EDI.MessageSeverity = Sets the severity level of the message.
  • EDI.MessageLocation = Sets the report location of a message.
  • EDI.Message = Adds a message to be written to the import/export report using current severity and location settings.
  • EDI.Message(MessageSeverity) = Add message with severity.
  • EDI.AddMessage(Message,MessageSeverity,MessageLocation) = Adds a message using the indicated severity and location.  Must be used in script as "Call EDI.AddMessage..."
  • EDI.Notice = Message is a notice
  • EDI.Warning = Message is a warning
  • EDI.Severe = Message is an error
  • EDI.Report = Message will be reported without any header
  • EDI.Debug = Message will be reported only if debug is enabled
  • EDI.DebugMode = Debug mode (True or False).  May also be enabled by debug option on menu at document runtime.
  • EDI.SelectionFailure = On Exports when set from "BeforeExport" function, causes document to be skipped.  (12/5/2019 release or later required)
  • EDI.Skip = On imports when called during segment processing causes document to be skipped. EDI.Message will only be reported if debug is enabled. (12/5/2019 release or later required)
  • EDI.Immediate = Print message on report immediately
  • EDI.EndOfDocument = Print message on report after parsing the document
  • EDI.EndOfDocuments = Print message on report after parsing all documents
  • EDI.EndOfProcessing = Print message at the end of the report

The following functions are for sales order imports to set the value of custom form fields at the order header or detail level. In the following functions <Name> refers to the 6 character custom form field name.  The script is responsible for clearing the CFF information in between orders or detail lines.

  • EDI.OrderHeaderCFF(<Name>) = Stores the value for a order header custom form field
  • EDI.OrderDetailCFF(<Name>) = Stores the value for a order detail line custom form field (Not functional at this time)
  • EDI.ClearOrderHeaderCFF = Clears all currently set order level custom form fields
  • EDI.ClearOrderDetailCFF = Clears all currently set detail level custom form fields

The following function allows for the adding of comments to the order.

  • EDI.Comment(<Comment Type>) = Stores the value as a comment line

Comment type should be either EDI.OrderComment or EDI.LineComment, however EDI.LineComment is not functional at this time.

The following function allow for the insertion of additional segments into an export document. To use set the InsertBefore or InsertAfter flag in a segment function.  The scripting engine will then call the function "InsertBefore" or "InsertAfter" depending on the flags set. In the function define the segment to be inserted with "EDI.InsertSegment" and then set the EDI.Element values.  If multiple segments are required setting the insert flags again will cause the function to be called again. EDI.Segment contains value of segment triggering the inserts.

  • EDI.InsertBefore = Set to "True" to request segment(s) to be inserted before the current segment
  • EDI.InsertAfter = Set to "True" to request segment(s) to be inserted after the current segment
  • EDI.InsertSegment = Set to the segment name to be inserted (only used in the "InsertBefore" or "InsertAfter" functions).
  • EDI.SkipSegment = The current segment will not be processed further

To specify the function to be called when EDI.InsertBefore or EDI.InsertAfter is enabled (set to True), set the variables specified below to the name of the desired function to be called.  Note it gets reset to the default function after each call.

  • EDI.InsertBeforeFunction = "YourAlternateBeforeFunctionHere"
  • EDI.InsertAfterFunction    = "YourAlternateAfterFunctionHere"

(beta) Calling SYSPRO Business Objects can be accomplished with the following functions. Note: Requires EDI Update and Support Library update after November 13, 2019.

  • EDI.BusinessObject = The 6 character business object name
  • EDI.BusinessObjectType = One of the following options
    • EDI.PostBusinessObject = Business object is a post type of transaction
    • EDI.PostBusinessObjectWithLog = Business object is a post type of transaction and logging of the XML is desired (not all business objects are supported). Logging of the XML is in the base\CadacusSFS\YYYYMMDD folder.
    • EDI.QueryBusinessObject = Business object is a query
  • EDI.XMLIn = XML string for the XMLIn portion of the business object call
  • EDI.Parameters = XML string for the Parameters portion of the business object call (when required)
  • EDI.BusinessObjectReturnFunction = Name of function in the script to call after the business object is called (optional)
  • EDI.Exception = Available in the return function if business object generated an exception
  • EDI.XMLOut = XMLOut return of the business object call - Available in the return function

 

Additional script functions:

The following functions will be called at the indicated location if they exist in the script file.

  • Initialize - is called once at the very start of the import or export process
  • BeforeExport - is called during an export after the document is selected for export but before any segments are written. (12/5/2019 release or later required)
  • BeforeUpdate - is called after the system locates the sales order it is going to process;  e.g., during the import of a document that updates (rather than creates) a sales order, such as the 860; note it is NOT called by the 832/860/DELFOR/DELJIT documents
  • AfterParse - is called during imports after a document has been parsed and before the import starts. Variables in an example which facilitates the setting of the Ship-To address are as follows:
Function AfterParse()
	EDI.ShipAddress1 = ""
	EDI.ShipAddress2 = EDI.Value("Line2")
	EDI.ShipAddress3 = EDI.Value("Line3")
	EDI.ShipAddress3Loc = ""
	EDI.ShipAddress4 = EDI.Value("Line4")
	EDI.ShipPostalCode = EDI.Value("PC")
	EDI.ShipToFlag = EDI.ShipToReplace
	EDI.Message = "Ship to address replaced."
End Function
Note: EDI.ShipToFlag is required to be set to either EDI.ShipToReplace or EDI.ShipToMerge. Replace sets the ship to address exactly as it is set in the script. Merge just sets the values that are not spaces into the shipto address to allow for only changing certain lines.
  • AfterProcessing - is called during imports after a document has been imported; used for reporting and other functions, not for updating information
  • AfterOrderClosed - is called after all processing is completed on a sales order.  The variable EDI.SalesOrder is available in this function to reference the sales order which was just created.
  • Finalize - is called at the end of processing

ST Address lines for this example:

N1*ST*HD SUPPLY FACILITIES MAINTENANCE~
N3*PO Box 4942~
N4*Orlando*FL*328024942*US~

Example - Do not write the first line

Function N3()
  EDI.Value("Line2")=EDI.Element(1)
End Function

Example - Do not write the LOC

Function N4()
  EDI.Value("Line3")=EDI.Element(1)
  EDI.Value("Line4")=EDI.Element(2)
  EDI.Value("PC")=EDI.Element(3)
End Function

Example - Set Element 1 of the BIG segment to a specific value

Function BIG()
  EDI.Element(1) = "XX"
End Function

Example - Save Element 2 of a REF segment for use in a later function

Function REF()
  if (EDI.Element(1) = "AA") then EDI.Value("SAVE1") = EDI.Element(2)
End Function

Example - Check saved value

Function IT1()
  if (EDI.Value("SAVE1") = "TEST-VALUE") then EDI.Element(2) = "NEW-VALUE"
End Function

Example - Segment setting up insert

Function PO4()
  EDI.InsertBefore = True
End Function 

Example - InsertBefore sample function

Function InsertBefore()
  EDI.InsertSegment = "REF"
  EDI.Element(1) = "AA"
  EDI.Element(2) = "REF Value"
End Function 

Example - Writing a warning message to the import/export report

Function IT1()
  EDI.Message = "This is a warning"
  EDI.MessageSeverity = EDI.Warning
End Function

Connect to SQL and query the database (It is suggested to use the newer EDI.ExecuteScaler and EDI.ExecuteNonQuery) :

Function N1()
   'Variable Declarations
        Dim ConnectionString
        Dim Local
        Dim PONum
        Dim objConnection, objRecordset, Returnvalue, Returnvalue1
        Set objConnection = CreateObject("ADODB.Connection")
        Set objRecordset  = CreateObject("ADODB.Recordset")
        PONum = "0000000000"
    'Check to make sure I am working in the N1 ship-to loop to identify the customer location
     If EDI.Element(1) = "ST" Then
        Local = EDI.Element(4)
        EDI.Value("Location") = EDI.Element(4)
    'Connect to SQL	
        objConnection.Open "Provider=SQLOLEDB;server= _
                            {Server IP address};database={DBName};User ID={SQLUID};Password={SQLPASS}"
    'Execute SQL Query
        Set Returnvalue = objConnection.Execute("SELECT Customer from EDIXRefLocation WHERE _
                            Location='" & Local & "'")
        Set Returnvalue1 = objConnection.Execute("Select SalesOrder From SorMaster Where Customer='" _
                            & Returnvalue(0).Value & "'And "&" CustomerPoNumber='" & PONum & "'")
    'Write comments on order and message on report
        EDI.Comment(EDI.OrderComment)= "Order Number:" & Returnvalue1(0).value
        EDI.Message=Returnvalue(0).value & "" & Returnvalue1(0).value
     'Close SQL Connection
        objConnection.Close
   'End Loop
    End If
End Function

Notes:

  • No validation of the returned information per ANSI/EDIFACT specifications is performed
  • The above samples are just for illustration

  Indicating when to use the scripts in the EDI Solution

Date, Reference and Product Qualifier Scripts

To indicate a script for a particular cross-reference entry, modify the entry and select the "Scripting" tab.

Enter the name of the script file.  This file name is relative to the script directory entered in the system setup.  Use the browse for assistance.

Enter the name of the function to call whenever the selected item of information is being processed by the EDI Solution.  Use the browse for assistance.

For product qualifiers enter either the modify or check script (or both) and indicate if the modify script should be called on imports and/or exports. The test function only works on the modify script.

The "Verify Script" button may be used to check the script is written properly. This is only a syntax check and does not guarantee the script will operate properly.

The "Test Function" button may be used to check the operation of a function.  Enter the information to be sent to the function in the "Input" box and click the button.  The function will be invoked and the return information displayed.

Note: For the Date qualifiers, the script is passed either the 8 digit (CCYYMMDD) year or the 6 digit (YYMMDD) year depending on the EDI version being output.  The function must pass back the date in the correct format.

Segment Scripts

Click on the "Scripting" tab in the document setup.

Enter the name of the script file.  This file name is relative to the script directory entered in the system setup.  Use the browse for assistance.

The "Verify Script" button may be used to check the script is written properly. This is only a syntax check and does not guarantee the script will operate properly.

The "Edit Script" button is not currently operational.  Scripts must be edited outside of SYSPRO.

Note:  using a tool such as the free version of EDI Notepad for analysis and troubleshooting is recommended.

Note:  When not getting expected results with a script, it is recommended to edit the document in a tool such as EDI Notepad.  For example, in EDI Notepad, open the segment in question, select "Edit" from the drop-down list, right-click and select "Properties" on the segment.  Review the tabs of elements and determine if sub-elements exist, since sub-elements will affect the numbered occurrence of the elements referenced after it.  Below is an example of sub-elements in EDI Notepad. In this example, CTP is a segment and the CTP05 element has 15 Sub-elements while none of the other CTP elements have sub-elements.  Consequently, to reference CTP06, EDI.Element(20) must be used (CTP01 through CTP04 plus the 15 sub-elements in CTP05 = 19).  

 EDI Notepad Example

Example

The following example assumes SYSPRO is installed in the directory "C:\SYSPRO7".

Our trading partner has asked us to supply an indicator on a LIN segment showing if the product is discontinued or not.  The partner wants a "Y" or an "N".  Our company indicates a part has been discontinued by setting the SYSPRO cycle count number to 99. Therefore to satisfy the trading partner a "Y" should always be sent unless the SYSPRO cycle count is equal to 99.

How to accomplish this with scripting.

Create a directory under the BASE directory called "EDIScripts" (Of course you can place this directory anywhere on the SYSPRO application server).  Run the EDI System Setup and go to the "Files and Paths" tab.  Enable scripting and enter "C:\SYSPRO7\BASE\EDIScripts\" as the script path.

Using a text editor copy the following lines into a file call "Sample.vbs" in the EDIScripts directory.

-----  Copy starting AFTER this line  -----

Function Discontinued(sInfo)
  Discontinued = "N"
  If (sInfo = "99") then Discontinued = "Y"
End Function

-----  Copy ending BEFORE this line  -----

Set up the outgoing EDI document as normal. While setting up the outgoing product qualifier for the discounted status go to the "Scripting" tab. Use the file browse to select the "Sample.vbs" script file. Use the function browse to select the "Discontinued" function.

Click on "Validate Script" to verify the script is properly written.

Enter "00" for the input parameter and click "Test Function".  The return value should show "N".

Enter "99" for the input parameter and click "Test Function".  The return value should show "Y". 

Output the EDI document as normal.

Why CADACUS SOLUTIONS FOR SYSPRO?

Since 1991, as developer of the standard EDI, Shipping, and Freight Calculator Solutions for SYSPRO, our products are fully integrated with SYSPRO and drive efficiencies for hundreds of users worldwide.

Why SYSPRO?

For 35 years, SYSPRO has had a single product focus resulting in an award-winning ERP system with a track record of success for over 14,000 customers worldwide.