Function Reference

Functions and Language Resources for VBScript Engine

This page covers only functions specifically added by SpeedBase.
For all other functions supported by vbscript engine, consult the following documents:

VB Script Function Reference (w3schools)

VB Script Function Reference (msdn)

VB Script Language Reference (msdn)

Both function names and parameter names in this document are case insensitive. They are displayed here in varying case format for better readability only.

Built-in Functions by SpeedBase For Record Window

Functions described in this section can only be used by computed fields, action buttons and script objects (procedure) created on the form designer window.
Field names must match with an existing field of the catalog for which you create the expression or script.
If a function is NOT tagged as "expression" it should not be used in computed field expressions.

Field("fieldName") or GetFieldValue("fieldName") Applies to: Record Window (Procedure / Expression)

Gets the current value of the field whose name is given in quotes.
The data type to return will be automatically determined depending on the data type of the target field. If you want to process data as a different type than the original, you must use nField to process as number, sField to process as text, dField to process as date. e.g. if you want to add two numeric values retrieved from fields of text data type, you must use nField function.

Usage in computed fields:
Field("name surname")
Usage in script procedures:
varCustomerName = Field("name surname")

nField("fieldName") Applies to: Record Window (Procedure / Expression)

- Gets the current numeric value of the field whose name is given in quotes.
- If the field data type is checkbox, this function returns 1 for a checked box and 0 otherwise.
- If the field data type is option list, this function gets the numeric value assigned to the selected option. (see field properties window)

Examples:
Assume that you have a decimal type field which is named as "price". The example given below calculates the 20% discounted price
nField("price") * 0.8
or
nField("price") / 100 * 80
Assume that you have two numeric fields one is "price", another is "tax rate" (as percentage).
We will create two computed fields, one for "tax value", another for "final price".
This expression will calculate the tax value:
nField("price") * ( nField("tax rate") / 100)
This expression will calculate final price (assume that the previous):
nField("price") + nField("tax value")

sField("fieldName") Applies to: Record Window (Procedure / Expression)

Gets the current text content of the field whose name is given in quotes.
When used for a checkbox field, this function returns by default "Yes" for a checked box and "No" otherwise (unless you have modified it from preferences window).
Assume that you have a small text field named as "Name" and an option list named as "Colors" including options of "green","yellow","red" etc. This expression creates and displays a full sentence from existing like "John likes green color.".
sField("Name") & " likes " & sField("Colors") & " color."

dField("fieldName") Applies to: Record Window (Procedure / Expression)
dField("fieldName", formatString) Applies to: Record Window (Procedure / Expression)

Gets the date value of the field whose name is given in quotes.
You should use this function to read date data if you want to process the result with other date functions.
See the section "Date Formatting Parameters" at the end of this page for the parameter formatString.
"Birthday: " & dField("dob") (The date format is determined by locale default)

If you include formatting string, the date will be formatted and you may use the result for display purposes.
"Birthday: " & dField("dob", "mmm d, yyyy dddd") e.g. Birthday: Jun 1, 2020 Monday

SetFieldValue "fieldName", newValue Applies to: Record Window (Procedure)

Sets the value of the specified field with the specified value on the currently displayed record window.
For checkbox fields, you may assign either "Yes" or 1 to set it checked, "No" or 0 otherwise.
This function cannot be used in computed field expressions.
Use "UpdateRecordField" function if you want to update a field value in the database instead of a record window.

Example:
The following procedure will add "Hello" to the beginning of the field named "name".
SetFieldValue "name", "Hello " & sField("name")

InsertTextToField "fieldName", textValue Applies to: Record Window (Procedure)

Inserts text into the specified field at the current cursor position. You may use this function to insert fixed text, date/time or processed text content into a text box via an action button.
This function is supported in version 5.9 and later.
See WriteToFile function above for examples.

InsertTextToField "Notes", Date

IsFieldModified("fieldName") Return Type: Boolean Applies to: Record Window (Procedure)

Returns true if the data of the specified field was modified by user since last save of the record.

Example:
Display a message if the user has modified the name field any time since the record window was opened.
if IsFieldModified("name") then showmessage "You have modified the name field!"

IsLastModifiedField("fieldName") Return Type: Boolean Applies to: Record Window (Procedure)

Returns true if the data of the specified field was just modified by user. This function can only be used to determine the modified field which triggered the "FieldModified" event. The script can then do the required processing if the user has just made a change in a specific field. Note that, this function can return true for more than one field if there are computed fields which recalculate a new value depending on the change of another field.

Example:
Display a message if the user has just modified the name field. (This script must be triggered by the "FieldModified" event)
if IsLastModifiedField("name") then showmessage "You have modified the name field!"

CheckFieldModified("fieldName") REMOVED

This function was removed with version 5.9 See "IsFieldModified" and "IsLastModifiedField" functions above for alternatives.

ShowObject "objectname", optional TabNo Applies to: Record Window (Procedure)

Makes the specified object on record window visible. If tab number is specified, the object will be displayed on that tab.
For fields, you may use field's system name, for all other objects, make sure that you have assigned an object name for that object on form designer window. You may use this function together with "hideObject" function to conditionally display data fields, notifications or warning messages.

ShowObject "Name Surname"

HideObject "objectname" Applies to: Record Window (Procedure)

Hides the specified object from record window.
For fields, you may use field's system name, for all other objects, make sure that you have assigned an object name for that object on form designer window.
This function cannot be used in computed field expressions.

MoveObject "objectname", xPos, yPos Applies to: Record Window (Procedure)

Moves the specified object to the desired position on record window.
For fields, you may use field's system name, for all other objects, make sure that you have assigned an object name for that object on form designer window. The position coordinates are measured in twips (1 inch = 1440 twips)
This function cannot be used in computed field expressions.

MoveObject "Name Surname", 3000,1000

SetObjectHeader "objectname", newHeader Applies to: Record Window (Procedure)

Renames the label of a data field or changes the text of a label object or changes the caption of an action button.
For fields, you may use field's system name, for all other objects, make sure that you have assigned an object name for that object on form designer window.
Beware that this change is only for display purposes and no data is actually changed after this command.

Usage in script procedures:
SetObjectHeader "name", "Customer's Full Name:"

GetObjectHeader("objectname") Applies to: Record Window (Procedure)

Retrieves the label of a data field or the text of a label object or the caption of an action button.
For fields, you may use field's system name, for all other objects, make sure that you have assigned an object name for that object on form designer window.

Usage in script procedures:
varLabelText = GetObjectHeader("name surname")

SetObjectForeColor "objectname", colorValue Applies to: Record Window (Procedure)

Sets the foreground/border/text color of any object shown on the record window.

Usage in script procedures: (set the text color of the "name field to red)
SetObjectForeColor "Name", vbRed
The following color constants are available: vbBlack, vbWhite, vbBlue, vbRed, vbGreen, vbYellow, vbMagenta, vbCyan
You may use RGB function to generate the color value precisely by specifiying each color level as red (0-255), green (0-255) and blue (0-255) respectively.
SetObjectForeColor "Name", RGB(255,0,0)

SetObjectBackColor "objectname", colorValue Applies to: Record Window (Procedure)

Sets the background color of any object shown on the record window.
See SetObjectForeColor function above for more information.

GetActiveFieldName Return Type: String Applies to: Record Window (Procedure)

Returns the name of the field which is currently being edited.

GetActiveFieldValue Return Type: String Applies to: Record Window (Procedure)

Returns the current value of the field which is currently being edited.

GetActiveTabNumber Return Type: Integer Applies to: Record Window (Procedure)

Returns the order number of the active form tab. The numbering begins from 1 for the first tab.

GetRecordHeader Return Type: String Applies to: Record Window (Procedure)

Returns the header of the current record window.

SetRecordHeader "newHeader" Applies to: Record Window (Procedure)

Sets the header of the current record window.
Beware that this change is only for display purposes and no data is actually modified after executing this command.

ReloadRecordData Applies to: Record Window (Procedure)

Reloads the current record data.

ReloadRecordWindow Applies to: Record Window (Procedure)

Refreshes the current record window as if it was newly opened and reloads record data.

CreateRecord "catalog name" Applies to: Record Window (Procedure)
CreateRecord "relational field name" Applies to: Record Window

If you enter a catalog name, opens an empty record window to create a new record on the desired catalog.
If you enter a relational field name, opens a new record window to create a related record for the currently displayed record.

ViewRecord "catalog name", record ID Applies to: Record Window (Procedure)

Opens an existing record with the specified ID from the specified catalog for viewing only (read-only).

EditRecord "catalog name", record ID Applies to: Record Window (Procedure)

Opens an existing record with the specified ID in edit mode.

SaveRecord Applies to: Record Window (Procedure)

Saves current record as if the user clicked the save button.

CloseRecord Applies to: Record Window (Procedure)

Closes current record as if the user clicked the close button.
If the record was unsaved at that moment, the user will receive a warning to save the record.

CloseRecordNoSave Applies to: Record Window (Procedure)

Closes current record even if the record was not saved. Any changes made will be lost.

DeleteRecord Applies to: Record Window (Procedure)

Deletes currents record as if the user clicked the delete button.
The user will be asked for confirmation before the record is actually deleted.

ForceDeleteRecord Applies to: Record Window (Procedure)

Deletes currents record immediately without asking for confirmation from user.

PrintRecord optional "reportName", optional "catalogName", optional recordID Applies to: Global, Record Window (Procedure)

Prints current record if the script is executed from the form using default report option.
Prints a specific record if catalog name and record ID are both specified.
Record Window Only: If you are calling this function from a record window and you want to print the current record, you may omit both catalog name and record ID parameters. If there is only a single report designed for current catalog, you may also omit the report name parameter.

PrintPreviewRecord optional "reportName", optional "catalogName", optional recordID Applies to: Global, Record Window (Procedure)

This function works same as PrintRecord function except for it opens print preview window rather than sending the output to the print device.

CheckCurrentEventName "eventName" Return Type: Boolean Applies to: Record Window (Procedure)

Compares the queried event name with the name of the event which triggered the execution of the current script. Returns true if the names match.
This functions allows you to maintain multiple script codes that are supposed to be triggered by separate events within the same script object. You may assign multiple events to the same script procedure and then execute only the relevant part of code depending on the current event name.
You may also call "CancelEvent" to cancel the event that triggered the script.
This function is supported in version 5.9 and later.

List of event names: (not case sentitive)

LoadCreate (occurs when the user clicked create record button)
BeforeCreate (occurs just before a new record is saved to database)
AfterCreate (occurs just after a new record is saved to database)
LoadUpdate (occurs when an existing record is loaded)
BeforeUpdate (occurs just before an existing record is saved to database)
AfterUpdate (occurs just after an existing record is saved to database)
BeforeDelete (occurs just before a record is deleted)
AfterDelete (occurs just after a record is deleted)
BeforeClose (occurs just before a record is closed)
FieldModified (occurs when data in a field is changed)
FieldEnter (occurs when the user clicks into any data field)
FieldLeave (occurs when the user leaves an any data field)
BeforeTabChanged (occurs just before the user moves to another tab of the record form)
AfterTabChanged (occurs just after the user moves to another tab of the record form)
KeyPress (occurs just when the user presses any key or key combination from keyboard)
Timer (occurs periodically depending on the time interval you set for this script)

Example procedure code:
If CheckCurrentEventName("fieldmodified") Then
...
End If
If CheckCurrentEventName("keypress") Then
...
End If

CancelEvent Applies to: Record Window (Procedure)

Cancels the event which had triggered the execution of the current script.
The user actions which can be canceled with this command are: Save/delete/close record, key press, tab change.
Example:
If the script was triggered by "BeforeClose" event, the script may validate the data and if some data was not correctly entered it can display a message and then disregard the attempt of the user to close the record.

GetPressedKeyCode Return Type: Integer Applies to: Record Window (Procedure)

Returns the key code of the pressed key from keyboard. This function will return a key code for any key including special keys like backspace, del, tab, enter, home, end, arrow keys, esc, pageup, pagedown keys.
Beware that the key code for uppercase and lowercase letters are same as they are generated by the same key of the keyboard.

GetPressedKeyChar Return Type: String Applies to: Record Window (Procedure)

Returns the related character only if a printable key was pressed.

SetPressedKeyChar "newChar" Applies to: Record Window (Procedure)

Modifies the pressed key character.

IsShiftKeyPressed Return Type: Boolean Applies to: Record Window (Procedure)

Returns true if the shift key is currently hold down.

IsControlKeyPressed Return Type: Boolean Applies to: Record Window (Procedure)

Returns true if the control key is currently hold down.

IsAltKeyPressed Return Type: Boolean Applies to: Record Window (Procedure)

Returns true if the alt key is currently hold down.

CharCount("fieldName") Return Type: Integer Applies to: Record Window (Procedure / Expression)

Returns the number of characters in a text field.

WordCount("fieldName") Return Type: Integer Applies to: Record Window (Procedure / Expression)

Returns the number of words in a text field.

WriteToClipboard "text" Applies to: Record Window (Procedure)

Copies the specified text to clipboard.

ReadFromClipboard Return Type: String Applies to: Record Window (Procedure)

Returns the current text value from clipboard.

CopyFieldValue "field name" Applies to: Record Window (Procedure)

Copies data from the specified field to clipboard.

PasteFieldValue "field name" Applies to: Record Window (Procedure)

Paste the current value from clipboard to the specified field.

ShowMessage("messageText", optional "Header", optional msgType) Applies to: Record Window (Procedure)

Displays a message box to the user.
If used, the parameter msgType can be either 0 (default), 1, 2 or 3. They will show "information", "exclamation", "denial" and "question" icons respectively.

ShowMessage "Please fill in name and surname fields!"
ShowMessage "Please fill in name and surname fields!","Missing Fields",1

Alternatively you may use this function to let the user select one from several options. Each option is displayed as a separate button in this case. Once the user clicks the desired button, the function returns a number representing the order of the button which you may use for further script processing.
You may use up to 4 button names in this function.
This feature is supported in version 5.9 and later.

n=ShowMessage("Do you confirm the changes?","Confirm", 3, "Yes, I do", "Not Really")
ShowMessage "You have pressed the button number " & n

InputText("Description","Header","default value") Applies to: Procedure

Displays a prompt in a dialog box, waits for the user to type text and returns the text data.
All parameters are optional.
You may use this function only when you are writing a script procedure for the record window.
This function is supported in version 5.9 and later.

name = InputText("Please type your name","Name:")

InputPassword("Description","Header") Applies to: Procedure

Allows the user to enter text with invisible characters.
See InputText function above for usage.
This function is supported in version 5.9 and later.

pass = InputPassword("","Enter Password:")

Built-in Functions by SpeedBase For Computed Fields Only

XIF(expression, true part, false part) Applies to: Expression / Procedure

If the result of the expression is true, evaluates and returns the true part, otherwise evaluates and returns the false part.
This function is used to generate conditional output from a computed field expression.
While this function works fine when you are writing a procedure, you are recommended to use the vb-script native statements of IF-THEN-ELSE as they bring more convenience and capable of containing multiple lines of code blocks when used within a procedure.

Example:
Assume that you have an option list box named "membership" containing the items "standard" and "premium".
Assume also that you wish to apply 20% surcharge to the price when premium is selected.
nField("price") * XIF(sField("membership")="premium", 1.2, 1)

Caution! Regardless from the expression is evaluated to true or false, both "true" and "false" parts are executed but only the result related to the evaluation is returned.
Example: You may expect the following expression to return 0 when "item count" is equal to zero but it will actually generate a "division by zero" error:
XIF( nField("Item Count") > 0, nField("total cost") / nField("Item Count"), 0)

ReturnValue "VarName or Expression" Applies to: Record Window (Procedure)

Used to return the calculation result back to the computed field which executes a procedure.
If you write a procedure instead of an expression for a computed field, you must use this function at the end of the script execution to display the final value in the computed field box.

Built-in Functions by SpeedBase For Views

You may create a "view script" for a view from the script manager window. A view script is executed for each row of a view that is being loaded. Since each row represents a single record, you may apply coloring to each row or specific cells of that row depending on the data of the related record.
See example #4 given on procedure examples page to try it yourself.

GetCurrentViewName Return Type: String Applies to: Views (Procedure)

Returns the currently selected view name. You may use this function to apply coloring to the desired views only.
This function is supported in version 5.9 and later.

SetRowBackColor colorValue Applies to: Views (Procedure)

Sets the background color of the entire record row in a view.
Usage:
SetRowBackColor vbRed
The following color constants are available: vbBlack, vbWhite, vbBlue, vbRed, vbGreen, vbYellow, vbMagenta, vbCyan
You may use RGB function to generate the color value precisely by specifiying each color level as red (0-255), green (0-255) and blue (0-255) respectively. SetRowBackColor RGB(255,0,0)
Example:
Display the entire record row in red background color if email field was left empty:
IF SFIELD("email")="" THEN SetRowBackColor vbRed

SetRowTextColor colorValue Applies to: Views (Procedure)

Sets the text color of the entire record row in a view.
See SetRowBackColor function above for more information.

SetCellBackColor "fieldName", colorValue Applies to: Views (Procedure)

Sets the cell background color of the specified field of the current record row in a view.
Example:
Display the background of "name" field in red color if the email field of the record was left empty:
IF SFIELD("email")="" THEN SetCellBackColor "name", vbRed

SetCellTextColor "fieldName", colorValue Applies to: Views (Procedure)

Sets the cell text color of the specified field of the current record row in a view.
See SetCellBackColor function above for more information.

SetColumnBackColor "fieldName", colorValue Applies to: Views (Procedure)

This function is used to unconditionally apply a default background color to an entire column of the view.
Use SetCellBackColor function if you would like to apply coloring depending on field data.

SetColumnTextColor "fieldName", colorValue Applies to: Views (Procedure)

This function is used to unconditionally apply a default text color to an entire column of the view.
Use SetCellTextColor function if you would like to apply coloring depending on field data.

Built-in Functions by SpeedBase Available Globally

AgeCalculate("fieldName") Return Type: Integer Applies to: Record Window
AgeCalculate( dateValue ) Return Type: Integer Applies to: Global

Calculates the age from the given birthday or birthday saved to a date field.

Tip: There are a number of useful date processing functions available in vbScript. Check the links given on top of this page to review them.

Caution: Age is calculated with respect to the time the function is executed. As time passes, the calculation result of a computed field depending on current time becomes invalid. To workaround this problem, you may create a daily recalculation task using the scheduled task window.

FormatDate(dateValue, optional formatString) Return Type: String Applies to: Global

Converts a date data to the desired display format. If "formatString" is not specified, it returns the date in short format determined by the operating system and locale.
See the section Date Formatting Parameters at the end of this page for the parameter formatString.

Limit(varName, MinVal, MaxVal) Return Type: Variant Applies to: Global
Limit("fieldName", MinVal, MaxVal) Return Type: Variant Applies to: Record Window

Limits the input value to MinVal at minimum and to MaxVal at maximum.
You may use this function to get limited value from a numeric field or select the min. or max. value from two fields.

Examples:
LIMIT ("age", 18 ) returns the value from the "age" field limiting the returned value with 18 at least.
LIMIT ("age", , 30 ) returns the value from the "age" field limiting the returned value with 30 at most.
LIMIT ("age", 18 , 30 ) returns the value from the "age" field limiting the returned value between 18 and 30
LIMIT ("age-1", nField("age-2") ) returns the greater one between the fields "age-1" and "age-2"
LIMIT ("age-1", , nField("age-2") ) returns the smaller one between the fields "age-1" and "age-2"

RoundUp(decimalValue, Digits) RoundDown(decimalValue, Digits) Return Type: Decimal Applies to: Global
RoundUp("fieldName", Digits) RoundDown("fieldName", Digits) Return Type: Decimal Applies to: Record Window

Rounds the input value upwards/downwards to the specified digits. If you omit digit part, it will round to integer.
Remember to use the Round function instead if you want to round the value towards nearest direction.
If digit is negative, the value is rounded to towards next multiple of power of ten.

Example:
Assume that the decimal field "dec1" has value 3.14
Assume that the decimal field "dec2" has value 2.71
Assume that the decimal field "dec3" has value 151

Round("dec1",1) returns 3.1
RoundUp("dec1",1) returns 3.2
RoundDown("dec2") returns 2
RoundUp("dec3", -1) returns 160

EncodeNumeric( integerNumber, optional preferredCharacterSet ) Return Type: String Applies to: Global

Converts the specified integer number to alphanumeric code. You may optionally force your own character set to generate the code from. If character set is omitted, the code is generated from "0123456789ABCDEFGHJKLMNPQRSTUVWXYZ".
This function generates an easily readable code with almost half number of characters, e.g. 1,256,000 is converted to XYH6.
Beware that the geneated code is sequential, so the code for the previous/next number can be estimated by anyone. If you want to generate a non-sequential code, consider using MD5 instead.

DecodeNumeric( "encodedNumeric", optional preferredCharacterSet ) Return Type: Integer Applies to: Global

Converts the code which was generated by "EncodeNumeric" function back to its corresponding integer number.

EncodeBase64( "String" ) Return Type: String Applies to: Global

Returns text encoded according to base64 standard.

DecodeBase64( "encodedString" ) Return Type: String Applies to: Global

Returns the original text from a base64 encoded text.

MD5("Value") Return Type: String Applies to: Global
MD5("fieldName") Return Type: String Applies to: Record Window

Calculates the MD5 hash of the value. The hash is represented as 32 chars of hexadecimal.

GetRandomNumber(integerMin, integerMax) Return Type: Integer Applies to: Global

Generates a random number between the numbers integerMin and integerMax.

CurrencyToWords(Value, optional "CurrencyName", optional "SubCurrencyName") Return Type: String Applies to: Global

Converts monetary numeric value to its equivalent text representation.

GetCurrentLoginName Return Type: String Applies to: Global

Returns login name of the currently logged in SpeedBase user.

GetCurrentUserName Return Type: String Applies to: Global

Returns the real name of the currently logged in SpeedBase user.

Sleep miliSeconds Applies to: Global

Stops execution for the specified time in miliseconds.
This function is useful in cases like when you want to slow down execution of a loop in a script, temporarily display an information, generate animations, executing very long calculations without loading processor etc.
The minimum possible value for sleep is around 15 miliseconds.
Caution! When executed, whole application enters into sleep, so the application becomes unresponsive until the sleeping time has elapsed.

Val(Value or VarName) Return Type: Decimal Applies to: Global

Converts the value to numeric.

GetTagValue(textXML, tagName) Return Type: Strign Applies to: Global

Returns the value enclosed in the specified XML element.

OpenURL "url" Applies to: Global

Opens a web site in your default web browser. This command can also be used to initiate a new email message.

OpenURL "https://www.speedbasesoftware.com/"
OpenURL "mailto:example@example.com"

HttpAddParameter "VarName", Value Applies to: Global

Saves a parameter and its value to memory to be used with a HTTP request.
If you save another value using the same parameter name, previous value will be updated with the new one.

HttpGet "URL", optional timeOutSec Return Type: String Applies to: Global

Sends a HTTP GET request to a web site and returns the response.
With this command, you may download the source code of a web page in text format and then process the content e.g. pull specific data.
If you have called HttpAddParameter function before, the saved parameters and values are sent with the request.
The default value for timeout is 5 seconds if not specified.

HttpPost "URL", optional timeOutSec Return Type: String Applies to: Global

Sends a HTTP POST request to a web site and returns the response.
If you have called HttpAddParameter function before, the saved parameters and values are sent with the request.
The default value for timeout is 5 seconds if not specified.

SaveVarToMemory "KeyName", Value Applies to: Global

Saves a value to memory which can be retrieved later by its key name. "KeyName" is arbitrary.
You may save any value to memory. They will remain available until SpeedBase application is terminated. So any script can retrieve that value even hours later.
If you save another value using the same key, previous value will be updated with the new one.
Beware that values you saved with this function are only available to the same running instance of SpeedBase. Any running instance on another computer may not read that value. If you want to make it available to all users and save it permanently, use "SaveVarToDB" instead.

Save specified fixed value:
SaveVarToMemory "AnyKeyNameYouLike", "hello world"
Save the value of the specified variable:
SaveVarToMemory "AnyKeyNameYouLike", myVar
Save the value of the specified field:
SaveVarToMemory "AnyKeyNameYouLike", NFIELD("price")
Alternative method for the previous example:
priceVal=NFIELD("price")
SaveVarToMemory "AnyKeyNameYouLike", priceVal

ReadVarFromMemory("KeyName") Return Type: Variant Applies to: Global

Reads a previously saved value with the same key from memory.

Read the value from memory and set it to a variable:
myVar=ReadVarFromMemory("KeyNameUsedToSave")
Read the value from memory and then set it to the "name" field:
myVar=ReadVarFromMemory("KeyNameUsedToSave")
SetFieldValue "Full Name", myVar
Alternative (single line) method for the previous example:
SetFieldValue "Full Name", ReadVarFromMemory("KeyNameUsedToSave")

ClearMemoryVars optional "KeyName" Applies to: Global

Deletes ALL previously saved values from memory, if no key name is specified.
Version 5.9 and later only: If a variable key name is specified, deletes only that variable from memory.

Delete the value saved by the key name "myKeyName" from memory:
ClearMemoryVars "myKeyName"
Delete all values saved by any key from memory.
ClearMemoryVars

SaveVarToDB "KeyName", Value Applies to: Global

Saves a value to database which can be retrieved at any time by its key name. "KeyName" is arbitrary.
You may save any value to database. They will remain available until you update them with another value or call clear function. So any running instance of SpeedBase connected to the same database can retrieve that value even years later.
If you save another value using the same key, previous value will be updated with the new one.
If you want to save only temporary data and/or make the value available only to the current user, use "SaveVarToMemory" instead.
Note that, saving/retrieving values to/from database is slower if you plan to save hundreds of values in a loop.

See SaveVarToMemory function above to review examples as this function works exactly the same way.

ReadVarFromDB("KeyName") Return Type: Variant Applies to: Global

Reads a previously saved value having the same key from database.

See ReadVarFromMemory function above to review examples as this function works exactly the same way.

ClearDBVars optional "KeyName" Applies to: Global

Deletes ALL previously saved values from database, if no key name is specified.
Version 5.9 and later only: If a variable key name is specified, deletes only that variable from database.

See ClearMemoryVars function above to review examples as this function works exactly the same way.

ReadFromFile("filePath") Return Type: String Applies to: Global

Reads and returns the text content of a file.
Version 5.9 and later: If you type an empty string or a folder path for the file path parameter, the open file dialog window will open where the user must select the folder and/or file.

sData = ReadFromFile("D:\myfolder\greeting.txt")
sData = ReadFromFile("D:\myfolder")
sData = ReadFromFile("")

WriteToFile "filePath", data Return Type: Boolean Applies to: Global

Writes data to a file. Returns true if the the file was written successfully.
If a file with the same name exists, it will be overwritten and it's previous content will be lost. Use "AppendFile" function if you want to add data at the end of an existing file.
Version 5.7 and later: If you type an empty string or a folder path or just file name for the file path parameter, the save as dialog window will open where the user must select the folder location and/or type a file name.

WriteToFile "D:\myfolder\greeting.txt", "Hello World!"
WriteToFile "D:\myfolder", "Hello World!"
WriteToFile "", "Hello World!"

AppendFile "filePath", data Return Type: Boolean Applies to: Global

Writes data at the end of an existing file. The file will be created if it doesn't exist.
This function is supported in version 5.9 and later.
See WriteToFile function above for examples.

OpenFile "filePath", optional "parameters" Return Type: Boolean Applies to: Global

Executes a file from your computer as if it or it's shortcut icon was double clicked.
You may use this function either to start another application or a document with it's associated program.
If the target program accepts command line parameters, you may specify them via "parameters".
Version 5.9 and later: If you type an empty string or a folder path for the file path parameter, the open file dialog window will open where the user must select the folder and/or file.

OpenFile "D:\myfolder\report.pdf"
OpenFile "C:\windows\system32\notepad.exe"

GetRecordCount("catalogName", optional "viewName") Return Type: Integer Applies to: Global

Returns the total number of records in a catalog.
View name is optional and if specified, the filtering rule of that view is applied.

nCount = GetRecordCount("Customers", "New Customers")

UpdateRecordField "catalogName", "fieldName", RecordID, newValue Return Type: Boolean Applies to: Global

Updates the value on the specified field of the specified record by ID of the specified catalog. Returns true unless an error occurs.
Beware that the update is done in the database only, so if the related record window is displayed, the field won't reflect the change until the record is reloaded. Use "SetFieldValue" function if you want to update a field value on a displayed record.

UpdateRecordField "Products", "product name", 123, "Laser Printer"

Beep Applies to: Global

Generates sound. Can be useful for debugging purposes.

SQL Queries

CAUTION!

An incorrectly crafted custom SQL query or query loop may instantly cause loss of data for large number of records in your database. You are recommended to take backup of your database before testing a script which modifies data with sql queries.

SQLExecute "textQuery" Return Type: Boolean Applies to: Global

Executes a custom SQL query (e.g. insert, update, delete) and returns true if it succeeds.
You may not process SELECT queries with this function. Use SQLSelect for that purpose.

For version 5.7 and later:

SQLEXECUTE "UPDATE [my catalog name] SET [my field name]='hello world' WHERE ID=1 "

For older versions:

SQLGetDBName

SQLExecute "UPDATE IT_products SET retired='1' WHERE IC_expiredate<20190101 "
bQueryResult = SQLExecute("UPDATE IT_products SET retired='1' WHERE IC_expiredate<20190101 ")

SQLSelect "textQuery" Return Type: String Applies to: Global

Executes a custom SQL Select query and returns a unique query ID.
For nested queries, the query ID helps to process the right "select" query when you call other SQL functions listed here.
When writing custom SQL queries, you must use the internally defined names used for tables and columns in your database. You may use SQLGetDBName function (see below on this page) to find out those names.
You may use the following functions to process the query: SQLEOF, SQLGetNextRow, SQLGetField, SQLQueryRowCount

SQLSelect "SELECT productname, expiredate FROM products WHERE expiredate<20190101 "
sQueryID = SQLSelect("SELECT productname, expiredate FROM products WHERE expiredate<20190101 ")

SQLReadRecords "catalogName", optional "viewName", optional readRecordCount, optional skipRecordCount Return Type: String Applies to: Global

Selects records from the specified catalog and returns a unique query ID.
If a view name is also specified, the filter of that view is applied to the query.
For nested queries, you will need the query ID to process the right "select" query when you call other SQL functions listed here.
If readRecordCount is specified, up to that much record is read and returned from the database. The maximum possible value is 1000.
If skipRecordCount is specified, that much record from the beginning of the record set is skipped and the following records are returned.
If you want to move thru a much larger records set, you must create a loop to create multiple queries and raise "skipRecordCount" at each iteration.
You may use the following functions to process the query: SQLEOF, SQLGetNextRow, SQLGetField, SQLSetField, SQLQueryRowCount

SQLReadRecords "Customers", "New Customers", 10, 0
sQueryID = SQLReadRecords("Customers", "New Customers", 10, 0)

SQLEOF optional "queryID" Return Type: Boolean Applies to: Global

Returns true when no records are returned from the SQL query, or, no records are left to process after the last SQLGetNextRow call.
If you omit query ID, the most recent query will be processed. So you may omit query ID if there are no nested queries.

IF SQLEOF THEN MSGBOX "No (more) records to process!"

SQLGetNextRow optional "queryID" Return Type: Boolean Applies to: Global

Moves to the next record thru a previously executed SQL query.
Returns true if successfull, false if an error occurs or there are no more records to move to.
If you omit query ID, the most recent query will be processed. So you may omit query ID if there are no nested queries.

SQLGetFieldValue "fieldName" OR fieldIndex, optional "queryID" Return Type: Variant Applies to: Global

Reads and returns processed data (same as displayed on the record window) from the specified field of the current record in a previously executed SQL query.
If you have executed a SQLReadRecords query, you must provide the field's object name (fieldName) as shown in field properties window.
If you have executed a SQLSelect query, you must provide the order number of the SELECT item (fieldIndex).
If you omit query ID, the most recent query will be processed. So you may omit query ID if there are no nested queries.

sProductName = SQLGetFieldValue("productname")
sProductName = SQLGetFieldValue(1)

SQLGetField "fieldName" OR fieldIndex, optional "queryID" Return Type: Variant Applies to: Global

This function reads and returns the exact data saved to the database from the specified field of the current record in a previously executed SQL query. For relational lookup and option list fields, this data is a ID reference. Data read from date fields is a numeric representation of the date value.
Use SQLGetFieldValue function if you want to retrieve the processed data as displayed on your records.
If you have executed a SQLReadRecords query, you must provide the field's object name (fieldName) as shown in field properties window.
If you have executed a SQLSelect query, you must provide the order number of the SELECT item (fieldIndex).
If you omit query ID, the most recent query will be processed. So you may omit query ID if there are no nested queries.

SQLSetField "fieldName", newValue, optional "queryID" Return Type: Boolean Applies to: Global

This function is applicable only for queries executed by SQLReadRecords function.
Updates the value of the specified field in a previously executed query. Returns true if succeeds.
If you omit query ID, the most recent query will be processed. So you may omit query ID if there are no nested queries.

SQLSetField "productname", "Laser printer"

SQLQueryRowCount optional "queryID" Return Type: Integer Applies to: Global

Returns the number of records between 0-1000 read from a previously executed SQL query.
If you omit query ID, the most recent query will be processed. So you may omit query ID if there are no nested queries.

Caution! For queries executed by SQLSelect, this function can only be used if there are no more than 1000 records in current query, otherwise you must execute a separate instance of SQLSelect function just to determine the number of records.

nRowCount = SQLQueryRowCount

SQLGetDBName("catalog name", optional "field name") Return Type: String Applies to: Global

Returns the actual table or column name used in the database for a catalog or field. Alternatively, you may view those names from the SQL Query window.

Useful VB Script Native Commands

EXIT FOR Applies to: Procedure
EXIT DO Applies to: Procedure

Exits current FOR-NEXT or DO loop respectively.

EXIT SUB Applies to: Procedure
EXIT FUNCTION Applies to: Procedure

Exits code execution immediately.
If the procedure type you write is a SUB, you must use "EXIT SUB". If it is a function, you must use "EXIT FUNCTION".
You may use this command to break out of a loop and end execution, or, to keep unused code following this command.

INPUTBOX("Description","Title","default value") Applies to: Procedure

You are recommended to use INPUTTEXT function described above instead of this function.
Displays a prompt in a dialog box, waits for the user to input text or click a button, and returns the contents of the text box.
You may omit the default value parameter.
You may use this function only when you are writing a script procedure for the record window.

name = InputBox("Please type your name","name:")

IF - THEN - ELSE

Conditionally executes a script block.
See examples given on procedure examples page.

Date Formatting Parameters

Example: "mmm d, yyyy dddd" formats the date as "Mar 1, 2021 Monday"

d: Display the day as a number without a leading zero (1-31).
dd: Display the day as a number with a leading zero (01-31).
ddd: Display the day as an abbreviation (Sun-Sat).
dddd: Display the day as a full name (Sunday-Saturday).
ddddd: Display the date as a complete date (including day, month, and year), formatted according to your system's short date format setting.
dddddd: Display the date as a complete date (including day, month, and year) formatted according to your system's long date format setting.
m: Display the month as a number without a leading zero (1-12).
mm: Display the month as a number with a leading zero (01-12).
mmm: Display the month as an abbreviation (Jan-Dec).
mmmm: Display the month as a full month name (January-December).
y: Display the day of the year as a number (1-366).
yy: Display the year as a 2-digit number (00-99).
yyyy: Display the year as a 4-digit number (100-9999).
h: Display the hour as a number without leading zeros (0-23).
Hh: Display the hour as a number with leading zeros (00-23).
N: Display the minute as a number without leading zeros (0-59).
Nn: Display the minute as a number with leading zeros (00-59).
S: Display the second as a number without leading zeros (0-59).
Ss: Display the second as a number with leading zeros (00-59).

Online Help Home Page :: SpeedBase Software Home Page