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 specified field with the specified value.
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.
Example:
The following procedure will add "Hello" to the beginning of the field named "name".
SetFieldValue "name", "Hello " & sField("name")
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.
Example:
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.
Example:
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.
CheckFieldModified("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. This function can only be used to determine
the modified field in scripts triggered by FieldModified event.
Example:
Display a message if the user has modified the name field.
if CheckFieldModified("name") then showmessage "You have modified name field!"
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.
GetCurrentEventName Return Type: String Applies to: Record Window (Procedure)
Retrieves the name of event which triggered the execution of current script.
This function is most useful if your script was set to be triggered from multiple events. You may then
write a single script which makes different decisions depending on which event has triggered the script.
You may also call "CancelEvent" to cancel the event that triggered the script.
List of possible returning values:
Beware that event names are case sensitive!
- 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 GetCurrentEventName="FieldModified" 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 function is supported in version 5.9 and later.
n=ShowMessage("Do you confirm the entered data?","Confirm", 3, "Yes, I do", "Not Really")
ShowMessage "You have pressed the button number " & n
INPUTTEXT("Description","Title","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","Title","default value") 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.
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.
SetRowBackColor colorValue Applies to: View (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: View (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: View (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: View (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: View (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: View (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
If a variable key name is specified, deletes only that variable from memory.
If no key name is specified, deletes ALL previously saved values 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
If a variable key name is specified, deletes only that variable from the database.
If no key name is specified, deletes ALL previously saved values 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.
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!"
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.
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:
When writing custom SQL queries, you may use the user defined catalog names and field names in the query as long as you enclose them with bracket characters.
For field names, you must use the "object name" of the field name that is shown in the field properties window. "Display name" cannot be used.
When using this method, make sure that there are no multiple catalogs/fields having the same name.
SQLEXECUTE "UPDATE [my catalog name] SET [my field name]='hello world' WHERE ID=1 "
For older versions:
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.
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).