Example Procedures
You may write a procedure for:
- Computed fields
- Event driven script objects created in Form Designer window
- Action buttons in Form Designer window
- Scheduled scripts
To write procedures, you need to have a basic level of understanding how to write algorithms. If you are somewhat familiar with mathematical expressions,
it's not a big deal to start your own procedures by trying and working on the example codes given on this page.
When writing procedures,
- Always use a dot (not comma) when you need to type a decimal mark in a numeric value.
- Always use & character (not "+" sign) when you want to concatenate text strings or functions which return text.
- Make sure that the number of opening parentheses are equal to the number of closing parentheses when writing an expression else it will generate an error.
- Leave a space character between each operator, value or function. Excessive use of spacing won't hurt anything.
- Remember to select the triggering events for the script objects you place to your form else they will never be executed.
- Remember that neither function nor field names are case sensitive. They are displayed here in varying cases for better readability only.
- Remember that the field names chosen in the examples below are arbitrary. You must replace the names with the actual field names if you want to adapt the procedure to your database.
Tip:
There is an application template which shows and guides thru a number of script examples in version 5.5 and later.
You may get a good idea about how to implement computed field expressions or action driven scripts by examining that template.
To import this template, click "Design" menu, select "Import Application Template" and then select the template "Calculation and Scripts".
This will create one new catalog named as "Script Examples". Create a new record on this catalog and check each of the examples explained on that record window.
Quick jump to the example procedures given on this page:
- Example #1: Computed Field With a Procedure
- Example #2: Displaying Conditional Objects
- Example #3: Conditional Text/Color Formatting for Records
- Example #4: Conditional Text/Color Formatting for Views
- Example #5: Displaying Data Field On All / Specific Tabs
- Example #6: Disable Saving Record If Required Field Was Left Blank
- Example #7: Prevent Changes on a Field by Users
- Example #8: Creating Keyboard Shortcuts
- Example #9: Download Currency Exchange Rates From a Web Site
- Example #10: Push Notification to Mobile Devices by Clicking a Button
- Example #11: Send Email From Your Web Site
- Example #12: Executing SQL Queries in a Loop
- Example #13: Exporting Records in a View to CSV File
Example #1: Computed Field With a Procedure
For the example given below,
- Create a text field named "Name Surname",
- Create an option list field named "Gender", with the options "Male" and "Female"
- Create a computed field setting compute type as "procedure" and return type "calculate to text".
- Copy and paste the code given below into the procedure box.
After creating all fields, type any name into the name box and change gender to see what happens.
Example #2: Displaying Conditional Objects
For the example given below,
- Create a text field titled "First Name",
- Create a text field titled "Last Name",
Open Form Designer, select Toolbox tab,
- Create a label with the text content "Please fill in the name box!".
- Open properties of the label and set the object name as "MyLabel".
- Create a script object, copy and paste the code given below into the procedure editor.
- Click to "Triggering Events" tab, add the following events: "Field Modified", "Load New Record", "Load Existing Record"
Example #3: Conditional Formatting for Records: Changing Background Color / Text
For the example given below,
- Create a text field titled "Name",
Open Form Designer, select Toolbox tab,
- Create a script object, copy and paste the code given below into the procedure editor.
- Click to "Triggering Events" tab, add the following events: "Field Modified", "Load New Record", "Load Existing Record"
This example requires version 5.6.0 or later.
Alternatively, you may use SETOBJECTFORECOLOR function to set the foreground/border/text color of any object shown on the record window.
The following color constants are available: vbBlack, vbWhite, vbBlue, vbRed, vbGreen, vbYellow, vbMagenta, vbCyan
You may also 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(0,255,0)
Example #4: Conditional Formatting for Views: Changing Background Color / Text
For the example given below,
- Create a text field titled as "Name",
- Create several example records with some records having the email field left as empty,
- Click "Design" menu, select "Script Manager" to open Script Manager window,
- Create a new script, select "view script" and the catalog when asked,
- Copy and paste the code given below into the procedure editor.
- Save changes, exit script manager and reload the view.
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.
Example #5: Displaying Data Field On All / Specific Tabs
For the example given below,
- Create a text field titled "Name",
Open Form Designer, select Toolbox tab,
- Create a script object, copy and paste the code given below into the procedure editor.
- Click to "Triggering Events" tab, add the following event: "After Change Tab"
The following example displays the field on first and second tabs only and adjusts the position of the field on each tab.
Example #6: Disable Saving Record If Required Field Was Left Blank
For the example given below,
- Create a text field titled "Name",
Open Form Designer, select Toolbox tab,
- Create a script object, copy and paste the code given below into the procedure editor.
- Click to "Triggering Events" tab, add the following events: "Before Create Record", "Before Update Record"
Example #7: Prevent Changes on a Field by Users
For the example given below,
- Create a text field titled "Name",
Open Form Designer, select Toolbox tab,
- Create a script object, copy and paste the code given below into the procedure editor.
- Click to "Triggering Events" tab, add the following events: "Field Modified", "Load Existing Record"
Example #8: Creating Keyboard Shortcuts
For the examples given below, open Form Designer, select Toolbox tab,
- Create a script object, copy and paste the code given below into the procedure editor.
- Click to "Triggering Events" tab, add the following events: "Key Press"
The following example illustrates how to insert a date stamp at the current cursor position
while you are editing a text field of a record.
Tip: As an alternative to a keyboard shortcut, you may create an action button which inserts a date using only the second line of the script given above.
Example #9: Download Currency Exchange Rates From a Web Site
For the example given below,
- Create a text field titled "Current Rate",
Open Form Designer, select Toolbox tab,
- Create an action button, copy and paste the code given below into the procedure editor.
Beware that the example code below is specific to the page content of the web site it retrieves data from.
You must rewrite the code depending on the page source retrieved if you want to get data from a different source.
Example #10: Push Notification to Mobile Devices by Clicking a Button
This example illustrates sending a push notification message to a mobile phone by clicking a button.
There are various online push notification services available on the internet. We used here the service
provided by pushover.net.
You must install their mobile application and register your notification application on their web site
before you can use this example.
For the example given below, open Form Designer, create an action button, copy and paste the code given below into the procedure editor.
Example #11: Send Email From Your Web Site
This example requires you to upload an email gateway script to your web site with php support.
Most hosting providers support php but you may get confirmation from your hosting provider if you are not sure.
The email gateway script utilizes PhpMailer.
Steps to setup email gateway:
- Download the email gateway script.
- Unzip the file, open the file named "sbm.php" with Windows Notepad program.
- Locate the text piece "your-security-code", replace it with any arbitrary length of random characters, save changes and close.
- Connect to your web site via any FTP client or the file management area of your web site control panel.
- Create a subfolder named "sbmailgateway" on your web root of your domain.
- Upload all 4 files into this new folder.
Steps to setup email sending script in your SpeedBase database:
- Open "Field List" for the desired catalog.
- Create 3 text fields to enter email content using the names: "Recipient", "Subject", "Message".
- Open "Form Designer" window and place the new fields.
- On "Form Designer" window, select Toolbox tab from right,
- Create an action button, copy and paste the code given below into the procedure editor.
- Replace the required parts in the script as described.
- Save changes and close form designer.
To send an email, create/open a record, fill in the recipient, subject and message fields and then click to the action button you have created.
Example #12: Executing SQL Queries in a Loop
For the example given below,
- Create a new catalog titled "Test Catalog",
- Create a new field on that catalog titled "Name",
- Create a few records on that catalog filling the name field with example names using lower case letters.
Open Form Designer, select Toolbox tab,
- Create an action button, copy and paste the code given below into the procedure editor.
Example #13: Exporting Records in a View to CSV File
The following code exports data from a number of specified fields from all records of a specified view.
Open Form Designer, select Toolbox tab,
- Create an action button, copy and paste the code given below into the procedure editor.
You must replace the catalog name, view name and field names given below to the actual names used in your database.
You may add more lines to export more fields. Replace also the export file path.
Tips:
- If you save this script as a scheduled script task, you may allow it to execute automatically with periodic time intervals.
- SQLGetFieldValue function is good to retrieve data in the same way as displayed in SpeedBase. You must prefere it
to read option list or relationship field data.
You may prefere using SQLGetField function in some cases to read the unprocessed raw data as saved to the database
for fields like numerics.
- If data in a text field in your database may contain a double quote character, they will cause a conflict with CSV file definition
and must be escaped by duplicating them e.g. REPLACE(SQLGETFIELDVALUE("name"), sQuote, sQuote & sQuote)
- The execution time of the script depends on the number of records and it may take some time if you have many thousands of records
in your database. You may need to raise the maximum execution time from the script editor if it time outs.