Home
 

The following is an explanation of the scripting capabilities of the EDI Solutions for SYSPRO.

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-- get it here.  To check whether the Cadacus Support System is already installed, look in Control Panel--> Programs.
  • Latest release of scripting system is August 14, 2017.
  • 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.  The script consists of one or more functions (described below). 

For more information on VBScript visit Microsoft's VBScript explanation.

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.  Also, do not write anything to the screen or attempt to 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.

Functions

There are several type of scripts within the EDI Solution.


 Connect to SQL and query the database:

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

Export date or reference modify 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(sInfo)
  AddZeros = "00" & sInfo
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 Product_Qualifier(Qualifier,Information)
  Product_Qualifier = 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").

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

Logging Functions:

  • 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)

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

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.
  • BeforeUpdate - is called during the import of a document that updates (rather than creates) a sales order, such as the 860; 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 = "This is a test"
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
  • 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

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:\Program Files\SYSPRO60".

Our trading partner has asked use 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:\Progra~1\SYSPRO60\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  -----

Setup 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.