> VBScripting in SYSPRO

VBScripting in SYSPRO

Programming languages such as Visual Basic and Net Express typically require a development environment to enter the source code and a compiler to compile the code before running. In most cases, only the compiled code (i.e. non-human-readable) is deployed to the machine that will run it.

VBScript (a subset of Microsoft Visual Basic) is, however, just text and the script can be created using any basic text editor, including SYSPRO's built-in VBScript Editor.

The script remains as text until it is run, at which point it is compiled and executed as interpreted code.

Many SYSPRO screens contain forms against which macros can be stored. Macros are a means of adding programming logic (in the form of VBScript) that is run under certain conditions, called events. This could be related to the form (e.g. when the form is loaded or refreshed) or against a particular field on the form (e.g. when it gains focus, or after it is changed). The possible events that can be used depend on the form or field you are accessing.

Within SYSPRO you can define VBScripts within the following:

  • Display forms
  • Entry forms
  • Custom forms
  • Customized panes (e.g. Advanced listviews, Graphs, Web browser, Reports,PDFs)
  • Associated panes
  • Electronic signatures
  • Alerts

  • Toolbar controls

Your VBScript code is saved for you on the SYSPRO application server and is automatically transmitted to the client workstation as required (the scripts are cached on the client workstation so that they don't need to be brought across from the server before they can be run).

VBScript in Forms

Scripted fields

A scripted field is a container that can be placed on a form, and which can be modified using VBScript.

During its creation you specify whether it is an alphanumeric, numeric or a date field and its size (up to 100 characters for alphanumeric and 12.6 for numeric). You can specify the number of decimals for numeric fields, and for all types you can flag the field as read only.

A scripted field can be added to both display forms and entry forms. On entry forms, the scripted fields are set as read/write by default. On display forms you can still manipulate the contents using script.

Scripted fields are global. Once created they are available for use on all forms, although their contents are only available within the program in which they were created. You can have a maximum of 300 scripted fields, although only 30 can appear on any one form. A scripted field can be set as read only, in which case the caption appears in gray. A scripted field appears with a script icon alongside it.

The list of available field events for a scripted field differs between display forms and entry forms. On display forms, only the OnMenuSelect field event is available for a scripted field.

On entry forms, the OnAfterChange, OnBeforeChange, OnButtonClick, OnGainFocus, OnLostFocus and OnMenuSelect field events are available for scripted fields.

If you delete a scripted field using the Field Selector and it is still present on a form with VBScript against it, you will receive a warning message stating that it will remain on all forms on which it already resides, but will be removed from the list of available fields. While this field remains on the form, it will continue to work as before. When it is removed from the form, it will no longer exist.

You can use VBScript to update the value against a scripted field located on other forms within the same program (e.g. a scripted field on the Sales Order EntryLine Information form can be updated by a script residing on the Order Header form. This value would not be overwritten by a refresh on the Line Information form).

Dates

Dates are always returned to a script in the format CCYYMMDD. When supplying dates in a script it needs to be supplied in the format CCYY/MM/DD or in the format CCYY-MM-DD (i.e. the same way it is supplied to e.net Solutions business objects).

Data Types

Unless it is numeric, script data is always of type 'String'. You can view the data type using the VarType function as indicated by the following command:

Msgbox VarType(variable_name)

If it returns an '8' it is a string; if it returns a '5' it is a double-precision floating point number.

Using e.net Solutions business objects within a form

You can use any e.net Solutions business object to interrogate or post data as a way of customizing forms using VBScript.

Before using an e.net business object outside of SYSPRO you need to successfully log on to e.net Solutions in order to receive a UserID. This UserID must be supplied each time a business object is called and is used to verify that you are a valid user as well as to retrieve your settings and the company's settings.

The first time that you need to use a business object within a form, you reference a system variable called enetGUID (instead of using the e.net Solution logon utility). If this is the first time that the system variable has been used since you logged into SYSPRO, a UserID is provided for you and inserted into this variable. If this system variable has already been used since you logged into SYSPRO, the enetGUID system variable will already contain the UserID which is what you will use.

Form actions

You can fire VBScript events on a form by right clicking a form and selecting Insert Form Action from the shortcut menu (see Forms).

Field Description
Insert Form Action Select this to add an action to a form.

A form action is an user-definable hyperlink located at the bottom of a form in the Action Panel. The hyperlink can be configured to call a SYSPRO program and pass it parameters, or to fire a VBScript event.

You can have multiple form actions defined per form and they can be any combination of these types. This option also displays the currently available form actions which can be shown or hidden on a form.

Removing a form action does not remove the VBScript function associated with the function; that must be done using the VBScript Editor.

VBScript in Listviews

You can use VBScript to control what is displayed and how it appears in a listview.

Once the XML to define the listview structure has been built, it is passed to the ListviewProperties variable which renders the structure.

You can use a wizard to assist you in building the XML of your listview. The Listview Designer wizard assists you in creating your own listview structure, while the Listview Properties wizard assists you in creating a listview using the output of a business object to populate the listview.

Right-click on a column header in the listview and select Customize->Macro for: to display the VBScript editor.

String lines of up to 2000 characters can be inserted into a VBScript and exported successfully as a customized pane.

VBScript in Tiles

You can use VBScript to control the behavior and appearance of a tile in the Favorites or Navigation panes. View the tile properties by expanding the Tile variable in the Variables treeview.

To control tiles

  1. Launch the VBScript Editor.

  2. Select the event you want to trap. The VBScript for: window is displayed, enabling you to configure various tile properties ranging from changing the picture, title and tooltip to injecting values into the tile.

    Variable Description
    RefreshInterval This is a value in minutes with decimals (i.e. a value of .5 would be 30 seconds). After this interval the OnRefresh event will fire; you can use this event to refresh the contents of the tile at nominated intervals.

    You can suspend the OnRefresh event from firing by right-clicking on a Task Panel and selecting the Suspend Refresh Events option. This can be useful for an administrator to prevent OnRefresh events from firing while testing out VBScripts. All OnRefresh events for all items in all Task Panels will be suspended until you uncheck this item.

    The following code shows the RefreshInterval being set to half a minute (30 seconds) against the tiles OnLoad function:

    Function Tile_OnLoad() 
Tile.CodeObject.RefreshInterval = .5 
End Function
    GlobalVariable This is a read-write variable for the current tile.
    DesktopAlert This allows you to define a popup message.
    TileNumber This is a write-only value that allows you to inject values into the tile.

    The number can be an integer between 0 and 9999999999.

    Tile values are separated by a | (pipe) character. The values will be injected into the XAML theme controlling your tile and will appear under the title of the tile.

VBScript in Toolbars

You can use VBScript to control the behavior and properties of buttons on any toolbar using VBScript.

  • Hide or display a button.
  • Disable or enable a button.
  • Change the value of a button (if it is an editable control).
  • Cause a button to be 'executed' (i.e. behave as if you had clicked it).

  • Modify the behavior of a new toolbar button.

To control toolbar functions

  1. Launch the VBScript Editor.

  2. Select the ToolbarButton variable from within the SystemVariables (actions) section of the Variables pane.

    The Modify Toolbar Buttons window appears.

  3. Select the toolbar button you want to modify. The properties (Caption and ID) of that button will be displayed.

  4. Indicate the options you want to apply and select Add to List.

  5. When you have completed adding your buttons to the list, select Insert VBScript Code to insert an XML snippet of code into your VBScript code at the current insertion point in the editor.

  6. You can cause a button to be executed by selecting the Execute option from the Action dropdown list (equivalent to clicking on the button or pressing Enter on an edit control).

    If the button is an editable control then you can change the value of the button as well as execute it.

Creating a new toolbar button using VBScript

The simplest way to add new buttons to an existing toolbar is by copying an existing button. This is done while in Customize mode:

  1. Hold down the Ctrl key on the keyboard.

  2. Click on the button you want to copy.

  3. Drag the button to another location on the toolbar.

    As you hover over the toolbar, an area will be highlighted with a black vertical bar. This shows where the button will be placed if you release the mouse button.

  4. Release the button and a duplicate of the selected button will be added to the toolbar.

  5. Change the name and functionality of the button by right-clicking on it while in Customize mode.

A new button can also be added to a toolbar when in Customize mode by right-clicking on a toolbar button to display its properties. At the Add New Button: option enter the name you want to assign to the button and press Enter. The new button is created, but is completely blank apart from the assigned name.

Configuring toolbar buttons to run a VBScript function

There are two parts to configuring a toolbar button to call a VBScript function:

  • Specify the name of the VBScript function against the button.

    While in Customize mode, right-click the button to display its properties. Enter the name against the VBScript function field within the properties list.

    When you press Enter, the button's properties will be closed and the name shall be saved against the VBScript function.

    Exit the Customize mode by clicking on the Close button on the Customize screen.

  • Create the function in the VBScript Editor

    To supply the code for a function using the VBScript Editor program, exit the Customize mode. Right-click the toolbar and select Macro for:xxxxxxxx to launch the VBScript Editor.

    The function must be manually created by clicking on the Edit VBScript button, which loads the VBScript for: screen, where you add your VBScript code.

    The first line of code to enter is the Option Explicit statement, which forces you to explicitly declare variables before you can use them.

    The next line of code will be the start of the function itself. The name of the function is the same as supplied to the toolbar, but with the spaces removed (as function names cannot contain spaces).

    The line of code to open the function is (assuming that the name of the VBScript function is My VB Script Code):

    Function MyVBScriptCode()

    The rest of the code to perform the function must then be entered, followed by the End Function statement.

    Below is a sample of code that displays the stock code description on a toolbar which is part of the Inventory Query program.

    Option Explicit
Function MyVBScriptCode() 
MyVBScriptCode = false
msgbox StockCodeDetails.CodeObject.Description,, "Stock Description" 
End function

    The first line of code within the function doesn't need to be present in this case, because the button containing the VBScript function name was created from scratch.

    This line of code [MyVBScriptCode = false] is useful when you have copied a button to create this one, as it prevents the new button from performing its original task (or a task that was linked to the original button when it was copied).

    When the function has been entered, exit the VBScript for: program. At the VBScript Editor screen, click on the Save button. You will be returned to the program containing the button.

VBScript in Customized Panes

  • The power of a customized pane is derived from the macro events and VBScripting. The customized panes for all object types have the same core macro events, however, some have additional macro events to cater for functionality specific to their object type (e.g. the OnRowSelected event is fired when you click on a listview row).

    Each object type can also have variables that can be used in the VBScripting that are specific to the object type (e.g. arrays that enable you to retrieve the values in the listview row that was clicked and those to retrieve the contents of an RTF file for the Rich Text Notepad object type).

  • A customized pane can be configured to have the OnRefresh macro event fired programmatically by other panes within the same program, or to fire the event at regular intervals. This keeps the displayed information up to date, even if the operator is not interacting with it.

  • When passing multiple pieces of information through to the customized pane using VBScript, do not use a semi-colon (;) as the delimiter as SYSPRO already uses this as its own delimiter. If the semi-colon is used, only the first piece of information will be received by the customized pane and placed in the RefreshValue variable. In addition, note that using a space as your delimiter could prove problematic if one of your pieces of data contains a space.

Resetting the contents of a customized pane form object

You can reset the content of a customized pane form using VBScript code that passes a space character to the UpdateFormValues variable (this variable is the one that you would normally use to populate the form, and is found in the CustomizedPane section within the Variables pane).

Passing multiple values to a customized pane from another pane

A pane within the same program as a customized pane can force the customized pane to fire its OnRefresh macro event, and at the same time it can pass through a string of text.

This is done as follows:

  1. Locate the CustomizedPanes section within the Variables pane.

  2. Double-click the variable representing the customized pane to be refreshed.

    When you double-click on the name of the customized pane, the Define Action for screen is displayed and this screen enables you to specify how the customized pane is to be refreshed.

    The default is to pass through the text value doRefresh.

    The maximum size of the text that can be passed through to the customized pane is 80 characters, however you are not limited to passing through only one piece of information.

    [Note]

    When passing through multiple pieces of information, you need to have a character to delimit where the different pieces of information start and stop (The more commonly used characters for delimiters are a space or a comma).

    When the customized pane is refreshed, the value passed through is available within the RefreshValue variable in the Variables pane.

    If you are passing through multiple pieces of information in the RefreshValue variable, they will appear as one string. You need to be able to separate them out within the VBScript code of the customized pane.

    This can be performed using the Split function.

    The Split function needs to be given the full name of the RefreshValue variable, along with the delimiter so that it knows how to split up the contents of the variable.

.NET User Controls

If you host a .NET User Control in a customized pane, and you use the ExecuteScript request event to execute a VBScript directly from within your control, you can return a value to your user control as the second parameter, by returning the string in the function call.

To return a value to your user control from the VBScript function, assign the value to the name of the function:

Function getNumber

getNumber = "423"

End Function

The value '423' will be returned in the second parameter of the call statement.

VBScript in Flow Graphs

You can use VBScript to control a node's behavior and appearance.

The OnLoad event fires as a flow graph is opened. When you double-click on a node to launch an application, the OnDblClicked event is fired. The variable NodeClickedCaption contains the caption of the item just clicked, and NodeClickedID contains the ID of the item just clicked. The ID is a unique identifier for a node in a flow graph, and it is this ID that can be used in the FlowGraphNodes variable to modify this or any other node in the flow graph.

If you want to adjust the appearance or behavior of one or more nodes, then double-click the FlowGraphNodes item in the variable treeview and the Modify Flowgraph Nodes window is displayed. You can click on any node in the current flow graph and its caption and ID will be shown in the dialog box.

Now enter any new properties as required (e.g. a new title, color, style and/or program name). Select the Add To List function to add your properties to the list. Now you can click on another node and repeat the process of adding properties.

When you have finished, select the Insert VBScript Code function to insert an XML code snippet into your current edit point in your script.

To implement different functionality depending on the node that is double-clicked, interrogate the FlowGraph.CodeObject.NodeClickedID variable.

[Note]

Unlike most other controls using VBScript within SYSPRO, the VBScript that is created for a Flow Graph is saved in the Flow Graph file itself. This means that once you have created your Flow Graph and created a script for it, you can email the Flow Graph file to another person or organization and it should work correctly - there are no extra files that need to be deployed.

To control Flow Graph nodes

  1. Launch the VBScript Editor from within the Flow Graph pane (Right-click->Macro).

  2. Select the event you want to trap. The VBScript for FlowGraph window is displayed enabling you to access the various Flow Graph properties from the Variables pane.

    Variable Description
    DesktopAlert This variable is used to display a message on the operator's screen when a certain condition has been met.

    You can define the alert from this variable, including the duration, which defines how long the alert will stay on the screen, and the animation style which specifies how the alert will appear or disappear.

    FlowGraphNodes This variable can be used to determine the ID of a shape and code used to perform tasks dependent on which shape was clicked. This includes changing the shape's title or color theme and calling a SYSPRO program.

    This code can be placed against the flow graph's OnLoad function to be executed as the flow graph is loaded, or against its OnClicked function to be executed when the operator clicks on the selected shape on this flow graph.
    NodeClickedCaption This variable is used to return the current caption of the shape that was clicked.

    This could be used within a message back to the operator to confirm that a specific task must be performed, to update a global variable, or to write out to a custom log file.

    NodeClickedID This variable is used to return the ID of the shape that was clicked.

    You can implement different functionality depending on the shape that is clicked by interrogating the FlowGraph.CodeObject.NodeClickedID variable.

    This is typically used to determine which shape was clicked, or make sure that a task is only performed when a specific shape was clicked.

    OnLoad This event is fired when the flow graph is loaded.

    If the script associated with the operator's Home Flow Graph contains an OnLoad function, this event will fire as SYSPRO is loaded, and the code within this function will be executed. This will happen whether the Flow Graph pane is visible to the operator or not.

    This event also fires when you enter and exit design mode.

    OnClicked This event is fired when the operator clicks on any shape on any page within the flow graph, and there is VBScript code against that flow graph's OnClicked function.

    This event is configured against the whole flow graph, and can be stopped by setting the function to false within the function.

VBScripting in Graphs

You can use VBScript to control the creation of graphs. You define the structure and then populate it using XML. The definition would typically be against the OnLoad function, and the population against the OnRefresh function.

Sample XML is provided by the GraphData variable, but for a graph to be useful you need to populate the graph using real values (which is usually supplied by an e.net Solutions business object).

The following table describes the graph elements to use when adding your VBScript:

Element Description
Title

This element resides within the Titles node and provides a title for the whole chart (i.e. the one at the top of the customized pane).

It is used to specify whether the title should be displayed or not using the Visible attribute (where 0 is not to display, and 1 is to display). It also contains the title to be displayed against the Text attribute.

Labels

This element is used to define whether the labels that appear alongside the axis containing the static information should be straight or angled.

If this element is not present the labels will appear straight by default.

Legend

This element is used to specify whether the box containing the legend should be displayed, and how many columns it should consume.

The Legend is displayed if the Visible attribute is set to 1, and the number of columns to be displayed is defined using the ColumnCount attribute.
PanelDirection

This element is used to specify whether the graph should appear in its standard orientation, or if it should be rotated through 90 degrees using the Rotated attribute.

Panel

This element is used to supply information regarding the X and Y-Axis of the graph. These are defined within the AxisX and AxisY elements respectively.

The X-Axis is the one that runs from left to right, and the Y-Axis is the one that runs from bottom to top.

Series

This element is used to define the type of graph (using the Style attribute), what will appear within the Legend box (using the LegendText attribute), and to which Panel this Series entry relates (using the Panel attribute).

There should be one Series element for each Panel when there are multiple graphs in one customized pane.

Tooltips This displays a combination of the text supplied against the LegendText attribute of the Series element, the text supplied against the Label attribute of the Point element, and the value. This is displayed when you move your mouse pointer over the graph.

VBScript in Executive Dashboards

  • An executive dashboard consists of two parts, a Shockwave (.swf) file created using a product called Crystal Dashboard Designer, and VBScript. The VBScript retrieves the latest copy of the .swf file from the server, then calls the business object and stores the results in a predetermined file name. The VBScript passes the .swf file to the customized pane and Shockwave uses the output from the business object to render the dashboard.

  • Within the Refresh details section, the Ignore OnLoad VBScript checkbox is enables you to prevent the executive dashboard from firing during the initial load of the program within which it resides.

    If this option is checked the executive dashboard OnLoad event will only fire the first time that the customized pane's OnRefresh event is fired.

    Some dashboards can take a significant amount of time to retrieve the data and render the dashboard, and control is not returned to the program until the dashboard has completed loading.

VBScripting in Search Windows

A search window object is linked to a VBScript which handles the requests for the target table and the columns to be searched.

The VBScript uses the COMFND business object to return the XML results that populates the Search Results listview.

The VBScript code contains additional columns that have been commented out with an apostrophe. If you want to add one of the columns you just have to remove the apostrophe against both the listview configuration and the XML input to the business object.

If you need to add other fields from the same table that do not appear in the listview, you can add them to the VBScript.

VBScripting in Favorites

Each tile in the Favorites pane can be configured to use one or more of the macro events associated with it.

To add a macro event, right-click on a tile and select Properties > Macro from the context-sensitive menu.

Notes and warnings

VBScript processing considerations

  • When a script is run, the whole script is processed before the form is updated (i.e. the script must complete before control is returned to the form).

    Therefore, if you set multiple properties against a field in a script, each successive one overwrites the previous one. When the script finishes, only the last one causes a change to the form. The same applies to field properties.

  • If you have the same (or similar) logic in multiple VBScripts, consider extracting this logic and placing it into a VBScript Module.

    The benefit of placing this code in one place is that if it needs to change, you only need to change it in one place and it is automatically available in all scripts that reference it.

FORM event considerations

  • The firing of the OnStartEdit and OnStopEdit events are completely controlled by SYSPRO developers. They are always fired at least once per editable pane - the first time it is loaded and readied for editing. Thereafter, the SYSPRO developer can chose to use stop and start edit, or disable and enable fields as required. Therefore, using these events in your script may not produce the results you require.

    The OnSubmit, and OnAfterSubmit events are more suitable for you to use to determine if a line has been entered or saved.

    We recommend you use this event sparingly and you should always check that it fires at the appropriate times before using it. This event is considered legacy and is available only to provide backward-compatibility.

  • When the content of a standard SYSPRO form is modified by an operator, SYSPRO enables the Save or Post button on the toolbar, for forms that have these buttons. This can be after changing one field when editing an existing item, or after certain required fields have been populated when adding a new item.

    However, if the operator calls up a program and a VBScript event makes a change to the current item, the SYSPRO program does not know that this change has happened, so the Save/Post button is not enabled.

    You need to enable this button programmatically by setting the PostChangeEvent variable to 1.

Client/Server considerations

  • VBScripts are saved on the client in the \Base\Settings\Vbscripts folder.

  • You can ensure that scripts are only saved on the server by enabling the VBScripts are saved on server only option (System-wide Personalization).

  • You can encrypt VBScripts on the client machine by enabling the VBScripts are encrypted on client option (System-wide Personalization).

Role-based considerations

  • If the current operator is assigned to a role, SYSPRO firsts look for scripts defined against the role (in the \Base\Settings\Role_xxx folder) and then for system-wide scripts (in the \Work\vbscripts folder).

    Role-based scripts take precedence.

    When you clear an existing role-based VBScript, the system level is used. Ensure that the VBScript file no longer exists in the Role_XXX folder after clearing the role-based script.

Global Login/Logout considerations

  • Only the SystemVariables variables are available to your VBScript for the OnLogin event.

  • The VBScript created for the OnLogin and OnLogout events are saved as the file IMPMENXX in the Work\vbscripts folder on the application server and is therefore executed for everyone who logs into or out of SYSPRO.

  • As the login script runs before the main menu is loaded, you cannot call other SYSPRO programs, or interact with any customized panes.

    However, the e.net Solutions business objects can still be used at this point.

Reserved word considerations

  • You can use VBScripting reserved words as the caption for custom form fields and scripted fields. Although the script engine would typically report an error, SYSPRO overcomes this problem by appending an uppercase letter A to the end of the variable name. Similarly, a lowercase letter a is appended to caption names that start with a numeric character.

    For example: If you create a custom form field against the Inventory Master table called Empty, then you can drag this onto the Stock Code Details form in the Inventory Query program. Providing the field is not used in any VBScript code, it will not cause a problem. SYSPRO automatically adds the letter A to the end of the variable name (making it EmptyA) so that this field can be accessed using VBScript.

And Else Implements On Single
As ElseIf In Option Static
Boolean Empty Integer Optional Stop
ByRef End Is Or Sub
Byte EndIf Let ParamArray Then
ByVal Enum Like Preserve To
Call Eqv Long Private TRUE
Case Event Loop Public Type
Class Exit LSet RaiseEvent TypeOf
Const FALSE Me ReDim Until
Currency For Mod Rem Variant
Debug Function New Resume Wend
Dim Get Next RSet While
Do GoTo Not Select With
Double If Nothing Set Xor
Each Imp Null Shared  

Activity considerations

Operator access to the following activities within this program can be restricted. You configure this using the Operator Maintenance program.

Activity Description
Forms – Customization by operator Controls whether an operator can access options from the context menu displayed when right-clicking form fields.
VBScript editing Controls whether an operator can edit any VBScripts or change customized panes.
Forms - Caption adjustments for group/company

This controls whether an operator can customize field properties at group and company level.

[Note]

  • If you are not allowed access to the Forms - Customization by operator activity, then you will be unable to customize field properties at group and company level, even if you have been granted access to the Forms - Caption adjustments for group/company activity.

  • If you allow this activity for an operator, then the changes made to captions can only be viewed by that operator while maintaining the captions.

    Only operators who cannot maintain captions are able to view the results of the changes made (i.e. it is assumed that the operator making the changes - the administrator - needs to be able to view all captions at all times).

    Therefore, if you customize a form at group, company or role level, you need to deny access to this activity for those operators who must use the customized form. Otherwise the form you design (customize) is not displayed for the operators.

  • If this activity was previously enabled for an operator and is now denied, then any caption adjustments previously made for the operator are replaced by the default captions for the company or operator group (i.e. all forms will ignore any settings applied at operator level for the operator and will apply the settings at either group or company level).

VBScript vs Trigger considerations

  • Trigger programs cannot return values to SYSPRO forms - a trigger program is invoked or executed at a single point within the SYSPRO application. Once invoked, the trigger program cannot send a result back to the SYSPRO application.

    VBScript, however, can launch an application and then apply the results to one or more fields on a form.

  • Trigger programs run asynchronously (i.e. when a trigger program is launched, the SYSPRO application continues to run, even though the trigger program has yet to complete its task). This can cause application timing issues since the trigger program may assume that certain files or tables have been updated (or not) at the time the trigger program is executed.

    VBScript code is executed synchronously (i.e. the code must complete its task before it returns control to the SYSPRO application).