ClientCode Scripting Bible
Scripting Engine Developer Guide
Client Code provides very flexible customisability by way of form design, config fields, reports, actions and stored procedures to implement business logic.
This document provides a programmers guide (bible and tutorial) to scripting which allows developers to perform client based tasks such as validate input, process batch scripts, change screen behaviour etc..
Prepared by:
Garry Lowther
ClientCode.com
Original Draft: September 2000
Current Version: Monday, 10 October 2005
Table of Contents
1. Introduction 4
2. Client Code Script Engine Architecture 5
3. CShowForm / CFormObjects 7
4. Client Code Form Events 10
5. Virtual Entities/Forms 11
6. Client Code Object Model 13
7. Example of setting ellipses lookups 15
8. Step by Step Example of Master-Detail Dynamic Grid Handling 16
9. Field Visibility Example Code – How to Use FormFields within ShowForm 21
10. Button Visibility Example Code – How to Use FormButton within ShowForm 22
11. Setting Selected Tab In Script 23
12. Detecting Which Button is Pressed 24
13. Firing an Action Button 25
14. Firing an Action From Script Code 26
15. Validating Action Values 28
16. Creating a Lookup Combo 30
17. Asking the User to Choose from a List 31
18. Using SQL Selection Object 32
19. Modal Virtual Form Interaction 33
20. Action Form Collection Manipulation 36
21. Firing A Report From Script 37
22. Using Classes in VBScript – Object Oriented Programming 38
23. Programming Grid Column/Cell Colours 40
24. Multi-Select Grid Rows 42
25. Launching Core Entity Forms From Script 43
26. Using FileSystemObject in VBScript 44
27. Read all Grid Rows 45
28. External Document Launching 46
29. Global Parameter Value 47
30. Launching E-Mail Client 48
31. Colouring Tabs 49
32. Crystal Report Form Script 50
33. Automating Word 52
34. Updateable Grid for Data Entry 53
35. Programmed Fields Re-Appearing on Record Save 55
36. AddItem Grid Rows 56
37. Unbound Virtual Grid Population 57
38. Refreshing Grids on Other Entity Forms 58
39. Running Mail Merges From Script 59
40. Diagnostics and User Activity Logging for Debugging and Audit Trails 61
41. Closing Action Forms 62
42. Post Code Lookup 63
43. Using Bookmarks in Grids 64
44. Accessing Grid SQL Query Recordset and Cloning 65
45. Unbound Grids - Formatting Columns 66
46. Form_Unload – Preventing form from closing from Script 67
47. Public Sub Form_ExternalRefresh – Catching events in script 68
48. Spin Increments – Setting Visibility from Script 69
49. Report Formulas and Parameters 70
50. Form Timers 72
51. Forcing ExternalRefresh events from Script 73
52. Capturing Main Binder Toolbar Buttons 74
53. Printing Contact Reports from Virtual Forms 75
54. Access System Settings 76
55. Designer Queries in Script 77
56. Setting the Report Destination at run-time from Script 78
57. Intercepting Report Selection Events from Script 80
58. Grid Column Filtering/Sorting for AddItem Grids 81
59. Using CommonDialogue Friendly Function to Find Files 83
60. Form_BeforeDeleteRecord Event 84
61. Form_CoreEntityLookupEvent 85
62. frmEntitySearch Buttons 88
63. Turning Off Virtual Form Menu 89
64. Dynamic Button Pop-Up Menus 90
65. Hiding Tabs in Entity Search and Virtual Forms 91
66. Preventing Entity Search based on Form Permission 92
67. Setting visibility of btnInternet on Internet Field Types 93
68. Setting Script Timeout Globally or for Each Form 94
69. Using the GridAlpha from script 95
70. Setting Field, Button & Tab Visibility from Script 98
1. Introduction
Client Code has been developed to be as flexible/configurable as possible from both a user, analyst and programmer perspectives. The reasons for this are:
• Compete with more established ERP/CRM systems
• Allow easier customisation on-site by analysts
• Closely match clients business processes
Most of the design goals have been achieved through:
• Designer
• Actions
• Reports
• SQL Server Stored Procedures
• SQL Server views/triggers/batch jobs etc..
• Client code scripting
The scripting feature is how we implement a fully programmable client. This allows power users, analysts, implementers etc the opportunity of programming the operation of the client software to perform a multitude of tasks.
The approach has been to implement support for an industry standard scripting language within Client Code. This document discusses such an implementation.
The goals of scripting integration were as follows:
• Use appropriate scripting engine: VBScript, JavaScript or VBA
• Exposure of Client Code object model
• Handle form business logic e.g. Change title to Mrs changes sex to Female.
• Field events for validation
• Form events for load/save/validate etc..
• Addition of code behind new widgets (buttons, text, lists etc..) to carry out ad-hoc tasks
• Customisable grids and behaviour
• Virtual entities – new forms
• Ad-hoc saveable scripts to be run from GUI
• Batch scripts to run scheduled
2. Client Code Script Engine Architecture
This section contains the script engine architecture:
The red boxes are the configurable forms which each have their own CShowForm class (mustard). Each CShowForm has access to a script engine which is owned by the form (because some non config forms do not have an associated CShowForm).
Each instance of a CScriptEngine (blue) object has access to the global objects (grey) business model hierarchy (discussed below). This contains all of the global objects used by Client Code to access the underlying business data.
In the case of non-config forms, they do not have an associated CShowForm, but still have a script engine to provide the scripting capability and access to the global objects.
Object Hierarchy
The object hierarchy consists of a collection class (CScriptEngines) together with a CScriptEngine instance for each form which exposes a scripting interface.
The CScriptEngine has access to a collection of underlying form objects which are the text boxes, lists, controls etc on the form.
Each forms underlying CFormObjects collection is accessible from ANY script for ANY form. This exposes the very powerful and flexible underlying Visual Basic Forms engine. This allows any script to access the entire set of forms within the application. An example of use might be when an update to a form needs to update a counter on a master form. See later sections for examples of this.
The CFunctionStatus is the class which is available to VBScript for modifying parameters as it is not possible to modify the function parameters as they are not passed by reference. See later chapter for more detail.
3. CShowForm / CFormObjects
CShowForm is the class which handles all form processing for each config form (Contact, Company etc..). The changes to CShowForm are necessary to facilitate the event processing of form events. The underlying form may need to be coded with extra widget handling, but most of it can be accommodated from within CShowForm.
An object model has been constructed which allows access to the following:
Object Properties Methods Events
Fields Yes Yes Yes
Buttons Yes No Yes
Menus Yes No Yes
Grids Yes Yes Yes
Tab Yes No Yes
Form Yes Yes Yes
Widgets (status bar, caption, width, window state etc..) Yes No
Function (form function) No Callable Yes
2 classes exist which are added as an object to the Script Engine instantiated for the form. This provides a simple abstraction to the form fields and widgets from within the script engine to facilitate easy access by the script programmer. It is necessary because CShowForm may not exist on the form if it is a non-config variety.
Class: CFormObjects
Properties: EventControl – the control which has raised the event
Methods: FormRef – the Form object
ShowForm – the CShowForm object for config forms
LoadControl – create a new form control and return reference
Events: None
Description: The collection class for the form objects.
Provides access to all form objects via specific method to differentiate types.
FormRef is discussed below.
CShowForm is also discussed below
This is accessible from within script as the FormObjectsLocal object.
Class: FormRef – Object reference to VB Form from CFormObjects
Properties: Type (Field=1, Button=2, Menu=3, Grid=4, Tab=5, Form=6, OtherWidget=7)
Field: BackColour, ForeColour, Caption, Value, ToolTipText
Button: Caption, ToolTipText
Menu: Caption, Checked, Visible
Grid: Row, ColumnValue, ColumnHeader, CellForeColour, CellBackColour
Tab: TabCaption
Form: Caption, WindowState, Height, Width
Methods: FormWidget (the actual underlying widget on form. Any property can thus be manipulated)
Events:
Field: Change, Click, DblClick, GotFocus, KeyPress, LostFocus
Button: Click
Menu: Click
Grid: AfterInitialise, DblClick, RowLoaded, DisplayCell, BeforePopulation
Tab: Click
Form: AfterLoad, AfterLoadRecord, AfterSaveRecord, BeforeSaveRecord, Unload
Description: Contains all methods and properties for the script to interact directly with the underlying form characteristics. Can modify field and button properties, process callbacks, grids etc..
The script for the form and the form design are closely coupled together with this class to provide a fully programmable form.
Class: CShowForm – Object reference to config fields object from CFormObjects
Properties: FormProperties – return object containing detail for form
GetFieldWidget(sTableName, sTableFieldName)
GetFieldDescriptionFromWidgetHwnd – return FieldDescription object from the window handle of the widget.
Methods: None
Events: None
Description: Used to access the config forms objects.
IMPORTANT:
It is not possible to modify the script function parameters as they are not passed by reference from within Client Code. It is possible to return a value for those functions documented below by using the FunctionStatus object.
Class: CFunctionStatus – Object reference to parameters list
Properties: SetParameter(sParameterName as string, Value as variant)
GetParameter(sParameterName as string) as variant
Methods: AddParameter(sParameterName as string,, Optional Value)
ClearParameters
Events: None
Description: Exposed to scripting engine as FunctionStatus. This is how the VBScript changes the parameters for passing back to Client Code.
See examples section for numerous examples.
4. Client Code Form Events
These events are raised for the specified objects:
FormObjectEvent_AfterFilter ' Grid
FormObjectEvent_AfterInitialise ' Grid
FormObjectEvent_AfterInsert ' Grid
FormObjectEvent_AfterLoad ' Form
FormObjectEvent_AfterLoadRecord ' Form
FormObjectEvent_AfterNewCompanyContact ' frmCompany only
FormObjectEvent_AfterSaveRecord ' Form
FormObjectEvent_AfterUpdate ' Grid
FormObjectEvent_BeforeDeleteRecord ' Form
FormObjectEvent_BeforePopulation ' Grid
FormObjectEvent_BeforePrint ' Form
FormObjectEvent_BeforeSaveRecord ' Form
FormObjectEvent_Change ' Field
FormObjectEvent_Click ' Button, Field, Menu, Grid
FormObjectEvent_CoreEntityLookup ' Form
FormObjectEvent_DblClick ' Field, Grid
FormObjectEvent_DisplayCell ' Grid
FormObjectEvent_ExternalRefresh ' Form
FormObjectEvent_GotFocus ' Field, Grid
FormObjectEvent_KeyPress ' Field, Grid
FormObjectEvent_LostFocus ' Field, Grid
FormObjectEvent_RowLoaded ' Grid
FormObjectEvent_RowSelection ' Grid
FormObjectEvent_Timer ' Form
FormObjectEvent_ToolbarButton ' Form
FormObjectEvent_Unload ' Form
5. Virtual Entities/Forms
Designer allows programmers to create ‘virtual entities’. These are defined as ad-hoc forms with no underlying entity. All entity fields would be populated entirely by the scripting object model.
The form can be invoked from anywhere and Designer will allow buttons, fields etc to be added to it with script code behind.
Virtual entities/forms are created in exactly the same manner as normal entities i.e. using the entity builder:
Pressing the New button loads the Entity Builder:
Note that when the Virtual Entity/Form check box is ticked, the Modal Form check box appears, and the Contact Management Links and Entity Links tabs become invisible (they are not of any use).
Enter the name of your entity and provide a sensible description to help self-document the entity.
If you want the form to be loaded modally, i.e. shown on-top of all other forms, centred on the screen, and has all focus such that the user must complete the form to return to Client Code, then check the Modal Form check box.
Select the Form Details tab. This allows you to set a caption for the form, and to assign an icon for it which will allow it to be available from the main list bar and it will also be shown on the top left of the form when loaded:
When you save your entity, you will then design the form in the same fashion as other forms, actions and queries.
The main difference is that there are no fixed fields or reference fields and no default buttons. This is because this is a virtual form i.e. it sits within the Client Code infrastructure but does not have any business logic to read or save from the Client Code object model. The whole idea is that you code this logic entirely through script. This has the main advantage of letting you write your own application rules without having to tweak existing rules.
When you create config fields in the Designer for virtual forms, no fields are created on the SQL Server database. It is the responsibility of the script developer to write code which will read/write these fields to/from whatever data source is necessary, which f course may not be a SQL Server database.
When developing script, the standard objects and mechanisms are identical to writing script for other forms, such that virtual form code looks identical to other forms. You can cut & paste code from all parts of the system and re-use global functions etc…
The method of accessing form fields is the same too, with the table name being the name of the newly created entity + “ConfigFields” which is in keeping with the standard system nomenclature for accessing config fields e.g.:
With FormObjectsLocal.ShowForm
sForenames = .GetFieldValue("AddContactConfigFields", "Forenames")
sSurname = .GetFieldValue("AddContactConfigFields", "Surname")
End With
6. Client Code Object Model
This section contains detailed documentation of the Client Code Object Model. This object model is split into 2 parts for the purposes of the scripting engine:
• Forms Objects – these provide access to the form upon which the scripts are invoked
• Global Objects – these provide access to the global business object model
Form Objects Examples:
Dim formObjectsfrmMain, formField, formObj
Set formField = FormObjectsLocal.GetFormObjectByName(“Contact”, “Male”)
If formField.formWidget.Value = 1 then
formField.formWidget.Text = “Man”
Else
formField.formWidget.Text = “Woman”
End if
‘ Access another form using different global collection
Set formObjectsMain = FormObjectsGlobal(“frmMain”)
If not formObjectsMain is nothing then
Set formRef = formObjectsMain.FormRef
formRef.Caption = “Someone changed sex!”
End if
The exposed Client Code Objects are:
Global Object Description
Initialisation UsrCfgUsers table fields for current user + collections of contacts, companies etc..
Database The CDatabase object which exposes the methods to access the SQL Server database.
WordProcessor Invoke Word automation
StringFunctions String manipulation functions
MessageBox Show messages and ask questions of user
FileSystem Access the underlying windows file system
Selection Select business data e.g. contacts, companies from pop-up dialogue grids
DataWidgets The grid functions to operate upon on-screen grids
DbGet Get standard database functions
Numbers General purpose number manipulation
CrystalReports Underlying crystal report related functions not CRPE
FormFunctions The functions which control Client Code forms
Diagnostics Diags.out file access to record own messages to output file
Miscellaneous Functions such as auto-dial, load web browser etc..
ShellExecute Execute shell functions such as launch e-mail dialogue etc..
Registry Access the registry
AnimateWait Show animation dialogue similar to Client Code start up
ProgressWait Show progress gauge or tick stages
BigInput Capture any text input from a dialogue box
ErrorHandling Standard error handler functions
MailMerge Mail merging functions
7. Example of setting ellipses lookups
Public Sub Form_AfterLoad ()
Call MakeWidgetEnabled("ContractConfigFields", "TSAuthoriser1", "btnLookupFind", False)
End Sub
Private Sub MakeWidgetEnabled(sTableName, sFieldName, sAssociatedWidgetName, bEnabledFlag)
Set widget = FormObjectsLocal.ShowForm.GetFieldWidget((sTableName), (sFieldName))
If Not widget Is Nothing Then
With FormObjectsLocal.FormRef.Controls(sAssociatedWidgetName)(widget.index)
.Enabled = False
'.backcolor = vbRed
End With
End If
End Sub
8. Step by Step Example of Master-Detail Dynamic Grid Handling
In this example, we create 2 grids using Designer on a form (or action) which are not linked to any entities and write script code to handle the interaction between them.
Step 1: Open form and create 2 grids in Designer.
Call these GridMaster and GridDetail.
Step 2: Place these on a convenient location like so:
Step 3: Save form design and test the design. The tab should look as follows:
Step 4: Open the script editor for this form and paste the following code into the editor:
Public Sub Form_AfterLoad ()
Call InitialiseGridMaster( _
FormObjectsLocal.ShowForm.GetFieldWidget( _
"RequirementConfigFields", "GridMaster"))
End Sub
' The table selection grid
Private Sub InitialiseGridMaster(dwGrid)
Dim i
If dwGrid Is Nothing Then
MessageBox.appmsg "GridMaster Not found!"
Exit Sub
End If
' Initialise grid
With dwGrid
.Columns.RemoveAll
.DataMode = 2 ' Add Item Grid
.AllowAddNew = False
.AllowUpdate = False
.Caption = "Main Client Code Tables - Select to view details"
.MaxSelectedRows = 1
.RecordSelectors = True
.SelectTypeRow = 1 'Single Select
' More settings
End With
' Add columns
With DataWidgets
.AddColumnToGrid (dwGrid), 0, "Table Name", 4000, True
.AddColumnToGrid (dwGrid), 1, "Actual Table Name", 2000, False
End With
' Add Rows
With dwGrid
.AddItem "©Contact©,©Contact©"
.AddItem "©Company©,©Company©"
.AddItem "©Placement©,©Contract©"
.AddItem "©Requirement©,©Requirement©"
.AddItem "©Users©,©Users©"
.AddItem "©FieldDescription©,©FieldDescription©"
.AddItem "©FormProperties©,©FormProperties©"
End With
' Select first row and fire detail event
dwGrid.AddItemBookmark 1
Call InitialiseGridDetail( _
FormObjectsLocal.ShowForm.GetFieldWidget _
("CompanyConfigFields", "GridDetail"), "Contact")
End Sub
Public Sub Grid_RowSelection ()
Set fd = FormObjectsLocal.ShowForm. _
GetFieldDescriptionFromWidgetHwnd (FormObjectsLocal.EventControl.hWnd)
If fd Is Nothing Then Exit Sub
If fd.TableFieldName = "GridMaster" Then
Set dwGrid = FormObjectsLocal.EventControl
sTableName = dwGrid.Columns(1).Text
Call InitialiseGridDetail(FormObjectsLocal.ShowForm.GetFieldWidget _
("CompanyConfigFields", "GridDetail"), sTableName)
End If
End Sub
Private Sub InitialiseGridDetail(dwGrid, sTableName)
Dim i, c
If dwGrid Is Nothing Then
MessageBox.appmsg "GridDetail Not found!"
Exit Sub
End If
' Initialise grid
With dwGrid
.Columns.RemoveAll
.DataMode = 2 ' Add Item Grid
.AllowAddNew = False
.AllowUpdate = False
.Caption = "First 10 Rows of Table: " & sTableName
.MaxSelectedRows = 1
.RecordSelectors = True
.SelectTypeRow = 1 'Single Select
.Redraw = False
End With
i = 0
sSQL = "Select Top 10 * From " & sTableName
Set rs = Database.OpenRecordset((sSQL))
Do While Not rs.eof
i = i+1
If i = 1 Then
' Add columns
For c = 1 to rs.Fields.Count
DataWidgets.AddColumnToGrid (dwGrid), c-1, _
rs.Fields(c-1).Name, 1000, True
Next
End If
' Add row to grid
sAddItemString = ""
For c = 1 to rs.Fields.Count
If c > 1 Then sAddItemString = sAddItemString & ","
sAddItemString = sAddItemString & "©" & _
StringFunctions.VBFormat(rs.Fields(c-1).Value, "@") & "©"
Next
dwGrid.AddItem sAddItemString
rs.MoveNext
Loop
rs.close
dwGrid.Redraw = True
End Sub
Public Sub Grid_DblClick ( Cancel )
Set fd = _
FormObjectsLocal.ShowForm.GetFieldDescriptionFromWidgetHwnd _
(FormObjectsLocal.EventControl.hWnd)
If fd Is Nothing Then Exit Sub
If fd.TableFieldName = "GridDetail" Then
Set dwGrid = FormObjectsLocal.EventControl
sField1 = dwGrid.Columns(0).Text
MessageBox.appmsg "You double clicked record: " & sField1 & "?"
End If
End Sub
Step 5: Edit the script if necessary and replace CompanyConfigFields with the table upon which your created the grids.
Step 6: Apply the changes and open the Client Code form and it should operate as follows when any row in the grid is selected in the master grid (with either the mouse click or cursor key up/down), the detail grid should show the first 10 records from the table:
The code demonstrates a number of features:
• Creation of empty grids with no standard functionality
• Dynamic creation of grid columns
• Grid Event processing: row selection and row drill down
• Master/Detail functionality which can be applied to any database tables
• Speed at which the script engine can interact with the database in real-time
• Flexibility of independence from the Client Code entity builder framework
9. Field Visibility Example Code – How to Use FormFields within ShowForm
See Chapter 70 – V2.00.070 onwards for much better method than that described below.
In order to fully address the issue of widget visibility from script, I attach some code which you will find useful.
Here is a technical explanation of the Client Code internal config forms engine:
Each CShowForm class (the run-time FormObjectsLocal.ShowForm object) has a number of child objects.
One of the objects is CFormField which is an on-form instance of the CFieldDescription class which describes the field.
By setting the Visible (or Enabled, BackColour, FontSize, ForeColour, Left, Top, Height, Width, ReadOnly, ShowLabel, ToolTipText) property of the CFormField object, you allow CShowForm to treat the field in the way you want it which overrides the form designed attributes.
The example below shows how I have done it for the contact availability date which I do not want to be viisible when the contact is a client.
The SetFieldVisible function can be placed on any form and used accordingly for any field.
' Hide/Show controls accordingly
Sub ProcessContactType()
' Determine whether the contact is a client or not
bClient = Not FormObjectsLocal.ShowForm.GetFieldValue("Contact", _
"ContactTypeCandidate")
' Now set the visibility of these fields
Call SetFieldVisible("Contact", "AvailabilityDate", Not bClient)
End Sub
' Generic function to set a fields (and labels) visibility
Private Sub SetFieldVisible(sEntityName, sTableFieldName, bVisible)
Dim objField, objLabel, formField
With FormObjectsLocal.ShowForm
' Get the widget and CFormField object for the specified field
Set objField = .GetFieldWidget((sEntityName), (sTableFieldName))
Set formField = .GetFormFieldFromFieldName((sEntityName), (sTableFieldName))
' If field found, set visiblity so that CShowForm class knows when to show
If Not formField Is Nothing Then formField.Visible = bVisible
' Make field widget visible/invisible right now without refreshing whole form
If Not objField Is Nothing Then objField.Visible = bVisible
' Do control label visibility now too
Set objLabel = .GetFieldWidget((sEntityName), (sTableFieldName), 1)
If Not objLabel Is Nothing Then objLabel.Visible = bVisible
End With
End Sub
----- Original Message -----
Sent: 30 January 2001 17:33
Subject: Request for new facilities
> GaRRY,
> There are a couple of things that I need:
> * When a config form is clicked on, all the controls refresh themselves. If there is any script on the form to modify the controls' appearance, the
> changes are lost when the refresh is completed. Can I have a hook to intercept the refresh?
> * When selecting a placement to display from the requirement form, the placement form is loaded but the controls are not displayed until the form
> is clicked on. Can you investigate this? I can send you an export schema if that will help.
10. Button Visibility Example Code – How to Use FormButton within ShowForm
In order to fully address the issue of button visibility from script, here is some code which you will find useful.
This is very similar to that in the previous chapter, but uses the form buttons collection.
Public Sub Button_Click
' Not interested in non-config buttons
if FormObjectsLocal.EventControl.Name <> "btn" then Exit Sub
Set btn = FormObjectsLocal.ShowForm.GetButtonFromIndex("btn", _
FormObjectsLocal.EventControl.Index)
If btn Is Nothing Then Exit Sub
Select case btn.ButtonFunction
Case “The One To Make Invisible”
Call SetButtonVisibility(btn.ButtonFunction, False)
Exit Sub
End Select
End Sub
See chapter 70 for V2 details on easily setting visibility.
' Set the visibility of the specified button.
' This uses the CShowForm class to control the buttons visibility between
‘ tab clicks
'
Private Function SetButtonVisibility(sButtonFunction, bVisible)
' Get the form button from the CShowForm class
Set formButton = FormObjectsLocal.ShowForm.GetButtonFromFunction( _
"btn", (sButtonFunction))
If formButton Is Nothing Then Exit Function
' Set its visibility
formButton.Visible = bVisible
' Now set the current widget instance visibility
Set btnWidget = formButton.FormWidget
If btnWidget Is Nothing Then Exit Function
' Only set the visibility if this button is in view
If FormObjectsLocal.ShowForm.isWidgetVisibleOnCurrentTab(btnWidget.hWnd) Then
btnWidget.Visible = bVisible
End If
End Function
11. Setting Selected Tab In Script
See Chapter 70 for V2 code.
Developers,
To force another tab to get focus on a config form and force redraw of the tab widgets, use the following code
With FormObjectsLocal.FormRef
.Tabby.Tabs(2).Selected = True
.tabby_click
End With
This performs the same processing as if the user selected the tab with the mouse.
The .tabby_click event corresponds to the Public Sub tabby_Click method of the underlying form (you VB'ers).
Garry.
-----Original Message-----
From: Matt Jessop [mailto:Mattb@itsquared.com]
Sent: 29 January 2001 09:52
To: 'garry@technical.demon.co.uk'
Subject: Selecting Tabs
Garry,
I am using the following command to select differrent tabs on a form, it works, but it is not drawing the form objects until you click on the form somewhere.
Me ? or you ?
g
FormObjectsLocal.FormRef.Tabby.Tabs(2).Selected = True
12. Detecting Which Button is Pressed
Create a button using Designer and give it a function name: “TabTest”
Drop it onto a form.
Write the following script code to catch the button click even for it:
Public Sub Button_Click()
' Not interested in non-config buttons
if FormObjectsLocal.EventControl.Name <> "btn" then Exit Sub
Set btn = FormObjectsLocal.ShowForm.GetButtonFromIndex(“btn”, _
FormObjectsLocal.EventControl.Index)
If btn is nothing then exit sub
If btn.ButtonFunction = “TabTest” Then
‘ Your code for this button in here
End if
End Sub
13. Firing an Action Button
This tiny script allows you to invoke an action from a config form.
Steps to reproduce:
1. Create action
2. Create an action button
3. Add the action button to the form
4. Write this code to fire the action associated with the button from script:
Sub YourFunction()
Call FireActionButton("Get In Touch")
End Sub
Private Sub FireActionButton(sActionName)
Set btn = FormObjectsLocal.ShowForm.GetButtonFromFunction("btn", (sActionName))
If btn Is Nothing Then Exit Sub
' Fire the action button
btn.FormWidget.Value = True
End Sub
See next example for more code relating to this.
14. Firing an Action From Script Code
This script allows you to invoke an action from any form using script code alone.
In the example, a button is created with no link to an action or extension. This is then fired to operate upon the details of an independent script-controlled grid to fire the action using the grid data.
This makes use of an exposed function on the underlying form:
FormObjectsLocal.FormRef.RunFormAction ActionId, ContactId
Steps to reproduce:
1. Follow section above: Step by Step Example of Master-Detail Dynamic Grid Handling in order to create master/detail script controlled grids.
2. Create action (or reuse existing action e.g. Note/Call etc…) – Note the ActionId
3. Create a button called RunActionAgainstContactId – do NOT assign to an action.
4. Add the button to the form beneath the detail grid.
5. Write this code to fire the action associated with the button from script:
Public Sub Button_Click
' Not interested in non-config buttons
if FormObjectsLocal.EventControl.Name <> "btn" then Exit Sub
Set btn = FormObjectsLocal.ShowForm.GetButtonFromIndex("btn", _
FormObjectsLocal.EventControl.Index)
If btn Is Nothing Then Exit Sub
If btn.ButtonFunction = "RunActionAgainstContactId" Then
Call RunActionAgainstContactId
Exit Sub
End If
End Sub
' Button clicked to fire an action against the currently selected contact
'
Private Sub RunActionAgainstContactId
Set dwGrid = FormObjectsLocal.ShowForm.GetFieldWidget( _
"CompanyConfigFields", "GridDetail")
If dwGrid Is Nothing Then Exit Sub
If instr(dwGrid.Caption, "Contact") = 0 Then
MessageBox.AppStopMsg _
"You can only fire this button when contacts are displayed in the grid."
Exit Sub
End If
' Get selected contactid
ContactId = CLng(GetGridCellValue(dwGrid, "ContactId"))
'MessageBox.appmsg "Running action against Id: " & ContactId
' ActionId=32 is Note
FormObjectsLocal.FormRef.RunFormAction 32, clng(ContactId), 0
End Sub
New: March 18th 2003 – New parameter now available in RunFormAction.
This is a comma delimited string containing ID’s of contacts.
e.g.
sContactId = “10, 20, 25, 26, 28”
FormObjectsLocal.FormRef.RunFormAction 32, 0, 0, (sContactId)
This will run the action over the 5 contact id’s listed in the string.
15. Validating Action Values
' Example to demonstrate the capturing in script of the on-screen fields
' in an action so that the action can be prevented from running if all
' expected fields are not correctly input by the user.
'
' Feb 2001
'
' Validate input here
Public Sub Form_BeforeSaveRecord ( Id, Cancel )
bCancel = Not ValidateActionFields
If bCancel Then FunctionStatus.SetParameter("Cancel") = True
End Sub
Private Function ValidateActionFields()
' Check the candidate Id - must be set when creating a placement
' candidateId = FormObjectsLocal.ShowForm.GetFieldValue("ContractPlacement", "CandidateId")
Set candidateIdCollection = FormObjectsLocal.Formref.candidateIdCollection
If candidateIdCollection Is Nothing Then
bCandidateError = True
Else
If candidateIdCollection.count = 0 Then
bCandidateError = True
Else
If clng(candidateIdCollection(1)) = 0 Then
bCandidateError = True
End If
End If
End If
If bCandidateError Then
MessageBox.AppStopMsg "Please select a candidate for the placement."
Exit Function
End If
' Make sure rates are set
payRate = cdbl(FormObjectsLocal.ShowForm.GetFieldValue("Requirement", "StandardPayRate"))
If payRate <= 0 Then
MessageBox.AppStopMsg "Please enter a valid candidate pay rate."
Exit Function
End If
chargeRate = cdbl(FormObjectsLocal.ShowForm.GetFieldValue("Requirement", "StandardChargeRate"))
If chargeRate <= 0 Then
MessageBox.AppStopMsg "Please enter a valid client charge rate."
Exit Function
End If
' Make sure we have valid dates
startDate = FormObjectsLocal.ShowForm.GetFieldValue("Requirement", "EarliestStartDate")
If len(startDate) = 0 Then
MessageBox.AppStopMsg "Please enter a start date for the placement."
Exit Function
End If
endDate = FormObjectsLocal.ShowForm.GetFieldValue("Contract", "ContractEndDate")
If len(endDate) = 0 Then
MessageBox.AppStopMsg "Please enter an end date for the placement."
Exit Function
End If
' All OK?
ValidateActionFields = True
End Function
16. Creating a Lookup Combo
This procedure takes the example of creating a job title combo on the company form and populating it with job titles. This is not possible to do using reference fields.
Steps to reproduce:
a. Open Designer for company form and create a new config field called JobTitles.
b. This should be of type Lookup, not searchable, and with no values.
c. Drop this onto the company form
d. Write the following piece of code into the script editor for the form:
Public Sub Form_AfterLoad
' JobTitles Combo
Call InitialiseJobTitlesCombo _
(FormObjectsLocal.ShowForm.GetFieldWidget( _
“CompanyConfigFields”, “JobTitles”))
End Sub
‘ JobTitles Combo
Private Sub InitialiseJobTitlesCombo(cmbJobTitles)
If cmbJobTitles Is Nothing Then Exit Sub
cmbJobTitles.clear
MySQL = “Select JobTitle From JobTitle” & _
“ Order by Ordering”
Set rs = Database.OpenRecordset((MySQL))
Do While Not rs.EOF
sJobTitle = rs(0)
With cmbJobTitles
.AddItem sJobTitle
If .ListCount = 1 Then .ListIndex = 0
End With
rs.MoveNext
Loop
rs.Close
End Sub
The widget on the form should look as follows when run in Client Code:
17. Asking the User to Choose from a List
This procedure shows how to use the internal frmSelection form to offer the user a choice of items in order to choose one.
Private Sub SelectionFormTest
Set frmSelection = FormFunctions.GetFormReference("frmSelection")
With frmSelection
.ClearItems
.AddItem "1. Do Nowt at " & Now
.AddItem "2. Do Something at " & Now
.Caption = "Selection Test"
.label = "Please select any option"
.ShowModal
If Not .Selected Then Exit Sub
MessageBox.AppMsg "You selected '" & .SelectedItem & "' ?"
End With
End Sub
For a discussion and examples of using the more sophisticated SQL selection mechanism, see the following section.
18. Using SQL Selection Object
This procedure shows how to use the internal SQL selection object (5.00.063 or later) to pass in a SQL statement, setup grid columns etc and retrieve the selected records from a modal dialogue box.
It is of use when the you need to offer the user a choice of selecting a range of records from any table or tables in the database.
' This function will allow the user to select a candidate contact from
' a pop-up and will show the correct priority and type to boot!
Private Sub SQLSelectionFormTest
Set SQLSelection = Selection.GetSelectSQLObject
With SQLSelection
.Columns = 6
.ColWidth(0) = 0
.ColWidth(1) = 0
.ColWidth(2) = 0
.ColWidth(3) = 1500
.ColWidth(4) = 2000
.ColWidth(5) = 1200
.Caption = "Select any Candidate"
.SQL = "Select Top 50 ContactId, Priority, Type, Christian as Forenames, " & _
“Surname, DateOfBirth as 'Date of Birth'" & _
" From Contact" & _
" Where Contact.Type = 1" & _
" Order by Surname, Christian"
.ContactSelection = True
.PriorityColumn = 1
.TypeColumn = 2
If Not .Selection Then Exit Sub
MessageBox.AppMsg "You selected ContactId:" & .SelectedRecord(1) & " ?"
End With
End Sub
' SQL selection of any number of placements
Private Sub SQLSelectionPlacement
Set SQLSelection = Selection.GetSelectSQLObject
With SQLSelection
.Columns = 4
.ColWidth(0) = 0
.ColWidth(1) = 1200
.ColWidth(2) = 2000
.ColWidth(3) = 2500
.Caption = "Select one or more Placements"
.SQL = "Select ContractId, Reference, Company.Name as Company" & _
", Christian + ' ' + Surname as Candidate" & _
" From Contract, Company, Contact" & _
" Where Contract.CompanyId = Company.CompanyId" & _
" And Contract.CandidateId = Contact.ContactId" & _
" Order by Reference"
.MultipleSelection = True
If Not .Selection Then Exit Sub
For i = 1 to .SelectedCount
If i > 1 Then s = s & ", "
s = s & clng(.SelectedRecord(cint(i)))
Next
If len(s) > 0 Then MessageBox.appMsg "You selected " & .SelectedCount & _
" placements: " & s & "?"
End With
End Sub
Note how you can set up the column widths, specify multiple record selection (using shift/ctrl keys), set caption, assign SQL statement, cater for contact priorities and contact types to allow colours.
19. Modal Virtual Form Interaction
This procedure shows how to use the modal virtual form capability of Client Code (5.00.063 or later) by invoking it from script code to display defaults, validate the data and return the data to the caller.
Step 1: Create a modal virtual entity
Step 2: Design the modal virtual entity form and create data capture fields
Step 3: Design the modal virtual entity script code to capture data and validate it
' On button click - validate input and close if OK
Public Sub Button_Click ()
With FormObjectsLocal.showform
sForenames = .GetFieldValue("AddContactConfigFields", "Forenames")
sSurname = .GetFieldValue("AddContactConfigFields", "Surname")
If len(sForenames) = 0 or len(sSurname) = 0 Then
MessageBox.AppStopMsg "Please enter both forenames and surname." & _
sForenames & sSurname
Exit Sub
End If
End With
' All OK, then close form using correct exposed method to preserve stacked scripts
FormObjectsLocal.FormRef.FormUnload
End Sub
Step 4: Create a button on the calling form for the user
Step 5: Write code for the button to invoke the modal virtual form, pass in default values and capture validated values from it.
Public Sub Button_Click
' Not interested in non-config buttons
if FormObjectsLocal.EventControl.Name <> "btn" then Exit Sub
Set btn = FormObjectsLocal.ShowForm.GetButtonFromIndex("btn", _
FormObjectsLocal.EventControl.Index)
If btn Is Nothing Then Exit Sub
If btn.ButtonFunction = "AddContact" Then
Call AddContactButton
Exit Sub
End If
End Sub
' Show a modal virtual entity to capture contact details to add to our grid
Private Sub AddContactButton
Set dwGrid = FormObjectsLocal.ShowForm.GetFieldWidget("CompanyConfigFields", "GridUpdate")
If dwGrid Is Nothing Then Exit Sub
Set frmModalForm = FormFunctions.GetFormReference("frmAddContact")
If frmModalForm Is Nothing Then
MessageBox.AppStopMsg "Could not find form: frmAddContact"
Exit Sub
End If
With frmModalForm
' Send initialisation data in
.SetFieldValue("Forenames") = StringFunctions.VBFormat(Now, "DDDD DD")
.SetFieldValue("Surname") = StringFunctions.VBFormat(Now, "MMMM YYYY")
' Show it
.ShowModal
' After modal form is closed, read the values from it
sForenames = .GetFieldValue("Forenames")
sSurname = .GetFieldValue("Surname")
End With
If len(sForenames) > 0 and len(sSurname) > 0 Then
dwGrid.AddItem "©" & sForenames & "©,©" & sSurname & "©"
End If
End Sub
Step 6: What it looks like in operation:
Step 7: This data is then added to the underlying form grid:
20. Action Form Collection Manipulation
This procedure shows how to add a contact to the list of default contacts in an action collection.
It also demonstrates how to use the FastContactSearch function to pop-up a contact selection dialogue.
Sub Form_AfterLoad
Dim coll, i, s
Set coll = FormObjectsLocal.formref.ContactIdCollection
If coll Is Nothing Then Exit Sub
If coll.count = 0 Then Exit Sub
' Add another contact
ContactId = Selection.FastContactSelection()
If ContactId = 0 Then Exit Sub
' Add to collection
FormObjectsLocal.formref.ContactIdCollection.Add cstr(ContactId)
' Show visuals
FormObjectsLocal.ShowForm.SetFieldValue("CallClient", "ClientContactId") = _
"< List: " & FormObjectsLocal.formref.ContactIdCollection.Count & _
" Client Contact records >"
End Sub
21. Firing A Report From Script
Reports can be easily invoked from the print button or by a new button which is linked to a specific report. This works fine for standard entities or new entities, where all record processing is taken care of when set up in Designer by the forms engine.
If however, a virtual entity has been implemented, or the report requires specific pre-processing (as opposed to writing script inside a report form), then invoking the report from script is the appropriate method.
This section shows how this is done.
Step 1: Create an entity.
Step 2: Create a Crystal report which links the tables and configure this in forms designer (see reports design and tutorial document).
Step 3: Add a button onto the form, but do not set it up as a report button as it will defeat the object of this example!
Step 4: Copy this code into the form script editor. Note how we capture the loaded or saved record id and this is used to create a SQL IN clause which is passed into the PrintReport function of the Initialisation.Reports collection.
Dim m_AdvertisementId ' as Long
Public Sub Form_AfterLoadRecord ( Id )
m_AdvertisementId = id
End Sub
Public Sub Form_AfterSaveRecord ( Id )
m_AdvertisementId = id
End Sub
Public Sub Button_Click
' Not interested in non-config buttons
if FormObjectsLocal.EventControl.Name <> "btn" then Exit Sub
Set btn = FormObjectsLocal.ShowForm.GetButtonFromIndex _
("btn", FormObjectsLocal.EventControl.Index)
If btn Is Nothing Then Exit Sub
If btn.ButtonFunction = "PrintReportFromScript" Then
Call PrintReportFromScript
Exit Sub
End If
End Sub
Private Sub PrintReportFromScript
If m_AdvertisementId > 0 Then
Initialisation.Reports.PrintReport "Advert Test.rpt", _
"(Advertisement.AdvertisementId = " & m_AdvertisementId & ")"
End If
End Sub
Please see section 49 on Report Formulas and Script for more detail and examples.
22. Using Classes in VBScript – Object Oriented Programming
VBScript version 5 is supported in Client Code. This adds sophisticated capabilities to VBScript, namely support for classes. Classes provide programmers with the opportunity to develop fully object oriented code by encapsulating re-usable business logic into a class. The class can be implemented to abstract the complexity of the logic by exposing a neatly designed interface which can be used by the programmer.
The potential of classes is shown in this very simple example:
Create a class inside a global module in Designer :
This is the code:
Class CTestClass
Private sTestString
Property Get TestString
TestString = sTestString
End Property
Property Let TestString (s)
sTestString = s
End Property
End Class
The class is called CTestClass (classes should be prefixed by C). The class contains its own private variable calls sTestString which can only be accessed by users of the class using the properties defined.
There are 2 properties, both called TestString which respectively Get and Set (via the Let operator) the value of the private variable. Private variables cannot be accessed directly by users of the class.
To see how the class might be used, see the following code:
Set m_TestClassObject = New CTestClass
m_TestClassObject.TestString = _
StringFunctions.VBFormat(Now, "DD-MMM-YYYY HH:nn:ss")
MessageBox.appmsg "CTestClass.TestString=" & m_TestClassObject.TestString
The code creates a new instance of CTestClass thus creating an object (the definition of an object in OOP (object oriented programming) is that it is an instance of a class) which exposes all of the properties/functions/subroutines of the class.
Thus in the above example, we assign a value to the TestString property (Let), then display it (Get).
Clearly the properties can do much more that simply set a value e.g. perform calculations, access the database, send messages etc…
When you access the Client Code objects e.g. Database, this is actually an internal (VB6) object which is an instance of the class CDatabase.
23. Programming Grid Column/Cell Colours
The grid is a Sheridan Data Widget. Thus the properties and methods and events for the grid must follow this conventions demonstrated below.
This example processes the Grid_RowLoaded event and colours a contact grid according to the normal contact colours for type and priority, but also colours rows of another grid where the cell is set to a particular value:
There are examples of how to do this in the ITContract database: Company and Contact forms. Look for Grid_RowLoaded event.
This is how to set the row colours for any grid:
Sub Grid_RowLoaded
With FormObjectsLocal.EventControl
.Columns(2).BackColor = vbRed
.Columns(2).ForeColor = vbBlue
End With
End Sub
And for a tighter control, you can do this:
Public Sub Grid_RowLoaded ()
Set dwGrid = FormObjectsLocal.EventControl
If dwGrid Is Nothing Then Exit Sub
Set fd = _
FormObjectsLocal.ShowForm.GetFieldDescriptionFromWidgetHwnd (dwGrid.hWnd)
If fd Is Nothing Then Exit Sub
Select Case fd.TableFieldName
Case "GridDetail"
If instr(dwGrid.Caption, "Contact") > 0 Then
Call DataWidgets.ColourContactPriorities ((dwGrid), _
CInt(GetGridCellValue(dwGrid, "Type")), _
CInt(GetGridCellValue(dwGrid, "Priority")) )
End If
Case "GridUpdate"
' Colour the row with my name in it to blue with yellow text
sName = GetGridCellValue(dwGrid, "Surname")
If instr(sName, "Lowther") > 0 Then
styleSetName = "Style" & vbYellow & vbBlue
With dwGrid
.StyleSets((styleSetName)).ForeColor = vbYellow
.StyleSets((styleSetName)).BackColor = vbBlue
For i = 0 To .Columns.Count - 1
.Columns(i).CellStyleSet ((styleSetName))
Next
End With
End If
End Select
End Sub
Private Function GetGridCellValue(dwGrid, ColumnName)
With dwGrid
For i = 0 to .Columns.Count - 1
If lcase(.columns(i).Caption) = lcase(ColumnName) Then
GetGridCellValue = .Columns(i).Value
Exit Function
End If
Next
End With
End Function
This last example uses the knowledge about contact types to colour the grid based upon the users preferences.
24. Multi-Select Grid Rows
In order to create a grid with multi-select rows, the .SelectTypeRow property must be set to either 2 or 3:
' Initialise grid columns
With dwGrid
.Columns.RemoveAll
.DataMode = 2 ' Add Item Grid
.AllowAddNew = False
.AllowUpdate = False
.Caption = "Placements for " & sSalesOffice
.MaxSelectedRows = 8
.RecordSelectors = True
.SelectTypeRow = 2 ' 1 - single, 2 - multi
End With
In order to select all highlighted rows into an array, use this code:
m_NumSelectedPlacementIds = 0
With dwGrid
' Multi select - Get in order as appear in grid
For r = 0 To .Rows - 1
bm = .AddItemBookmark(r)
For i = 0 To dwGrid.SelBookmarks.Count - 1
If CStr(bm) = CStr(.SelBookmarks(i)) Then
m_NumSelectedPlacementIds = m_NumSelectedPlacementIds + 1
ReDim Preserve m_SelectedPlacementIds _
(cint(m_NumSelectedPlacementIds))
m_SelectedPlacementIds(m_NumSelectedPlacementIds) = _
CLng(.Columns(0).cellValue(bm))
End If
Next
Next
End With
25. Launching Core Entity Forms From Script
If a grid is under script control and the user is permitted to drill down into the underlying record, these functions should be used :
If FormFunctions.isformloaded("frmPerson") Then
Set f = FormFunctions.GetAFormInstance("frmPerson", True)
Else
' Load new form then
Set f = FormFunctions.LoadFormByName("frmPerson", clng(ContactId), _
"Script Contact")
End If
f.LoadContactDetails clng(ContactId)
' Load entity form using same technique
If FormFunctions.isformloaded("frmContractorInvoice") Then
Set f = FormFunctions.GetAFormInstance("frmContractorInvoice")
Else
' Load new form then
Set f = FormFunctions. LoadFormByName ("frmContractorInvoice",1, _
"Script ContractorInvoice")
End If
f.LoadEntityDetails 1
Or for new entity forms
If FormFunctions.isformloaded("frmAdvertisement") Then
Set f = FormFunctions.GetAFormInstance("frmAdvertisement")
f.LoadEntityDetails 0
FormFunctions.AddFormToTabView (f), ""
Else
Set f = FormFunctions.LoadFormByName("frmAdvertisement", 0, "New Advert")
End If
26. Using FileSystemObject in VBScript
The file system object component (SCRRUN.DLL) needs to be installed and registered for it to be used in script.
This gives access to the FileSystemObject which exposes many file system features. Search MSDN to find complete list. (“Microsoft Scripting Run-time Library Features”)
This example demonstrates how to use the object from a Client Code VBScript function to get attributes from a folder and file and to copy a single file:
Private Sub CopyFileUsingFileSystemObject
Dim fso, fldr, f
Set fso = CreateObject("Scripting.FileSystemObject")
If fso Is Nothing Then
MessageBox.appstopmsg "Could not instantiate the Scripting.FileSystemObject"
Exit Sub
End If
Set fldr = fso.GetFolder("c:")
MessageBox.AppMsg "Folder name: " & fldr.Name
Set f = fso.GetFile("c:\Winnt\ClientCode32.ini")
MessageBox.AppMsg "Last modified: " & f.DateLastModified
' Copy file
fso.CopyFile "c:\winnt\ClientCode32.ini", "c:\temp\Copied.ini"
End Sub
27. Read all Grid Rows
If a grid is scripted, i.e. one that you have total control over?
If so it is known as an AddItem grid. Other grid population mechanisms are known as Bound (bound to a SQL recordset) and Unbound (grid calls you back for each row of data it requires). Standard Client Code grids usually use Unbound as they are more efficient.
Scripting currently cannot populate grids using any technique other than AddItem.
This example demonstrates reading a single column from all rows in the grid:
Dim strAdvertIdList, bm ' Bookmark
strAdvertIdList = ""
With dwGrid
For row = 1 To .Rows
bm = .AddItemBookmark(row - 1)
if len(strAdvertIdList) > 0 then strAdvertIdList = strAdvertIdList & ", "
strAdvertIdList = strAdvertIdList & .Columns(0).CellValue(bm)
Next
End With
28. External Document Launching
These functions are useful for launching the operating system features to open browsers, e-mail, auto-dial and documents:
' Fire E-Mail Client to send mail to recipient Miscellaneous.EMailHotLink(sEMailAddress as String)
' Fire Browser to view address
Miscellaneous.WorldWideWebHotLink(sWWWAddress as String)
' Play specified .wav file
Miscellaneous.PlaySound(sWaveFile as String)
' Dial specified number using TAPI
Miscellaneous.AutoDialNumber(sTelNumber as String)
' Fire Shell Association program
ShellExecute.OpenThisDoc(hWnd as long, sFilename as string)
In this last function, you will need to pass in the actual window handle.
Do this as follows:
' Check if file exists
if FileSystem.FileExists(sFilePathAndName) then
' Open the specified file.
' This function will open word for .DOC's, IE for .jpg, gif etc...,
‘ media player for .mpg etc...
ShellExecute.OpenThisDoc FormObjectsLocal.FormRef.hWnd, (sFilePathAndName)
end if
29. Global Parameter Value
Each script runs in its own address space for the current form.
The only ways to share variables between forms/scripts is to either communicate through the database (slow/inefficient) or via formobjectsglobal and access form widgets (cumbersome).
A neater solution is to have a shared collection of parameters which can be accessed from any script direct into/out of memory.
This is accomplished by a let/get property on the Initialisation object:
Public Property Let GlobalParameterValue(sParameterName as String, sValue as Variant)
Public Property Get GlobalParameterValue(sParameterName as String) as Variant
This is used in the following example:
' Assign a variable to the global parameters
Initialisation.GlobalParameterValue(“PlacementId”) = 100
…….
' Retrieve a variable from the global parameters from any form script
PlacementId = clng(Initialisation.GlobalParameterValue(“PlacementId”))
30. Launching E-Mail Client
In order to send an e-mail from script, an object reference to the Client CodeEMailGateway.dll must be instantiated. This provides a generic encapsulation of the various e-mail clients and an easy to use set of properties and methods which allow for e-mails to be sent to many recipients with attachments.
This example demonstrates the mechanism:
Sub SendEMail
Const RecipientTo = 1
Const RecipientCC = 2
Const RecipientBCC = 3
Const ProtocolMAPI = 1
Const ProtocolOutlook = 4
Const ProtocolMailTo = 5
' Get the e-mail address and attachment to send
sEMailAddress = FormObjectsLocal.ShowForm.GetFieldValue( _
"ReferenceConfigfields", "RefereeEmail")
sAttachment = FormObjectsLocal.ShowForm.GetFieldValue( _
"ReferenceConfigfields", "ReferenceRequest")
sCandidateName = FormObjectsLocal.ShowForm.GetFieldValue( _
"ReferenceRequest" , "CandidateId")
' Get the normal Client Code Email object references from Client CodeEMailGateway.dll
With EMailGateway
.NewMessage
.AddRecipient sEMailAddress, sCandidateName, RecipientTo
.Subject = "Reference request for " & sCandidateName
.Message = "Please find attached a request for a reference."
.AddAttachment sAttachment
.ShowDialogue = True
.Send(sErr)
End With
End Sub
31. Colouring Tabs
It may be necessary to colour the tabs on data entry forms for data validation or to draw attention to certain tabs or even to satisfy the artistic design tendencies of customers!
This is how to do this:
set tab = FormObjectsLocal.FormRef.tabby.Tab(1) ' First tab
with tab
' Set the tab back colour
.BackColor = vbRed ' Or any RGB colour
.BackColorSource = 1 ' Important - it can’t work this out on its own!
' Set the tab foreground colour - the text
.ForeColor = vbYellow ' Or any RGB colour
.ForeColorSource = 1 ' Important - it can’t work this out on its own!
end with
32. Crystal Report Form Script
When a report has been designed to have an associated data input form to capture parameters (See Reports Design Document and Tutorial), it is possible (build 5.00.081 or later) to manipulate the complete query before this is passed to the report for execution.
This allows the script developer to manipulate the query and add/remove portions of the SQL query depending upon user input. This example is taken from the report tutorial:
This example demonstrates the mechanism:
' This function is called after the user has requested the print of the report having filled in
' all on-screen values.
'
' This is the query from Client Code when invoked:
'
'Select
'*
'From
' ((Requirement Inner Join Contact Contact On
'Requirement.ContactId = Contact.ContactId)
'Inner Join Users On
'Requirement.UserId = Users.UserId)
'Inner Join Company On
'Requirement.CompanyId = Company.CompanyId
'
'Where 1=1
' And Requirement.RequirementId In
' (12,13,7,16,90,42,19,48,60,61,64,65,66,70,71,72,88,92,91,6,32,34,14,21)
' Or Requirement.RequirementId In ' (77,10,4,94,96,95,1,39,40,44,68,79,82,83,85,86,87,5,93,11)
'Order By Requirement.Reference ASC
Public Sub Form_AfterSaveRecord ( Id )
'MessageBox.appmsg "Pre Tweak: " & vbcrlf & ReportProperties.SQLQuery
' Tweak the formula now
ReportProperties.SQLQuery = RemoveRequirementIdInClause
' Get the input reference
sReference = FormObjectsLocal.ShowForm.GetFieldValue("RequirementsListConfigFields", "AField")
If len(sReference) > 0 Then
ReportProperties.SQLQuery = _
AddAndClause("And Requirement.Reference Like '%" & sReference & "%'")
End If
'MessageBox.appmsg "Post Tweak: " & vbcrlf & ReportProperties.SQLQuery
End Sub
Private Function RemoveRequirementIdInClause()
Dim x, y
Const AndRequirementIdIn = "And Requirement.RequirementId In ("
Const OrRequirementIdIn = "Or Requirement.RequirementId In ("
sFormula = ReportProperties.SQLQuery
Do While True
x = instr(sFormula, AndRequirementIdIn)
If x = 0 Then x = instr(sFormula, OrRequirementIdIn)
If x = 0 Then Exit Do
' Now find matching closing bracket
y = instr(x, sFormula, ")")
' Get pre and post string segments
sPre = Left(sFormula, x - 1)
sPost = Mid(sFormula, y+1)
' Assemble again
sFormula = sPre & sPost
Loop
RemoveRequirementIdInClause = sFormula
End Function
Private Function AddAndClause(sAndClause)
Dim sFormula
sFormula = ReportProperties.SQLQuery
' Find Order by
x = instr(sFormula, "Order By")
If x > 0 Then
sFormula = Mid(sFormula, 1, x-1) & " " & sAndClause & " " & Mid(sFormula, x)
End If
AddAndClause = sFormula
End Function
33. Automating Word
Script can access the same word processor classes as used by Client Code. This is exposed through the WordProcessor object as the following example demonstrates:
Private Sub WordMagic
With WordProcessor
.Initialise False
.OpenFile "D:\CVData\Upload\File1.doc", False, True
.AppendFile "D:\CVData\Upload\File2.doc", True
.SaveAs "D:\CVData\Upload\" & StringFunctions.VBFormat(Now, _
"YYYYMMMDDhhnnss") & ".rtf"
End With
End Sub
In the above example, File1 is opened in word and File2 is appended to it. This is then saved as an RTF file with the current date/time stamp.
Other useful functions for automating word are:
Public Sub CloseFile(sFileName as String) ‘ Close named file
Public Property Get Description() As String ‘ Get name of word processor version
Public Property Get GetFileContents() As String ‘ Get unformatted text from open document
Public Property Let SetFileContents(s As String) ‘ Set unformatted text in open document
Public Sub HighlightWord(sWord as String) ‘ Highlight word in open document
Public Sub MailMerge(sDataSource as String) ‘ Mail merge current document using data source
Public Sub NewFile() ‘ Open a new document
Private Sub ReplaceInSection(section As Object, sOldWord As String, sNewWord As String)
‘ Replace old word with new word in named section
34. Updateable Grid for Data Entry
This section demonstrates how to create an updateable grid and use it for data input purposes:
Create a new field of type Grid called: GridUpdate.
Create a button to retrieve the grid data with button function: GridUpdateButton.
Open script editor to write code to initialise the grid:
' Editable grid
Call InitialiseGridUpdate( _
FormObjectsLocal.ShowForm.GetFieldWidget("CompanyConfigFields", _
"GridUpdate"))
….
' The editable grid which can capture data input and validate it.
' Can also allow add new rows if necessary.
Private Sub InitialiseGridUpdate(dwGrid)
Dim i
If dwGrid Is Nothing Then
MessageBox.appmsg "GridUpdate Not found!"
Exit Sub
End If
' Initialise grid
With dwGrid
.Columns.RemoveAll
.DataMode = 2 ' Add Item Grid
.AllowAddNew = True
.AllowUpdate = True
.Caption = "Updateable Grid"
.MaxSelectedRows = 1
.RecordSelectors = True
.SelectTypeRow = 1 'Single Select
End With
' Add columns
With DataWidgets
.AddColumnToGrid (dwGrid), 0, "Forename(s)", 1500, True
.AddColumnToGrid (dwGrid), 1, "Surname", 1500, True
End With
' Add Rows
With dwGrid
.AddItem "©Garry©,©Lowther©"
.AddItem "©Matt©,©Jessop©"
End With
End Sub
‘ Capture button click
If btn.ButtonFunction = "GridUpdateButton" Then Call GridUpdateButton
….
' Called when the button is pressed to see the data
Private Sub GridUpdateButton
Set dwGrid = FormObjectsLocal.ShowForm.GetFieldWidget( _
"CompanyConfigFields", "GridUpdate")
If dwGrid Is Nothing Then Exit Sub
On Error Resume Next
With dwGrid
' Important to force save of existing updated data
.Update
For row = 1 To .Rows
bm = .AddItemBookmark(row - 1)
If row > 1 Then s = s & VbCrLf
For col = 0 To .Columns.Count - 1
s = s & " " & .Columns(col).cellValue(bm)
Next
Next
End With
MessageBox.appmsg "Grid contents: " & s & "?"
End Sub
Now run Client Code and open the form, the initial data should appear as follows:
You can then type into any row and update the text.
To add new rows, simply place the cursor into any cell in the row marked with an asterisk and all further rows can be added this way as shown below:
Pressing the Show Data button will show the input data.
In order to capture when user is inputting data, write code in the Grid_AfterUpdate event:
35. Programmed Fields Re-Appearing on Record Save
The problem with saving from tabs and causing fields under script control to show incorrectly can be a problem in certain circumstances.
The simple fix is to write code for the form after save record event which will force the first tab into focus, or if this is not acceptable, then the following script sample demonstrates how to cause the tabs to redraw the visible fields so that the fields do not show up:
' Called after record has been saved.
Public Sub Form_AfterSaveRecord ( Id )
' Force a refresh of the currently selected tab so that programmed fields do not
' show up on save when curren tab is not the first.
With FormObjectsLocal.FormRef
selectedIndex = .tabby.selectedtab.index
If selectedIndex <> 1 Then
.Tabby.Tabs(1).Selected = True
.Tabby.Tabs(selectedIndex).Selected = True
.tabby_click
End If
End With
End Sub
36. AddItem Grid Rows
Build 1.00.015 has easy to use functions to add fields and entire recordsets to a grid:
DataWidgets.AddItem (dwGrid As SSDBGrid, ParamArray FieldList())
DataWidgets.AddRecordsetRow (dwGrid As SSDBGrid, rs As Recordset)
DataWidgets.PopulateGridFromSQLQuery (dwGrid As SSDBGrid, sSQL as String)
DataWidgets.InitialiseAddItemGrid (dwGrid As SSDBGrid, sCaption as string)
This function is passed the handle to a grid and a list of values for each column:
‘ Initialise grid columns, SQL etc..
Set rs = Database.OpenRecordset( (sSQL) )
Do while not rs.EOF
DataWidgets.AddItem (dwGrid), rs(“ContactId”), rs(“FullName”)
rs.MoveNext
Loop
rs.Close
If the recordset contains all possible columns and you do not want to do any field manipulation (formatting, lookups), another function can be used which accepts a recordset:
‘ Initialise grid columns, SQL etc..
Set rs = Database.OpenRecordset( (sSQL) )
Do while not rs.EOF
DataWidgets.AddRecordsetRow (dwGrid), (rs)
rs.MoveNext
Loop
rs.Close
Both of these functions remove the need to manipulate strings and field delimiters in order to add a row to a grid.
The ultimate function is PopulateGridFromSQLQuery which requires only a reference to the grid and a SQL statement in order to fully populate itself. It even generates the columns in the grid from the query if none exist.
‘ Get the top 50 companies ordered by company name
sSQL = “Select Top 50 * From Company Order by Name”
DataWidgets.PopulateGridFromSQLQuery (dwGrid), (sSQL)
This one function call populated the entire grid from a SQL statement.
A full (very small) example of how to populate a grid created in Designer is:
Public Sub Form_AfterLoad()
Set dwGrid = FormObjectsLocal.ShowForm.GetFieldWidget( _
“EntityName”, “GridFieldName”)
if dwGrid is nothing then exit sub
With DataWidgets
.InitialiseAddItemGrid (dwGrid), “Top 50 Contacts”
.PopulateGridFromSQLQuery (dwGrid), “Select top 50 * From Contact”
End With
End Sub
37. Unbound Virtual Grid Population
Build 1.00.015 has easy to use functions to bind a SQL statement to a grid to provide:
• Virtual unbound recordsets – the data is demand paged from the server
• Sortable columns – standard column sorting (right mouse column header click)
• Automatic column generation – for prototyping purposes, all columns are generated
The techniques demonstrated here should be used for all grids which can be fully populated from a SQL statement.
The following new functions are now available:
DataWidgets.InitialiseUnboundGrid (dwGrid As SSDBGrid, sCaption as string)
DataWidgets.PopulateGridFromSQLQuery (dwGrid As SSDBGrid, sSQL as String)
Note: the word unbound derives from the 3 modes that the data grid can operate:
• Add Item – rows are added to the grid 1 at a time like a list box. This is used for small non-SQL oriented data sets.
• Bound – the grid is bound to a data control on a form. This is useful when developing prototypes but does not scale to production databases.
• Unbound – the grid is populated from a SQL recordset which is demand paged from the server.
A simple example demonstrates how to add an unbound grid to view all companies:
Public Sub Form_AfterLoad()
Set dwGrid = FormObjectsLocal.ShowForm.GetFieldWidget( _
“EntityName”, “GridFieldName”)
if dwGrid is nothing then exit sub
With DataWidgets
.InitialiseUnboundGrid (dwGrid), “All Companies”
.PopulateGridFromSQLQuery (dwGrid), “Select * From Company”
End With
End Sub
Not only can the grid now efficiently process very large recordsets, but it also automatically provides column sorting: by right mouse clicking on the column header.
Warning: Column sorting is very generic so you must obey an important rule when writing your SQL query. This rules relates to fields of type TEXT in SQL Server. TEXT fields cannot be sorted unless cast to a VARCHAR data type. For example the JobTitle column should be written as:
SELECT….. Convert(Varchar(64), JobTitle) as JobTitle …. FROM….
This will ensure that sorting the JobTitle column will be efficiently processed.
It is normal to add your own style to columns (width, type etc..) and this should be done using DataWidgets.AddColumnToGrid before calling DataWidgets.PopulateGridFromSQLQuery.
38. Refreshing Grids on Other Entity Forms
When a form wishes to fire a button on another entity form which can be virtual or not, perhaps to refresh a grid or view, then this example shows how to click the button programmatically from another form script:
Private Sub RefreshBidManagerGrid
Set frmBidManager = _
FormFunctions.GetAFormInstance( _
"frmFormDesignedByUser", True, "", "BidManager")
If frmBidManager Is Nothing Then exit sub ‘ Form not open
Set btn = frmBidManager.GetShowForm.GetButtonFromFunction( _
"btn", "RefreshBidStagesGrid")
If btn Is Nothing Then Exit Sub ‘ Button not found
btnIndex = btn.FormWidget.Index
frmBidManager.btn(btnIndex).Value = True
End Sub
If the form is a virtual entity, replace "frmFormDesignedByUser" with "frmVirtualFormDesignedByUser" in the GetAFormInstance function.
28 March 2002 Update:
It is now possible to use the frm{EntityName} in order to access forms which is more intuitive. The example below shows how this works:
If FormFunctions.isformloaded("frmAdvertisement") Then
Set f = FormFunctions.GetAFormInstance("frmAdvertisement")
f.LoadEntityDetails 0
FormFunctions.AddFormToTabView (f), ""
Else
Set f = FormFunctions.LoadFormByName("frmAdvertisement", 0, "New Advert")
End If
' Even works for Virtual Entity Forms
Private Sub OpenToDoList
If FormFunctions.isformloaded("frmToDoList") Then
Set f = FormFunctions.GetAFormInstance("frmToDoList")
FormFunctions.AddFormToTabView (f), ""
Else
Set f = FormFunctions.LoadFormByName("frmToDoList", 0, "My ToDo List")
End If
End Sub
39. Running Mail Merges From Script
The mail merging technology now includes a script friendly interface to generate mail merges without requiring any pre-processing of the data sources into the expected format.
A new object is exposed: MailMerge, which can be accessed from the script editor.
This object exposes a number of properties/methods:
Name Type Description
NewDocument Method Called to instantiate a new merge document
KeySQL Property The SQL which generates the ID’s of the keys
DataSourceSQL Property The SQL which generates the full data file
Template Property The word template .DOT file
AppendDocument Method Append a document to collection
SaveAs Property Save the merged document as this file name
UserId Property Set the user who is running the merge
RunMailMerge Method Run the merge and generate the document
Note that KeySQL and DataSourceSQL are mutually exclusive i.e. only 1 should be set at any 1 time.
The DataSourceSQL gives the programmer much more control over the data source production.
The use of this technique is now described by use of 2 examples:
Private Sub MailMergeTest
‘ Option 1 – Tight control using .DataSourceSQL
With MailMerge
.NewDocument
.DataSourceSQL = _
"Select ContactId, Christian + ' ' + Surname as ContactName" & _
" From Contact Where ContactId = " & _
FormObjectsLocal.FormRef.ContactIdCollection(1)
.Template = "C:\Program Files\ClientCode\DataSourceTest.dot"
.AppendDocument "C:\Program Files\ClientCode\DataSourceTest.dot"
.SaveAs "C:\temp\DataSourceTest.rtf"
.RunMailMerge
End With
‘ Option 2 – Use existing Universal Data Source.tmm schema
With MailMerge
.NewDocument
.KeySQL = "Select CompanyId, 26531 as ContactId, 99 as RequirementId, " & _
" 25 as ContractId, " & _
" 33518 as CandidateId, 4 as AdvertisementId, " & _
" 3 as ContractorInvoiceId, 1 as TestEntityId" & _
" From Company Where CompanyId = " & m_CompanyId
.Template = "\\ClientCode2000\ActionTemplates\client mailshot.dot"
.AppendDocument "D:\CVData\P\Synonyms Test.doc"
.SaveAs "\\ClientCode2000\ActionTemplates\Mailshots\MMTest.rtf"
.RunMailMerge
End With
End Sub
The .DataSourceSQL property accepts any SQL string returning any recordset from any tables in the database. The programmer determines all fields and records which are written to the data source. The programmer must therefore manage the Word templates and associated design-time TMM data source file. This is the property to use if very tight control of the merge process is required. Note this property is available only in build 1.00.117 or later.
The .KeySQL property accepts a SQL string returning only the keys of the underlying entities. In this example, we want to merge the document consisting of the current company (m_CompanyId), requirement 99, placement 25, candidate 33518, advertisement 4, contractor invoice 3 and test entity 1.
This allows you to control the relationships between the records.
The .Template property is the full path to the word .dot file.
.AppendDocument can be called any number of times to append any number of documents (CVs perhaps) to the end of the merge.
The .SaveAs method tells the merge that the resulting merged file will be saved to the specified file name.
The .RunMailMerge method starts the process of creating the data source (.tmm file), instantiating word, opening the .dot template and merging to a new document using the data source. If the .SaveAs file is set, it then saves the document. The resulting document is then made visible for viewing.
40. Diagnostics and User Activity Logging for Debugging and Audit Trails
For debugging, consider using the Diagnostics object for debugging as this logs to the diags file in real-time without intrusion and can capture hundreds of messages. Turn diagnostics on either via the Configure->Diagnostics menu option, or using the /Diag command line option.
To use in your code, just use the following syntax:
Diagnostics.Diag _
"Your message goes here... Concatenate variables, properties etc..."
The main advantage to this approach is that you can leave these in production systems and messages are only logged when diagnostics are enabled when attempting to debug a problem encountered on-site.
The .out file produced also contains version + anciliary information which can help diagnose problems.
Note, that when diagnostics are enabled, all MessageBox.AppMsg and AppStopMsg and QuestionYesNo etc.. are also logged to the .out file.
For logging user activity to SQL Server as an audit trail, use the following:
Initialisation.Users.DbInsertUserActivity (Initialisation.UserId), _
"The message to be logged", startDate, endDate
The startDate and endDate can be set to Now, but these can be used to capture the time taken for a particular operation.
All records are written to the UserActivity table only if the Configure->User Activity Logging menu option is set by administrator.
41. Closing Action Forms
It may be necessary to close an action form from script for example when a user is not permitted to run an action. In this case the following method can be called from the Form_AfterLoad event in order to stop the form from loading and displaying:
FormObjectsLocal.FormRef.FormUnload
Note: From 15 Jan 2002 this method is available to all config forms.
42. Post Code Lookup
Added post code database lookup capability to the system and exposed this as script. If the customer has licensed the post code address file (PAF) from QAS Systems, then the post codes will exist in the database.
The object PostCodeLookup is exposed through script and can be used as follows:
Public Sub Button_Click ()
Set btn = _
FormObjectsLocal.ShowForm.GetButtonFromIndex("btn", _
FormObjectsLocal.EventControl.Index)
If btn Is Nothing Then Exit Sub
On Error Resume Next
Select Case btn.ButtonFunction
Case "AlternativeAddressPostCodeLookup"
Call AlternativeAddressPostCodeLookup
End Select
End Sub
' Button lookup from alternative address fields.
Private Sub AlternativeAddressPostCodeLookup
sPostCode = FormObjectsLocal.ShowForm.GetFieldValue( _
"ContactConfigFields", "Alternative1PostCode")
If len(sPostCode) = 0 Then
MessageBox.VBMsgBox gVBmsgBoxSpaceOut ( _
"Please enter a post code before searching for a matching address.")
Exit Sub
End If
' Do the lookup now
With PostCodeLookup
.PostCode = sPostCode
If .Search Then
FormObjectsLocal.ShowForm.SetFieldValue("ContactConfigFields", _
"Alternative1StreetAddress") = .Street
FormObjectsLocal.ShowForm.SetFieldValue("ContactConfigFields", _
"Alternative1StreetSuburb") = .Suburb
FormObjectsLocal.ShowForm.SetFieldValue("ContactConfigFields", _
"Alternative1City") = .City
FormObjectsLocal.ShowForm.SetFieldValue("ContactConfigFields", _
"Alternative1County") = .County
FormObjectsLocal.ShowForm.SetFieldValue("ContactConfigFields", _
"Alternative1PostCode") = .PostCode
End If
End With
End Sub
Other properties of the PostCodeLookup object are as follows:
Enabled Read True of post code lookup enabled for site
PostCodeRadialSearching Read True if radial searching enabled for site
TopRecordCount Write Number of records to display in wildcard post code search
PopUpSelection Write Set to True if a wildcard pop-up selection dialogue is to be displayed.
43. Using Bookmarks in Grids
As a rule, it is not advisable to enumerate through the grid to access the rows and cells directly as this is inefficient. See the following section to see how to access the underlying unbound recordset.
In order to retrieve all rows in a non-additem grid, use the following code:
With dwGrid
.Redraw = False
.Movelast
.Movefirst
For i = 0 to .Rows - 1
.Bookmark = i
ContactId = .Columns(0).Text
If i = 0 Then
s = s & (ContactId)
Else
s = s & ", " & (ContactId)
End If
Next
.Redraw = True
End With
44. Accessing Grid SQL Query Recordset and Cloning
When a grid is populated from a single SQL statement using the function: DataWidgets.PopulateGridFromSQLQuery, the grid handling is performed automatically by the application.
There are occasions however, when you may require access to the underlying recordset in order to perform some extra processing (calculating totals etc..).
The following example shows how to populate a grid, retrieve the automatically generated underlying recordset, clone it and enumerate through the cloned recordset to access the values:
With DataWidgets
.InitialiseUnboundGrid (dwGrid), "First 1000 Rows of Table: " & sTableName
sSQL = "Select Top 1000 * From " & sTableName
' One stop shop - even easier than drinking beer!
.PopulateGridFromSQLQuery (dwGrid), (sSQL)
End With
' Test access to the recordset using cloning technique
s = ""
Set rs = DataWidgets.GetUnboundScriptedGridRecordsetFromHWnd(dwGrid.hWnd)
If Not rs Is Nothing Then
Set cloneRS = rs.Clone
With cloneRS
.MoveFirst
Do While Not .EOF
s = s & " " & .Fields(1).Value & .Fields(2).Value
.MoveNext
Loop
.Close
End With
End If
MessageBox.appmsg "Recordset contents=" & s
45. Unbound Grids - Formatting Columns
As regards formatting, there are 2 ways to achieve this: Using SQL (fastest and most efficient) and using Row Level Callbacks (slower, but more control)
SQL Method
Code the SQL Select with the convert function to get a UK display date format:
Select convert(varchar(11), StartDate, 113) as StartDate
The numbers might not be exactly what you want but this is what you do to get your formatting:
Select Convert(varchar(11), convert(decimal(10,5), numField)) as numField
This will give you a number with 5 decimal places. (Notice the double convert to get the value into text format because ADO will strip trailing zeros).
Row Level Callbacks
Use the Grid_RowLoaded event to capture the loaded row from the SQL statement and format the specified column explicitly. Example:
Public Sub Grid_RowLoaded
Set dwGrid = FormObjectsLocal.EventControl
If dwGrid Is Nothing Then Exit Sub
Set fd = _
FormObjectsLocal.ShowForm.GetFieldDescriptionFromWidgetHwnd(dwGrid.hWnd)
If fd Is Nothing Then Exit Sub
Select Case fd.TableFieldName
Case "DateViewGrid"
' Format the third column (0 base columns) which is a date into
' the format e.g. 19-Feb-2002
With dwGrid
If .Columns.Count > 2 Then
.Columns(2).Text = _
StringFunctions.VBFormat(.Columns(2).Text, "DD-MMM-YYYY")
End If
End With
End Select
End Sub
Warning: Failure to check the column counter before setting the column value will result in accessing a non-existent grid cell which will cause the grid to bring down the application!
46. Form_Unload – Preventing form from closing from Script
By using the Form_Unload (Cancel ) event, it is possible to prevent the user from closing the form without saving the changes controlled by script.
The application knows when a form is ‘dirty’ i.e. fields have been changed but not saved and it correctly prompts the user in these instances. The application however, knows nothing about the script behaviour and which fields it may control, so offers this event to allow script to prevent the form from closing in such circumstances.
Here is an example:
' Ask user to quit
Public Sub Form_Unload ( Cancel )
Cancel = _
(MessageBox.QuestionYesNo("Are you sure you want to quit?", _
"Form_Unload") <> vbYes)
FunctionStatus.SetParameter("Cancel") = Cancel
End Sub
In the above example, the event is called from the application when the user wants to close the form. The event simply asks the user for verification and returns the status to the application.
If the user answers Yes, the form will close, otherwise it will not.
47. Public Sub Form_ExternalRefresh – Catching events in script
When any data changes within the system, any script can catch all events associated with the change using the following event implemented in build 1.00.052.
Public Sub Form_ExternalRefresh ( FormName, Param1, Param2, Param3 )
The first parameter is the name of the form which is raising the event, and the following parameters will vary depending upon what type of update has occurred. In general Param1 will denote what has been updated, and Param2 will be the ID of the record.
The following example shows how to invoke the scheduled activity/note history form from script and capture the update event from it in script (in order to perhaps refresh a bespoke appointments grid).
' Load the last task created
MaxTaskId = Database.GetSnapshotField("Select max(TaskId) From Task", 0)
Set frmMainBinder = FormFunctions.GetAFormInstance("frmMainBinder", True)
Call frmMainBinder.EditTask((MaxTaskId))
Public Sub Form_ExternalRefresh ( FormName, Param1, Param2, Param3 )
If FormName = “frmMainBinder” and Param1 = “TaskId” then
‘ Refresh the bespoke appointments grid…
End if
End Sub
48. Spin Increments – Setting Visibility from Script
It is sometimes necessary to set the visibility of some fields by script.
The numerical data entry fields spin increment widgets: are separate from the actual field.
They have the same index as the field so it is possible to write a generic function as follows:
Public Function gSetSpinIncrementVisibility(sEntityName, sTableFieldName, bVisible)
With FormObjectsLocal.ShowForm
Set objField = .GetFieldWidget((sEntityName), (sTableFieldName))
If objField Is Nothing Then Exit Function
Set formField = .GetFormFieldFromFieldName((sEntityName), (sTableFieldName))
index = objField.Index
Set spin = FormObjectsLocal.formref.controls("spinIncrementValue")(index)
If .IsWidgetVisibleOnCurrentTab((objField.hwnd)) Then
spin.Visible = bVisible
End If
End With
End Function
This can be used as follows:
Public Sub Tab_Click ()
Call DoInvisibleFields
End Sub
Public Sub Form_AfterLoadRecord ( Id )
Call DoInvisibleFields
End Sub.
Public Sub DoInvisibleFields
Call SetFieldVisible("ContactConfigFields","Formula1",
bDoFormula)
Call SetFieldVisible("ContactConfigFields","Formula2",
bDoFormula)
'set spin increments invisible
Call gSetSpinIncrementVisibility("ContactConfigFields", " Formula1",
Not bDoFormula)
Call gSetSpinIncrementVisibility("ContactConfigFields", " Formula2",
Not bDoFormula)
End Sub
49. Report Formulas and Parameters
In build 1.00.062, it is now possible to pass formulas from script into crystal reports.
This has the major advantage that the report is more programmable from script and temporary tables do not need to be used to pass in non-derived data.
The example below shows how this can be achieved:
' Modify SQL here to remove context sensitivity
Public Sub Form_AfterSaveRecord ( Id )
ReportProperties.AddScriptFormula("StartDate") = "01-Jan-2003"
ReportProperties.AddScriptFormula("EndDate") = "31-Jan-2003"
End Sub
In Build 1.00.079 – it is now possible to control printing and formulas from a scriptable form without needing to have a report form. The following code shows how to do this:
sReportName = "ProjectIssuesSummary.rpt"
Set report = Initialisation.Reports.GetReportProperties((sReportName))
MySQL = "(Status = 'In Progress')"
report.AddScriptFormula("ReportParameters") = "Outstanding Issues ('In Progress')"
Call Initialisation.Reports.PrintReportFromReportPropertiesObject((report), (MySQL))
In build 1.00.111 – it is now possible to add report parameters (for stored procedures) using the same technique:
sReportName = "p_TestSPInCrystalReport.rpt"
Set objReport = Initialisation.Reports.GetReportProperties((sReportName))
objReport.AddScriptParameter("vchrSurname") = "A%"
objReport.AddScriptParameter("vchrUpdatedSince") = "01-Jan-2001"
Call Initialisation.Reports.PrintReportFromReportPropertiesObject((objReport), "")
In build 1.00.012 – the ability to control the report from script has been significantly enhanced to allow the developer to control the report much more tightly. 2 new methods and a new property have been exposed:
Method #1: OpenExportedReport – Specifies that the exported report is not to be launched (Word, Excel etc..)
Method #2: TabViewName – Specifies the name of the tab view (allows multiple views of same report)
Property #1: ExportedFileName – The full path of the file produced on export
The following code demonstrates how to use these:
Private Sub PrintReportForCompany(CompanyId, sCompanyName, sReportName, iReportDestination)
Set objReport = Initialisation.Reports.GetReportProperties((sReportName))
With objReport
.AddScriptParameter("vchrSurname") = Left(sCompanyName, 1) & "%"
.AddScriptParameter("vchrUpdatedSince") = "01-Jan-1900"
' The report destination – see this section
.Destination = iReportDestination
' We don't want the print engine to open the document after export
.OpenExportedReport = False
' We want unlimited report windows (tabs) when printing to screen
.TabViewName = "Rpt:" & GetTabNameFromCompany(sCompanyName)
End With
' Now launch the report
Call Initialisation.Reports.PrintReportFromReportPropertiesObject(
(objReport), "")
' Read the exported filename back now in order to rename/copy etc..
If len(objReport.ExportedFileName) > 0 Then
Call ProcessExportedReport(CompanyId, sCompanyName,
objReport.ExportedFileName)
End If
End Sub
Private Sub RunSalesInvoiceReportAndEMailAsAttachmentWithTimesheetImage
(sReportName, sGUID, lInvoiceId)
Const CrystalReportDestination_WP = 2
Const CrystalReportDestination_PDF = 5
Set objReport = Initialisation.Reports.GetReportProperties((sReportName))
With objReport
.Destination = CrystalReportDestination_WP
.AddScriptParameter("vchrUser") = CStr(Initialisation.UserId)
.AddScriptParameter("vchrReportName") = sReportName
.AddScriptParameter("vchrInvoiceString") = ""
.AddScriptParameter("vchrGuid") = CStr(sGUID)
.OpenExportedReport = False
.TabViewName = "Printing Invoice..."
End With
Call Initialisation.Reports.PrintReportFromReportPropertiesObject((objReport), "")
If Len(objReport.ExportedFileName) > 0 Then
sFileName = objReport.ExportedFileName
'MessageBox.AppMsg "Printed report: " & sFileName & "?"
' Copy this file to a unique location so that it can be referenced in
‘ a note/history against the invoice etc.. for tracking purposes
sDestinationFile =
FileSystem.DirWithSeparator(Initialisation.GetSystemSetting(
"ConsolidationInvoiceFolder")) & _
"Invoice" & lInvoiceId & "_" & Initialisation.User.LoginName & "_" & _
StringFunctions.VBFormat(Now, "DD-MMM-YYYY") & "_" & StringFunctions.VBFormat(Now, "hhnnss") & _
"." & FileSystem.SuffixFromFileName((sFileName))
Call gCopyFile(sFileName, sDestinationFile)
' Attach this e-mail as an attachment with timesheet image etc..
Call EMailInvoiceAttachmentsToPlacementRecipients(
sDestinationFile, lInvoiceId)
End If
End Sub
50. Form Timers
In build 1.00.066, it is now possible to raise a form timer event in scripted forms. The timer fires every minute starting 1 minute after the form is loaded. The benefit of this is so that forms can be used to refresh display statistics etc.. without requiring user intervention.
The following example code re-calculates the current gross domestic product of a nation every minute:
‘ This function fires every minute
Public Sub Form_Timer ()
Call CalculateGrossDomesticProductStatisticsEveryMinute
End Sub
51. Forcing ExternalRefresh events from Script
In build 1.00.069, it is now possible to raise a global external form refresh event in scripted forms. This event is normally called when any record is saved from a non-virtual form.
The script developer can now raise this event to all loaded forms to for example, refresh grids or special views of data.
‘ Code to Cause refresh event in interested parties e.g. Project - ExternalRefresh
FormFunctions.InvokeScriptedFormRefreshMethodForAllForms _
"frmFormDesignedByUser", "Entity Update", "ProjectIssueId", m_ProjectIssueId
See section 48 above for information about how to capture the event from script on any form and process the request.
In order to let all interested forms know that a task has been updated, use the following:
' Need to tell Task Grids what we have done
Call FormFunctions.FireTaskUpdateEventMessenger (TaskId)
52. Capturing Main Binder Toolbar Buttons
In build 1.00.072, it is now possible to capture the toolbar button clicks directly in script to take appropriate actions e.g. Print, Save, Search etc...
The Id parameter contains the ID of the toolbar button (ID_Print, ID_Save etc..) and the Cancel parameter can be set to True to stop ClientCode processing the button.
The following example shows how a new event is now available which is fired for every toolbar click when the form has focus:
' Catch users button press to do the printing
Public Sub Form_ToolbarButton ( Id, Cancel )
Select Case Id
Case "ID_Print": Call PrintCurrentGrid
' Ignore all others
End Select
' We will process the button thank you!!!
FunctionStatus.SetParameter("Cancel") = True
End Sub
53. Printing Contact Reports from Virtual Forms
In build 1.00.072, it is now possible to utilize the Crystal report functionality for printing contact reports directly from script on any form. This technique is probably best utilized on a virtual form where scripted grids are under control and the user is accessing the main print button on the toolbar.
The following example follows on from the previous section and shows how to print the contents of the current unbound scripted grid:
Private Sub PrintCurrentGrid
Set dwGrid = GetCurrentGridInView
If dwGrid Is Nothing Then
MessageBox.AppMsg "Please select a grid to print."
Exit Sub
End If
' Print the contact oriented report now
CrystalReports.PrintContacts FormObjectsLocal.FormRef, dwGrid
End Sub
Private Function GetFavouritesListGrid
Set GetFavouritesListGrid = FormObjectsLocal.ShowForm.GetFieldWidget(EntityName, "FavouritesListGrid")
End Function
Private Function GetSearchResultsListGrid
Set GetSearchResultsListGrid = FormObjectsLocal.ShowForm.GetFieldWidget(EntityName, "SearchResultsListGrid")
End Function
Private Function GetCurrentGridInView
Set dwGrid = GetSearchResultsListGrid
If Not FormObjectsLocal.ShowForm.IsWidgetVisibleOnCurrentTab((dwGrid.hwnd)) Then
Set dwGrid = GetFavouritesListGrid
If Not FormObjectsLocal.ShowForm.IsWidgetVisibleOnCurrentTab((dwGrid.hwnd)) Then Set dwGrid = Nothing
End If
Set GetCurrentGridInView = dwGrid
End Function
See section 50 for further details about invoking reports.
54. Access System Settings
An administrator can set system wide settings using the Configureïƒ System Settings menu:
To access these from script, use the Initialisation.GetSystemSetting(sName) function.
This is available from version 1.00.091.
Example:
sSystemSetting = Initialisation.GetSystemSetting ("YourSettingName")
In version 1.00.131, the programmer can now generate and update their own settings using the Initialisation.SetSystemSetting(sName, sValue) property:
Initialisation.SetSystemSetting("GoToAssistDefaultId") = lDefaultId
This function writes the new value directly to the database SystemSetting table.
55. Designer Queries in Script
From build 1.00.096, it is possible to use the designer to add queries to config and virtual forms.
This allows the queries to be accessible from script, therefore allowing a systems administrator to control the grid layout/fields etc.. without requiring script access – thus speeding up the customization process.
' Get the query for this view
Set gridQuery = _
FormObjectsLocal.FormRef.GridQuery.GetQueryFromCollection((sQueryName))
' if gridquery is nothing warn the user and get out
If gridQuery Is Nothing Then
MessageBox.Appmsg "gridQuery is nothing?"
Exit Sub
End If
' the SQL Query
sSQL = gridQuery.QueryProperties.SQLQuery
' define the sql to append to grid sql
sAppendSQL = "..... " & m_PlacementId
' prepare the SQL before populating the grid
sSQL = Replace(sSQL, "--PlaceHolderForAdditionalSQL", sAppendSQL)
Data Widgets Grid
' dont redraw now
dwGrid.Redraw = False
' populate the grid
With DataWidgets
.InitialiseUnboundGrid (dwGrid), "Rates"
Call FormObjectsLocal.FormRef.GridQuery.InitialiseGrid((dwGrid), (sQueryName))
' set the grid properties
With dwGrid
.AllowAddNew = False
.AllowUpdate = False
.MaxSelectedRows = 8192
.RecordSelectors = True
.SelectTypeRow = 1 '1 - single, 2 - multi, 3 - more control
End With
'MessageBox.AppMsg "SQL:" & sSQL
' populate the grid now
.PopulateGridFromSQLQuery (dwGrid), (sSQL)
End With
' redraw grid
dwGrid.Redraw = True
Alpha Grid
Set alphaGrid = _
FormObjectsLocal.ShowForm.GetFieldWidget(EntityName, "PlacementGrid")
If alphaGrid Is Nothing Then Exit Sub
With grdA
.ColumnPrefixToRemove = "Placement"
.EnableAutoEntityLoading "Placement", "Placement", "Placement_PlacementId"
' Apply the query to the grid.
.SetQuery Query.QueryProperties
End With
56. Setting the Report Destination at run-time from Script
From build 1.00.111, it is possible to allow the user to select the report destination at run-time:
This is the report form script:
' Report destinations
Const CrystalReportDestination_Screen = 0
Const CrystalReportDestination_Printer = 1
Const CrystalReportDestination_WP = 2
Const CrystalReportDestination_SpreadSheet = 3
Const CrystalReportDestination_HTML = 4
Const CrystalReportDestination_PDF = 5
Const EntityName = "BossDumpConfigFields"
Public Sub Form_AfterLoad ()
Call PopulateReportDestinationCombo
End Sub
Private Sub PopulateReportDestinationCombo
Set cmb = FormObjectsLocal.ShowForm.GetFieldWidget(EntityName, "ReportDestination")
With cmb
.AddItem "Screen"
.ItemData(.ListCount-1) = CrystalReportDestination_Screen
.AddItem "Printer"
.ItemData(.ListCount-1) = CrystalReportDestination_Printer
.AddItem "Word"
.ItemData(.ListCount-1) = CrystalReportDestination_WP
.AddItem "Excel"
.ItemData(.ListCount-1) = CrystalReportDestination_SpreadSheet
.AddItem "Web Page"
.ItemData(.ListCount-1) = CrystalReportDestination_HTML
.AddItem "PDF"
.ItemData(.ListCount-1) = CrystalReportDestination_PDF
.ListIndex = 0
End With
End Sub
' Called before report is fired
Public Sub Form_BeforeSaveRecord ( Id, Cancel )
' Point at user specified destination
Set cmb = FormObjectsLocal.ShowForm.GetFieldWidget(EntityName, "ReportDestination")
ReportProperties.Destination = cmb.ItemData(cmb.ListIndex)
End Sub
57. Intercepting Report Selection Events from Script
From build 1.00.114, it is possible to capture the list of reports selected at run-time from the Print toolbar button. This allows the developer to orchestrate the printing of reports entirely from script.
The following example shows code which is available in the frmCompanies form to capture a new event called Form_BeforePrint which is called after the report selection dialogue is displayed, but before each report is printed by the Crystal sub-system.
‘ frmCompanies Form Script
Public Sub Form_BeforePrint(reportCollection, Cancel)
If reportCollection Is Nothing Then Exit Sub
If reportCollection.Count = 0 Then Exit Sub
' Tell ClientCode that we will process the report selection from here
FunctionStatus.SetParameter("Cancel") = True
s = "Report Collection: "
For i = 1 to reportCollection.Count
s = s & vbcrlf & reportCollection(i)
Next
' Test access to the recordset using cloning technique
s = s & vbcrlf & vbcrlf & "Recordset contents:" & vbcrlf
Set rs = FormObjectsLocal.FormRef.GetRecordsetForSelectedTab
If Not rs Is Nothing Then
Set cloneRS = rs.Clone
With cloneRS
.MoveFirst
Do While Not .EOF
s = s & " " & .Fields(1).Value & .Fields(2).Value
.MoveNext
Loop
.Close
End With
End If
MessageBox.AppMsg (s)
End Sub
Thus the sequence of events is as follows:
ïƒ ïƒ
This is the print button on the main toolbar when the companies form is displayed.
Then the company reports selection dialogue pops up.
Then the event is raised in script code, which processes the list and cancels the default behaviour.
58. Grid Column Filtering/Sorting for AddItem Grids
From build 1.00.116, it is now possible to sort and filter AddItem grids in the same manner as Unbound grids providing some rules are followed.
Have now enhanced ClientCode to support grid sorting and filtering for AddItem grids used with the DataWidgets.PopulateGridFromSQLQuery method.
The latest release is at: www.clientcode.com
Grid sorting works by default, however in order to ensure that filtering works, you need to provide explicit Select column names and some comment fields and an Order By clause in the SQL as shown in the slightly modified code below.
Let me know if this works on your timesheetHR system?
Garry
Form: Consolidation
Function: ShowSelectedTimeSheets
...
'DWA 9 Jan 2004
' 10 Jan 2004: From GL to DWA: Need to Select SPECIFIC column names in order to allow FILTERING
sSQL = "SELECT TimeSheetID, PlacementID, SupplierCompany, JobTitle " & _
", Company, Candidate, ContratorType, StartDate, EndDate, ProcessDate" & _
", NetPay, PayVAT, GrossPay, NettCharge, VATCharge, GrossCharge, Freq, Pending" & _
", SalesInvoiceProduced, PurchaseInvoiceProduced" & _
" FROM v_Consolidation_SelectedTimeSheetList_v3 " & _
" WHERE Company = '" & strClient & "'" & _
vbcrlf & "-- Grid Filter Start --" & vbcrlf & "-- Grid Filter End --" & vbcrlf & _
" Order By TimeSheetID"
...
You may ask the question: ‘Why not just use an Unbound grid instead of an AddItem if using a full SQL statement?’
The answer is because Unbound grids are NOT editable (I don’t believe) as they are populated from a recordset, thus it is not possible to add CHECK BOX columns and have the user select rows. This IS however possible with an AddItem grid.
Here is an example which uses the AddItem technique but adds a check box column:
sSQL = “as above code…”
' Initialise grid columns
With dwGrid
.Columns.RemoveAll
.DataMode = 2 ' Add Item Grid
.AllowAddNew = False
.AllowUpdate = True
.MaxSelectedRows = 8192
.RecordSelectors = True
.SelectTypeRow = 3 '1 - single, 2 - multi, 3 - more control
End With
With DataWidgets
…
.AddColumnToGrid (dwGrid), 22, "Select", 600, True ‘ The check box one
end with
' Select column is an updateable check box
With dwGrid
For x = 17 To 22
With .Columns(x)
.DataType = 11
.Style = 2
End With
Next
End With
' Lock these cells as non-updateable
With dwGrid
For i = 0 To 19
.Columns(i).Locked = True
Next
.Columns(21).Locked = True
.Columns(10).ForeColor = vbBlue
.Columns(11).ForeColor = vbBlue
.Columns(13).ForeColor = vbBlue
.Columns(14).ForeColor = vbRed
.Columns(15).ForeColor = vbRed
.Columns(16).ForeColor = vbRed
.Columns(20).Locked = True
.Columns(22).BackColor = vbGreen '&hffff00 '&H00FFFFC0&
.Caption = "Pending && Selected TimeSheets - " & strClient
End With
DataWidgets.PopulateGridFromSQLQuery (dwGrid), (sSQL)
dwGrid.Redraw = True
Then in button_Click event handling:
With dwGrid
For row = 0 to .Rows-1
.Bookmark = row
If (cbool(.Columns("Sales").Value) = False) _
and (cbool(.Columns("Select").Value) = True) Then
' Build comma-delimited list of selected non-SelfBill TimesheetIds
…
End If
Next
End With
59. Using CommonDialogue Friendly Function to Find Files
The FileSystem.CommonDialogue method is not script friendly (passes parameters byref) so a wrapper function: FileSystem.FileDialogue has been developed to provide the same functionality for pointing at files on the file system.
Public Sub Button_Click ()
' Not interested in non-config buttons
If FormObjectsLocal.EventControl.Name <> "btn" Then Exit Sub
Set btn = FormObjectsLocal.ShowForm.GetButtonFromIndex("btn", _
FormObjectsLocal.EventControl.Index)
If btn Is Nothing Then Exit Sub
Select Case btn.ButtonFunction
Case "ImportHTML": Call ImportHTMLFile
End Select
End Sub
Private Sub ImportHTMLFile
sFile = FileSystem.FileDialogue("Find HTML Page", (sFile), "*.*", _
"All Files (*.*)|*.*", 1)
MessageBox.appmsg "File=" & sFile
End Sub
Use 1 for File Find and 2 for new file.
60. Form_BeforeDeleteRecord Event
Build 1.00.122 introduced a programmable event to allow developers to control the end-user deletion of key records.
Public Sub Form_BeforeDeleteRecord(Id, Cancel)
The Id is the current record to be deleted.
Cancel is a flag which the programmer can set to False to prevent the application deleting the record.
' Catch deletion event and prevent if e.g. timesheets have allocated hours etc..
Public Sub Form_BeforeDeleteRecord (Id, Cancel)
MessageBox.AppMsg "In script: " & FormObjectsLocal.FormRef.EntityName & " - Id:" & Id
FunctionStatus.SetParameter("Cancel") = True
End Sub
Currently this event is only available in frmEntitySearch, frmCompanies and frmContacts.
61. Form_CoreEntityLookupEvent
Build 1.00.124 introduced a programmable event to allow developers to fully control the end-user selection of core entity lookup records. These are the buttons on the fields of type Contact, Company, User or Currency. This event ALSO applies to the Re-assign Company pop-up menu on the contact form.
Public Sub Form_CoreEntityLookup (TableName, TableFieldName, RecordId, RecordText, Cancel)
The TableName and TableFieldName correspond to the FieldDescription record of the field in question. The RecordId is the current Id of the core entity (and the .Tag value of the associated text box) and is used to return the new value to the caller. The RecordText is the text currently displayed in the text box and is used to return the new value to the caller. The Cancel flag if set to True, forces the application to abandon its own processing of the button.
The event is fired when a user selects the spyglass button within the text box containing the field:
and also when the user selects the re-assign Company pop-up menu from the contact forms, Re-assign Company Name & Address button:
The event fires BEFORE the user is prompted to select a lookup so that script can COMPLETELY override the behaviour of the pop-up record selection.
This provides the ability for the script programmer to decide what SQL to generate, what columns to show and also what business rules to enforce in limiting the end-user to fit the current context.
The following example code shows how this event is utilized in a form for which a candidate (not a client, supplier, doctor, lawyer etc..) must be selected in the above field:
Public Sub Form_CoreEntityLookup (TableName, TableFieldName, RecordId, _
RecordText, Cancel)
Select Case TableFieldName
Case "CandidateReference"
Call CandidateLookup(TableFieldName, RecordId, RecordText)
Case Else
Exit Sub ' Let application handle
End Select
' Set the function status now
With FunctionStatus
.SetParameter("RecordId") = RecordId
.SetParameter("RecordText") = RecordText
.SetParameter("Cancel") = True
End With
End Sub
' Do a contact lookup using script functions
Private Sub CandidateLookup(TableFieldName, RecordId, RecordText)
Set SQLSelection = Selection.GetSelectSQLObject
With SQLSelection
.Columns = 9
.ColWidth(0) = 0
.ColWidth(1) = 0
.ColWidth(2) = 0
.ColWidth(3) = 1500 ' Forenames
.ColWidth(4) = 1800 ' Surname
.ColWidth(5) = 3000 ' Address
.ColWidth(6) = 1200 ' Home Tel No
.ColWidth(7) = 1200 ' Work Tel
.ColWidth(8) = 1200 ' Mobile Tel
.Caption = "Select any Candidate"
.SQL = "Select ContactId, Priority, ContactType As Type, " & _
"Christian As 'Forename(s)', Surname," & _
" TheAddress as 'Address', HomeTelNo as 'Home Tel No.', WorkTelNo as 'Work Tel No.', MobileTelNo as 'Mobile Tel No.'" & _
" FROM v_AllContacts" & _
" Where ContactType = 3 " & _
" Order by Surname, Christian"
.ContactSelection = True
.PriorityColumn = 1
.TypeColumn = 2
If Not .Selection Then Exit Sub
RecordId = .SelectedRecord(1)
End With
Set Contact = Initialisation.Contacts.FindFirst((RecordId))
RecordText = Contact.Christian & " " & Contact.Surname
End Sub
The following example is how to utilise the code in frmPerson – The contact form for managing the company re-assignment:
' Called whenever a core lookup button invoked - this includes the assign company menu
Public Sub Form_CoreEntityLookup(TableName, TableFieldName, RecordId, RecordText, Cancel)
' Display the message box for debug
'MessageBox.AppMsg "Form_CoreEntityLookup: " & TableName & ", " & TableFieldName & _
' ", " & RecordId & ", " & RecordText & ", " & Cancel
' Now get the list of contacts for this type of lookup
Select Case TableFieldName
Case "CompanyName"
Call CompanyLookup("Client", RecordId)
Case Else
Exit Sub ' Let application handle
End Select
' Set the function status now
With FunctionStatus
.SetParameter("RecordId") = RecordId
.SetParameter("RecordText") = RecordText
.SetParameter("Cancel") = True
End With
End Sub
' Do a company lookup using script functions
Private Sub CompanyLookup(CompanyType, CompanyId)
Set SQLSelection = Selection.GetSelectSQLObject
With SQLSelection
.Columns = 2
.ColWidth(0) = 0
.ColWidth(1) = 5000 ' Company
.Caption = "Select a " & CompanyType & " Company"
.SQL = "select Company.CompanyId, Company.Name as 'Company Name'" & _
" From Company, CompanyType, EntityType" & _
" Where Company.CompanyId = CompanyType.CompanyId" & _
" and CompanyType.EntityTypeId = EntityType.EntityTypeId" & _
" and EntityType.EntityName = 'Company'" & _
" and EntityType.Name = '" & CompanyType & "'" & _
" Order by Company.Name"
If Not .Selection Then Exit Sub
CompanyId = .SelectedRecord(1)
End With
End Sub
62. frmEntitySearch Buttons
In order to control the use of buttons on the entity search form (frmEntitySearch), the following script can be used to (for example) hide the New button:
These are the VB constants used:
Private Const BTN_COMPANY = 0
Private Const BTN_CONTACT = 1
Private Const BTN_SEARCH = 5
Private Const BTN_PRINT = 6
Private Const BTN_CLOSE = 7
Private Const BTN_UPDATE = 8
Private Const BTN_NEW = 10
so to disable the New button, I'd use the following script
Public Sub Form_AfterLoad ()
' May need to identify which entity search is operational
if FormObjectsLocal.FormRef.EntityName = "SalesInvoice" Then
FormObjectsLocal.FormRef.btn(10).Visible = False
End If
End Sub
63. Turning Off Virtual Form Menu
When a virtual form is created, the form menu is normally of no use.
This menu for example has no use and uses up valuable screen space:
Use this code in the form script to turn off the form menu:
Public Sub Form_AfterLoad ()
Set ctl = FormObjectsLocal.FormRef.ctlFormCaptionController
If Not ctl Is Nothing Then ctl.ToolbarVisible = False
End Sub
64. Dynamic Button Pop-Up Menus
Build 1.01.011 has new script enabled code to allow developers to dynamically create pop-up menus which are displayed on a button click.
This allows programmers to provide buttons with groups of functions.
The approach is to allow the programmer to show a menu on say a button click, then respond to the Menu_Click event when the user fires the menu (which may not be immediately, so we cannot block).
This is what the user interaction looks like using the code that follows:
Private Const DeleteSelectedEMailOnly = "Delete selected e-mail only"
Private Const DeleteAllEMails = "Delete all e-mails"
Public Sub Button_Click ()
…
‘ Fire the pop-up menu now
Call FormFunctions.ShowPopUpMenu(FormObjectsLocal.FormRef, _
DeleteSelectedEMailOnly, DeleteAllEMails)
End sub
' Get the menu callback from those menus we have fired dynamically
Public Sub Menu_Click (Caption, Index, Cancel)
Select Case Caption
Case DeleteSelectedEMailOnly: Call DeleteEMail(True, False, True)
Case DeleteAllEMails: Call DeleteEMail(False, True, True)
End Select
End Sub
As you can see, the button click fires our code to display a pop-up menu, and when the user selects menu option, the Menu_Click event fires and we can determine which option was selected.
65. Hiding Tabs in Entity Search and Virtual Forms
Build 1.01.012 has slightly enhanced code to allow the script developer to turn off the tabs where not required (in the case of a design query overriding the default search).
This is done by coding the frmEntitySearch form script as follows:
Public Sub Form_AfterLoad ()
Select Case FormObjectsLocal.FormRef.EntityName
Case "SalesInvoice"
FormObjectsLocal.FormRef.tabby.TabStyle = 3 ' Turn off all tabs
End Select
End Sub
This will cause the form resize event to ignore the tabs and therefore draw a large grid and appropriately positioned buttons:
To remove the tabs in a virtual form, you can employ exactly the same technique.
An alternative, which is NOT recommended is to use the following code to make the one and only tab invisible:
Public Sub Form_AfterLoad ()
FormObjectsLocal.FormRef.tabby.Visible = False
End Sub
You should of course position your grid in the Designer to be higher than originally designed (same top co-ordinate at the tab).
66. Preventing Entity Search based on Form Permission
Build 1.01.014 provides a new FormUnload method on the frmEntitySearch form to allow the developer to prevent users from loading entity searches if they are not permissioned for access to any version of an entity form.
This is done by coding the frmEntitySearch form script as follows:
Public Sub Form_AfterLoad ()
If Not gIsUserPermissionedForEntityForm( _
FormObjectsLocal.FormRef.EntityName) Then
MessageBox.AppMsg "You are not permissioned to open this form."
FormObjectsLocal.FormRef.FormUnload ' 1.01.014 or later
Exit Sub
End If
End Sub
The global function used is as follows:
' Return True if the user is permissioned to view a form for the specified entity
Public Function gIsUserPermissionedForEntityForm(sEntityName)
MySQL = "Select Count(*) from formversionusers fvu, formproperties fp where fp.formid = fvu.formid" & _
" And fvu.UserId = " & Initialisation.UserId & " And fp.EntityName = '" & sEntityName & "'"
gIsUserPermissionedForEntityForm = _
(cint(Database.GetSnapshotField((MySQL), 0)) > 0)
End Function
67. Setting visibility of btnInternet on Internet Field Types
Similar to the Spin increment shown in section 48, it is necessary to se the visibility of the globe icon (btnInternet) used on internet fields, this can be done using the function below, again the globe has the same Index as the field so you can use pretty much the same function as the one in section 48
It is sometimes necessary to set the visibility of some fields by script.
Public Function gSetBtnInternetVisibility(sEntityName, sTableFieldName, bVisible)
With FormObjectsLocal.ShowForm
Set objField = .GetFieldWidget((sEntityName), (sTableFieldName))
If objField Is Nothing Then Exit Function
Set formField = .GetFormFieldFromFieldName((sEntityName), (sTableFieldName))
index = objField.Index
Set globe = FormObjectsLocal.formref.controls("btnInternet")(index)
If .IsWidgetVisibleOnCurrentTab((objField.hwnd)) Then
globe.Visible = bVisible
End If
End With
End Function
Example:Call gSetBtnInternetVisibility("RequirementConfigFields", "ApplicationURL", True)
68. Setting Script Timeout Globally or for Each Form
In build 2.00 onwards, it is possible to set the script timeout for each form in order to control the timing of the script engine.
This is done by the exposure of the ScriptEngines object which is a collection of all script engines within the system. The ScriptEngines object exposes a property and an object of interest to scripts:
ScriptEngines.ScriptTimeoutMilliseconds
ScriptEngines.GetScriptEngine(hWnd as Long)
The following example code shows how to set the global and local script timeouts:
Public Sub Form_AfterLoad ()
' Set script timeout for all subsequently instantiated script engines
ScriptEngines.ScriptTimeoutMilliseconds = 60000
' Set script timeout in milliseconds for this form only
Set objEngine = _
ScriptEngines.GetScriptEngine((FormObjectsLocal.FormRef.hWnd))
If Not objEngine Is Nothing Then
objEngine.ScriptTimeoutMilliseconds = 10000
End If
End Sub
69. Using the GridAlpha from script
When scripting populating a grid alpha from script is is very strongly recommended that you base it upon a query. This has the following advantages:
1) Simplified Code.
2) User customization to the grid is saved and restored automatically.
3) Simplified SQL.
4) Changes to the grid and its functionality can be carried out by a non scripter.
To set up a grid in this manner carry out the following steps:
1) Create a query, associated with the form in question (Note: the query should follow all of the best practices defined in the Grid Queries Design Document.doc).
2) Set the options on the query to enable/disable the relevant functions on the grid (EG: Allow Printing.)
3) Add the following code to script to initialise the grid (Note: This code only needs to be executed ONCE in the forms lifetime. NOT every time the grid is filled or the record is changed) :
With FormObjectsLocal
‘ Get a reference to the query and the grid.
Set Grid = .ShowForm.GetFieldWidget(“ContactConfigFields”,”QuotesGrid”)
Set Query = .FormRef.GridQuery.GetQueryFromCollection(("ContactQuotes"))
End with
‘ Check that we have found both objects.
If Grid Is Nothing Or Query Is Nothing Then Exit Sub
‘ This is the line that does the work, the grid will load all of its properties from the grid.
Grid.SetQuery Query.QueryProperties, False
The SetQuery method takes 2 arguments, the first is the query properties object, and the second is a Boolean specifying whether the grid should immediately fill the grid using the queries SQL. Pass it true if the grid query does not require any contextual information (EG: the current record being viewed.)
Assuming that you do need to filter the records in the query then you will need to actually fill the grid, telling it how to filter the grid.
‘ Get a reference to the grid.
Set Grid = FormObjectsLocal.ShowForm.GetFieldWidget(“ContactConfigFields”,”QuotesGrid”)
‘ This is the line that fills the grid. The only argument is an optional filter clause. This will be appended to the Queries SQL.
Grid.FillGridFromQuery "ContactId = " & m_ContactId
You would call this whenever the contact Id changed, or when you wanted the grid to refresh.
As you can see the above code is far simpler than working with prior grids, defining the columns etc in script. All of this information will be loaded from the query, and if the user changes any of it (Moves a column for example), it will be persisted and restored without the scripter having to do any work.
In addition, non scripters can go in and alter the grid layout etc without having to mess around with code.
In situations where the grid can not be based upon a query (I struggle to think of any such situation at present), it can be set up manually as follows:
With Grid
‘ Adds a column to the grid based upon a field in the results:
‘ Arguments: Caption, Field Name, Width, Visible.
.AddColumnWithUnderlyingFieldName “ContactId”, “ContactId”, 1000 , False
.AddColumnWithUnderlyingFieldName “Forename(s)”, “Christian”, 1000 , True
.AddColumnWithUnderlyingFieldName “Surname”, “Surname”, 1000 , True
' Fill the grid.
.FillGridFromSQL sSQL
' Group by the Surname column.
.FieldGrouped("Surname") = True
End With
Both of the above situations require the grid to be populated using SQL, however it also supports AddItem type population. This can be achived as follows:
‘ The Add Item grid works with ADO data types (Used for formatting and sorting).
Const adVarChar = 200
Const adDBDate = 133
Const adInteger = 3
With Grid
‘ Adds a column to the grid based upon a field in the results:
‘ Arguments: Caption, Width, Visible, DataType (Used for formatting and sorting).
.AddColumnWithDataType “ContactId”, 1000 , False, adInteger
.AddColumnWithDataType “Forename(s)”, 1000 , True, adVarChar
.AddColumnWithDataType “D O B”,1000 , True, adDBDate
sCDL = .DefaultAddItemColumnDelimiter
sRDL = .DefaultAddItemRowDelimiter
‘ Construct an Add Item string
‘(Note you can do this a line at a time, or put all lines into a string and then
‘ add them to the grid in one go as shown here)
sAddItemsString = “1” & sCDL & “Dhugal” & sCDL & “01/06/1977” & sRDL & _
“2” & sCDL & “Garry” & sCDL & “01/06/1917”
‘ Add the items to the grid.
.AddItems sAddItemsString
End With
Grouping within the grid can be achived as follows:
' Group by the Surname column.
Grid.FieldGrouped("Surname") = True
70. Setting Field, Button & Tab Visibility from Script
In build 2.00.070 onwards, it is possible to program field, button and tab visibility very easily…
Field Visibility:
FormObjectsLocal.ShowForm.ShowFieldFromName(EntityNameCF, "EarliestStartDate") _
= True
This deals with ALL related widgets such as labels, lookup buttons, spin buttons etc..
Button Visibility:
FormObjectsLocal.ShowForm.ShowButtonFromFunction(“Fire Interactive Video”) = False
Tab Visibility:
FormObjectsLocal.ShowForm.ShowTabFromIndex(1) = True
This deals with cases when tab to be hidden is selected.
Tab Selection:
FormObjectsLocal.ShowForm.SelectTabFromIndex(2) = True
This ignores cases when tab to be selected is not visible.
Appendix A - Type Mismatch Errors
This excerpt from MSDN explains the reason why some functions in Client Code will not be allowed to be called with BYREF parameters because of limitations in the VBScript (or JavaScript) language.
INFO: Type Mismatch Errors When You Pass Parameters from ASP to a Visual Basic Component
________________________________________
The information in this article applies to:
• Active Server Pages
• Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, versions 5.0, 6.0
________________________________________
SUMMARY
The code sample below illustrates various scenarios that causes
Type Mismatch
errors with method calls from an Active Server Page (ASP) using Visual Basic Script to Visual Basic COM components.
'VB Code : [Project=prjParam;Class=clsParam]
'By default, the parameter is passed 'ByRef'
Sub x( a as string )
a = "Changed"
End Sub
'ASP Code
k = "Hello"
'Create the above VB object
Set obj = Server.CreateObject("prjParam.clsParam")
obj.x k 'Type Mismatch error occurs
obj.x (k) 'Using PARANTHESIS forces 'ByVal' , 'k' does not change
Call obj.x (k) 'Type Mismatch error occurs
Call obj.x cstr(k) 'The CSTR function returns a string,
'the address of the variable (k) is not passed.
'The value of 'k' doesn't change
Set obj = Nothing
The following is another example that can cause the error:
'VB Code : [Project=prjParam;Class=clsParam]
'If you do not specify, by default the parameter is passed 'ByRef'
'Note: Parameter type is VARIANT
Sub y( a as variant )
a = "Changed"
End Sub
'ASP Code
k = "Hello"
'Create the above VB object
Set obj = Server.CreateObject("prjParam.clsParam")
obj.y k 'changes 'k'
obj.y (k) 'Using PARANTHESIS forces 'ByVal' , 'k' doesn’t change
Call obj.y (k) 'changes 'k'
Set obj = Nothing
MORE INFORMATION
VBScript only supports VARIANT ByRef parameters. You can use VBScript to call a procedure that takes ByRef strings, but the default behavior of components built with Visual Basic is to fail with a type mismatch error when trying to pass ByRef parameters to these components. OLE Automation's default type-coercion function fails when asked to convert a ByRef variant into any other ByRef type.
VBScript does not impose this restriction. However, it is the default behavior of the component that decides that a ByRef variant cannot be converted into a ByRef string.
If a parameter to a procedure is enclosed in parenthesis, the parameter is first evaluated as an expression. Because the result of an expression cannot be passed by reference, the parameter is passed ByVal and no error is reported.
Avoid using ByRef parameters unless they are explicitly needed. This is because:
• ByRef procedures cause more overhead during cross-process marshaling, because COM must marshal the value both to and from the object. ByVal parameters require only one-way marshaling.
However, if you are willing to accept the overhead of any marshaling at all, an extra parameter marshal back is unlikely to cause a huge performance degradation.
• ByRef parameters can introduce hard-to-find bugs in your code if you accidentally change the value of one of the parameters.
• JScript does not support ByRef parameters of any type, so if you plan to write components that will support JScript, you should not use ByRef parameters at all.
