Scripting for EDI

Introduction

Areas of the EDI Import/Export process have been opened to modification via scripts. Visual Basic (VB) is the main scripting language. Java Script (JScript) is allowed as of 2024/02/13 (see notes). 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 Script 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 messages to the screen or ask 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:

Note: These scripts will not work in SYSPRO 8 and therefore they are not suggested for use. It is recommended for existing scripts to be converted to segment based scripts.

These script 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:

Note: These scripts will not work in SYSPRO 8 and therefore they are not suggested for use. It is recommended for existing scripts to be converted to segment based scripts.

These script 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:

Note: These scripts will not work in SYSPRO 8 and therefore they are not suggested for use. It is recommended for existing scripts to be converted to segment based scripts.

These script 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 script 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).

The EDI scripting is different from most programming as the script is not the main routine, the EDI Import/Export program is. It helps to think of the main EDI program calling a function (the script) and the script does one thing and returns. If the function needs to do more then one thing (change a element value, insert a segment), the function can perform the required operation and then use the options below to tell the main EDI program more processing is required.

Script Variables

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

EDI.Partner = Current trading partner (read only)
EDI.Document = Current document (read 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 (read only)
EDI.Company = Value of the SYSPRO company (read only)
EDI.Branch = Sets the SYSPRO branch for a sales order (inbound only)
EDI.GeographicArea = Sets sales order header geographic area (inbound only)
EDI.AlternateKey = Sets the sales order header alternate key (inbound only)
EDI.Warehouse = Sets the warehouse to use for the order (inbound only)
EDI.TargetWarehouse = Sets the target warehouse for a SCT order (inbound only, requires 03/08/2023 or newer)
EDI.LineWarehouse = Sets the warehouse to use for an order line (inbound only, option to recheck warehouse for each line in document setup must be set, Import programs and Support Library date 8/26/2020 or later required)
EDI.PriceCode = Sets the price code to use for the detail lines. Option can be set before processing the first stock line or during stock line processing, but can not be set after the segments have been read.
EDI.ShipVia = Sets the ship via on the order. This must be a valid ship via code if coded ship vias are in use or the full ship via if otherwise. Requires programs and library released 9/2/2021 or later.
EDI.TermsCode = Sets the terms code on the order. Requires programs and library released 9/7/2022 or later.
EDI.SpecialInstrs = Sets the special instructions on the sales order header.
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 (inbound only). Valid values are :
- EDI.ShipToReplace = Replaces the entire ship to address with the information provided
- EDI.ShipToMerge = Merges the information provided with the ship to details. Information provided from the script will only be used for the lines for which the script sets a value. Other lines will be left alone.
EDI.InputTest = Read only boolean flag (True or False). Set to the value of the input test flag of the input parameters (inbound only). Note : There are internal checks that can set the input test on for an individual order such as the purchase order check. (Requires import programs and support library dated 12/14/2021 or newer)
EDI.DocumentType = Sets the sales order document type (Requires import programs and support library dated 05/09/2022 or newer). The DocumentType must be one of the following :
- EDI.DocumentTypeOrder = Document type of regular sales order
- EDI.DocumentTypeBilling = Document type of billing
- EDI.DocumentTypeSCT = Creates a SCT sales order (requires 03/09/2023 or newer library and import)
EDI.SetAsStatus8 = True or False (boolean). Forces the document status to status 8 (ready to invoice). Requires import programs and support library dated 05/09/2022 or newer.
EDI.SetAsForwardOrder = True or False (boolean). Forces the document status to status "F" (Forward order). Requires import programs and support library dated 05/09/2022 or newer.
EDI.OrderType = Order type for order header (inbound only). Requires import programs and support library dated 05/09/2022 or newer.
EDI.SysproDate = Current SYSPRO date in CCYYMMDD format. Requires import programs and support library dated 04/25/2024 or newer.

Inbound order status

The following will allow the setting of the order status for new orders created by the imports:

EDI.Status = Valid sales order status. Requires support library and import dated 06/20/2022 or newer. The status may be set using one of the read-only values below.

The following are read-only variables for setting EDI.Status.

EDI.StatusSuspense; ("S")
EDI.StatusInProcess ("0")
EDI.StatusOpenOrder ("1")
EDI.StatusOpenBackorder ("2")
EDI.StatusInWarehouse ("4")
EDI.StatusReadyToInvoice ("8")
EDI.StatusForward ("F")
EDI.StatusCancelled ("\") (Requires import and support library data 07/27/2023 or newer)

The follow still work, but are depreciated by the EDI.Status variable.

EDI.SetAsStatus8 = True or False (boolean). Forces the document status to status 8 (ready to invoice). Requires import programs and support library dated 05/09/2022 or newer.
EDI.SetAsForwardOrder = True or False (boolean). Forces the document status to status "F" (Forward order). Requires import programs and support library dated 05/09/2022 or newer.

SQL-related Functions

EDI.Username = SQL user name
EDI.Password = SQL password (write only)
EDI.Database = SYSPRO SQL database
EDI.Server = SYSPRO SQL Server
call EDI.BuildConnectionString(Username,Password) - Builds a SQL connection string for use with the SQL calls
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

Notes:

The Support Library released 3/17/2020 or later is required for the SQL functions.
The SQL username and password can be entered into the System Setup in SYSPRO.
If a username and password is not provided, SQL Integrated security will be used. In order for this to work from a client, the SYSPRO communications service must be logged in as a domain user with SQL rights.
The connection string will be built automatically. There is no need to call BuildConnectionString unless the username and password needs to be supplied from the script.
Use EDI.SQLError to display or log a message if the SQL calls are not operating as expected.

Sample SQL call to retrieve a customer name. This assumes either the system is setup for integrated security to work or the username/password is in the setup.

Function AfterParse()
  sInfo = EDI.SQLExecuteScalar("SELECT Name FROM ArCustomer where Customer = '000000000000001'")
 
  EDI.Message = "If a SQL error occured it was : " + EDI.SQLError
  EDI.Message = "Customer name is " & sInfo
End Function

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 = "Scripting-CCYYMMDD.txt" in the SYSPRO Base\CadacusSFS folder)
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)

Alternate Logging Functions

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

Variables and the storage of information between functions:

The following functions allow the storage and retrieval of information between segment calls.

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 retrieves the value as an integer data type
EDI.dValue(<ID>) = Same as Value, but specifically stores and retrieves the value as a decimal data type. Note: VBScript has an issue doing some arithmetic on decimal data types.
EDI.rValue(<ID>) = Same as Value, but specifically stores and retrieves the value as a double data type. Requires support library dated 09/09/2021 or newer.
EDI.Holdvalue(<ID>) = true or false - The values are automatically reset inbetween documents. If a value is needed across multiple documents set EDI.HoldValue(<ID>) = true. Requires support library dated 12/28/2021 or newer.

Note: When moving values from a SQL recordset, do not use EDI.Value. Use the appropriate EDI.sValue, EDI.iValue, etc.

Custom Form Field Functions:

The following functions all for SYSPRO custom form fields to be retrieved.

EDI.RequestCFF(<Name>,<Type>) = Requests the import/export make the value of the CFF indicated available to future function calls. Once the need for the CFF is established, use EDI.CFF to retrieve the value. The EDI.RequestCFF function is generally placed in an "Initialize" function in the script. It can not be in the same function as the EDI.CFF call used to retrieve the value. It must be in a function that is executed before the function where the value is required.

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

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. Property must be set before EDI.Message is invoked.
EDI.MessageLocation = Sets the report location of a message. Property must be set before EDI.Message is invoked.
EDI.MessageNumber = Sets the report message number stored in the EDIReportDetail table if report storage is enabled (Requires support library and import/exports dated 2022/09/04 or later).
EDI.Message = Adds a message to be written to the import/export report using current severity and location settings
EDI.AddMessage(Message,MessageSeverity,MessageLocation) = Adds a message using the indicated severity and location. Must be used in script as "Call EDI.AddMessage..."
EDI.DebugMode = True or False (boolean). Enables or disables the debug mode setting. May also be enabled by debug option on the menu at document runtime.

The following are the message attributes available

EDI.MessageSeverity = Sets the severity level of the message. Must be set before the message is added.
EDI.MessageLocation = Sets the report location of a message. Must be set before the message is added.

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.DebugMode = Debug mode (True or False). May also be enabled by debug option on menu at document runtime.

The following are the message severities available

EDI.Notice = Message is a notice (default).
EDI.Warning = Message is a warning.
EDI.Severe = Message is an error.
EDI.Report = Message will be reported without any header.
EDI.SelectionFailure = On Exports when set from "BeforeExport" function, causes document to be skipped. The message will be reported only if "Enable selection failure" messages is enabled during the export. (12/5/2019 release or later required)
EDI.Skip = During imports, when called during segment processing, causes the current document to be skipped. (12/5/2019 release or later required). During exports, the document not to be exported. The document option to not export documents if there are errors and/or warnings must be enabled. Errors or warning produced by the normal processing will still be displayed. (08/10/2020 release or later of export document required, 12/5/2019 release of support library or later required). The actual EDI.Message will only be reported if debug is enabled.
EDI.SkipWithNotice = Same as EDI.Skip except on exports a message will always be reported. (05/18/2022 release or later required)
EDI.Debug = Message will be reported only if debug is enabled.

The following are the message locations available

EDI.Immediate = Print message on report immediately (default).
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.

Formatted messages (requires library and import/exports dated 8/24/2022 or newer)

Advanced messages can be formatted by the system and the message details stored in the report tables (if enabled).

The format of the message uses %1, %2 etc. to represent the location of detail information. The message detail is provided using the call EDI.MessageDetail(InfoType,Info) where InfoType is one of the types listed below and info is a string representing the information. EDI.MessageDetail must be called before EDI.Message.

Example 1:

call EDI.MessageDetail(EDI.StringType,"Hello")

EDI.Message = "The scripting says %1"

The result on the report will be the message "The scripting says Hello". If reports are being stored to the data tables, the EDIReportDetail table Info1 field will contain "Hello".

Example 2:

call EDI.MessageDetail(EDI.StringType,"00232")

call EDI.MessageDetail(EDI.DateType,"20220824")

call EDI.MessageDetail(EDI.Numeric,"123.4")

EDI.Message = "The total order for customer %1 on %2 is %3"

The result on the report will be the message "The total order for customer 00232 on 08/24/2022 is 123.4". If reports are being stored to the data tables, the EDIReportDetail table Info1 field will contain "00232", Date1 will contain 2022/08/24 and Value1 will contain 123.4.

Formatted message detail into types

EDI.StringType = Message detail is a alphanumeric string
EDI.NumericType = Message detail is a number (integer or decimal)
EDI.DateType = Message detail is a date in CCYYMMDD format

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.

Note: 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 (Requires 7/14/2020 support library or newer)
call EDI.ClearOrderHeaderCFF = Clears all currently set order level custom form fields
call EDI.ClearOrderDetailCFF = Clears all currently set detail level custom form fields

Sales Order Comments

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 is one of

EDI.OrderComment - Comment line is a general order comment.
EDI.LineComment - Comment line is attached to the current detail information.

Notes:

Order comments can only be assigned during or after the first document segment (BEG, BIG, etc.).
Line comments on work with import programs and the support library dated 6/2/2020 or later.
There is a maximum of 9 line comments per detail line.

Adding segments to inbound and outbound documents

The following functions 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 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"

SYSPRO Business objects

Calling SYSPRO Business Objects can be accomplished with the following functions. Note: Imports require EDI update after November 13, 2019, exports require EDI update after January 6, 2022. Imports and Exports require 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. Logging of the XML is in the base\CadacusSFS\YYYYMMDD folder. Not all business objects are supported. A list of supported business objects is available by clicking this link.
- 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

Sample business object call to INVTMB

Function PO1()
  ' Setup the call to the business object
  EDI.BusinessObject = "INVTMB"
  EDI.BusinessObjectType = EDI.PostBusinessObjectWithLog
  EDI.XMLIn = "<PostInvBinTransfers><Item><Warehouse>E</Warehouse><StockCode>A100</StockCode><Quantity>1</Quantity><FromBin>E1</FromBin><ToBin>E2</ToBin></Item></PostInvBinTransfers>"
  EDI.Parameters = "<PostInvBinTransfers><Parameters><IgnoreWarnings>N</IgnoreWarnings><PostingPeriod>C</PostingPeriod><ApplyIfEntireDocumentValid>Y</ApplyIfEntireDocumentValid><ValidateOnly>N</ValidateOnly><WarnIfLotDatesDiffer>N</WarnIfLotDatesDiffer></Parameters></PostInvBinTransfers>"
  EDI.BusinessObjectReturnFunction = "INVTMBReturn"
End Function

Function INVTMBReturn()
  ' Process the return from the business object here
  EDI.MessageSeverity = EDI.Notice
  EDI.Message = "Exception : " & EDI.Exception & ":"
  EDI.Message = "XMLOut : " & EDI.XMLOut & ":"
End 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). This function was enhanced on 04/21/2021 to work with shipment exports and to supply the shipment number or dispatch note when available.
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

Include files and global functions (requires programs and support library dated 2004/02/13 or later):

Many times there may be functions which are used in multiple scripts across the system. These can be placed in an separate file and automatically added to a script. To include another file in a script add the following line to the main script.

'IncludeFile "filetobeincluded.vbs"

Multiple files may be included in a script.

A function may only appear once in a script included any included files. For example if the "Initialize" function is used it can not be in the main script AND in an included script.

If there is code which is global in nature and always should be run as well as localized code which requires the same function name a "Global" function may be created. A global function is the standard function name with "Global_" prepended to the front. Both the global function and the standard function will be executed. The Global function is executed first (if it exists) and then the standard function is executed (if it exists). Global functions should only be used in an include file as there is no need for them in the base script.

For clarification of global functions, consider the following possibilites for an "Initialize" script.

"Function Initialize" in the main script, no initialize function in any included files - This is the standard method.
"Function Initialize" in an included script, no initialize function in the base script - This is an optional method for a universal function, but precludes the use of the same function in the main script.
"Function Global_Initialize" in an included script, no initialize function in the base script - This is an optional method for a global function.
"Function Initialize" in the main script and "Function Global_Initialize" in an included script - This is a method allowing the utilization of a global function for common code and a localized function. The global function is called first regardless of positioning in the scripts.

Note: The "Validate Script" function on the document maintenance scripting tab only checks the main script. It does not check any included scripts.

JavaScript (JScript) (requires programs and support library dated 2004/02/13 or later):

JScript is available as an alternative to VBScript. There are a few differences as noted below. JScript and VBScript files can not be mixed using the IncludeFiles option.

JScript is case sensitive for function names. Therefore the calls to functions such as "Initialize" etc. must be exactly as cased in the documentation. All segment names must be in upper case ("BEG"). The use of the "Global_" prefix must match.
JScript uses "//" for comments. Therefore the IncludeFile option must use the format as follows : //Include "Included.js"
The "EDI." variables and procedures do not appear to be effected by the case sensitivity.
JavaScript files must use a ".js" extension (all other files are assumed to be VBScript).

Scripting Examples

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

Scripting for EDI - Multiple Insert Afters sample

Scripting for EDI - Multiple Insert Afters Multiple Times Method 1

Scripting for EDI - Multiple Insert Afters Multiple Times Method 2

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

Note: These scripts will not work in SYSPRO 8 and therefore they are not suggested for use. It is recommended for existing scripts to be converted to segment based 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).

{arijfancybox url="https://www.cadacussolutions.com/_internal/edi_notepad_example2.html" width="820" height="630" title="EDI Notepad Example"} EDI Notepad Example {/arijfancybox}

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.

Scripting for EDI

Introduction

Enabling scripting

Writing a script

Functions

Indicating when to use the scripts in the EDI Solution

Example

Solutions

Tutorials

Technical Help

Why SYSPRO™?

Why Cadacus Solutions?

Cadacus Support