[FAQ] FileMaker Pro - database for Macintosh and Windows

Archive-name: databases/filemaker-pro/faq
Posting-Frequency: monthly
Last-modified: 1996/10/09
Version: 3.1
URL: <ftp://ftp.th-darmstadt.de//pub/database/filemaker-pro/[FAQ]%20FileMaker%20Pro> "Text release"
URL: <ftp://ftp.th-darmstadt.de//pub/database/filemaker-pro/faq.fp3.sit> "FMP 3.0 template"
Changes: indicated within the table of contents by "!"


---------------------------------------------------------------------------
Frequently Asked Questions on FileMaker Pro

1 Where can I get information from
! 1.1 Internet sites
! 1.2 Software and templates
! 1.3 Newsgroups
! 1.4 Mailing lists
! 1.4.1 FMPRO-L
! 1.4.2 FMPRO
! 1.4.3 FMPRO-CGI
! 1.5 Forums
! 1.6 Books

2 Version History
2.1 What is the current version of FMPro for Macintosh
2.2 Is Filemaker Pro relational
2.3 When will FMPro 3.0 be released
! 2.4 Features of FMPro 3.0
2.5 FileMaker Pro Server

3 FMPro 3.0
3.1 Think relational
! 3.1.1 Many to many relations (m:n)
3.2 What are portals?
3.2.1 Sorting Portal Records
3.3 Bug & Wish List
3.4 Relational Examples
3.4.1 Self Joining list
! 3.5 Globals

4 Field Formats and Index Values
4.1 What do I need index values for?
4.2 Lookups
4.3 Text formats and Index basics
4.3.1 Character translation for indexed values
4.4 Printing index fields
4.5 Number format
4.6 Date format
4.7 Time format
4.8 Hidden fields
! 4.9 Sorted lists

5 Repeating Fields
5.1 Convert repeating fields to non repeating fields
5.2 Convert nonrepeating fields to repeating field
5.2.1 How to create ASCII-29
5.3 Merge separate records to a single repeating field
5.4 Removing duplicates or Sychronizing databases
! 5.4.1 Removing duplicates - another approach

6 Find
! 6.1 Some facts
! 6.2 Perform scripted find
! 6.3 No matching records found
! 6.4 Find on multiple fields
! 6.5 Find with AND / OR / NOT
! 6.6 Find on a subset

7 Scripting
7.1 ScriptMaker
7.1.1 What's that restore option? - Find & sort within a script
7.1.2 Special options
7.1.3 Documenting scripts
7.1.4 Recursive and Conditional scripts
7.1.5 Dummy entries
7.1.6 Go to last record
7.1.7 Schedule Scripts
7.2 AppleEvents
7.2.1 FMP documentation
7.2.2 Classes
7.2.3 References
7.2.3.1 References table
7.2.4 List of events
7.2.5 Sending events
7.3 AppleScript
7.3.1 System requirements
7.3.2 General documentation
7.3.2.1 Books
7.3.2.2 Online documentation
7.3.4 Tips
7.3.5 Examples
7.3.5.1 Load all image files
7.3.5.2 Idle handler
7.3.5.3 Object not found / error -1728
7.3.5.4 Multiple finds
7.3.6 Call AppleScript
7.4 Aretha / Frontier
7.5 Other tools
7.5.1 Quickeys
7.5.2 KeyQuencer
! 7.5.3 Tools for Windows

8 Miscellaneous Tips
8.1 Removing password
8.2 Undocumented features
8.3 Shortcuts
8.4 Compatibility between DOS and Mac release
8.5 Buttons
8.6 Resources for dialog boxes
8.7 The special name "-" as list separator
8.8 WWW, HTML and CGI
! 8.8.1 HTML text conversion
8.9 Comments within calculations
8.10 Empty fields
8.11 Sort Order
8.12 Switch printers
8.13 Error Codes
8.14 Get User's Name / Limit Access to Certain Users
! 8.15 Use // as literal text
! 8.16 Faster Screen Redraw
! 8.17 Date calculation
! 8.18 Displaying Data as a Table
! 8.19 Zoom window size

9 Data Exchange
9.1 Copy a layout from one database to another
9.2 Modify layout objects
9.3 Copy a record or list of records to other applications
9.4 Export / Import
! 9.5 Export formatted text / RTF
! 9.6 Access Files Remotely

10 Useful Tools
10.0 Useful tools
10.1 Print2Pict
10.2 Acid Jazz etc.
10.3 BBEdit Lite
10.4 Ram Disk
10.5 Send Mail from within FMP
10.6 Dump Mail to FMP
10.7 Fax from FMP
10.8 Speed Doubler
10.9 ROFM CGI
! 10.10 GTQ library

11 Special Solutions
! 11.1 Templates for collecting CDs
! 11.2 Numbers to Text

12 About
! 12.0 About

------------------------------

Subject: 1 Where can I get information from

1.1 Internet sites

Claris WWW
(updaters, templates, technical support articles, trial software and
product literature):
http://www.claris.com
Claris FAQs on FileMaker Pro 2.x:
http://www.claris.com/TS/FAQs/IndexFileMakerPro2.x.html
http://www.claris.com/TS/FAQ/FileMakerPro2.x.html
Claris FMPro and HTTP (featured article):
http://www.claris.com/TS/Featured/FMProCGI/Article.html
Claris FTP server (anonymous downloads):
ftp://ftp.claris.com
Claris Tech Info Database (more than 4000 searchable articles):
gopher://spinaltap.micro.umn.edu/70/...aris/FileMaker
gopher://spinaltap.micro.umn.edu:70/77/computer/Claris
http://www2.claris.com/techinfo/
http://www2.claris.com/cgi-bin/eg/techinfo_search.html "search engine"
Tips from the File Maker User Group (FMUG)
at Massachusetts Institute of Technology (MIT):
http://web.mit.edu/mugs/www/fmtips.htm
Misc. sites collected by Joel Bowers <jmba@BLUEFIN.NET>:
ftp://ftp.th-darmstadt.de//pub/datab...pro/faq.fm.sit "Home
site of the FAQs, compressed database release"
http://www.cis.ohio-state.edu/hypert...es/filemaker-p
ro/faq/faq.html "FAQ monthly html release"
http://www.kudosnet.com/ISO/ISOmain.html "ISO Magazine"
http://rowen.astro.washington.edu/ "ROFM CGI AppleScript OSAX"
http://macweb.com/ "WWW server for FMP"
http://www.amug.org/~hposoft "CSA member"
http://ape.com/Diana/
http://www.blueworld.com/lists/fmpro/ "mailing list FMPRO"
http://www.bluefin.net/~jmba/ "CSA member"
http://canstock.com/
http://delcom.accsyst.com
http://www.icon.co.at/icon/e-filefax.html "Send Fax"
http://www.in.net/~wrskas/Index.html "templates for barcharts, FMP3"
http://www.macvantages.com/ "CSA member"
http://www.magic.ca/~octal/octal.html "CSA member"
http://www2.pps.ca/emplys/kb/kbhome.html
http://www.shore.net/~tsginc/FM.htm "CSA member"
http://www.syspac.com/~suncou
http://www.tiac.net/users/eweiser/index.html "CSA member"
ftp://ftp.th-darmstadt.de//pub/database/filemaker-pro/ "Plain text FAQs,
old postings of FMPRO-L, various templates"
http://www.Xon-Xoff.com "French User Group"
http://www.venus.it/FMP [Italian]
http://www.algonet.se/macboden/ [Swedish]
More Links that may have some FileMaker Connections
http://www.artscope.com
http://www.macsite.com/macsite/searcher.html
http://www.intac.com/~salenick/sa.html
http://users.aol.com/stevem7994/DPpa...lyPlanner.HTML
http://www.powerfiles.com "PowerFiles - A good source for Database
Solutions, Templates, Demos and Tips (FMP 2.x and 3.0)." (by Allison
Glass)

1.2 Software and templates

sumex-aim.stanford.edu (info-mac) and its mirrors, most in directory
Applications and Database, e.g.:
ftp://ftp.switch.ch//mirror/info-mac/app/
ftp:/info-mac/app/
ftp:/info-mac/data/
(a notation supported e.g. by Anarchie, a ftp and archie software by
Peter Lewis, to get it from your favorite mirror)
Umich and its mirrors, e.g.:
ftp://mac.archive.umich.edu/mac/util/filemaker/
ftp:/mac/util/filemaker/
Archie:
Many templates contain either .fm or FMP within the name - so you may
perform an archie or index search on those characteristic expressions.
Claris:
ftp://ftp.claris.com/pub/USA-Macintosh "Software"
ftp://ftp.claris.com/pub/USA-Macintosh/Updaters/ "Updaters"
ftp://ftp.claris.com/pub/USA-Macinto...ions_Showcase/ "Solutions"
ftp://ftp.claris.com/pub/USA-Macintosh/Templates/ "Templates"
ftp://ftp.claris.com/pub/USA-Macintosh/Trial_Software/ "Trial_Software"
America Online:
Business Section/Database Support/ "FileMaker Pro Templates"
CompuServe:
Claris Support
Claris BBS:
The Claris BBS is a file upload/download service. You can download
updaters, trial software, templates, and frequently asked questions.
Call 408-987-7421 with your communications software set for 8/N/1 to
connect at speeds up to 14,400 bps.
Claris Solutions Alliance:
Commercial Database Developpers. Demos and Solutions are available on
the Claris sites.
(see information in section Forums)
Demos:
http://www.ccsi.com/n-perspect
http://www.magic.ca/~octal/octal.html
Commercial templates and free tips to AppleScript and FMP.
http://www.amug.org/~hposoft/
Commercial templates, free tips and demos.

1.3 Newsgroups

news:comp.sys.mac.databases
Posting concerning FMPro may appear in comp.sys.mac.apps,
comp.sys.mac.misc, comp.databases, de.comp.databases, several local and
IBM-PC related groups as well, but I highly recommend to keep them in
comp.sys.mac.databases only.

1.4 Mailing lists

FMPRO-L is the main list on FileMaker Pro where these FAQs were composed
from. It is a well attended list with about 20 messages per day.
There are currently two further lists for discussion of Filemaker Pro
topics available: The regular Filemaker Pro list (fmpro@blueworld.com),
and one for discussion of Filemaker Pro CGIs (fmpro-cgi@blueworld.com),
both created in August 1995. They offer WWW support on
<http:www.blueworld.com>, e.g. to subscribe and search for old articles
(searchable archive).

1.4.1 FMPRO-L

send to this list: FMPRO-L@LISTSERV.DARTMOUTH.EDU
Claris FileMaker Pro discussions
This mailing list is for discussions on all aspects of the FileMaker Pro
database development environment, published by Claris Corporation. Both
the Mac and the Windows versions of the program are welcome topics of
discussion, as are future speculations on the direction of the
environment. Posters should refrain from submitting enclosed database
files to the list, and any freeware and shareware databases should be
submitted to the appropriate Mac and/or Windows anonymous ftp sites. On
the Macintosh side, these are sumex-aim@stanford.edu and
mac.archive.umich.edu.
To subscribe to this list:
Send to: LISTSERV@listserv.dartmouth.edu
Subject: subscribe
Message: subscribe fmpro-l <your full name here>
To Unsubscribe from this list:
Send to: LISTSERV@listserv.dartmouth.edu
Subject: unsubscribe Message: SIGNOFF FMPRO-L

1.4.2 FMPRO

send to this list: fmpro@blueworld.com
"This list was created after consulting with several prominent FMPRO
developers as well as Claris Tech., all of whom had no knowledge of an
existing FMPRO list. As well, we've created the associated FMPRO-CGI
list to allow for an adequate forum for that specific topic." (from
bill@blueworld.com (Bill Doerrfeld))
Commands should be put in the message body of the message sent to the
list request address, "fmpro-request@blueworld.com". Here are the
commands currently recognized by this list:
To subscribe to the list fmpro: subscribe fmpro
If you need a digest version of the list, use the command: subscribe
digest fmpro
This command unsubscribes you from one of the lists: unsubscribe fmpro
To get the help file, send: help

1.4.3 FMPRO-CGI

send to this list: fmpro-cgi@blueworld.com
"CGI stands for "Common Gateway Interface". The CGI definition provides
a standard for external gateway programs to interface with information
servers such as MacHTTP. If You're running Your own WWW-Server, You can
define FORMS, that Users can fill out. The contents can go into a
FMP-Database - using these CGI's)
More Information: http://hoohoo.ncsa.uiuc.edu/cgi/overview.html "
(from schneima@uiag.com (Markus Schneider))
Commands should be put in the message body of the message sent to the
list request address, "fmpro-request@blueworld.com". Here are the
commands currently recognized by this list:
To subscribe to the list fmpro-cgi: subscribe fmpro-cgi
If you need a digest version of the list, use the command: subscribe
digest fmpro-cgi
This command unsubscribes you from one of the lists: unsubscribe
fmpro-cgi
To get the help file, send: help

1.5 Forums

Full-service Support Forums
(from Jim_Spelman@iguanabbs.com)
Claris does not provide person-to-person support via the Internet.
[They] invite you to visit [their] full-service support forums on the
online services below, which include discussion boards monitored by
Claris Technical Support staff, TechInfo database, and extensive
software libraries. [Their] forums on America Online and eWorld also
offer product registration and ordering.
America Online: keyword CLARIS
CompuServe: GO CLARIS
eWorld: shortcut CLARIS
AppleLink: Third Parties: Claris or CLARIS.TECH
Claris BBS 408-987-7421
Contact the online service of your choice for a membership kit. Prices
and policies current as of 12/1/94. All prices are in U.S. dollars
unless specified otherwise.
America Online, 800-827-6364 in U.S. or 1-703-448-8700
Free kit, 10 hour free trial offer, $9.95 per month includes 5 hours
free time, $3.50 for each additional hour.
(keyword: CLARIS)
CompuServe, 800-848-8199 or 1-614-529-1349 in the U.S., (+44)
272-760-681 in Europe, 9-9 London time
Kit is $25 and includes $25 usage credit for first month. The $25 is
refundable if you discontinue during the first month. Subscription price
is $8.95 per month for access to basic services, with extended services
being $9.60 per hour at 2400 baud and $16 per hour at 9600.
(keyword: GO CLARIS)
eWorld, 800-775-4556 or 1-408-974-1236
Free software kit offer until 12/31/94. Offer good in U.S. only. Monthly
fee of $8.95 which includes 2 hours of use during non-prime time (6 PM
to 6 AM local time). Each additional hour is charged at $4.95 per hour.
An additional $2.95 per hour will be charged for all prime-time usage (6
AM to 6 PM local time).
(go to shortcut: CLARIS)
AppleLink, 800-877-8221
Kit is $70. Monthly minimum charge of $12. Connect charges at 2400 baud
are $12 per hour plus a 5.5 cent surcharge for every 1000 keystrokes.
Rates for 9600 baud are $37 hour with no surcharge.
(Third Parties: Third Parties A-G: Claris)
Claris Solutions Alliance:
Membership $249 per year. "Our goal is to provide consistent,
industry-leading marketing service and support to third-parties creating
new Windows and Macintosh solutions that address the specific business
productivity needs of users." Contact Claris, P.O. Box 58168, MS C-22,
Santa Clara, CA 95052
800-800-8954 (FAX),
408-727-8227 (Customer Assistance)
408-727-9054 Technical Support Macintosh
408-727-9004 Technical Support Windows
Directory from Claris Customer Assistance (408/727-8227). This is a
150+ page book listing Consultants, Trainers and Custom Developers of
Claris Products.

1.6 Books

Various books are available, from beginner to advanced users.
See www.claris.com
Stephen Cobb, Chey Romfo: 'The Stephen Cobb User's Guide to FileMaker'.
Windcrest (Blue Ridge Summit, PA)
Rich Coulombre, Jonathan Price: 'FileMaker Pro 2.0 for Macintosh'.
Addison Wesley Publishing Company, 1993,
ISBN 62212-2, Pages/Price unknown
Guy Kawasaki: 'Database 101'. Peachpit Press Inc., 1991
Maria L. Langer: 'FileMaker Pro 2.0 for the Mac in a Nutshell'.
SYBEX,1993
Charles Rubin: 'The Macintosh Bible Guide to FileMaker Pro'. Peachpit
Press Inc., 1993
Charles Rubin: 'The Macintosh Bible Guide to FileMaker Pro 2.0'.
Peachpit Press Inc., 1993
Michael Singer: 'Database Design with FileMaker Pro'. John Wiley & Sons,
ISBN 0-471-02022-2, $ 24.95 (3/94), Pages unknown
Barrie Sosinsky: 'Using FileMaker Pro 2.0 for the Mac'. Que Corp, 1992
Barrie Sosinsky: 'Using FileMaker Pro 2.0 for Windows'
Books for FMP 3.0:
C. Ann Brown: 'FileMaker Pro 3 for Macintosh: Visual QuickStart Guide'.
Peachpit Press, 1996/04, ISBN 0-201-88357-0 (1995/10), 256 pages, $16.95
Don Crabb, Jeff Gagne: 'The Guide to FileMaker Pro'. Hayden Books
Alex Kennedy: 'Filemaker Pro 3.0'. Addison Wesley, 1996/04, ISBN
0-201-87762-7 (1995/12), 512 pages, $39.95
Tom Maremaa: 'FileMaker Pro for Macs for Dummies', IDG Books Worldwide,
Inc., 1995/12, ISBN 1-56884-906-0 (1995/03)
Chris Moyer: 'Special Edition Using FileMaker Pro', Que, 1996/01, ISBN
0-7897-0662-8 (1995/11), $34.99 (Retail Price)
Steven A. Schwartz / MacWorld: 'FileMaker Pro 3.0 for Mac', 2nd Edition.
IDG Books Worldwide, 1996/03, ISBN 1-56884-728-9 (1995/09), $34.99
Charles Rubin: 'The Macintosh Bible Guide to FileMaker Pro 3.0'. 3rd
ed., 1996/04, Peachpit Press, ISBN 0-201-88356-2 (1995/10), 496 pages,
$24.95
Course Material:
John Latrash: 'Claris FileMaker Pro 3.0 Macintosh and Windows 95: Quick
Course'. South-Western Educational Publishing
Newsletters:
'The FileMaker Report', published 10 x a year,
Elk Horn Publishing / P.O. Box 1300 / Freedom / CA 95019 / Phone (408)
726-1232 / Fax (408) 726-1233
16 pages $65 per year
'Inside FileMaker Pro', published monthly
The Cobb Group / P.O. Box 35160 / 9420 Bunsen Pkwy, Ste. 300 /
Louisville, KY 40220 / 40232-9719 / Phone: 800-223-8720
12 pages $39 per year
The ISO Magazine
<http://www.kudosnet.com/ISO/ISOmain.html>
FMPro online user magazine / ISO <ISOeZine@aol.com>
First year subscriptions are free
Audio and Video Cassettes:
check www.claris.com

------------------------------

Subject: 2 Version History

2.1 What is the current version of FMPro for Macintosh

FMPro 3.0:
Macintosh: 3.0v3 (US), other national releases
Windows95: 3.0
Win3.1: ?
Windows3.1: announced for June 1996
(source: BUSINESS WIRE--April 23, 1996, from Steve Dell
<stevedell@EARTHLINK.NET>)
trial software:
<ftp://ftp.claris.com//pub/USA-Macint...leMakerPro3.0v
2Trial.sea.hqx>
<ftp://ftp.claris.com//pub/USA-Windows/Trial_Software/fmtrial.exe>
updaters from 3.0v1
<ftp://ftp.claris.com//pub/USA-Macintosh/Updaters/>
FMPro 2.1:
US: 2.1v3
trial software:
<ftp://ftp.claris.com//pub/USA-Macint...leMakerPro2.1v
3.sea.hqx>
updater from 2.0:
<ftp://ftp.claris.com//pub/USA-Macint...erPro2.1v3.sea.
hqx>
<ftp://ftp.claris.com//pub/USA-Windows/Updaters/fmup21v3.exe>
and on info-mac mirrors
German: 2.1Dv3
updater from 2.0 available on www.claris.com

2.2 Is Filemaker Pro relational

FMPro 3.0: Yes. It supports many-to-one and one-to-many relations.
Many-to-many relations are possible by a manual table.
As a personal statement: I doubt that FMP fulfills every definition of a
relational database. My experience is that it supports various statical
(manual) relations and works as a kind of "bidirectional lookup". It
lacks dynamic relations based on other criteria such as a field or file
name.
FMPro 2.1 has some features, e.g. lookup fields, that may perform some
relational tasks.

2.3 When will FMPro 3.0 be released

It's available since 18/12/1995.
Pricing is:
Mac $199
Win(95) $199
upgrade $99
The runtime version should be available for CSA members from March '96
on.

2.4 Features of FMPro 3.0

Relational capabilities:
* Supports one-to-many, and many-to-one relations
* many-to-many relations can be masked to one-to many relations
* Relations are defined in a define relations dialog box. Where you
relate the current file to another, and then build relationships from
field to field.
* Has a portal tool that let you view data from one file in another
files layouts. You define a portal area then drag an external field into
it.
* Lookup functions remain.
User Interface:
* Button drawing and defining is done in one step.
* Buttons in 3D-look
* Layouts can be hidden
* Possibility to toggle window refresh on and off
* Fixed paper margins
* Redirect Cancel command
Scripting
* If..then..else (conditional) statements in scripts
* Loops
* Support for relational requirements
* Improved halt or pause of scripts (while other scripts execute)
* Replace field by results of a calculation (Set field, Paste result)
* Copy record, Copy all records to clipboard as tab-delimited text
* No Paste of multiple fields in one step
* Scroll window instead of (Home, Page Up, etc.) with freeze or zoom
percentage
* Comment lines within scripts
* Perform on exit script
* In support of new features:
Revert Record/Request, Exit Record/Request,
Toggle Text Ruler, Set Use System Formats, Recover,
Show Message, Beep,
Change Password, Set Multi-User
Flush Cache to Disk (Save changes)
* PlainTalk support (Mac only)
* Better support for Apple events and Applescript (Mac only)
* Send/execute of complete AppleScripts (Mac only)
* Object Linking and Embedding OLE: insert, update (Windows only)
* Dynamic Data Exchange DDE: send (Windows only)
* Send mail (Windows only)
* No copy/paste of scripts/scriptsteps
Calculations
* New functions:
LeftWords, MiddleWords, RightWords
PatternCount, WordCount
Substitute, Occurence
GetRepetition
GetSummary
DayOfWeek
* About 30 status messages
* Loops: Loop...ExitLoop
* Field verification (validation) by calculations
* User-specified error message
* Autoenter of data via calculations (entered data is editable)
* Global fields, entered only once
* Custom date format
Data storage and exchange
* file size maximum expanded to 2 gigabytes (previously 32MB)
* ability to open up to 50 files simultaneously (previously 16 files)
* Conversion from FMP 2.1 by simple "point and click" process
* Indexing of fields only on demand
* Display index by word or field
* Database opens with default password
* Drag and drop text from one field to another and to other applications
* Export functions are improved, making it easier to select and arrange
fields for export
* Automatic generation of a new database from text files, spreadsheets,
database files
* The TCP/IP support will let you network cross-platform over LAN's and
the Internet.
* Phone dialing through speaker or modem
* The file limit will be bigger and it can support 50 simultaneous
users.
* Containers instead of Picture/Sound, additionally for movies and OLE
(Windows 95). Data is held internal or as file reference
* No SQL support by FMP itself, possible via third party products
* Only AppleScript as interface (Mac only)
Text editing
* Mail Merge within FMP (more powerful than sliding objects)
* Individual paragraph formats
* Text with Tabs and fill characters
* Rulers within text fields
* Text formatting tool bar
* with font, size, alignment, tab stops, character styles
* Vertical adjustment of text within field (Top, bottom, centered)
* Field contents can be included in text with markers
* No hyphenation or soft-hyphens
* Replace data based on calculation (even calculations on itself)
* Revert record as undo on complete record
* Value listings improved, e.g. as list from related fields
System Requirements Macintosh:
* System 7.0 or later, recommended 7.1 or later
* CD-ROM or 1.4 MB disk drive
* 4 MB RAM, recommended 8 MB for 680X0 Macintosh, required 8 MB by Power
Macintosh
System Requirements Windows 95:
* Intel compatible PC
* Windows 95 or Windows NT 3.51 or later
* 8 MB RAM, recommended 16 MB for Windows NT
* CD-ROM or 3.5" disk drive
("Claris will consider adding support for Windows 3.1 for a future
version of FileMaker Pro [...] based on the speed of migration to
Windows 95..."; MS Access also doesn't support Windows 3.1)
(From a Claris press release, 24/08/95)

2.5 FileMaker Pro Server

Q. What exactly is FileMaker Pro Server 2.1?
A. FileMaker Pro Server now supports the Novell IPX/SPX network protocol
(using the Novell MacIPX system extension) in addition to the AppleTalk
protocol supported in the previous version of FileMaker Pro Server.
FileMaker Pro Server also introduces multi-protocol support for
cross-platform workgroups.
(From a Claris press release, 24/08/95)
>> Further information in this press release, dated 24/08/95, is
available.
>> Is anyone willing to compose an appropriate description? See:
<ftp://ftp.th-darmstadt.de//pub/datab...emaker-pro/New FM Pro 3
Info>
keywords: multi-protocol, performance improvements of FMP Server 2.1,
68K/PPC machines only, upgrad price $99 (after 3/95 free), up to 100
users may now open up to 16 files

------------------------------

Subject: 3 FMPro 3.0

3.1 Think relational

FileMaker Pro 3.0 is a further improvement of the simple, flat database
concept of a table.
It supports "relational capabilities". What's this? If you knew FMPro
2.1, it's a kind of editable, bidirectional lookup.
A relation is a kind of static connection when a certain field of a
record of a database matches its counterpart somewhere else. Based on
this relation you may get the contents of other related fields to this
matching record.
The basic relation is from one field to another one, a one-to-one
relation. Example: a relation from a customer database to get related
data from an address database, based on the matching customer id.
You may have multiple records that share the same information - this is
a many-to-one relation. Here the concept of relations demonstrates its
major power: you change the information only once, and immediately it
becomes available for all other records that hold a relation to this
information. Example: A customer ordered multiple times. His address is
still the same.
Some definitions on database concepts (by demey@hgins.uia.ac.be (Hendrik
Demey)):
* A relationship: a "virtual" link between 2 different databases (based
on a link between 2 *identical* fields in these 2 databases), allowing
information from database 1 (the parent) to be displayed in database 2
(the child). The relationship is based on an exact match between two
fields in the parent and child database. The trigger field is also
called a "primary key". When a child database looks at information from
its parent, it will display this "primary key". Some databases are
constructed to display information from several "parents", in that case
all the "primary keys" are called "foreign keys", because they uniquely
identify information in other databases.
* A related field: Information in a related field exists only in another
(the parent) database, and not in the database in which this field is
being looked at (the child). Related fields are represented by their
name being prerended by a double colon (::related_field_name). If You
try to export all the info of a certain record to a tab delimited file,
starting from a database with one or more related fields, based on one
or more relationships, the info from the related field is not exported
to this text file, as it does not exist in this specific child database,
but only in its related parent.
Comment: you may include related records for export. This will include
EVERY matching record! Example:
Field 1, Related::Field 2 will export as
Key1,"The first related field"
,"Another related field, matching to Key1, too"
* A portal is a new kind of lay-out tool in FM3, by which You create a
"window" inside the child database enabling You to view the related
fields from a parent database. In a one to many relationship (e.g. a
database of a school, with the parent a list of all pupils, the class
they attend and their home address; the child is a database of all
classes. Selecting a specific class will give a view of all pupil
records attending this specific class), this portal will present a list
view of all related records, based on a specific relationship. A
specific portal can only be based on *only one* relationship. In a
datase, several relationships can be created to different (or the same,
even the same child) files, therefore, more than one different portals
can be created. If specifically enabled, it is possible to create new
records with new information (e.g. a new pupil entering a certain class)
in a portal, that will be posted into the parent database. Again, if
specifically enabled, records viewed through a portal can be deleted
from their parent, while being looked at from the child database.
* A lookup physically copies information from the parent database, again
based on a specific relationships (this is different in FM3 from earlies
versions!). Exporting a tab delimited text file from a record contains
also the info carried over from a related file through a lookup field.
Information in a lookup field is static. Once copied over from the
parent database, the information inside this lookupfield stays
unchanged, even if the content of the parent field in the parent databse
is changed in the meantime. Only if specifically asked by a relookup
command, or by changing the information in the trigger field (the one on
which the relationship of this lookup is based) will a new relookup be
performed by FM3 and the content of the lookup field be renewed/matched
to the parent. Existing contents will get overwritten.
* Pros and cons of related fields/portals versus lookup fields:
- Information in a portal or related fields is dynamicaly updated, each
time the information in the parent database is changed. Lookup fields
are static.
- Related databases are smaller than databases based on lookup fields.
- Related databases present consistent information.
- Lookups present static information.
Take a set of invoices and the tax amount (a percentage) based on a
related field. If You change this tax level, all invoices (included
older ones) will represent this new value, and all invoices (including
the older ones) will be recalculated. With a tax based on a lookup
field, the information in the older invoices remains unchanged, and
therefore correct. Only newly created invoices will be based on the new
tax level. (Insofar a relookup order is not being issue!!!)
- In a lookup field, an exact match is not necesssary; they are more
flexible regarding the match field to copy next higher or lower matching
values. In a relationship an exact match is an absolute necessity in
order to obtain related records. The dialog box of the lookup definition
contains 4 radio buttons, enabling the user to define an exact match,
copy an item lower or higher than, or put up a dialog box with a
message.
* Types of relationships:
- One-to-one: only one record corresponds to a certain trigger.
Information based on a one-to-one relation can easily be entered in the
same database, in one record.
- One-to-many: one record in the parent corresponds with multiple
records in the child (or vice versa). This kind of information is
represented on the "layout" of an FM3 database by a related field (for a
many-to-one) or a portal (for a one-to-many).
- Many-to-many: several one-to-many relationships exist at the same
moment, and between the same groups.

3.1.1 Many to many relations (m:n)

There are multiple problems on defining many-to-many / m:n relations. FMP's solution is
not to support them directly. However, you may use them by mapping a m:n relation
manually down to m:1 and 1:n. For that purpose you may have to create a separate file.
Example: There's a nice learning example with multiple teachers and pupils. A teacher may
have different pupils, a pupil several teachers.
Teacher database M:N database Pupil database
Key, Teacher Name Teacher key, Pupil key Key, Name
T1, AAA T1, P1 P1, ZZZ
T2, BBB T1, P2 P2, ZZX
T3, CCC T2, P2 P3, ZZY
Now you may get every pupil taught by teacher AAA, or any teacher of pupil ZZX.
Workaround:
Relations within FileMaker Pro are based on indexed lines. A line is composed of all
characters up to the end of line (in fact it's an end of paragraph, entered by <return> or the
full, last line) with a maximum of 60 characters; from the 61st character and up is not
used. Only text fields should hold return symbols. You may use them e.g. for a one-to-may
key or a many-to-one key, and thus as well for a many-to-many key.
Example:
The teacher database may hold the record and fields
Teacher: T1, Pupils: "P1<return>P2"
This will relate to both pupils ZZZ (P1) and ZZX (P2)
Within the Pupil database you may use
Pupil: P1, Teachers: "T1<return>T2"
Now you may find from P1 all other pupils of his class of teacher T1.

3.2 What are portals?

Q: What are portals?
A: Imagine them as a window to look through at datas within another
database.
Actually, there are some limitations:
* In a portal, records are displayed in their order of creation only.
* Deleting portals should be done very carefully.
If you delete a record, and permit deletion of related records, you may
remove data that is still used for other records.
If you do not use deletions properly, you may delete by mistake actual
or related records.
A related record may contain more data than what you see within the
portal.
But here the Pros:
They are fast and flexible!
You may use them to navigate around quickly by the "Go to related
record" script command.
Additional note on sliding portals: From FMP 3.0v3 on fields must be
perfectly enclosed to slide properly.

3.2.1 Sorting Portal Records

Portals have no option to sort, but show the records in order of
creation.
There are various workarounds:
- For complete databases this will force a permanent, resorted order:
* sort the database in question in correct order,
* create a clone,
* import to the clone
- For small sets of records
1) Flag the records in the portal
2) Sort the records in the many file (contains the portal records)
3) Duplicate the sorted records (in creation order the duplicates are
already sorted)
4) Delete the flagged records
5) View the records in the one file through the portal
(reported by Kirk Bowman <bowman@ONRAMP.NET>)
Example from Ted Shapiro, NY:
----------
By the way, here's a not too horrible method for sorting the contents of
a portal, aka the SORTAL (tm):
- Many students have registered for one class.
- Each class has a unique ID#.
- In the CLASSES file there is a portal to view students in the related
file ROSTERS.
In CLASSES -- script: SORTAL Part 1
Freeze Window
Copy Class ID#
Perform script (External: ROSTERS "SORTAL Part 2")
Refresh Window
In ROSTERS -- script: SORTAL Part 2
Perform Script (Find & Flag Class ID)
Perform Script (Sort StudentLastName)
Perform Script (DupeLoop)
Perform Script (Find Flagged & Delete)
script: Find & Flag Class ID
Enter Find Mode
Paste (Select, "Class ID")
Perform Find
Replace (No Dialog, "Flag", "1")
script: Sort StudentLastName
Sort (Restore, no dialog)
script: DupeLoop
Go to Record/Request (First)
Loop
Duplicate Record/Request
Clear (Select, "Flag")
Go to Record/Request (Exit after last, Next)
End Loop
script: Find Flagged & Delete
Perform Find (Restore) --- this is find Flag=1
Delete All Records (No Dialog)
----
Another solution is to go via a separate database. Example: database 1
holds field A as trigger for portal B. A script finds all matching
values in database 3 and sorts them properly. Then the script lets
import all relevant portal data to a special database 2 that only holds
e.g. the portal data. The portal then gets all data from this portal
database 2 to database 1.

3.3 Bug & Wish List

Send suggestions and bug reports to www.claris.com on their suggestion
page.
http://www2.claris.com/forms/suggestion.html "Suggestions & Bug reports"
"James Fortier" <jim40er@halcyon.com> maintains a list that may be
available on
http://www.ecxs.com/filemaker/ftp/Fi...Folder.sea.hqx

3.4 Relational Examples

This section will describe unusual solutions for relations to
demonstrate the capabilities. Please forward your examples for
inclusion!

3.4.1 Self Joining list

In case you want to see a portal of records that match a valid search
criterion only:
"Now here's something else that's way cool about Self-joins. If you
want to generate aggregate data on only a subset of your database, you
can do so in the following manner:
1) Create a text field called, say, FoundSet.
2) Create a calculation field called, say, FoundSetIndex as a
concatenation of YourKeyValue (such as Customer ID, Client ID, or
whatever) & FoundSet. Make the result text.
3) Create a self-relation on FoundSetIndex, i.e., have the same file
point back to itself.
4) Define your aggregate function to be dependent on the related
(self-joined) field (this is important).
5) Find the records you want to work with
6) Go to the FoundSet field and replace the value in your current found
set with something like an "F". This makes only the current found set
records create the self-join.
7) Stand back and view in amazement the power of FMP3.
PS Don't forget to clear the value in the FoundSet field before you exit
the script."
(from Jim Burd <burdman@cl.k12.md.us>)
<value sensitive lists under construction, currently about 8
alternatives to verify and classify>

3.5 Globals

Globals are helpful im many ways.
They may hold variables, such as
- a predefined constant value that you use throughout your database
This may be e.g. a user-defined delimiter for phone numbers, a certain
key word, a signature, logo, warning etc.
- a variable value, such as the layout number, a buffer for some
calculation, replace, set field, copy or paste script step etc.
Globals are not as global as you might expect. When a database is shared
between multiple users, every user may modify the global value on his
own, although he may not be able to save this modified value.
Be warned that when you clone a database, the value of a global field
will be removed as well!
Global fields may be repeating fields. Thus you may use e.g. a
calculation or script to extract one of multiple choices for variables
or even pictures.
Global fields may not get indexed. This may look obvious, but results in
some drawbacks. A major one is visible for relations: They only may be
built on indexed values. But as soon as a calculation includes an
unindexed value, such as another unstored calculation or global field,
it may not get indexed any more.
BTW you get a warning when you build a relation on a global field
itself, but it still is possible. A suitable example is e.g. a dummy
relation (global field one to related field one, both contain the value
"1") to a shared database in order to use the same - maybe global -
information within different databases.
Any global field within another database may be accessed by any
relation, regardless how nonsensical or matching this relationship is.
"did you know you can still modify globals:
- even if the file is "locked" in the finder
- even if the file is on a locked disk/volume
- even if the access privileges are set to disallow edits
The only way to prevent mods are to set the access privileges for that
field to be read-only.
Interestingly, the first two attempts above allow changes which don't
get saved to disk, but the third does get saved - which could be a
"gotcha" if you assume otherwise."
(by Eric Scheid <ironclad@planet.net.au>)

------------------------------

Subject: 4 Field Formats and Index Values

4.1 What do I need index values for?

Most operations are based not on the value typed by hand, but on an
indexed value that was derived from the actually typed value.
Rapid find commands, comparisons, lookups and calculations all are
triggered on the index value.
The index of a field can be displayed by cmd-I. It is a list of all
indexed values, sorted in alphabetical order.
The index can be used to select and paste previously entered values
rapidly.
Index also works on check boxes and radio buttons - as cut, copy and
paste do.
Indexing works different on various field formats.
Indexing is not done immediately. At least after selecting the next
record, the index should get updated properly.
Due to conversions, bugs or system crashes, the index may get corrupted.
In this case find and lookup won't work properly. Importing in a clone
of the database will force the construction of a new index.
Calculations take the indexed value for comparisons. Such "A" = "a" is
true. If you want to use an exact comparsion, you may use the Exact
function. Exact("å", "a") then will return false.

4.2 Lookups

Lookups are based on the indexed values of all records. It will copy a
value from the first, matching record that it finds.
For FMP2.1 lookups are based on matching words:
Example:
Lookup File "main", Current File "main"
Copy the contents of "Field1" into the field "Lookup_Field"
when the value in "Field2" matches a new entry in "Field2"
This will cause a self lookup, pasting a matching value.
Text Field2 contains "A B" (record 1) and "B A" (record 2). Both fields
are indexed as "A " and "B ". Creating a new record "A B" will copy
Field1 from Record "A B" to the Lookup_Field. Creating a new record "B
A" will copy from Record "A B", too, since the triggers "A " and "B "
are the same.
A lookup on "A" or "B" may match an existing record as well, since all
words of the actual record ar matched. This may even work on incomplete
words.
If you want to copy from exact matching values, use the non-separating
delimiter option-space. "B<option-space>A" gets indexed as it is. Words
only get indexed up to 20 characters!
Lookups will be used only after you entered new data in the trigger
field (Field1 in example).
A relookup on all selected records can be forced by selecting the lookup
trigger field and choosing Relookup from the menu Edit.
Lookups don't work based on multiple trigger fields. Concatenating
multiple fields by a calculation and triggering on the calculation is
possible.
Relookups on calculations don't work by selecting the calculation field,
but by selecting any of the fields it is based on.
For FMP3 lookups are based on exactly matching lines. They use the same
mechanism as used for relations - check the matching section.

4.3 Text formats and Index basics

For an introduction, see the manual "working with Information in
Records" (Section 4), "Using the Index" (4-9):
- "Each word in a text field is indexed separately so you can later find
the records that contain any word you've typed"
- "Punctuation and special characters aren't indexed"
- the first character of a word is converted to Upper Case
- all other characters will get lower case
- an additional blank character gets appended at the end of the Word
Example: "this wordTest" is indexed as "This ", "Wordtest "
The ordinary range for characters is [a..z], [A..Z]. The numbers [0..9]
get treated as usual. The non-separating blank "option-space" is treated
like an ordinary character, thus preventing the separation of words.
Some unusual behaviors I found are (undocumented - don't rely on them):
- Indexed words are limited to the first 20 characters! Since find
requests and lookups are based on the index, take care of this
limitation!
- Text following an underscore won't get indexed for FMP2.1! "Text_A"
and "Text_B" both get indexed as "Text ". Don't use the underscore as
non-separating delimiter!
- Some special 8-bit characters get translated! Most of the accented
characters are translated to plain characters. Some special characters
get translated to two character combinations, e.g. the German Umlaute
ae, oe, ue and ss. The ligatures fi and fl get lost for FMP2.1!
- Between characters &, -, / and ' are handled properly, otherwise
ignored.
- Between numbers ,, . and : are handled properly, otherwise ignored.
- Other characters get ignored. All ignored characters work like a
space, for index display, calculation, find and word delimiter.
This behavior belongs to text fields. The text limit is 64k, with any
number of lines. For other field types the behavior may be different, as
described below.
FMPro 3 takes an almost identical mechanism for words, but builds its
index on lines with a maximum of 60 characters instead.

4.3.1 Character translation for indexed values

Some of the following 8-bit characters might get lost while mailing.
Messages posted to newsgroups will be converted to the ISO 8859-1
(Latin) character set (by YA-NewsWatcher 2.0.29 or better). Messages
mailed will get translated as Quoted Printable (MIME) (by Eudora 1.5.3
or better). Please don't complain about messed up characters in this
subsection.
====== Original and Converted Character for Index (US-Version) ======
Ä Å Ç É Ñ Ö Ü á à â ä ã å ç é è ê ë
A A C E N Oe Ue a a a ae a a c e e e e
í ì î ï ñ ó ò ô ö õ ú ù û ü
i i i i n o o o oe o u u u ue
ß
ss
Æ Ø
Ae O
ª º
a o
æ ø
oe o
À Ã Õ ‘ ¦
A A O Oe oe
' (upper apostrophe = ANSI 213 = 0xD5)
'
Þ þ ligatures fi and fl get lost
======================================================================
Modifications for FMPro 3.0:
====== Original and Converted Character for Index (US-Version) ======
' '
' '
Þ þ
fi fl
======================================================================

4.4 Printing index fields

The Index itself is not printable. Workarounds are:
* export all text, then sort and remove the duplicates.
"It's easy to do on an UNIX mainframe (sort -u flatfile) but takes some
time [...] It will not return the real index but the contents of a
field." (from Patrick Delges <pdelges@SEGI.ULG.AC.BE>)
* export all text, open a text editor with spelling capabilities and
create a dictionary with all words,
* export all text, convert all blanks to Carriage Returns, thus creating
a list of all words, import, use strategy to remove all duplicates,
* to print the index of a particular field, create a new layout,
selecting the columnar report option. Select the field that you wish to
print the index of.
Double click on the body part and make it a sub summary when sorted on
the field in question. Now sort the database on that field and print
from this layout.
(from tscott@metz.une.edu.au (Timothy Scott))
* If you want to export just the index of the field, create a new field
of type summary. Sort the database on the field you want to export.
Select the field you want to export and deselect the others. Now select
your summary field just by clicking on the field name. You now get an
extra option "Summarise By..." - click on this button, tick to the left
hand side of the field name, click on OK and then untick the new field
(if you want to) that FMPro wishes to export.
(from tscott@metz.une.edu.au (Timothy Scott))
* Consider Print2Pict to get the contents of a print as readable text
file.
* A final solution to extract a table of all field contents by Excel
Did you ever try to create an index for all fields of a FMP3 database?
Here's a quick and dirty method that I just developped by using excel
macros (German release 4.0)
steps required:
export all from fmp, e.g. tab delimited
open for Excel, option e.g. text tab delimited
create a macro table
make the proper macros,
then let it go.
The two macros required are, as pasted from Excel,
Extract (a)
=AUSWÄHLEN("S1")
=ZELLEN.EINFÜGEN(1)
=AUSWÄHLEN("Z1S1")
=FORMEL("=Z1S2")
=AUSWÄHLEN("Z2S1")
=FORMEL("=WENN(GLÄTTEN(ZS(1)) = GLÄTTEN(Z(-1)S2);"""";GLÄTTEN(ZS(1)))")
=KOPIEREN()
=AUSWÄHLEN("Z1S2")
=ENDE.AUSWÄHLEN(4)
=AUSWÄHLEN("ZS(-1)")
=AUSWÄHLEN(POSTEXT(AKTIVE.ZELLE())&":Z3S1")
=EINFÜGEN()
Dateiname=DATEI.ZUORDNEN(1)
=KOPIEREN("s2")
=NEU(1)
=EINFÜGEN()
Dateineu=DATEI.ZUORDNEN(1)
=GEHEZU(Z1S2)
=RÜCKSPRUNG()
(hm, pasting from Excel as international macro doesn't work. Maybe
someone does request the excel macro file itself and translate it
properly. Here's a dictionary: select = auswählen, insert =
zellen.einfügen, formula = formel, copy = kopieren, reftext = postext,
cell = zelle, active = aktive, paste = einfügen, new = neu, get.document
= datei.zuordnen. goto = gehezu, return = rücksprung, trim = glätten,
row = Zeile, r = z, column = Spalte, c = S, activate = aktivieren, sort
= ordnen, edit = bearbeiten, delete = löschen, if = wenn,
datei.berechnen = calculate.document, paste.special = inhalte.einfügen)
This macro creates a first column that computes its value as "" if it is
the same as the previous one, otherwise the value itself, both related
to the next, second column. IT REQUIRES THAT THE FIRST COLUMN OF THE
TABLE HAS NO EMPTY CELLS AND AN ENTRY FOR EVERY RECORD.
The second macro takes an unprocessed column, moves it to the second
column, sorts, computes the values and appends them to a new table. You
must place it as the second column of your macro table, otherwise the
goto commands will fail.
SortAndFilter (b)
=AKTIVIEREN(Dateiname)
=AUSWÄHLEN("S3")
=KOPIEREN()
=AUSWÄHLEN("S2")
=EINFÜGEN()
=ORDNEN(1;"Z1S2";1)
=AUSWÄHLEN("S3")
=BEARBEITEN.LÖSCHEN(1)
=WENN(Z1S2="";RÜCKSPRUNG()
=DATEI.BERECHNEN()
=AUSWÄHLEN("S1")
=KOPIEREN()
=AKTIVIEREN(Dateineu)
=AUSWÄHLEN("Z1S(1)")
=INHALTE.EINFÜGEN(3;1;WAHR;FALSCH)
=ORDNEN(1;AKTIVE.ZELLE();2)
=GEHEZU(Z2S2)
=RÜCKSPRUNG()
As you might see, this macro shall stop on the first, blank column. You
may force a new execution of the second script. If you assigned a proper
name and opt-cmd abbreviation (in my case it is Name "SortAndFilter" for
Z1S2, cmd-opt-b) the simple cmd-opt-b keystroke will let it run up to
the next blank column.
Now, maybe someone else can help me on the "" problem - unfortunately
this is not empty, but sorts in front of any other value. How may I
force it to be empty? Assigning NV() is no good, skip blank lines on
paste.special with results only does not work, and the German docs are
incredibly poor.

4.5 Number format

Entries may be up to 255 characters in only one line. Most characters
are accepted, but only numbers will be recognized. Thus you can enter
currency symbols, decimal points, percent symbols etc.
The valid numbers are 0..9
Leading "-" is recognized as minus
, (comma) and . (dot) may be used as delimiters. The symbols are treated
differently in international versions! In Germany "," is a decimal
delimiter, "." marks thousands, in US it's just vice versa. FMPro seems
to recognize the system settings.
Yes and True are translated to 1
No and False are translated to 0
All other characters are just ignored, except blank and Š (="...") that
work as separators.
No additional blank is appended to the index value. Only 122 characters
will get indexed.
Examples:
"1.2 DM" is "1.2" (US) or "12" (German)
"-3" is "-3"
"4-5" is "45"
"6Š7" is "6" and "7"
"8 Pounds 9 Pence" is "8" and "9"
Some less known fact is, that the internal representation will use 15
digits only! Thus the TextToNum(of a 16 digit credit card number) will
get rounded to 15 digits. The random function only produces 15 digit
values.
Counting serial numbers from
999 999 999 999 999 will continue with
999 999 999 999 1000 thus taking the leading part as text, increasing
only the last part before the delimiter. This is the same usage as
serial numbers with a leading string.
The maximum possible serial number is "2147483647". Counting from there
on FMP may produce "-2147483648", "--2147483647", "---2147483648".
2147483648 equals 2^31. The possible integer range of 32 bit is
-2147483647 ... 2147483647.
As indicated elsewhere, 2147483647 ist the last possible number for a
goto last record script.
One may assume that the internal representation of numbers is either as
32 bit integers (within the range -2^31 ... +2^31) or as 64 bit floating
number with 54 bits mantissa (2^54 = 1.8 * 10^16, artificial limit at
10^16 - 1), one bit sign and maximum 9 bits exponent for a 64 bits
representation. A typical size of 32 bit CPUs for the data type floating
double (64 bits) is 2.2 * 10^-308 to 1.7 * 10^308 (plus and minus, 15
digits precission)

4.6 Date format

Entries may be only one line long.
The date format depends on the system settings. Possibilities are
day/month/year, month/day/year and year/month/date.
For further explanations the German format DD.MM.YYYY is used.
Input may be any valid one to four digit number, from x.x.x to
xx.xx.xxxx. The valid range is from 01.01.0001 to 31.12.3000.
Any single non-numerical character may be used as delimiter.
If the year is omitted, the actual year will be used. Taking only two
digit years should take the actual century.
Examples:
"1.1.1" is "01.01.0001"
"29.2.94" is "29.02.94"
"1-4+04" is "01.04.04"
"1-4" is "01.04.95"
Some more words on the actual date format: The date is stored similar to
a numerical value. Calculations on dates will work like those on
numerical values. Conversions are done automatically but may work
different than you might expect! Take care and it will be very powerful.
The date with the number 1 is "01.01.0001" although our calendar didn't
exist at that time, but only since the Gregorian calendar was introduced
today 728 460 is "16.06.1995"
maximum 1 095 727 is "31.12.3000"
next higher values might be displayed as "?" and won't get indexed, but
e.g. 1 000 000 000 is "04.01.*0,1" as date

4.7 Time format

Although various output formats are possible, the input is limited to
numerical values and the colon as delimiter.
AM/PM is available for 12-hour formats.
Additional time information is allowed, depending on your system
settings. Example: while typing, it was "15:48:23 Uhr", indexed as
"15:48:23"
Input format is hours/minutes/seconds Input may be from "x:xx" to
"xx:xx:xx" in the valid range The valid range is between -32767..32767
hours.
Similar to the dates, the internal representation can be handled as
number in seconds from time "00:00:00" (see above).
The time with the number
0 is "00:00:00"
-60 is "-00:01:00"
Numbers outside the valid range may be converted properly.
Tip: If you needed the input format minutes/seconds, just use "HH:MM"
instead! Most of the time this is fine, e.g. for recording the length of
CD tracks. If you need the real time for another calculation, convert it
by a division /60.

4.8 Hidden fields

In order to paste values to a certain field, e.g. while using scripts,
find dialogs etc., the field must be available on the choosen layout.
If the field shall be available but not be displayed you may
- assign a field size of 1 by 1 point to the field:
select layout mode, display and edit size (recommended)
- assign an invisible font color to the text:
choose background color as text font
- hide the field behind another field:
just move another field on top of it. Fields are staggered by order of
creation but may be moved by Arrange forward/backward
- exclude the field from the tab order:
edit the tab order, exlude it or move it to a late tab order
(recommended as additional step)
FMP3 offers a new command "Set Field" that works both on visible and
excluded fields. Set Field also works on fields in the header or footer
of a list layout, while those fields are not editable otherwise.
Warning: Set Field may only enter data of the proper type, such as a
real date to a date field. If you want to find e.g. the records within
the last thirty days, by a find string including a ">" symbol, you have
to use a paste command instead, such as
Paste Result["> "&Status(CurrentDate)-30]

4.9 Sorted lists

FMP3 offers pop up lists based on two fields. In general they get
alphabetized by their second field. If you don't want this behavior,
turn off indexing for the second field or, if you have to have it
indexed, make an unindexed calculation field to show the value. (from
Stefan Schutt <stefan@MOUSEUP.PP.FI>)

------------------------------

Subject: 5 Repeating Fields

5.1 Convert repeating fields to non repeating fields

* Import all data in a clone of the database, splitting repeating fields
to single records.
If repeating fields don't seem to split properly, check that they are
real repeated fields. Contents that are separated by <returns> may look
like separate repetitions, but are in fact within the same field.
(by Steve Rothman <steve_rothman@rdm.scitex.com>)
* Create a script to copy from repeating field entries to separate
fields.
* Create the single fields as calculation fields from the repeating
fields, change the format from calculation to the format desired.
<gopher://spinaltap.micro.umn.edu/00/...aker/Splitting
the Contents of a Repeating Field into Separate Fields>

5.2 Convert nonrepeating fields to repeating field

Create a script:
<gopher://spinaltap.micro.umn.edu/00/...leMaker/Moving
Data to Repeating Fields Using a Script>
* Export as text, convert record delimiter to repeating field delimiter
(ASCII-29) and reimport.
The fields of comma-separated text will be stored in a way like:
repeatingfield 1<ASCII-29> 2<ASCII-29>3
To move separate fields A, B, C to a single repeating field,
- export as text: A, B, C
- use a standard text editor or a conversion utility to replace the
comma with <ASCII-29>
- reimport to a repeating field
This works with merge format and tab separated text as well.
* Use commercial software (MakeRepeats)

5.2.1 How to create ASCII-29

The character ASCII-29 is not on the keyboard and is invisible most
often. However, some editors will display it, e.g. as a small box.
Editors like MS Word or Alpha allow for input of decimal code to create
this character.
The easiest solution is to open any exported database with a suitable
editor, copy the ASCII-29 character and paste it where necessary. For
reusage it's appropriate to put it in the scrapbook.
The very easiest solution is to select this "" - if you are lucky, it's
still there.

5.3 Merge separate records to a single repeating field

* Create a script to copy from several records to a repeating field.
* Export as text, convert delimiter to ASCII-29, reimport
The procedure is similar to merging separate fields, but requires some
additional effort.
Example:
Consider a database with the records spearated by Carriage Return <CR>,
exported as comma delimited, with fields: Name, Text
"A","1"<CR>
"B","1"<CR>
"A","2"
It should be merged to a database
"A","1"<ASCII-29>"2"<CR>
"B","1"
This can be done by sorting the records and replacing the <CR> with
<ASCII-29>. To separate between the records A and B some additional
criterion is required.
Unfortunately FMPro doesn't allow the comparison of a record with the
previous one. So one chance is to make this comparison externally, e.g.
via a spreadsheet application.
Another solution is:
create the fields:
serial number SN (number): Auto-enter serial number
serial number lookup SNL(number): Copy SN if Name matches
mark record MR (calculation): If( SN = SNL, Name, "")
The sorted database now looks like this: fields:
Name, Number, SN, SNL, MR
"A","1",1,1,"A"<CR>
"A","2",3,1,<CR>
"B","1",2,2,"B"
Export the fields MR and Number, replace <CR>," with <ASCII-29>" and
import. Further more you can find all non-blank MR, sort them similar
and import all other fields.
For further improvements think about "replace all with new serial
number", "relookup" and "sort, import to clone".

5.4 Removing duplicates or Sychronizing databases

First, you should make sure what a duplicate is.
FMP2.1 may treat "firstname lastname" and "lastname, firstname" as
duplicate since both entries contain the same words. Even a later record
that only contains "lastname" may get recognized as a duplicate of the
first ones. Check the sections for indexing to understand why.
FMP3 introduced another type of relation that is based on lines (total
max. 60 characters) instead of words (multiple, up to 20 characters
each). A find for duplicates is still based on the indexed words.
In order to remove duplicates, you have to identify them as a duplicate.
You can do so on a single field that should be unique (such as the
Social Security Number), a combination of multiple or all fields (e.g.
FirstName & LastName) or a combination of all (e.g. LastName &
Left(FirstName, 3) & Length(comments))
This is a "duplicate key" suitable for comparisons. You then may
identify all records by a find for "!", a lookup or a FMP3 relation. A
further step may be the comparison whether the other fields are really
identical, e.g. by comparing all fields by calculation or script.
Here is a simple example to mark every record that has a matching
duplicate record with the value "1".
* use a key field "serial number" (number):
auto-enter serial number
This field holds a unique identifier for every record
* define a lookup field "unique lookup" (lookup, number):
if "duplicate key" fields are equal, then copy the serial number
This will copy the serial number of the matching record. If there is an
already existing record, it will get another serial number than the own
one. Due to the indexing mechanism it will return for newly created, not
yet matching entries a zero value (nothing, 0 or a dummy value when
defined so).
* define a calculation field "unique calculation" (calculation, number)
=
if(serial number = unique lookup, 0, 1)
Thus every record gets flagged by a number or any other specified text
that there is another, duplicate record. You may use this flag to find
and remove duplicates or to display a warning. Original and duplicate
may be within the same or different files.
Lookups only show proper values at the point of creation. They get
updated if the actual record is modified or a relookup is performed, but
not if the first, original record is removed. For FMP3 one may use
relations instead.

5.4.1 Removing duplicates - another approach

Here's a more detailed description by Jason Hirschhorn
<jason@hirschhorn.com> that uses the "!" as find duplicate command
instead.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
FileMaker Pro 2.x
Removing duplicate information from a database is frequently necessary,
especially after updating a central database from another database. Here
is a method to remove duplicate records from a FileMaker Pro 2.0 file
that takes advantage of the new ScriptMaker features of FileMaker Pro
2.0.
Before you begin, save a copy of your database as a backup and make a
clone as a working copy of the database. Fill the working copy with
several duplicate records. Read the User's Guide and, using the working
copy, practice making scripts until you understand and are comfortable
with using ScriptMaker. You will also need a field with a unique value
that will uniquely identify an individual or item and its duplicates but
will not find non-duplicate records. Some examples of this are:
* a field with a social security number (unique to an individual and
will find all duplicate records of the person)
* a field with a unique code number (unique to an item and that will
find all duplicates of a part, customer or other item number; do not use
record number as this is a changing value)
* a field that combines the contents of several fields to create a
unique value. (this unique value must identify duplicate records without
finding any other records.
A word about the unique field If you do not have a field that already
contains a value unique to the individual person, company, or item, you
will have to create one. For
example, if you have an address book database you would not want to
remove all the people that share the last name "Jones," just duplicates
of each individual. And, if the database contains the fields FirstName,
LastName, Address, City, State, Zip, and PhoneNumber, you can create a
calculation field that concatenates (combines) two or more of these
fields to create a unique field. You can call your new unique field
DuplicateCalc:
DuplicateCalc (calculation, text result) = FirstName & LastName & Zip
The result will be a single text string that looks this:
FirstNameLastNameZip. Some examples might look like this:
JohnSmith10825 MadelineKwan60610
PierreMyers33976
This approach works well for most databases, but it's still possible to
have more than one "John Smith" in the same zip code. In this case you
may want to add another field, such as a phone number, to be sure that
this DuplicateCalc field will always be unique und
er any circumstance. Just make sure the maximum length of the unique
field is less than 20
characters; FileMaker Pro 2.x does not index any single text string
beyond that limit.
This limit of 20 characters is for every single word. You may use
multiple words, but both "word1 word2" and "word2 word1" get indexed the
same, and "word1" alone may relate to both. FileMaker Pro 3.0 takes
instead a line of 60 characters as index for relations and lookups.
Setting up the scripts
Now that you have a field you can depend on to match duplicate records,
you are ready to set up the scripts that will delete duplicates. In your
working copy of the database create a new layout (call it "Find
Duplicates") that will show only the unique field and the fields used to
define it.
Advantages & Disadvantages
The advantage of this process is that it automates the task of removing
duplicates by using the scripting capability of FileMaker Pro. The
drawback is that it slows down as the database increases in size and the
first occurrence of a record is retained and all others are deleted, not
necessarily the one with the latest entered information. Duplicate
records
with spelling errors will NOT be found using this method.
Creating the scripts
The scripts must be created from the last to the first. The script that
finds and deletes the duplicates must be created before the cleanup
script. If you must edit the scripts after creating them do not replace
the "Find Requests" or repeat step 1 before editing the script.
1. Choose Find from the Select menu, and type an exclamation point (!)
into the "DuplicateCalc" field created above, and click Find. Review the
records to confirm that only duplicates have been found. To fully review
the records go to a layout that shows all the information in each
record. Be sure to return to the "Find Duplicates" layout before
continuing.
2. Open ScriptMaker from the Scripts menu and create a new script called
"Delete Loop". This script should have the following steps:
Perform Find [Restore]
Go to Next Record/Request [Exit script after last]
Go to Next Record/Request [Exit script after last]
Go to Record/Request [No dialog, 1]
Copy [Select, "DuplicateCalc"]
Enter Find Mode [ ]
Paste [Select, "DuplicateCalc"]
Perform Find [ ]
Omit
Delete Found Set [No dialog]
Perform Script [Sub-scripts, "Delete Loop"]
With that script completed, now create the second script Remove
Duplicates. This script will perform some setup work for the "Delete
Loop" and do some clean up work afterwards. This script is the one that
you will select from the Scripts menu to perform the remove duplicate
process.
[Note: For FMP3 you may use the newer loop functions instead]
3. Open ScriptMaker from the Scripts menu and create a new script called
"Remove Duplicates". Include this script in the menu. For convenience
wehave used the name "First Field", which is the first field that shows
in the Find Duplicates layout, but it cannot be the DuplicateCalc field.
This script should have the following steps:
Enter Browse Mode [ ]
Go to Layout [Find Duplicates]
New Record/Request
Go to Field ["First Field"]
Paste Literal ["ABC123"]
Duplicate Record/Request
Perform Script [Sub-scripts, "Delete Loop"]
Enter Find Mode [ ]
Go to Field ["First Field"]
Paste Literal ["ABC123"]
Perform Find [ ]
Delete Found Set [No dialog]
You have now successfully created a set of scripts that will delete all
the duplicate records in your database. Note: using the value "ABC123"
will work even if the "First Field" is defined as a number.
[Note: For FMP3 you do not have to create a dummy duplicate record
"ABC123" but may check whether 0 records were found]
Before testing the script, make sure you have a backup copy in case
something goes wrong, then choose "Remove Duplicates" from the Scripts
menu. FileMaker will begin identifying and removing duplicate records
from your file.
You are now ready to work on the data from the original database.
1) In your working copy find all records.
2) Delete all records (use the Delete Found Set option from the Edit
menu).
3) Import records (it does not matter if you replace or add to an empty
database) from the original database.
4) Choose "Remove Duplicates" from the Scripts menu.
Review the data to confirm that no valuable information has been lost,
i.e., that the deleted records did not have more information than the
saved record. If you have lost information do steps 1 through 3 listed
above and verify that the information was there in the first place. Then
manually find the duplicate(s) of the full information record and delete
them manually. You can now choose "Remove Duplicates" from the Scripts
menu.

------------------------------

Subject: 6 Find

6.1 Some facts

- If no other option were choosen, a find command on "string" will
search for all records where an indexed word begins with "string"
- The last search criterion gets stored. It may be used for a Refind and
as a search criterion for scripts while using the Restore Find Request
option of Enter Find Mode or Perform Find. While defining a script, the
last performed find gets used. Changes can be done by performing a new
find, open & close the edit script dialog and accept the replacement of
Find Requests.
- As indicated above, not more than one restored find is possible per
script. Additional finds may be used by defining additional scripts and
calling them from within the first script.
- The search criterion of a script may be displayed by using the Refind
command after performing the script.

6.2 Perform scripted find

Sounds to be obvious, but the find command will be performed by the
script command 'Perform Find [...]'.
Example script to find values matching the content of Field_A:
Copy [Field_A, select entire content]
Enter Find Mode [No Pause, Don't restore find request]
Paste [Field_A, select entire content]
Perform Find [Don't restore find request]
The omit command itself is not a selectable script option for FMP2.1.
However, as usual, there's a workaround:
- Perform any omit find, e.g. enter find mode, paste "a" to field Name,
click omit, perform find.
- Define a script "find omit":
Enter find mode[restore]
Cut[select entire contents, "Name"]
That's all. Whenever you call this subscript, the omit box is already
checked in find mode.
For FMP3.0 this was simplified. The Omit script step in Find mode does
the same as marking the omit checkbox. The Omit script in browse mode
does, as usual, exclude the current record.

6.3 No matching records found

If no matching records were found, a dialog gets displayed: no records
match this request:
- "modify find" will return to the find dialog
- "cancel" will omit the find and select ALL records
An automated script without dialog in FMP2.1 showed all records while no
matching record was found. Thus e.g. a delete on a found set is most
critical.
For FMP3.0 this was modified. Now while you find no matching records,
you'll get a less critical set of zero found records. Another option
instead of "modify find" or "cancel" is "continue" with zero found
records. Furthermore you may detect easily whether the find command was
successful, e.g. by
Set Error Capture [On]
Perform Find [Restore]
If ["Status(CurrentError) = 0"]
Perform Script [Sub-scripts, "WhateverYouWantToDo"]
Else
Show Message ["There are no records found!"] ***
End If
You may check the ErrorCode itself or check the number of found records
as well.
For FMP2.1 a workaround by dummy records is possible:
Performing an unsuccessful find in a script will not select zero
matching records but all - that's really annoying for printouts,
exports, deleting records etc.
A workaround is to create a dummy record and to add an additional dummy
search criterion, e.g.:
Copy [Field_XYZ, select entire content]
New Record/Request
Go to Field [Field_1]
Paste Literal ["dummy"]
Enter Find Mode [No Pause, Don't restore find request]
Paste [Field_1, select entire content]
New Record/Request
Go to Field [Field_1]
Paste Literal ["dummy"]
Perform Find [Don't restore find request]
This script will find all matching records and the dummy record, or, if
no matching records were found, the dummy record only.
To exclude the dummy record (that is the last of all, and all<999999):
Go to Record/Request [999999, Perform without dialog]
Delete Record/Request [Perform without dialog]
Another solution is to detect if only the dummy record was found:
Go to Record/Request [999999, Perform without dialog]
Go to Previous Record/Request [Exit script after last]
This script jumps to the last record, tries to go back to the previous
one and exits if the last (dummy) record is the one and only. Thus you
may use a kind of an
if(dummy is only record found,
exit/return to main script,
perform further part of script)
Tip: A similar structure may be used as conditional script!
See sections on conditional scripting and dummy entries.

6.4 Find on multiple fields

Multiple finds may be performed. Logical AND search may be achieved by
adding criterions to multiple fields. Logical OR search may be achieved
by creating new requests. Find details in the next subsection.
Sometimes a find shall be performed on the occurrence of a criterion IN
ANY OF several fields.
A scripted solution is to copy the criterion,
...
Enter Find Mode [No pause, Don't restore find request]
Paste [Field 1]
New Record/Request
Paste [Field 2]
New Record/Request
Paste [Field 3] ...
Perform Find [Don't restore find request]
...
Another solution is to concatenate the fields to a single search field
SearchField = Field 1 & ", " & Field 2 & ", " & Field 3
The "," are just for aesthetic purposes. This find is faster to perform
but will increase the memory usage of the database and slow down
imports.

6.5 Find with AND / OR / NOT

In order to use search by multiple criteria according to a logical AND,
OR and NOT, you may use:
- multiple words in a single field (equals Word1 AND Word2),
- multiple fields (equals Field1 AND Field2),
- multiple requests (equals Field1 OR Field2) and
- the omit command (kind of NOT Field).
It's not possible to find Field1 AND (NOT Field2). The logical
equivalent, according to deMorgans law is NOT ((NOT Field1) OR Field2),
or speaking in FMPro: enter find mode, paste literal to Field1, mark
omit, create new request, paste literal to Field2, perform find, find
omitted.
But the omit works different than a real NOT! It will omit those records
from a found set that match your omit request. Thus (Field1 OR (omit
Field2) is different to ((omit Field2) OR Field1)
Example:
OMIT B, FIND A will find all that are not B, plus all that are A
FIND A, OMIT B will find all A, THEN omit from the found set all that
are not B
Example (X is match, (x) is after omitting):
[data] OMIT B + FIND A = found | FIND A + OMIT B = found
A B - X X | X -
A NB (x) X X | X X
NA B - |
NA NB (x) X |
While AND and OR are independent on the sequence, OMIT is not the same
as a logical NOT, but instead dependent on the sequence of requests.

6.6 Find on a subset

Assuming that the subset was created by a previous find, the manual
solution is not to select the Find command (cmd-F) but the Refind
command (cmd-R). You may use AND / OR / NOT and multiple search
criterions as described above.
The scripted solution works similar. As a refind you may use the find
criterions from the last find before creating/editing the script. It is
accessable by the restore request option. For multiple searches see the
sections above.
Sometimes the subset was created manually, or it is not possible to
perform the desired find by multiple searches in a single step. The
solution is to use a mark field: (unmark all, perform find,) mark found
set, perform next find on mark and new criterion.

------------------------------

Subject: 7 Scripting

7.1 ScriptMaker

Check the manuals, chapter 5, "Using ScriptMaker and Buttons". It's
rather basic, but there's not much more behind.
Unfortunately, you can't edit scripts manually, you can't copy/paste
script steps and you can't copy scripts between databases. You may
duplicate scripts and databases.

7.1.1 What's that restore option? - Find & sort within a script

That's one of the options not so obvious. When you create a script, FMP
will keep the options of your previous commands as options. This is
valid for: Find, Sort, Page Setup (printing), Import and Export.
When you create a find script step and use the restore option, the
criterions of the last used find commands get used.
You may view these criterions when you perform a refind command. You
also may print the script definitions.
You have to ensure that your actual layout displays the fields where
find criterions will get used!
If you want to modify the stored information, you will have to perform a
modified find, edit the script, and after clicking ok you will get a
dialog box to keep or replace the information. Click the check box to
replace Find Requests. Now the new information gets saved.
By and large this is all the same for the other restore options.
As you might imagine: there is only one valid restore option for each
type of script command within a script. Nevertheless, you may use
multiple finds easily:
Perform first search, create script "Find 1";
perform other search, create script "Find multiple";
additional to the find criterions in "Find multiple" you may perform
additional find requests within by calling the sub script "Find 1".

7.1.2 Special options

- When you use Copy[] without options while no field is selected (e.g.
Goto Field[]), all fields on the current layout get copied to the
clipboard. The fields are copied in the creation order, seperated by
tabs.
There is no known solution to paste in multiple fields in a single step.
- Using summary fields in scripts:
"Remember to use the Refresh command with an Enter Browse Mode step if
you wish to copy the values from Summary fields. However, copying a
summary field in a script may not work reliably in all cases. If a
script specifies a Copy step for a summary field that has not already
calculated, it will copy a null value. This happens because the script
progresses before the summary field is given time to calculate. Be sure
you are using FileMaker Pro 2.1v2 or later as this was fixed in this
version. "
(from www.claris.com TechInfo)

7.1.3 Documenting scripts

Scripts may be printed, including all parameters and options set.
See Print2Pict

7.1.4 Recursive and Conditional scripts

You may call scripts recursively in order to loop, repeat or do while,
but you should provide a certain break criterion. This has been a most
requested feature for FMP 2.1, and FMP 3.0 offers both if/else/end if
and loop/end loop constructs.
Here are some workarounds for FMP 2.1
A typical example is:
Script A:
Perform Find[restore]
-- now you are within the first record of the found set
Perform Script ["B"]
-- this is the recursive part
Do...
-- do some final processing
Script B:
Copy [field 1]
Paste [field 2]
...
Go to Next Record [exit on last]
Perform Script ["B"]
This option [exit on last] is a powerful workaround to implement
conditional scripting. Its concept is:
if this is the Last/First record
then exit
else process
The simple exit is equivalent to a "do nothing" and is sufficient for
many tasks. In order to implement real if/then/else scripts you may use
and manipulate special search criterions (such as a simple mark field)
or modify the plain
if A
then B
else C
to something like
Script C:
find A
mark all
perform Script B
find marked
perform C
Script B:
exit on last
perform B
unmark all
As you may see, the mechanism is based on a find and work with certain
sets of records. In order to use this behavior you have
- to create a proper search criterion
- to ensure that a find on this set never fails
- to do the processing itself
A proper search criterion may be based on any fixed value, calculation
or user input.
You must take care that your search command will return a proper set of
records. If it fails without dialog, the find command will select ALL
records, and therefore all records will get processed (fatal if you use
a Delete Found Set command).
For that reason it is recommended to use dummy entries. A find will be
performed on the search criterion OR dummy record and will return at
least the dummy record. See the section on dummy entries for details.
See the section "no matching record found" for another example.
I should mention that recursive and conditional scripts can be done
within AppleScript or FMP 3.0 much better.

7.1.5 Dummy entries

Dummy entries may get used to buffer values and to improve the
performance of recursive, conditional and find scripts. Most of the time
they are more or less elegant workarounds for missing features or bugs.
Personally I use two solutions, dummy records or dummy variables. Both
may be permanent (static) or variable (dynamic). A third exception is
that I buffer user inputs by selecting the find mode.
The dynamic record approach is the safest. A script may contain this:
Enter Browse Mode
Go to Layout ["any layout with the field to paste a dummy value to"]
Create New Record
Go to Field ["the field to paste a dummy value to"]
Paste Literal ["dummy"]
Enter Find Mode [use restore, paste literal or by user]
Create New Request [use paste literal "dummy" or restored "dummy"]
Perform Find
Now you will have all found records plus all found dummies.
A new dummy record will be the last of all (found and unsorted) records.
You may delete it since it is of no further use, e.g. by Go to Last,
delete record, then process and step backwards, or step from the first
to the next, exit on last, and delete the last within an outer script,
etc.
The advantage is that after processing there won't be any dummy records
left, the data of other records is not touched. The disadvantage is that
it is slower to create and delete new dummy records every time, and that
a serial counter will continue with an increased value.
If you keep the dummy record, you should hide it from the user, since it
may confuse. As soon as this dummy record gets deleted, the performance
is endangered. For a permanent dummy record I recommend to use the first
record as dummy and help, but to protect and hide this record from the
ordinary user as good as possible: Take it as record one, displaying the
text "No records found", but omit it when other fields were found.
For other purposes I insert a dummy value to a special field of an
existing record. Best suited is a number field, named e.g. as "[mark]".
Number fields get indexed faster than text fields, but may contain text
as well. Go again in browse mode to a layout that contains the dummy
field,...
Go to Field["[mark]", select all]
Paste Literal ["999 dummy"] -- BTW this gets indexed as 999
Enter Find Mode [use restore, paste literal or by user]
Create New Request [use paste literal "999" or restored dummy]
Perform Find
You may keep the dummy value, but it is better to delete it, e.g. by a
Perform Find[restored ">0"]
Go to Field "[mark]", select all]
Paste Literal [""]
Replace
The drawback is that you may need an additional field, and that the
actual record where you paste the dummy value will be processed either
every time or never, dependent on your script, but not dependent on its
other field contents. I prefer it e.g. when I know that the actual
record must be within the set of records, or when the processing is only
time consuming, but does no harm.

7.1.6 Go to last record

Go To Record 2147483647 will take you to the last possible record since
this is the maximum number of records possible in an FM Pro file.
(From Michael Singer's book)
Any other number higher than the real maximum number of records will do
so as well. Personally, I use serial number 99999999.
When you open the database, you will get not the record you edited last,
but the first of the records found. Creating a script "go to last" that
is autoexecuted while opening the database will put you to the last of
this records. Including a "select all" step may put you to the last new
record, while otherwise the find and sort order is still maintained.

7.1.7 Schedule Scripts

How can I run a script at a specified time?
This has become one of the most frequently asked questions on the
MacScripting list. There are several commercial, shareware, and freeware
utilities that will allow you to do this. Your commercial option is
Scheduler, which comes with Script Debugger. You can find a demo version
of it on gaea.
Your freeware options are Cron 1.0.0 and Cron1.0d16. Cron 1.0.0 is a
cdev and an application that can launch application and document
aliases. You rename the aliases to reflect the time that you want them
to launch. Cron 1.0d16 was written by Chris Johnson of GateKeeper fame.
You can always find the latest version of Chris' Cron at
http://gargravarr.cc.utexas.edu/cron/cron.html.
Your shareware options are Chronograph and CronTab. Chronograph has both
a 68k and PPC version of the daemon. It also follows the UNIX cron
format. CronTab is an older collection of AppleScript scripts and
applets.
You can also find these cron programs and scripts on gaea as
Chronograph1.1UR.sea, Cron1.0.0.sit, Cron1.0d16Distribution.sit, and
CronTab
Ziff-Davis Publishing also has a soluton that is available in the
ZiffNet forum on CompuServe. It is called T-Minus Ten and is an
application and an extension. If you have a CompuServe account, you can
get the file. Due to ZD's copyright restrictions, the file cannot be
uploaded to any other service.
(from the AppleScript FAQs)

7.2 AppleEvents

Section under construction - please provide information

7.2.1 FMP documentation

For information on scripting FMP check the template "FileMaker and Apple
Events"
(formerly called "FileMaker Events and Objects")
Included with FileMaker Pro 2.x in the Apple Events Example folder on
the Utilities disk. This FileMaker reference database provides
documentation of every object and event accessible by AppleScript and
Apple Event-savvy programs. Topics include syntax examples, the
FileMaker containment hierarchy, keyforms, and event parameters and
id's. The new version of this database adds new information, examples
for AppleScript and Frontier, and a matrix of events and objects. The
update can be found with FileMaker Pro 2.1v2 and later. Also in the
Apple Events Example folder, see examples of scripts that take data from
a FileMaker database and create charts in Excel or Resolve.

7.2.2 Classes

Those classes are supported by FMP:
capp: class Application
cwin: class Window
cdoc: class Document
cDB : class Database
ctbl: class Layout
ccol: class Field
crow: class Record
ccel: class Cell
cSCP: class Script
cmen: class Menu Item
cmnu: class Menu
Most classes provide read-only properties. Check FMP itself for details,
using e.g. the open dictionary command of the ScriptEditor.

7.2.3 References

Elements of objects may get referenced in multiple ways. Here are some
(all?) types and some examples
name: its name
tell application "FMP"
absolute: its numeric index
show window 1
relative: before/after another element
copy fields of records before (current record of database 1)
range: a range of elements
(syntax?)
ID: the ID of an object. In general an integer property, but on cells it
is a list {<record ID>, <cell ID>}
exists record 10
test: satisfying a test
repeat with every record whose cell "searchfield" = FindCriterion
"show every record whose cell <cellName> is <cellData>" no longer
accepts FMPro special characters within FMP3. To use those you have to
use "requests". Unfortunately, use of requests adds several apple events
to a search.

7.2.3.1 References table

This is the hierarchy of FMP objects:
capp - cwin - cdoc - cDB - ctbl - ccol - ccel
- crow - ccel
- ccel
- cSCP
- cDB ...
- cSCP
- cdoc - cwin ...
- cDB ...
- cSCP
- cmnu - cmnu ...
- cmen
ref. by cwin cdoc cDB ctbl ccol crow ccel cSCP cmen cmnu
name | x | x | x | x | x | x | x | x | x | x |
absolute | x | x | x | x | x | x | x | x | x | x |
relative | | | | x | x | x | x | x | | |
range | | | | x | x | x | x | x | | |
ID | | | | x | x | x | x | x | | |
test | | | | x | x | x | x | x | | |

7.2.4 List of events

open / print / quit / run Copy / Cut / Paste Begin Transaction / End
Transaction / Event Info / Save -- Usage? Class info / Close / Count /
Create / Data Size / Delete / Do Menu / Do Script / Duplicate / Exists /
Get Data / Open / Redo / Set Data / Show / Sort / Undo
??? What's the difference between open / print / quit (required suite)
and Open / Print / Quit (FMP Core, Table, Database suite)
??? What's the difference between Set and Set Data?
??? What's the difference between Get, Get Data and no event at all?
- most important events:
* Set
set cell "Name" of layout 0 to "Martin"
* Get <reference>
set MyName to [get] cell "Name" of layout 0

7.2.5 Sending events

In the FileMaker you just create a new script in ScriptMaker, and
- add command "Send AppleEvent [...]"
- push button "Specify..."
- push button "Specify Application" and select your applet.
- choose from popup menu "Other..."
- input: Event Class: "PIPS" and Event ID: "pip2"
If you don't use any parameters, that's all. If you do, you must either
1) put value of your parameter in the AppleEvent definition dialog
(select "Script text" radio button), or
2) put it in one of FileMaker Fields (select "Field value" radio button
and show the field you want to use).
The AppleScript looks like this:
on «event PIPSpip2» (howmany)
beep howmany
end «event PIPSpip2»
(by Ilmo Kotivuori <ilmo.kotivuori@ILMO.PP.FI>)
The simple events sent from FMP can only send a single parameter (in the
keyDirectObject parameter, '----'). You need to build up more complex
data structures for most events from and to FMP, so you'll have to use
e.g. an AppleScript as an intermediary.
(by Wayne Walrath <wkw@acmetech.com>)

7.3 AppleScript

Section under construction - please provide information

7.3.1 System requirements

System 7.1 or better
AppleScript 1.0 or better (actual 1.1.1?)
Editor:
ScriptEditor (System 7.5) or
ScriptDebugger (URL:?) or
ScriptWizard or
Scripter

7.3.2 General documentation

??? please check and provide information
FAQs:
http://www.sysnet.com/~pfterry/applescript.faq.html

7.3.2.1 Books

See http://www.clar