AExtensions to the field information are only fully active for normal OPL databases, although you can temporarily change the field widths, decimal precision, and Field Label Aliases for standard Data files as well.
When you make changes to the field properties, a file is created within your database called zPsiDatFieldInfo. This is opened automatically to enhance the field display and properties when you reopen the database tables that use it.|Ai
ReadMe
Title
Details
Jotter
Title
Created
"Modified
Notes
"Projects
"Contract
Project
Contact
&Telephone
Notes
Tasks
"Contract
Started
DueBy
&Completed
Notes
BzPsiDatFieldInfo
Table
FieldNo
Alias
"Position
Length
"Decimals
&ExtraType
Special
Timings
.Description
Time
AText fields can be enhanced by :-
1. Adding a field label alias to replace the fieldname in the display.
2. Add a 'Lookup'
3. Add a 'Help' (lookup)
Options 2 &
3 require the 'Ref Table' to contain the name of a valid table within the database. This must include at least one Text field itself.
'Lookup' text is enforced - once enabled you will not be able to enter text in the field unless it matches a string in the lookup table.
'Help' text is optional - you can manually enter other text.
BFloating point fields can be enhanced by :-
1. Adding a field label alias to replace the fieldname in the display.
2. Specifying a display format (ie. length and number of decimals).
If you specify a number outside the range that can be displayed it will revert to a general type of the same overall length, if possible.
The 'Specify Format' option must be set to 'Yes' for the values to be used, otherwise they are both set to zero.
When you import DBF files, the field size information is retained so that it can be duplicated when exporting back to DBF
format.
3. The field can be defined as a 'Stopwatch'. When tapped, a stopwatch field will wait for a key to be pressed to start and stop the timer. You then have the option of saving or ignoring the value.
Extended Field Type Information
Text Field Extensions
Date Field Extensions
Date/Time fields can be enhanced by :-
1. Adding a field label alias to replace the fieldname in the display.
2. Changing the display format from 'Date/Time' to 'Date' or 'Time'
Floating Point Extensions
Other ExtensionszOther fields can be enhanced by :-
1. Adding a field label alias to replace the fieldname in the display.
(more to come)f
6HJJ..&JPPRD2>6F
Anytype 56K Modem
Null Modem Adapter (9 Pin)fffff
Null Modem Adapter (25 Pin)
1.4MB Floppy Disks (Box 10)
CD-R (Box 10){
CD-RW (Box 5)fffff
Mouse Mat
Parallel Printer Cable (3m)
Serial Modem Cable (9pin - 3m)
Serial Modem Cable (9pin - 5m)
Serial Modem Cable (9pin - 10m)33333
Mouse (3 button + wheel)
Mouse (Optical){
Screen Polish (100ml)
PCMCIA CF Adapter33333
Compact Flash Disk (32MB)33333
**{ Catalogue
{ Catalogue
Perspex Printer Stand
Printer Sharer 2:1
Screen Polish (100ml)
Serial Modem Cable (9pin - 10m)
Serial Modem Cable (9pin - 3m)
Serial Modem Cable (9pin - 5m)
Swivel Base for 12" Monitor
ZIP 100MB (pack of 3)
ZIP 100MB (single)
ZIP 100MB (pack of 3)
ZIP 100MB (single)
Compact Flash Disk (32MB)
Parallel Printer Cable (3m)
Inventory
Sales Invoice
by Kevin Millican
Inventory
<CD><SQL><NOE><CNT>
Inventory
Order No
<CD><SQL><NOE>
Inventory
Inventory
/Catalogue WHERE Item<>'zzz' ORDER BY Item <CNT>
\6{ Inventory
Inventory
Unit Price
*xref:("Catalogue","Item",[4],"Price","$.")
Inventory
Sub Total
[5]*[6] <SUM>
Inventory
<NOE>IBi
&Catalogue
Price
Default
Item(
BzPsiDatFieldInfo
Table
FieldNo
Alias
"Position
Length
"Decimals
&ExtraType
Special
>zPsiDatDefaults
"DefTable
AppName
Creator
*UseDefault
HasMBM
Picture
&Inventory
"Customer
OrderNo
("Quantity
Price
"SubTotal
"Comments
:zPsiDatReports
mytab
prnnam
prnnum
"prntitle
prnfnt
prnfnts
&prnrecrow
&prnrowspc
prnunit
prngrd
&prnorient
"showzero
prnzero
NzPsiDatReportFields
prnnum
Inventory
Portrait
Sales Invoice
|BBBBBBBBBB
Compact Flash Disk (48MB)
Compact Flash Disk (64MB)
Data Cartridge (1.35GB)
Data Cartridge (12GB DAT)
Mouse (3 button + wheel)
Mouse (Optical)
Mouse Mat
Null Modem Adapter (25 Pin)
Null Modem Adapter (9 Pin)
PCMCIA CF Adapter
Parallel Printer Cable (3m)
1.4MB Floppy Disks (Box 10)
Anytype 56K Modem
CD Labels (50)
CD Marker Pen (Black)
CD-R (Box 10)
CD-RW (Box 5)
Compact Flash Disk (32MB)
Compact Flash Disk (32MB)
Mouse (Optical)
Mouse Mat
Null Modem Adapter (25 Pin)
FF8>J>80>BF
Compact Flash Disk (48MB)33333
Compact Flash Disk (64MB)
ZIP 100MB (single){
ZIP 100MB (pack of 3)
Swivel Base for 12" Monitor
Perspex Printer Stand
Printer Sharer 2:1
CD Labels (50)333333
CD Marker Pen (Black)ffffff
Data Cartridge (1.35GB)
Data Cartridge (12GB DAT)
DThis database demonstrates a number of PsiDat features and can be used as the basis of a system to generate sales quantities / invoices. It can handle any number of clients and separate order numbers.
The 'Catalogue' table holds a list of the available sale items and their respective prices.
The 'Inventory' table holds the list of ordered items.
Navigate to the required client and order number and then press shift-F to automatically filter the available records. Then try exporting to HTML to generate a file called inventory.htm that can be opened in Web or Opera.
When you add a record, the current Customer, Order Number, and Date will be copied down to the new record. To create a new customer or order number, simply edit these three fields.
The database can be easily improved by using the field properties, eg. making the Customer field a 'Help', or changing the Date field modifier to <NOW> instead of <CD>. It may also be useful to create an index for the Inventory table based on Customer and/or OrderNo.
Abogus Company Ltd
Null Modem Adapter (9 Pin)
fffff
#@fffff
Abogus Company Ltd
CD-RW (Box 5)
fffff
Abogus Company Ltd
PCMCIA CF Adapter
33333
3@33333
Abogus Company Ltd
Mouse Mat
Someother Trading inc.
ST0001
1.4MB Floppy Disks (Box 10)
Someother Trading inc.
ST0001
CD-R (Box 10)
Someother Trading inc.
ST0001
Parallel Printer Cable (3m)
Someother Trading inc.
ST0001
Screen Polish (100ml)
Someother Trading inc.
ST0001
Anytype 56K Modem
Someother Trading inc.
ST0001
Serial Modem Cable (9pin - 3m)
@33333
Someother Trading inc.
ST0001
ZIP 100MB (pack of 3)
Abogus Company Ltd
CD Labels (50)
333333
@3333337@
Abogus Company Ltd
CD Marker Pen (Black)
ffffff
?ffffff
Abogus Company Ltd
Data Cartridge (1.35GB)
Abogus Company Ltd
Perspex Printer Stand
Select
Psion Selector
PsiDat Filtered Lookup Demo
RevoPlus
BJXXXX4>:JJLLH>H_
Organiser I
Psion
Organiser II CM
Psion
Organiser II XP (16KB)
Psion
Organiser II XP (32KB)
Psion
Organiser II LZ (32KB)
Psion
Organiser II LZ (64KB)
Psion
MC200
MC400/Word
MC600
DOS 3.2
Series 3 (128KB)
Series 3 (256KB)
Series 3a (256KB)
Series 3a (512KB)
Series 3a (1MB)
Workabout
SIBO & Custom_
Series 3a (2MB)
DH@HFJFD>DBB>4:8
Siena (512KB)
Series 3c (1MB)
Siena (1MB)
Series 3c (2MB)
Series 5 (4MB)
Geofox One (4MB)
Series 5 (8MB)
Geofox (16MB)
Series 3mx
Workabout mx
SIBO & Custom
Osaris (4MB)
Osaris (8MB)
Series 5mx
MC218
Series 7
netBook
Organiser II XP (32KB)
MC600
Series 3c (1MB)
Workabout mx
Osaris (4MB)
Osaris (8MB)
MC218
Series 5mx
Series 7
netBook
RevoPlus
Siena (1MB)
Series 3c (2MB)
Geofox One (4MB)
Series 5 (4MB)
Series 5 (8MB)
Geofox (16MB)
Series 3mx
Workabout mx
Osaris (4MB)
Series 3 (128KB)
Series 3 (256KB)
Series 3a (256KB)
Series 3a (512KB)
Series 3a (1MB)
Workabout
Series 3a (2MB)
Siena (512KB)
Series 3c (1MB)
BzPsiDatFieldInfo
Table
FieldNo
Alias
"Position
Length
"Decimals
&ExtraType
Special
Models
Model
Memory
"Infrared
6OriginalPrice
Price
Default
Memory
Model(
>zPsiDatDefaults
"DefTable
AppName
Creator
*UseDefault
HasMBM
Picture
Select
MinYear
MaxYear
&MinMemory
&MaxMemory
Model
Memory
"Infrared
6OriginalPrice
2CurrentPrice
Organiser II LZ (32KB)
Organiser II LZ (64KB)
MC200
MC400/Word
MC600
Organiser I
Organiser II CM
Organiser II XP (16KB)
Organiser II XP (32KB)
Organiser II LZ (64KB)
MC200
MC400/Word
MC600
$$$$.{
Models
Models
Models
Models
Models
Select
<DEF>1984
Select
<DEF>2001{
Select
Select
<DEF>65536
Select
SModels WHERE Year>=[1] AND Year<=[2] AND Memory>=[3] AND
Information has been obtained from numerous sources so its' accuracy cannot be guaranteed. Note that some manufacturers may use one designation to cover more than one size under other schemes.
Tip: use the comments field to add details of your devices that use each type.
Please report any glaring errors to Kevin@Millican.net
, LR44
1128MP, 1166A@
KX675, KA76
,4667, 8990, PX675, Type KA, PX76A
, UCAR 157
Photo/Electronic
Silver
EPX76
RS76P
SR44P
V76PX
SR44T
11077SOP@
B-SR44, 76, 4075
Photo/Electronic
Alkaline
10mm Dia x 30mm H
3LR50
1811A
GP23A
, 23M
Photo/Electronic
Alkaline
35.6 x 9 x 48.3mm
4LR61
1412AP@
Size J, GP1412AP
Photo/Electronic
Alkaline
13mm Dia x 25.8mm H
PX28A
A544P
4LR44
V34PX
4LR44
4LR44D
4LR44
1414A@
V28PX, 544U
Photo/Electronic
6 / 5.6
Alkaline
16.8mm D x 44.9mm H
PX32A
RPX32@
V164PX
HM-4N@
1404M
, V164P
Photo/Electronic
Alkaline
MN175
5NR44
1501M
EP175, HM-SC, PC175A, TR175
Watch/Electronic
Silver Oxide
11.6mm Dia x 2.1mm H
D381/391
TR1120SW
SR1120SW
V381Q
SR1120SW
1170SO
1170SO
!SG8, RW30, RW40
, UCAR 191, SR55LU
Watch/Electronic
Silver Oxide
11.6mm Dia x 2.1mm HU
D381/391
TR1120W
SR1120W
V391U
SP391
SR1120W
1160SO
1160SO
SG8, RW30, RW40
, SR55HU
Watch/Electronic
Silver Oxide
11.6mm Dia x 3.1mm HU
D389/390
SR1130W
SR1130W
V389U
SP389
SR1130W
1138SO
1138SO
/SG10, RW39, RW49
, UCAR 189/389, SR54H, M, SR54U
Watch/Electronic
Silver Oxide
11.6mm Dia x 3.1mm H
D389/390
390 (3mm H)
SR1130SW
SR1130SW
SR1130SWQ
1159SO
1159SO
SG10, RW39, RW49
, SR54L|ABattery Reference Cross-Checker
by Kevin Millican
Information has been obtained from numerous sources so its' accuracy cannot be guaranteed. Note that some manufacturers may use one designation to cover more than one size under other schemes.
Tip: use the comments field to add details of your devices that use each type.
Please report any glaring errors to Kevin@Millican.net
Watch/Electronic
Silver Oxide
11.6mm Dia x 3.6mm H
W2, RW51, 387, 214, HS-BE
Watch/Electronic
9.5mm Dia x 1.05mm H
SR910SW
Watch Electronic
7.9mm Dia x 1.65mm H
SR716W
Watch/Electronic
7.9mm Dia x 5.4mm H
1181SO
S13EU
Watch/Electronic
Silver Oxide
7.9mm Dia x 3.6mm H
1179SO
S312EU
Watch/Electronic
Silver Oxide
11.6mm Dia x 4.2mm H
1183SO
S41EU
Watch/Electronic
Silver Oxide
11.6mm Dia x 5.4mm H
1184SO
tCBattery Size Calculator
The following OPL code can be copied into Program editor and translated to provide a visual indication of any cylindrical battery size.
PROC BatSize:
LOCAL d%,d,h,xd,yd,xh
LOCAL sw,sh
REM *** ----------------------------------- ***
REM Screen width (sw) and height (sh) in mm
REM These figures are for a Series 5
sw=134
sh=49.5
REM *** ----------------------------------- ***
WHILE d%<>0
dINIT "Input Battery Size"
dFLOAT d,"Diameter [mm]",1,134
dFLOAT h,"Height [mm]",0.1,134
d%=DIALOG
IF d%<>0
PRINT "Approximate Battery Cell Size"
PRINT GEN$(d,5)+"mm Diameter x "+GEN$(h,5)+"mm Height"
&AThis application is provided without warranty or guarantee of any kind. Use of PsiDat is permitted solely on the basis that the user will not hold the author responsible for any loss or damage to data or equipment howsoever caused.
By using PsiDat, you are implicitly agreeing to these terms.
Table1
ColA9
ColA10
ColB10
ColA11
ColB11
ColA12
Index1
ColA9
ColA10
llican
BPsiDat is provided as fully-functional freeware but as the author, Kevin Milican, I retain all rights to it.
PsiDat may be provided free-of-charge via any medium, provided that it is supplied in it's original SIS installation file which must not be modified in any way. No charge, except reasonable copy expenses may be made.
Subject to the above, PsiDat may be included on any magazine cover disk, CD, DVD, with the sole provision that the author receives a complimentary copy of the relevant edition. The same may be said for any shareware/freeware compilation CD/DVD.
The following regular compilations may include PsiDat without necessarily providing a copy to the author:-
3-Lib Shareware Compilation CD
^AThank you for using this program. In writing it, I hope I have made most things fairly self-explanatory but databases are by nature complex.
In this document, I have tried to concentrate on the more subtle features of the program but I have also included a 'Getting Started' section which provides a general overview.
Thanks again !
Kevin Millican
ABy default, real numbers display in a general format as required to display the full precision of the data.
You may specify a fixed format for each real field in the 'Field Properties' dialog. This is particularly worth doing if you wish to export the current table as a dBase file.
AOPL fieldnames are fairly restrictive and cannot contain characters other than alphanumerics (A-z,0-9). However, any descriptive names can be used for display by creating field name aliases using the 'Options|Field Properties' option.
You can select whether to display these aliases or the original fieldnames. This is important when exporting or saving the current table as a new table.
Table1
"Priority
Title
Details
ANumeric fields can have the 'Stopwatch' 'Special Use' attribute set in the field properties.
When you tap on the field, PsiDat will wait for a keypress to start the timer and another to stop it. You then have the option to accept the value with the Enter key or press any other key to keep the original value.
Although this function can be used with any numeric field, it is intended for the Real types so that the decimal part of the time can be recorded.
AWhen you open a table you are initially in browse mode.
To edit the data, tap a field or press the space bar. Some fields will respond by producing an entry dialog, eg. Date/Time, Memo, Text with Lookups, but numeric and standard text fields will allow you to edit them directly.
You can move down to other fields using the up and down keys or press Enter to confirm a value and exit edit mode.
Pressing the Esc key returns you to browse mode.
{CPsiDat can generate example OPL code for opening, reading, and editing an open table.
The code is pasted into a multiline edit box so that it can be copied and pasted as required.
A few things to note :-
1. The OPL manual suggests that it is a good idea to name the required data fields explicitly in the SELECT statement.
I strongly disagree; it's far too easy to create an OPEN statement that exceeds the permissible string length on databases with several fields and long fieldnames. Also - these real fieldnames are not useful for any purpose when manipulating the data. This is why I have used 'SELECT *' in PsiDat and its example code.
2. The ODbPutWord: and ODbPutInt: statements require the variable to be passed as a long integer. This is why I have used longs for the sample code when an ordinary 16bit integer (%) could have been used to store the result from field types 0-4.
AYou can copy the current record to the clipboard using the relevant menu item or Ctrl-C
By default, a tab-delimited copy routine will be used (ie. tab characters separate each field), but you can change this using the 'Clip Options' dialog.
It is also possible to copy all the current table records using the 'Copy All Records' option under the 'Edit' menu.
To copy individual numeric or text values, you have to be editing the field normally and press Ctrl-C (no need to highlight).
GPS/Map Po
HTML Outpu
Importing
Indexes
Internatio
Keypresses
List View
LongBinary
Lookups
Lookups wi
Merging
Number Ent
OPL Code
Password
Printing
Real Numbe
Record Num
Reports
Restructur
Searching
Stopwatch
Summary Fi
Text Modif
UserMenu:
Variables
Field Type
Introducti
Contact
Copyright
Disclaimer
Getting St
Aliases
Autofill F
Autofilter
Banners
Bit Fields
Browsing
Calculated
Calculated
Calculatio
Calculatio
Copying Da
Custom OPL
Date/Time
DBase Impo
Editing Da
Exporting
Exporting
Field Modi
Field Type
Field Type
BAs these are mainly used for True,False values (though not a real 'True' because this would have the value -1 instead of 1), the following extra features are available :-
If you add <TF> to the calculation field then 1 is displayed as TRUE and 0 or a null entry as FALSE.
If you add <YN> to the calculation field then 1 is displayed as YES and 0 or a null entry as NO.
Regardless of whether this option is specified, 'n*','N*','f*','F*' are accepted as inputs for value 0 and 'y*','Y*','t*','T*' and '-1' are accepted as value 1. The latter case means that it is not necessary to put a negative sign in front of a logical condition to ensure that it is positive for use in the bit field.
Calculations - Special FunctionsD
!Autofilter (<SQL> Field Modifier)G
UserMenu: OPL Source CodeH
JEThe easiest way to create a database is to import one or open an existing file created by the Data application and select 'Save As'.
To create a database from scratch, use the 'Create New Table' option under the 'Structure' menu. You can use any field type but it is recommended that you avoid those preceded by '---' since these cannot be edited by PsiDat at present. Where possible, it is recommended that you use the OPL types since these can also be easily opened by other OPL programs.
You must ensure that your fields have different names.
You can move forwards and backwards through the fields you have designed using the '>' and '<' dialog buttons.
To add another field, move to a blank entry and type a field name.
To remove a field, just delete its field name.
You can reposition or insert a field by altering the choice in the 'Special' field at the bottom of the dialog and pressing the 'Special' dialog button.
There is an extra option to shuffle the fields together by removing the blank entries. This will also ensure that all fieldnames are valid but will not spot any duplicate field names.
When you have finished, press the Enter key or 'Done' dialog button and give the database and table names. Note that you can save several tables to one database file.
When your table is created you can then 'Open' it from the main menu.
DPsiDat provides a versatile system for producing hardcopy reports. This allows a high degree of control over the output but this sophistication comes at a price: there is a significant amount of planning required.
On the upside, once you've got your report working to your satisfaction, the layout can be saved for future use.
Initially you must define the global report parameters. Most of these are the defaults for the individual fields and can be changed independently. However, it is important to note that the default font size also controls the line spacing for the report as a whole.
For example, you want most of the report fields to print with a font height of 10 points - use this value in the defaults and and it saves you having to change it individually for each field. But if you think the report rows are too close together, you can go back to the default font size and increase it without altering the actual size of text used for each field.
Note also that each record in the report can use up to 8 lines for content and 1 or 2 lines white space between records. (TIP: you can use the font characteristics to highlight which row is which because the header field names match)
Courier
2APsiDat can create database tables containing all 17 EPOC DBMS field types. Four of these (64bit integer, Unicode Text, Binary, and LongText16) and have no obvious use and are therefore unsupported in terms of editing data. These field types are preceded by '---' in the table creation/restructure dialogs.
"AThe 'Merge In' function under the 'Edit' menu will attempt to merge in the records from one table to the current one. For this to work, the order and position of fields in the two tables must coincide, even if only partially.
Within reason, merging can cope with minor field type changes.
:EYou can directly open files created by the built-in Data application but you cannot edit them. However, once opened, you can create more meaningful field name aliases for each field and use 'Save As' to save the data as a new standard OPL/PsiDat table.
Embedded objects and text formatting will be lost but the text in Memo fields should be preserved along with all other Data fields.
You can import dBase III+ and dBase IV files (*.DBF) complete with memo files (*.DBT) when used.
It is also possible to import delimited text files with or without fieldnames in the first row. PsiDat will try to create a table using the following field types (in this order of preference) :-
Integer
Long Integer
Double Float
Text field lengths will be the minimum required to store all the data up to the maximum possible for the procedure.
PsiDat will scan the text file to determine the number of fields and will try to fit the data to the most efficient field type without data loss. For example :-
If the data cannot be stored in an integer variable but consists of otherwise valid numbers (without decimal points), then a LongInteger variable will be used.
Any data not fitting comfortably into a numeric field type will be saved as text.
DateTime data will be imported as text but it can be restructured to the correct format later.
CPsiDat's 'Restructure' option is really an extension of the table creation routine and 'Save As' option.
It allows you to modify the field positions, alter text field lengths, change numeric field types, add and delete fields, and choose which fields to copy data from.
This is a very powerful option but one limitation of this power is that it is not currently possible to restructure a table and save it to a table of the same name in the same database. If you wish to do this you must follow the restructure by a second stage, ie. open the restructured table and 'Save As' it to the original table name.
In the save file dialog there is a checkbox to copy data. If you untick this then a blank table will be created.
Text fields containing dates and/or times can be converted to DateTime fields. PsiDat will try to analyse the format but you may be asked to confirm it.
The format string is up to 6 characters long and just represents the order of the values (eg. 'DMYhms', 'YMD', 'hms') and their availability.
CYou can export a table as a dBase III+ or dBase IV (*.DBF) file. If your table contains memo fields then a corresponding memo file (*.DBT) is created as well.
There is an option to ignore the memo fields if you prefer.
You can also export the current table as a standard delimited text file or a structured text file (see Exporting Structured Text).
In both cases, it should be noted that the exported table field names will mirror those currently displayed. If you don't wish to use your aliases then untick the 'Use Aliases' option in the 'Options' menu.
NB: There is an element of auto-correction to field names when exporting dBase files; and spaces or non-alphanumeric characters are stripped and the length is restricted to the allowed 10 characters.
However, if you use long field names or aliases with similar start strings, you may end up with duplicated field names. This may (or may not) cause problems with the program used to import the file later on.
DThe LongBinary field type is used to store complete files within a table.
Any type of file can be stored and restored, however the original file is not deleted and the original filename is not preserved.
If the file is a recognisable EPOC
file then it can be 'launched'; a temporary file is created in the \system\temp\ folder that has most space available and then opened. When the user closes the file, PsiDat switches back to the foreground. If the file was changed the option to store the revised version is offered.
PsiDat will recognise Word, Sheet, Data, Sketch, Record, OPL '.OPO', and EPOC picture ('*.MBM') files directly. Other file types are offered to the system to see if it recognises them and, if so, can also be launched although this takes longer.
EPOC picture files can be launched if there is an application that has registered the file type. They can also be viewed using PsiDat's built-in picture viewer. You can scroll around using the cursor keys (plus Shift or Ctrl) or by dragging the picture with the pen.
Record files are identified as 'Sound Files' - these can be 'Played' on all EPOC devices or launched by those that have the Record program present.
@The selection and application of field types is described below:-
Text - used for any combination of characters up to a maximum length of 255 characters.
DateTime - always saves both date and time but can display Date, Time, or Date & Time.
LongText8 (Memo) - used for storing text that exceeds 255 characters or contains formatting characters such as line breaks or tabs. The maximum size is only limited by available memory and disk space.
Numeric field types - generally use LongInteger for whole numbers and DoubleFloat for numbers with fractions. Other numeric field types can be used to save memory or for special purposes.
Bit - Strictly a numeric field type but worth mentioning on its own because the Bit field type is often used to store Yes/No or True/False (= 1 or 0) values.
LongBinary - used by PsiDat to store pictures and launchable files although OPL programmers may use them to store any kiE
nd of data. Size is only limited by available memory and disk space.
EPsiDat can import and export dBase III/IV (*.DBF) files complete with memo (*.DBT) files.
An option is provided to allow codepage conversion between 850 (as used in most PC databases) and 1252 (as used by EPOC
devices). This should nearly always be left ticked in the dialog box.
PsiDat's conversion maps supported characters (eg. accented characters) correctly and provides a bidirectional one-to-one mapping of characters unique to each codepage. This should allow unsupported characters to be preserved in an import-edit-export cycle.
NB: When exporting dBase files it is recommended that you use the dBase III option, especially if the file does not include LongText8 (memo) fields. Many PC databases will automatically save dBase III when the user opts to save a dBase IV
table that doesn't include memos.
PsiDat will recommend this because most PC database applications will fail to load a type IV structured table without memos.
It recommended that the codepage conversion options are used in both directions.
One further note of technical interest is that dBase IV files save the codepage in the file header to allow conversion. However, code page 1252 is not (as far as the author can ascertain) supported. If you opt not to use codepage conversion, PsiDat will specify its dBase IV codepage as the closest supported one, ie. 1251 (Windows ANSI). This will lead to some peculiar conversions although most accented characters will still work provided the PC application performs the conversion - many don't.
{BDate/Time fields are only modifiable by tapping on the field when browsing. You cannot manually edit the displayed text and affect the stored Date/Time field. If you try this then the datetime dialog box will pop up.
When you tap a Date/Time field a dialog appears set to the currently stored Date/Time or the system time if blank. You can alter these figures to any valid date and time.
The 'General Preferences' section allows the user to modify the Date/Time format generally.
However, you may also select whether the Date/Time, Date, or Time is displayed for each field by selecting the option in the 'Field Properties' dialog.
AWhen you enter a numeric value, you can enter a valid mathematical expression which will be calculated before the field is updated.
Note that for integer fields it is sometimes necessary to ensure that a calculation occurs rather than an assignment.
Eg. You can enter PI in a Real field and it will be computed correctly but you have to enter 1*PI to store 3 in an integer field because PI is not an integer.
Arial
Arial
Arial
roperties' dialog.
DPsiDat uses its own databases to store the resources needed for the dialog boxes, menus, and messages.
The default language resource is PsiDatRSC.English and there is a corresponding PsiDat-English.HLP helpfile.
To create a new resource file in, for example French:-
Copy PsiDatRSC.English to another folder and rename the copy PsiDatRSC.Fran
ais. Then copy this back to the PsiDat application directory. Then open the new copy in PsiDat and change the English text strings to French.
It should be noted that the text strings are currently limited to 60 characters length - use abbreviations where necessary and try not to exceed the corresponding English strings.
When PsiDat loads a different language, it also tries to switch to the corresponding language helpfile if it exists, or reverts to English otherwise.
When PsiDat loads a new language resource file, it always reloads the default English version first. Then it tries to overwrite any strings with the alternate language. The advantage of this approach is that new versions of PsiDat will still work with outdated language resource files (except that any new text strings missing from the alternate language will just appear in English until they are added to the old resource)
Courier
ut you have to enter 1*PI to store 3 in an integer field because PI is not an integer.
RDThe floating point variable types can be used to obtain Latitude and Longitude values via RealMaps.
This position can be obtained from a GPS while RealMaps is in GPS moving map mode or by positioning the RealMaps cursor on a map and pressing the Enter key.
To use the values, tap on the field in the same way as a calculation (NB: these fields are deliberately not included in the 'Recalculate' all fields option)
Manual data in these fields can be entered using spaces to represent the degree symbol or minutes/seconds.
52.50409
52 30.245
52 30 14
all represent about 52
30'14" North (or East)
However, the Southern and Western coordinates must have a minus sign in front of the degrees portion - you cannot use S,W etc.
By default the lat/long coordinates are shown as plain decimal numbers. However, the field modifiers <DMS1> to <DMS5> may be used to change this as follows; eg. for a latitude of 35.96160 degrees:-
<DMS1> means display as 35.96160
<DMS2> means display as 35
57.70'N
<DMS3> means display as 35
57.696'N
<DMS4> means display as 35
57'41"N
<DMS5> means display as 35
57'41.8"N
DIndexes are used to sort tables and speed up searches. All numeric fields and text fields can be used but there is a limitation on the length of text that can be used in a sort field of 240 characters (this is why the text field length defaults to this amount).
Indexes are created by adding one or more fields to an index description in their order of priority. Each component field in an index may be sorted in ascending or descending order.
If you index a field with length > 240 characters then the sort text has to be truncated to 240 characters or less. OPL restricts this to the last field in the sort and only one truncated field is allowed.
Therefore, if you add a field and truncate the sort length, PsiDat will assume that you have finished and switch to the index naming dialog.
If you are going to use indexes, it is recommended that you pay close attention to the length of text fields when the table is defined. Longer-than-required text fields do not affect the size of database tables unless indexes are used.
FThe EPOC DBMS considers absolute record position to "not have much significance". As a result, reporting of record number and number of records is fraught with pitfalls.
There are also limits within the system that prevent record numbers above 65535 being reported. In most databases this isn't an issue. If the number of records is greater than 65535 PsiDat will still be able to position to an absolute record but will not report the current record number.
Another problem that can occur is when a record is being edited and the change makes it fall out of the current SQL query criteria.
In normal editing PsiDat will cope with this situation by refreshing the view when the number of records in it suddenly drops for an unexplained reason.
This causes the current record to switch to the first record in the view or disables the SQL filter if the last record in the view was being edited.
There is one situation that can still cause problems :-
If a summary view or report is generated with the 'Recalc during summary' option enabled, then;
if the calculation causes all the records to drop out of the current view an untrappable error occurs.
The chances of this happening in a real application are slight and the worst that should happen is that the program will exit suddenly with an error message.
A special field modifier <INF> can be used to show the record contents in the record info in addition to '# of N'.
Up to three fields can be used in this manner to make navigation of records with large numbers of fields easier.
It is important to note that adding/deleting this feature on a field is only activated when the table is opened in order to keep the speed up.
GPsiDat can perform two types of search.
The simple 'Find' search (S key) will search for the occurence of the desired text in any available text field.
The 'Again' option (Enter key) will find the next occurence until the last record is reached.
The 'SQL' search (Q key) allows much more specific queries to be performed, eg. for a field called 'Name' the query to find all names begining with M would be :-
WHERE Name LIKE 'm*'
and this would be entered in the dialog without the WHERE statement as
Name LIKE 'm*'
To search for all names including 'mike' you would enter:-
Name LIKE '*mike*'
This would find 'Mike Smith', 'Mike Brown', 'Steve Mikellos' etc.
PsiDat allows you to save the SQL query string so that it can be reused. If you try to save a query that already exists for the current table, then you are given the option to remove it from the picklist.
You can apply successful SQL searches as filters. This means that only matching records can be displayed in the current view. This is particularly useful when generating reports since it allows you to only include specific records.
Notes :
1. The OPL database engine does not allow direct comparison between two fields so you cannot look for
Forename LIKE Surname directly.
If you need to compare two fields then you should add a numeric field with a calculated expression so that it holds the result of whether Forename is like Surname.
2. It is important to use single quote marks when searching for text matches.
3. If you use field name aliases to change the displayed field names then you must still use the proper fieldname in the query.
4. The syntax for comparing a datetime field is to put the literal date between '#' marks, eg.
WHERE Date > #04/04/2000#
(NB: The OPL documentation describes a date-literal as :-
'a character string enclosed by the # character, which can be interpreted as a valid date.' - the author is uncertain how broad this interpretation can be)
EDThe 'Default Table' dialog under the 'Options' menu allows user-defined banners to be incorporated in the current database.
These can be text-based (similar to the built-in PsiDat banner), or may include a standard EPOC MBM picture file.
This allows PsiDat databases to look much like user-written applications.
Normally this dialog would be invoked from the table which the designer wishes to open automatically when the file is opened from the system screen.
If the table has indexes, the first index will be opened automatically.
When the user presses Ctrl O or selects 'Change Table' they can then reopen the database without the banner or automatic table selection (if this wasn't the case it would be difficult to re-edit the other tables and preferences).
If you untick the 'Use MBM' banner, and select 'OK' then any existing MBM file embedded in the database will be removed.
The 'Change MBM' option is used to select an existing MBM file from disk. This is then embedded in the database as a LongBinary field (type 16) so that the database can be distributed as a single file.
BBy creating a 2nd record in the zPsiDatDefaults table, you can prevent a user from switching to and manually editing any of the zPsiDat... structure tables (although properties may still be adjusted if the default menu is not overridden). Enter the password in the first field (DefTable).
To unlock an open database, type '~' (Ctrl 126) to reveal a password dialog.
NB: This is a very low level of protection designed solely to prevent casual users from accidentally restructuring a table or changing something they shouldn't. Any OPL programmer could easily get round this so don't attempt to use it for anything important you wish to keep hidden.
&BBy tapping the far left of the screen or typing shift-V a list view is shown.
Use the scroll bar or up/down keys to navigate and then tap on the desired record.
Pressing Esc or tapping the toolbar will abort maintaining the original record position.
Pressing Enter will move to the top record in the view.
The list view is fairly sluggish due to the fact that it automatically adjusts the column widths in realtime.
It should also be noted that the list view is modal and most functions, including the menus, are disabled whilst it is displayed.
e hyperlinks etc.
hEIt is possible to specify that a text field is calculated but the calculation is limited to concatenation (ie. the addition of strings) or use of a single procedure call returning a string variable.
For example, if field 1 contains the text 'Eggs' and field 2 contains the numeric value '6', you could have a text field 4 with this calculation in the field properties :-
[1] in packs of [2]
which would return the string:-
Eggs in packs of 6
You cannot extract portions of strings using LEFT$, MID$, RIGHT$ or perform numeric calculations (although you can use the result of other calculated fields)
A special syntax is used to call OPL procedures as follows:-
User-defined functions can be provided as a standard OPL executable (<anything>.OPO) which must be embedded in the 'Picture' field of the 2nd record in the zPsiDatDefaults table. [For more details, see Custom OPL Routines]
Procedures intended for text strings must include a single text variable which is passed to the procedure.
The procedure is called without the $ sign.
These features are best explained by example:-
If an OPO file has been created that includes the following routine :-
PROC upcase$:(a$)
RETURN UPPER$(a$)
If the database table contains two strings and the second one has the formula :-
upper:([1])
Then the 2nd field will calculate the uppercase value of the text string in field 1.
Courier
@Numeric fields can perform calculations on any field in the current record. To reference a field, you use the numeric value of the displayed field, ie. The top displayed field is [1], the 2nd line field is [2] etc.
An example expression to multiply the first field by the second would be :-
[1]*[2]
Any valid OPL EVAL
command may be used. DateTime fields can be used but their value is given as the number of seconds past 0:00 hrs on 1/1/1970. If this is equivalent to more than 65 years difference, the value is only accurate to the nearest 60 seconds.
When making comparisons, you must ensure that the sign (ie. -ve/+ve) is correct for unsigned variables. One notable exception to this is t
he Bit field type which has been set up to save -1 as 1 automatically.
The built-in variable PI can also be used in expressions.
You can recalculate all fields in a record by pressing R or selecting the option in the menu.
Recalculation of all fields occurs automatically if the 'Auto Recalc' option is set and a field is manually edited or a new record created.
If you want to use any modifiers (eg. <TF>, <YN> for bit fields, or summary operators), add a tab keypress (comma or the word REM are still supported) and then the modifier so that the calculation still works.
The special/calculation string can be up to 255 characters long but some caution is required:- When a calculation field is processed, it creates a new string for the EVAL function to operate on containing the actual values of the fields used. If the new string length exceeds 255 characters then the calculation will not be processed.
HGText fields can reference other tables within the same database to provide mandatory Lookups or optional Help text.
To use this feature, you can either create a table containing a string field to hold the lookups, or make use of a built-in lookup table which PsiDat will create for you (zPsiDatLookup).
Then open the table you wish to use the lookups and change the field properties for the appropriate text field so that the 'Special Use' choice field reads either 'Help' or 'Lookup'.
If you are using a user-defined table then enter the name of the lookup table in the 'Ref Table' section, otherwise leave it blank to use the one that PsiDat provides.
The advantage of using a specially-defined table is that it can be processed on its own easily (eg. imported from elsewhere). It is also quicker for large numbers of entries.
The built-in facility is ideal for small numbers of lookups/helps and has the advantage that new entries can be made on-the-fly from the main table (This isn't practical for user-defined lookup tables which may conceivably contain many fields if they are used to link data in the future).
Sometimes it is desirable to use a Help rather than a lookup so that the field can be edited (eg. units) but undesirable for the edited text to be saved as a new help. The <NOA> modifier may be used to achieve this. The <ST> modifier can be used to initiate the cursor at the start of the field rather than the end.
If you wish to use a new record autofill command or other flag for this field, it must follow the table name and be separated from it by a tab character (space and comma are still supported).
Eg. If you are using a lookup table called 'myLookup' and want this field to take the value of the last viewed record when creating a new record, the 'Ref Table' option would contain:-
myLookup <CD>
myLookup <CD>
myLookup,<CD>
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier New
Courier
Courier
Courier
Courier
f the PsiDat program for the sole use of the user's custom code as follows :-
GLOBAL u(100),u%(100),u&(100),u$(20,255)
[None of the above variables are used by PsiDat itself.]
In addition, all of the PsiDat global variables and routines may be accessed in the user's executable file [See Variables]
HThis is a list of useful PsiDat global variables and procedures:-
true, true% -1
false,false% 0
myfile$ The filename of the currently selected database.
mytab$ The name of the currently open table.
nf& The number of open fields in the current table.
buflen& The current usable length of the memo/longbinary buffer in bytes.
buffer& The address of the memo/longbinary buffer.
text& The usable start of the memo/longbinary buffer (ie. buffer&+4).
rom The operating system ROM revision number.
language$ The currently selected resource language.
reopen: Procedure to reopen a database table that has been temporarily closed (The main table in the PsiDat editview always has ID=0, ie. is handle A)
modtoolbar: Procedure to switch the toolbar on or off.
toolbar% Current status of the toolbar (true = on)
PsiDatMenu: The main PsiDat menu (called if usermenu: is not found). May be called by usermenu: as one of it's options.
getdat$:(i%) The contents of field number i% (NB: it's safer to access this through a global array variable i%(i%) since the display position may not always be the same as the actual field position. This is especially true for standard Data files where the useful fields are 1,3,5,7 etc. so the third visible field is numbered 5 and i%(3)=5) This call may only be used for field types 0-11
Example : S$=getdat$(i%(3))
assigns the value of the third visible field to S$
ODbGetDateTime: must be used to read dates (See DATA.OPX documentation)
putdat:(i%,s$) This procedure writes the equivalent value of s$ into the i% numbered field of the current record. (Use of i%() syntax is advised but not absolutely necessary in revision 1.11 because there is no way of writing to a Data application file and facilities for display reordering/hiding of fields has not been implemented yet. It's arguable that this can be ignored anyway because users will have absolute control over their data structures) The call can be used with field types 0-9, and 11, ie. all numeric types plus text fields.
ODbPutDateTime: must be used to write datetime fields (See DATA.OPX documentation).
ftyp%(i%) (Or ftyp%(i%(i%)) !) ftyp%(field_number) returns the field type.
Arial
Courier New
Arial
Courier New
Arial
Courier New
Arial
Courier New
Arial
Courier New
Arial
Courier New
Arial
Courier New
Arial
Courier New
Courier New
Arial
Courier New
Arial
Courier New
Arial
Courier New
Arial
Courier New
Arial
Courier New
Arial
Courier New
Arial
Courier New
Arial
Courier New
Arial
Arial
Arial
Courier New
Arial
Arial
Arial
Courier New
Arial
Arial
Arial
Courier New
Arial
Courier New
Arial
Cv1.12+ features a useful enhancement to the table Lookup/Help facility in that the user-defined lookup tables may be filtered using SQL.
For example, suppose that there is a lookup table 'MyLookup' containing fields 'Item', 'Price', and 'Type'.
If the main table contains fields 'MinPrice', 'MaxPrice', 'Type', & 'Selection', we could define a Lookup for the 'Selection' field as :-
MyLookup WHERE Price >= [1] AND Price <= [2] AND Type LIKE '[3]'
This will filter the Lookup to make the lookups within our price range and fit the type criteria.
It is possible to order the lookup table view as well, eg.
MyLookup WHERE Price >= [1] AND Price <= [2] AND Type LIKE '[3]' ORDER BY Price
However, you cannot use ORDER BY on its own since ' WHERE ' is essential for PsiDat to detect that a query is in use. A workaround is to specify a criteria that will view all records :-
MyLookup WHERE Type NOT LIKE 'junk xyzpqr' ORDER BY Price
Courier
Courier
Courier
Courier
Courier
iBPsiDat can produce multi-record formatted HTML tables and single-record view HTML pages using the 'Export HTML' option.
By default, the routine will intercept characters like '<' and '>' and replace these with codes to show these symbols. Similarly a newline character is converted to <P> ... </P> pairs.
However, if the user wished to save information containing HTML tags, the output document would appear as plain text in a browser rather than a formatted HTML layout.
By using the <HTM> modifier you can tell PsiDat to output the raw contents of a field when exporting HTML and thereby include hyperlinks etc.
as well, eg.
MyLookup WHERE Price >= [1] AND Price <= [2] AND Type LIKE '[3]' ORDER BY Price
However, you cannot use ORDER BY on its own since ' WHERE ' is essential for PsiDat to detect that a query is in use. A workaround is to specify a criteria that will view all records :-
MyLookup WHERE Type NOT LIKE 'junk xyzpqr' ORDER BY Price
JA number of functions have been provided to make calculated fields easier. These are shown as example calculations below. '[1]' means the datetime (or the equivalent numeric value) from field number 1 :-
Before:(2001,4,5,[1]) - before 5th April 2001 ?
After:(2000,4,4,[1]) - after 4th April 2000 ?
ThisYear:([1]) - in the current year ?
ThisMonth:([1]) - in the current month ?
ThisWeek:([1]) - in the current week ?
IsToday:([1]) - matches today's date ?
All of the above return 1 for true or 0 for false
WeekNo:([1]) - the week number based on the EPOC machine settings for the first day of the week.
WeekDay:([1]) - returns the day number (1-7)
Day:([1]) - the number of days since 1/1/1970
Month:([1]) - returns the month number (1-12)
The following functions can be used for comparisons with datetime fields and equivalent numeric values :-
DateTime:(year,month,day,hour,minute,second)
ie. DateTime:(2001,4,5,15,30,0) - returns the equivalent numerical value of 5th April 2001 3:30pm
Now: - returns the equivalent numerical value of the current system time
Some text calculation functions are also provided :-
The DayNm$:(n$) text function to return a three character day-of-the-week name from a number ("1"-"7") or datetime value.
The $ sign and quotes in text functions are always dropped in calculation expressions because of the way the procedures are called, so:-
DayNm:(1) returns 'Mon'
DayNm:([1]) returns 'Wed' when field 1 contains a datetime or value equivalent of 11 April 2001, or a weekday number 3.
(It should be noted that seven seconds on 1/1/1900, 00:00:01 to 00:00:07 will return 'Mon' to 'Sun' instead of the correct dayname for 1/1/1900)
MonthNm:(1) returns 'Jan'
MonthNm:([1]) returns 'Apr' when field 1 contains a datetime or value equivalent of eg. 11 April 2001, or a month number 4.
(It should be noted that twelve seconds on 1/1/1900, 00:00:01 to 00:00:12 will return 'Jan' to 'Dec' instead of the correct monthname for 1/1/1900)
Date:([1]) returns the text equivalent of the date portion of the datetime in field 1
Time:([1]) returns the text equivalent of the time portion of the datetime in field 1
(NB: in v1.12+ the text value returned from a datetime field in a calculation expression has changed from earlier versions; the whole datetime is always supplied to the calculation regardless of the current 'Date','Time', or 'Both' setting in the datetime field properties)
Left:([1]|5) returns the first five characters
Right:([1]|6) returns the last six characters
Mid:([1]|5|4) returns four characters from field 1 beginning at the fifth character.
Note: Other characters or more than one field can be included in the expression. eg. :-
Right:(00000[1]|5)
would return 00012 when field 1 contains the value 12
1FA table lookup cross-reference function xRef: can be used in calculations.
O$ is a two character string used to define the LookUpFieldName$ and XRefFieldName$ field types :-
0 1 2 % 4 & 6 7 8 . D $
ie. O$ is the same string that would be used in the Data.OPX ODbOpen: and ODbOpenR: procedure calls but with any '?' characters replaced with a single digit equal to the field type number or a 'D' for a datetime.
Example 1:
Looking up a text field and cross-referencing to a doublefloat, returning a numeric value.
xref:("Prices","Item",[1],"UnitCost","$.")
Note: [1] is already text so it is not put in quotes.
Example 2:
Looking up a LongInteger field and cross-referencing to an Integer, returning a numeric value.
xref:("Stock","StockNo","[3]","MaxDisc","&%")
Note: [3] is a numeric field so it is put in quotes.
Example 3:
Looking up an unsigned integer (type 4) and cross-referencing to a singlefloat (type 8), returning a numeric value.
xref:("LookTab","ItemNo","[1]","RefVal","48")
Note: [1] is a numeric value so it is put in quotes.
Example 4: Text Calculation
Looking up a Text field and cross-referencing to another Text field, returning a string value to a text field.
xref:(Prices|Item|[1]|ExtraInfo|$$)
Note: All items are text, "|" (Ctrl-124) is used to delimit the string into 5 components.
Example 5: Text Calculation
Looking up an unsigned integer (type 4) and cross-referencing to an integer, returning a string value to a text field.
xref:(LookTab|ItemNo|[1]|RefVal|4%)
Courier
Courier
Courier
Courier
Courier
EWhen you open a table you are initially in browse mode.
If there are more fields than can be displayed on the screen, you can move up and down them using the up and down arrow keys and by using PgUp, PgDn, Home, and End.
To move between the records you can use the left and right arrow keys or the 'Previous' and 'Next' toolbar buttons.
You can move to a relative position in the table by tapping on the record position indicator bar or by pressing G to move to a particular record number.
You can add a new record using the N key ('New') or the 'Add' button on the toolbar (why? : 'New' is too easy to confuse with 'Next' !)
The Del key deletes a record after user confirmation.
You may also use F to move to the first record and L to move to the last record.
Most of the menu shortcuts are available using the Ctrl key as reported in the menu and will also function on their own. Exceptions to this include: closing the program (Ctrl-E), closing a database (Ctrl-X), changing the table (Ctrl-O) and the printing functions.
shift-K will switch RealMaps to the foreground if running.
~ is used to unload custom OPL routines (with password confirmation if used)
Ctrl-B and shift-Ctrl-B (in this case the Ctrl is required) will toggle through four text attribute options for the field and label text.
Whilst editing a field, Ctrl-C and Ctrl-V copy and paste text.
The Space bar allows you to edit the current record. In this mode, the left and right arrow keys are used for inline editing.
[See Editing Data]
JPsiDat supports four types of structured text export :-
Idealist Natural
This format contains record name and fieldname data. Each new record is preceded by it's record type name after a colon character (':') on a new line. Then each field is preceded by it's fieldname after a hyphen ('-') on a newline. Linefeed characters have no other significance so memo text fields can be exported as easily as numbers and normal text fields.
Lotus Notes Structured Text
This form of structured text comprises the fieldname followed by a colon character (':') and two space characters followed by the field data. It can contain memo fields with carriage returns because it is the responsibility of the receiving application to look for the fieldnames at the beginning of each line. Records are terminated with the new page character (ASCII 12).
Outputs a complete HTML page with a title and a table containing field names and data. The table background, line colour, and alternating line cell colours can be specified (attractive defaults are provided). Removing these attributes will reduce the filesize.
A 'Single Record' view is also available. This can be selected in the dialog produced by the 'Options|HTML...' menu item.
An option is also provided to specify the URL and title for a hyperlink back to the calling page.
There is an option to specify a maximum number of records per page for multi-record HTML exports. Leave this at zero to put all records on a single page. When a number is input, a series of pages will be generated. Eg. a page with filename 'mypage' would output pages called 'mypage1.htm', 'mypage2.htm' etc. The pages are hyperlinked together with a simple navigation at the bottom of the page.
In the 'Options|HTML...' dialog, it is also possible to define a special <BODY> tag for both the multi-record tabular layout and the alternative single record view.
In v1.13 XML export has been added as a subtype from the HTML export dialog.
XML is the new standard for sharing data across the internet and has many similarities with HTML. However, whereas HTML is optimised for document output, XML is designed to produce text structured so that it's content can be easily understood by other applications.
One thing that is unclear from the standard, is exactly how paragraph and line ends should be stored within long text records. Strictly speaking, all characters are relevant; so tabs, CRLF combinations etc. can all be included between the field delimiters. However, even a brief glance at the way browsers display XML
documents suggests that paragraphs and newlines benefit by being stored with blank field markers. Therefore four options have been included for handling new paragraph and newline codes within the output document.
Browsing+
Importing"
Exporting Tabular Formats!
Banners$
OPL Code#
Copying Data&
Printing
PsiDat makes use of Twiddlebit Software's GPRINTER.OPX to print database reports. The author would like to thank them for making this OPX available freely.
For more information on printing see 'Reports'
Reports
Summary Fields
The calculation field in the numeric field property options may contain the summary operators :-
<CNT>, <SUM>, <AVG>, <MIN>, <MAX>
(note 'CNT' is used to indicate 'COUNT')
You can view the summary results by selecting this item from the 'View' menu.
Record Numbers'
Restructuring Tables
Keypresses
See Browsing
GPS/Map Positioning
Bit Fields
Exporting Structured Text0
LongBinary Fields
iBPsiDat can produce multi-record formatted HTML tables and single-record view HTML pages using the 'Export HTML' option.
By default, the routine will intercept characters like '<' and '>' and replace these with codes to show these symbols. Similarly a newline character is converted to <P> ... </P> pairs.
However, if the user wished to save information containing HTML tags, the output document would appear as plain text in a browser rather than a formatted HTML layout.
By using the <HTM> modifier you can tell PsiDat to output the raw contents of a field when exporting HTML and thereby include hyperlinks etc.
mHA field modifier is a flag put in the 'special use' or 'calculation' line of the field properties dialog. If this line contains a calculation, or a lookup/help table name, then separate it from the flags with a tab character. The following field modifiers are supported:-
Special
<SQL> Autofilter
Summary Operators
<CNT> Summary operator - Count
<SUM> Summary operator - Sum
<AVG> Summary operator - Average
<MIN> Summary operator - Minimum
<MAX> Summary operator - Maximum
New Record Autofills
<CD> Copy down
<+1> Increment number
<DEF> Default value (everything following '<DEF>' is used as the value so it should be the last modifier on the line)
<NOW> Create date/time
Recalculation
<MOD> Modified date/time
Display/Input
<YN> Yes/No text (Bit Fields)
<TF> True/False text (Bit Fields)
<MF> Male/Female text (Bit Fields)
<DMS1> display as eg. 35.96160
<DMS2> display as eg. 35
57.70'N
<DMS3> display as eg. 35
57.696'N
<DMS4> display as eg. 35
57'41"N
<DMS5> display as eg. 35
57'41.8"N
<UC> show in upper case
<LC> show in lower case
<ST> start edit at beginning of text instead of the end
<NOA> do not add new text to help entries
<NOE> switch field off by default on text and HTML exports
<HTM> do not convert newlines and '<'/'>' characters in this field when exporting HTML
<AP1> append help text instead of replace (with comma separator)
<AP2> append help text instead of replace (with comma+space separator)
<AP3> append help text instead of replace (with space separator)
<AP4> append help text instead of replace (with '/' separator)
<AP5> append help text instead of replace (with ' / ' separator)
Information
<INF> display this field contents in 'Show Record Info' as well as the record number.
Lookups
<MXW> forces help/lookup popup width to be maximum instead of related to the target field width.
<L2F> makes a custom lookup/help popup display the first two text fields instead of only the first one.
<L3F> makes a custom lookup/help popup display the first three text fields.
<L4F> makes a custom lookup/help popup display the first four text fields.
For more information on each item, search for the modifier in this helpfile.
Field Types2
Field Types (Unsupported)/
DBase Imports/Exports1
Calculated Text4
International Support
Merging6
Field Modifiers8
Text Modifiers
<UC> and <LC> can be used as field modifiers to display the field text in upper- and lower-case respectively.
These modifiers do not affect how data is input or stored.
Custom OPL Routines9
Variables:
Password<
HTML Output=
List View>
2Calculations - Cross Referencing Fields with xRef:@
Lookups with FiltersB
}Hv1.15+ incorporates the powerful <SQL> field modifier.
When the <SQL> modifier is added to a numeric, datetime, or text field type, it enables an automatic filter to be applied based on the current record's values for those fields. This can be quickly applied by pressing the keyboard shortcut shift-F
For example, a list of orders may have (text) 'client' and (numeric) 'order' fields. Adding the <SQL> field modifier to these would result in a query like :-
WHERE client='My Client Ltd' AND order=1234
or whatever the current values for these fields were. This provides a simple method of creating a relational-style sub-table without overcoming the display size constraints and lack of true table joins in EPOC databases.
As a result of this, it has been necessary to deprecate the auto-generation of time data for DateTime fields that are set to display only dates. Applying SQL filters to dates has always worked fine provided that less-than/more-than rules were applied. However, absolute date queries would fail because SQL only functions with absolute dates and cannot check the time component of the DateTime field which was invariably not set to 00:00:00 (12:00:00 am)
v1.15+ therefore sets the time component of DateTime fields to zero for all new DateTime fields that are set to display date-only. This modification affects new dates created with the <NOW> modifier or when a blank datetime field is edited. It also affects existing fields that are updated automatically by the <MOD> modifier. DateTime fields set to display both date and time, or time-only, are unaffected. It is still possible to enter a specific time or use the 'Now' button in the DateTime editing dialog.
This has the (small) potential to prevent existing databases from working as expected, so this behaviour can be prevented by a custom OPL routine that includes the following line in the userauto: procedure:-
forcezero%=0
It is recommended that the <NOE> field modifier is used in conjunction with <SQL> to optimise the HTML output with the autofilter activated (because the current autofilter fields are shown at the head of the HTML report when the autofilter is activated).
@The 'Ref Table' or 'Calculation' entry box on the Field Properties dialog may contain flags to fill the field with data when creating a new record.
Text, datetime, and numeric fields can use the 'Copy Down' flag which is indicated by :-
You can define a default value for a field by giving it the <DEF> flag followed by the value you want to use as the default. A typical use might be to set the default for a Bit field to 1 by using:-
<DEF>1
Numeric fields can also use the 'Increment' flag which is indicated by :-
In the <CD> and <+1> cases, the autofill data is based on the record which was being viewed when the new record is created.
If you use the <CD> option in a field that has a Lookup or Help text option, the flag must follow the reference table name and must be separated by a tab character (or at least one space or comma character).
The DateTime fields can use the <NOW> #
option to default to the date and time the record was created or the <MOD> option to update the field to the current time whenever a recalculation occurs.
The calculation options may also contain the summary operators :-
<CNT>, <SUM>, <AVG>, <MIN>, <MAX>
(note 'CNT' is used to indicate 'COUNT')
If you use summary operators on fields that also have calculations then you should enter the calculation first, followed by a tab (comma and space are still supported but the tab key is preferred), and then follow this with the summary operator(s).
DHLPe
Introduction
Copyright
Disclaimer
Getting Started
ContactaThe author/copyright holder may be contacted as follows :-
Email : kevin.millican@altavista.net
Indexes
Searching
Aliases
Lookups
Stopwatch
Calculated Fields%
Courier
Number Entry
Autofill Flags
Date/Time Fields*
Real Number Fields
Editing Data
xGPsiDat applications can be customised by embedding a user-defined OPL excutable (*.OPO) file in the zPsiDatDefaults table.
To do this, an extra record must be created by opening the zPsiDatDefaults table directly. The OPO file must be embedded in the 'Picture' field of the 2nd record. [It's also possible to lock the zPsiDat tables by putting a password in the DefTable field of the 2nd record].
When the database is opened this file is extracted to the PsiDat application directory, given the filename <mydb>.OPO - where <mydb> is the name of the database. This OPO file is then loaded using LOADM - making the users procedures available as long as the database is open.
There are two special PROCs that PsiDat will use automatically if present in the users OPO file:-
usermenu: and userauto:
Neither have any parameters.
usermenu:
This replaces the built-in menu and may be used to disable any of the menu options that do not have keyboard shortcuts. It can also be used to add extra functions specific to the database.
userauto:
This procedure is called just before entering the main editing loop after successfully opening a database table. It can be used to automate user routines on starting up the database.
To avoid clashing with any PsiDat routines, it is recommended that all GLOBAL user variables are preceded by 'u_' (eg. u_num% instead of num%) and all procedure names, except the special ones mentioned above also start in this fashion (eg. PROC u_save: instead of PROC save: )
However, some unused global variables have been initialised at the start of the PsiDat program for the sole use of the user's custom code as follows :-
GLOBAL u(100),u%(100),u&(100),u$(20,255)
[None of the above variables are used by PsiDat itself.]
In addition, all of the PsiDat global variables and routines may be accessed in the user's executable file [See Variables]
[See UserMenu: for a Menu Template]
WThe following is the current OPL source for the main menu and can be used as a template for a UserMenu: procedure within a custom OPL routine. It also demonstrates a trivial use of the UserAuto: procedure.
INCLUDE "BMP.OXH"
INCLUDE "DATA.OXH"
INCLUDE "DBASE.OXH"
INCLUDE "DBUTILS.OXH"
INCLUDE "DATE.OXH"
INCLUDE "SYSTEM.OXH"
INCLUDE "SYSRAM1.OXH"
INCLUDE "GPRINTER.OXH"
PROC usermain:
PRINT "This routine is only meant to be run within PsiDat"
PRINT "as an embedded user extension..."
PROC usermenu:
REM Global variables used in user routines should be
REM defined here too
LOCAL m%,mypos&,tempalias%,sm$(255)
GOTO protected::
PRINT "**THIS PROCEDURE IS PROTECTED**"
protected::
ONERR handerr
IF browsemode%=1
sm$=AutoFilter$:
ENDIF
REM For normal files browsemode%=1
REM For built-in Data application browsemode%=2
REM repdefined% is true when a report layout is defined or loaded
(.txt)