|
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/computer/Claris/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/database/filemaker-pro/faq.fm.sit "Home
site of the FAQs, compressed database release"
http://www.cis.ohio-state.edu/hypertext/faq/usenet/databases/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/DPpage/DailyPlanner.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-Macintosh/Solutions_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-Macintosh/Trial_Software/
2Trial.sea.hqx>
<ftp://ftp.claris.com//pub/USA-Windows/Trial_Software/>
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-Macintosh/Trial_Software/
3.sea.hqx>
updater from 2.0:
<ftp://ftp.claris.com//pub/USA-Macintosh/Updaters/.
hqx>
<ftp://ftp.claris.com//pub/USA-Windows/Updaters/>
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/database/filemaker-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/FixesFeaturesFolder.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)
=AUSWHLEN("S1")
=ZELLEN.EINFGEN(1)
=AUSWHLEN("Z1S1")
=FORMEL("=Z1S2")
=AUSWHLEN("Z2S1")
=FORMEL("=WENN(GLTTEN(ZS(1)) = GLTTEN(Z(-1)S2);"""";GLTTEN(ZS(1)))")
=KOPIEREN()
=AUSWHLEN("Z1S2")
=ENDE.AUSWHLEN(4)
=AUSWHLEN("ZS(-1)")
=AUSWHLEN(POSTEXT(AKTIVE.ZELLE())&":Z3S1")
=EINFGEN()
Dateiname=DATEI.ZUORDNEN(1)
=KOPIEREN("s2")
=NEU(1)
=EINFGEN()
Dateineu=DATEI.ZUORDNEN(1)
=GEHEZU(Z1S2)
=RCKSPRUNG()
(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 = auswhlen, insert =
zellen.einfgen, formula = formel, copy = kopieren, reftext = postext,
cell = zelle, active = aktive, paste = einfgen, new = neu, get.document
= datei.zuordnen. goto = gehezu, return = rcksprung, trim = gltten,
row = Zeile, r = z, column = Spalte, c = S, activate = aktivieren, sort
= ordnen, edit = bearbeiten, delete = lschen, if = wenn,
datei.berechnen = calculate.document, paste.special = inhalte.einfgen)
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)
=AUSWHLEN("S3")
=KOPIEREN()
=AUSWHLEN("S2")
=EINFGEN()
=ORDNEN(1;"Z1S2";1)
=AUSWHLEN("S3")
=BEARBEITEN.LSCHEN(1)
=WENN(Z1S2="";RCKSPRUNG();)
=DATEI.BERECHNEN()
=AUSWHLEN("S1")
=KOPIEREN()
=AKTIVIEREN(Dateineu)
=AUSWHLEN("Z1S(1)")
=INHALTE.EINFGEN(3;1;WAHR;FALSCH)
=ORDNEN(1;AKTIVE.ZELLE();2)
=GEHEZU(Z2S2)
=RCKSPRUNG()
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"
"67" 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/computer/Claris/FileMaker/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/computer/Claris/FileMaker/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.claris.com/techinfo/CAMON9220228814.html for a
comprehensive list.
Derrick Schneider: "The Tao of AppleScript (second edition)", Hayden
Books, Carmel, 1994.
Price $24.95 (U.S.); $31.95
Danny Goodman: "Danny Goodman's AppleScript Handbook", Random House
Electronic Publishing, 1995.
Price $39.00 (U.S.); $55.00 (Canada.)
Tom Trinko: "Applied Mac Scripting", M&T Books, 1995.
Over 800 pages, Price $34.95 (U.S.); $47.00 (Canada.)
Dave Mark: "Ultimate Mac Programming: Methods of the Macintosh Masters",
IDG Books Worldwide, 1995.
Price $39.95 (U.S.); $54.95 (Canada.)
"AppleScript Language Guide: English Dialect", Addison-Wesley, 1994.
Price $29.95 (U.S.). ISBN 0-201-40735-3
same documentation as found in the AppleScript Developer's Kit from
Apple Computer
"AppleScript Finder Guide", Addison-Wesley, ISBN 0-201-40910-0
Steve Michel: "Scripting the Scriptable Finder", Heizer Software, 1995.
Price $49.00 (U.S.)
Heizer Software at (800) 888-7667 or (510) 943-7667.
Claris Corp.: "TechInfo Journal", Claris Support Service.
quarterly; call Claris Customer Assistance at (800) 325-2747.
The Cobb Group: "Inside FileMaker Pro"
Monthly, Price $59.00/yr or $7.00 each (U.S.). ISSN 1068-6908.
"INSIDE MACINTOSH: Interapplication Communication", Addison Wesley
Publishing Company, Menlo Park, California.
Price: $36.95.
"Inside Macintosh - AppleScript", ?
7.3.2.2 Online documentation
Web & FTP:
Scripts, scripting additions, demos, and other scripting-related files
can be found at the following ftp site:
<ftp://gaea.kgs.ukans.edu/>
<ftp://gaea.scriptweb.com>
<http://www.scriptweb.com/scriptweb>
<ftp://gaea.scriptweb.com/pub>
<ftp://gaea.scriptweb.com/pub/applescript/>
<http://rever.nmsu.edu/~elharo/faq/Macintosh.html>
<http://www.ultranet.com/~mfenner/applescript.html>
<http://mmm.dartmouth.edu/pages/macscripting/macscripting-home.html>
Internet:
The MACSCRPT mailing list is a highly active forum devoted to scripting
on the Macintosh and focuses on AppleScript and UserLand Frontier. To
subscribe to the Macintosh Scripting mailing list send a message using
one of the following methods:
- To subscribe to the MACSCRPT list,
Send message to: LISTSERV@dartmouth.edu
Subject: subscribe
Message: subscribe macscrpt
APPLESCRIPT Mailing Lists:
Purpose: For discussion of AppleScript.
applescript-implementors@abs.apple.com
is for users creating AppleScript savvy applications.
applescript-users@abs.apple.com
is for users of AppleScript.
applescript-language@abs.apple.com
is a private list for creators of AppleScript language pieces.
AppleScript-request@abs.apple.com
is for more information
AppleLink
"AppleScript Developer's Forum" in Developer Support: AppleScript Talk
"FileMaker Support Forum" in Third Parties:Third Parties
A-G:Claris:Claris InfoCenter:Technical Support - (Read):Technical
Support - FileMaker
"UserLand Forum" in Third Parties: Third Parties P-Z: UserLand
Discussions
America Online
Keyword CLARIS, Technical Support:FileMaker Pro:FileMaker Message
Boards:Scripting
Keyword MOS, Message Board:General Discussion:AppleScript
Keyword USERLAND
CompuServe
Go MACCLARIS, Browse Messages, FileMaker [2]
Go MACDEV, Message Sections:Scripting Month [10]
Go USERLAND
eWorld
Message Sections -Computer Center:Apple Customer Assistance Center:Apple
Products &
Technologies:The World of Apple Software:System Software Discussion:
AppleScript 1.1 folder and
Apple events folder.
7.3.4 Tips
- Fields are like columns, Records like rows of a table. Single elements
are Cells.
- All fields are available via layout 0
- The last record may be used by ID -1, e.g.
tell record -1 -- (same as 'tell last record')
- Within FMP3 "Document" refers to the found set, as sorted. "Database"
refers to the entire database, in record creation order (ignoring the
found set). This was also supposed to be true in FMPro 2.1v3, but was
not.
(from owen@astro.washington.edu (Russell E. Owen))
-"create new record" (and create new request) has two bugs for FMP3:
1) it sometimes creates in the wrong database, specifically:
tell document <databaseName
create new record
end tell
ignores the "tell document" and instead creates the record in the
top-most database, but:
create new record at database <databaseName>
works just fine. Be careful!
2) One can supply a list of data with the create command (in field
creation order -- ick), but it uses the current layout or layout 1 (I'm
not sure which) to set the data. There doesn't seem to be any way to
force it to use layout 0 (an invisible layout that contains all fields).
The documented method:
create new record at layout 0 with data <list-of-data>
does NOT work. A workaround is:
set recID to create new record at database <databaseName>
set record ID recID of database <databaseName> to <list-of-data>
(see ROFMUtil for a subroutine to create <list-of-data>).
(from owen@astro.washington.edu (Russell E. Owen))
7.3.5 Examples
Useful examples:
* data of first cell
cell 1 of database 1
* data of first cell of selected records
cell 1 of document 1
* data of first cell of current record
set CurRec to (Current Record of Database 1)
cell 1 of CurRec
-- should be equivalent to
cell 1 of (get Current Record of Database 1)
* data of all cells:
cell 1 of every record
-- is equivalent to
field 1
7.3.5.1 Load all image files
-- Load all the Image files from a (prompted for) folder into a
FileMaker
database.
-- "PictureLoads" and this file must be in the same location. (V1.1)
-- find the image files
set ImageTypes to {"PICT", "JPEG", "BMPp", "TIFF"}
set ImagesLocation to (choose file with prompt .
"One of the Image files to load" of type ImageTypes) as string
set x to (length of ImagesLocation) - .
(length of (name of (info for file ImagesLocation)))
set ImagesFolder to (characters 1 thru x of ImagesLocation) as string
set ImagesList to list folder ImagesFolder
-- where the hell are we? (only works in compiled "application" version)
set DefaultLocation to (path to current application) as string
set x to (length of DefaultLocation) - .
(length of (name of (info for file DefaultLocation)))
set DefaultFolder to (characters 1 thru x of DefaultLocation) as string
-- fire up FileMaker and JPEGView applications
set chk to {}
set PictureName to ""
tell application "Finder"
set FileMaker to application file id "FMPR" as string
set JPEGview to application file id "JVWR" as string
end tell
tell application FileMaker
run
open file (DefaultFolder & "PictureLoads")
end tell
tell application JPEGview
run
end tell
-- walk the list, open file in JPEGview, copy to new FileMaker record
repeat with ImageName in ImagesList
--(debug)set PictureName to ImageName
set ImageLocation to ImagesFolder & ImageName
set ImageInfo to (info for file ImageLocation)
set candidate to false
if not (folder of ImageInfo or alias of ImageInfo) then
set ImageType to file type of ImageInfo
if ImageTypes contains ImageType then
set candidate to true
set chk to chk & ImageName & ImageType
end if
end if
if candidate then
tell application "JPEGView"
activate
open {alias ImageLocation}
copy
close windows saving no
end tell
tell application "FileMaker Pro"
activate
Create New Record
--(debug)set PictureName to PictureName & return
& (ImageName as string)
Do Script "PasteImage" -- just does a Paste
[Select, "PictureField"]
copy (ImageName as string) to (Cell "FileName"
of last Record)
end tell
end if
end repeat
-- a little clean up
tell application "JPEGView"
quit
end tell
-- de nada
(From Sam Malenfant)
7.3.5.2 Idle handler
"When a stay-open applet has an idle handler but doesn't return a sleep
time, the default is to call it every thirty seconds. Strangely, when
there is no 'on idle' handler the Applet sucks time all over the place.
The solution: always include an 'on idle' handler in your stay-open
Applets but return a very large sleep time."
(by "Wayne K. Walrath" <wkw@FUTURIS.NET>)
on idle
return 30000
end idle
7.3.5.3 Object not found / error -1728
Error -1728 is object not found:
on error -1728
-- "object not found", which could mean no records match
-- or the database wasn't found, or a cell wasn't found or...
-- it's a shame the error message isn't more specific
if (not exists database <databaseName> then
error "Database " & <databaseName> & " does not exist"
else if (not exists cell <readCellName> then
error "Cell " & <readCellName> & " does not exist"
else
-- either one of the search cells does not exist (a hassle to
test)
-- or no records match; assume the latter
set recData to {} end if
end error
(example by Russell E Owen <owen@ASTRO.WASHINGTON.EDU>)
7.3.5.4 Multiple finds
AppleScript is very powerful to use for nested finds.
Here's one example on how to find on multiple fields that may be
configured by the user easily. A similar ScriptMaker approach would
require any possible set of find combinations, including multiple
copy/paste of find criterions.
Within FMP you should create a field with a predefined value list of
field names, e.g.:
[search_fields] (text):
section
subject
body
comments
Format this field as a checkbox field. Autoenter of values is possible.
Enter the search criterion to a find field and call the AppleScript.
Within the AppleScript use the find criterion on all names of the search
fields by something like
repeat with TheName in TheSearchFields
set cell "[mark]"
of (every record whose cell TheName = TheSearchCriterion)
to 1
This will search for an exact match, but others are possible as well as
multiple and nested AND/OR solutions.
Unfortunately there seems to be no command for a found set. Thus all
records have to be marked, then a find on [mark]=1 within FMP has to be
performed, then the mark field should get deleted again.
7.3.6 Call AppleScript
As described before you may send various AppleEvents from within FMP.
When you compiled the script as application, you may pass events to it:
on event xxxxyyyy
-- do something
end
Another solution is to pass complete scripts to an AppleScript editor.
You may send e.g. the "run script" event to the ScriptEditor:
send AppleEvent ["syso", "dsct", "ScriptEditor"]
and pass the script as text. Example:
within the ScriptMaker of FMP select "send AppleEvent". Specify the
application "ScriptEditor". Specify "Send the [Other] event" with event
class "syso", event ID "dsct". Specify the script text as:
tell application "Eudora"
make new message at end of mailbox out
set field "Subject:" to "text"
set field "To:" to "your nickname"
queue
end tell
"Effectively you call the Run Script osax (which should be present in
your Scripting Additions inside your Extensions folder). The trick is to
"tell" a non scriptable application (such as the Script Editor or any
other utility with no "aete" resource) to "run script etc..."
FMP can send simple events with only 1 direct param.
(inspired by raif@fl.net.au (Raif S. Naffah))
7.4 Aretha / Frontier
Yet another scripting system for AppleEvents.
It's free, it's faster than AppleScript, it's powerful.
<http://www.hotwired.com/staff/userland/aretha/>
please provide further information
7.5 Other tools
check for:
- ControlTower
- HyperCard
- MacPerl
- UserTalk
Ross Brown has written an extension called Menu Events. It allows you to
control the menus in an application that is System 7 savvy though
unscriptable. It is archived at
ftp://gaea.scriptweb.com/applescript/addons/
along with a companion program
ftp://gaea.scriptweb.com/applescript/addons/
James Davis (jedavis@cs.stanford.edu) has written an AutoType osax that
lets you simulate keyboard activity from a script. AutoType sends
keystrokes to the front application. It is archived at
ftp://gaea.scriptweb.com/applescript/osaxen/AutoType1.0.sit
(from http://www.sysnet.com/~pfterry/applescript.faq.html)
Probably the slickest solution is PreFab's Player. PreFab Software's
Player extension lets you select menu items and click buttons and
checkboxes in applications. Player has a smaller memory footprint than
QuicKeys, and it is easier to incorporate Player into your scripts than
QuicKeys. Player is available for both Frontier and AppleScript. Prefab
Software can be reached at 617/628-9555, voicemail; 617/628-9043, fax;
617/628-9025, inquires and sales; CompuServe: 70214,424; and Internet:
player@prefab.com, http://www.tiac.net/prefab/
(from ftp://gaea.scriptweb.com/applescript/PRs/)
7.5.1 Quickeys
Quickeys 3.0
CE Software, Inc.
Quickeys 3.0, a macro program for the Macintosh, now allows you to write
AppleScript commands
directly in your macros. Quickeys also provides scheduling facilities
and extensions that allow you to
mount volumes and choose printers. Suggested retail price as of Sept. 1,
1994 is $139.00 (U.S.).
Contact CE Software at (515) 221-1801.
7.5.2 KeyQuencer
"We often meet problems that can't be solved within FileMaker. Selecting
printers, sending faxes with the click of a button, using alerts and
dialogs, dialing phone numbers etc. We need to go beyond FileMaker.
Features:
- small memory footprint (full installation takes up a total of 124k on
my 68k Mac)
- modular; remove unused commands to save memory or add capabilities
with third party extensions (developer's toolkit included)
- powerful, clean and reliable (has never crashed my Mac and I haven't
seen an application that doesn't like KeyQuencer)
- networkable; control a mac remotely over AppleTalk
- fast; no process overhead as in AppleScript solutions
- best of all: Apple Events savvy, easy to execute macros from a
FileMaker script using Send AppleEvent
One disadvantage is that KeyQuencer isn't recordable, but I still think
it's easy to use."
(from Johan Solve <macboden@ALGONET.SE>)
<ftp://umich-mac/system.extensions/cdev/keyquencer1.21.sit.hqx>
<ftp:/info-mac/gui/key-quencer-121.hqx>
<ftp:key-quencher-121.sit>
Shareware 10 $
It was developped by Alessandro Levi Montalcini - his next release is
supposed to be commercial.
It is also useful to import plenty of pictures. It's easy to use in
order to script special tasks by automating manual tasks.
7.5.3 Tools for Windows
While all other tools in general are suited for Macintosh only (as long
as not stated otherwise), there are some matching tools for Windows:
Q: Is there under Windows a applescript-similar (comparable) programm ?
A: Actually there a quite a few of them. None ship WITH Windows
however. There is a REXX version for Windows, Visual Basic for
Applications is for controlling supported applications, OLE can do some
things, but it is mostly document based. WinBatch is an excellent
AppleScript-like thing. It can do ANYTHING you can do in Windows, as
well as some system-level things.
(From: Kurt Knippel <MondoMail@AOL.COM>)
Subject: 8 Miscellaneous Tips
8.1 Removing password
Contact Claris. They can do it for you, but it may take some time (> 2
weeks). They have a form for you to fill out.
The actual release is rather safe against hackers, but successful cracks
were reported.
8.2 Undocumented features
Shortcuts:
Control-C exits a field (like clicking in the record area)
Control-[ deletes the current field (like Select All & Delete)
Control-_ selects within the field to the end of the field text
(From Sam Malenfant <Sam_Malenfant@CARLETONBE.OTTAWA.ON.CA>)
In the Define fields dialog, the default button changes to Done (instead
of Create or Change) if you hit the delete key or erase the name of the
selected field.
(reported by JoelBowers@eWorld)
Shortcuts that work both in ScriptMaker and Sort, Export/Import:
Tab toggles control between the steps and the script
In the steps, type the first letter to move to a step
In the steps, use up and down arrow keys to move to next step
When the step is highlighted, use the spacebar to trigger move step to
script
In the script, spacebar triggers the "specify" dialog for the
highlighted script step
To move a highlighted step up or down in the script, use command key +
up/down arrows
(From Sheila Kliewer)
Script steps:
A Copy[] line in a Script without a specific field when not in any field
produces a tab and repeating-char delimited string of the whole record,
which can in turn be pasted into a specific field (where for instance it
might be parsed to get at Field'n' or Field'n'Repetition'm').
(From Sam Malenfant <Sam_Malenfant@CARLETONBE.OTTAWA.ON.CA>)
Invisible characters, such as tab or repeating-separator-29 can be
pasted into a text field and used in calculations with Position() for
instance to find and replace. With scripting or "default data", a field
call TAB for instrance, might be useful for parses.
(From Sam Malenfant <Sam_Malenfant@CARLETONBE.OTTAWA.ON.CA>)
8.3 Shortcuts
A new tip for FMP3 for Macintosh:
When in layout mode you can hold down the control key when selecting a
layout object. Depending on the object, you will be presented with a
popup menu of actions that you can take on that layout object. These
include the text attributes, colors and formatting, setting of a
portal's relations etc..
Undocumented Shortcuts, most of them collected from FMP2.1 for Macintosh
release:
Control-C exits a field (like clicking in the record area)
Control-[ deletes the current field (like Select All & Delete)
Control-_ selects within the field to the end of the field text
(From Sam Malenfant <Sam_Malenfant@CARLETONBE.OTTAWA.ON.CA>)
In the Define fields dialog, the default button changes to Done (instead
of Create or Change) if you hit the delete key or erase the name of the
selected field.
(reported by JoelBowers@eWorld)
Escape is even faster...
Shortcuts that work both in ScriptMaker and Sort, Export/Import:
Tab toggles control between the steps and the script
In the steps, type the first letter to move to a step
In the steps, use up and down arrow keys to move to next step
When the step is highlighted, use the spacebar to trigger move step to
script
In the script, spacebar triggers the "specify" dialog for the
highlighted script step
To move a highlighted step up or down in the script, use command key +
up/down arrows
(From Sheila Kliewer)
Documented Shortcuts from the Quick Reference:
c = /command/apple, o = option, s = shift
* Layout Mode:
Fields:
Redefine a field on a layout o-double click
Objects:
Constrain resizing to horizontal or vertical s-drag a handle
Duplicate by dragging o-drag an object
Move selected objects by one pixel at a time Arrow keys
Release grid while resizing c-resize
Reset default format of an object c-click
Squares object being resized o-resize
Select all objects of same type c-o-A
Parts:
Drag selected layout part across objects o-drag
Reorder selected part s-drag
Reorient part labels c-click
T-square on/off c-T
Tools:
Constrain line tool to horizontal or vertical o-drag
Constrain oval tool to a circle o-drag
Constrain rectangle tool to a square o-drag
* Browse Mode
Edit:
Copy c-C
Copy found set c-o-C
Delete without confirmation c-E
Delete without confirmation c-o-E
Paste c-V
Paste without style c-o-V
Paste from last record c-'
Paste from last record and move to next field c-s-'
(German release: use instead of ')
Move:
Next field Tab
Previous field s-Tab
Next Record / Request / Layout c-Tab
or o-Tab
Previous Record / Request / Layout c-s-Tab
or o-s-Tab
Text:
multiple for Align left/right/center/full, Bold, Italics, Underline etc.
Next point size up c-s->
Next point size down c-s-<
One point larger c-o-s->
One point smaller c-o-s-<
(how about the German keyboard where > is s-<?)
Delete one character left of cursor Delete
Delete one word left of cursor o-Delete
Move cursor by one word o-Left/Right
Move cursor to beginning/end of line c-Left/Right
Misc:
Help c-?
Network dialog box c-o-O
Show/Hide status area c-o-S
Zoom Window Full/Previous Size c-s-Z
8.4 Compatibility between DOS and Mac release
There's not much problem about.
Check the manuals, appendix E, "Cross-Platform Considerations".
Have another look at
<http://www.claris.com/TS/Featured/CrossPlat/Article.html>
- take care to use 8.3 file names,
- use the same fonts:
Different fonts and small differences within the same font family may
produce different display. Take care of proper alignment and use a
little wider fields
The standard setup of Mac contains Helvetica, while on Windows a similar
font is named Arial (same for Times and Courier). There's a translation
table to use the equivalent font. See [FontSubstitutes] section of the
WIN.INI file, e.g.:
[FontSubstitutes]
Helv=MS Sans Serif
Tms Rmn=MS Serif Times=Times New Roman
Helvetica=Arial
Monaco=Courier New
Otherwise, the standard translation is
PC Mac:
MS Serif Times
Times New Roman Times
Times New York
Tms Rmn Times
Courier New Courier
Courier Monaco
Commercial equivalent font packages are available.
I recommend TTConverter to translate TrueType fonts between Mac and PC
<ftp://ftp.switch.ch//mirror/umich-mac/util/font/>
- Printing: expect slightly different results
- Pictures: on PCs use the setting "Store Macintosh picture formats" to
display as PICT on Macs;
- other problems due to specific platforms:
Differences in character sets (e.g. not equal, greater than or equal)
and exact color mapping.
Certain ScriptMaker commands work different (e.g. send mail, page setup)
AppleScript is not supported on PCs, PC batch commands don't work for
Mac.
JPEG and QuickTime are special.
8.5 Buttons
- You may use any text and picture within a layout as button. Select an
object and use the menu Scripts / Define Button (for FMP2.1) to assign a
script to the button.
- Fields may not be used as buttons for FMP2.1. You may create a proper
background for a field that looks like a button and place an invisible
button above the field.
For FMP3 you may group a field with a button function. This will not
permit to select this field by mouse click, but by script or tab order
only. There is no status function to detect what field you clicked on.
If you need this information, you'll have to create one script per
button & field.
- An invisible button is an object that doesn't contain any visible
information, e.g. a rectangular box without filling and outline.
- Objects created by the layout tools of FMP get displayed very fast
while using little memory.
- Since the drawing capabilities of FMP are limited, you may take
virtually any other application to create objects and to paste them on
FMP layouts.
- Objects created by other tools may use very complex picture formats
and increase processing time and memory requirements. Take care to use
the minimum resolution (e.g. 72 dpi for screen buttons), the minimum of
components and the minimum color depth.
- As an option you may design complete button areas and backgrounds by
separate tools, paste them on a layout and place invisible buttons above
the according area.
* Example: The buttons of the [FAQ]FMP database (faq.fm) were created
e.g. by drawings within FMP itself, designed at 400 %, displayed at 100
%. The color depth was set to 16 colors by the control panel "monitors".
Then a screenshot was made by cmd-shift-3. The screenshot picture was
opened by SimpleText and a rectangular button selected and copied
directly from that display, pasted to an FMP layout and defined as a
button. The buttons were marked as non-printing objects.
- Try to assign a different line color to a button. Thus you may get
variations of a button easily. The greyed buttons within faq.fm were
produced by assigning a dark grey to them. This function is gone for
FMP3
8.6 Resources for dialog boxes
(from Dale Saukerson <sauke001@GOLD.TC.UMN.EDU>)
FileMaker is great, but I find myself doing excessive amounts of
scrolling thru the various dialog boxes when revising a database'
structure and layout. Many of the dialog boxes are minimal size, even
for a small 14" monitor. To correct this, I used ResEdit to increase the
size of the offending dialog boxes.
Listed below are the numbered DLOG resources for common dialog boxes,
for version 2.1v1 of FileMaker Pro for the Macintosh.
By opening these, you can drag the elements around and increase their
relative length, much like you would move objects around in a FileMaker
layout. If you have even minimal ResEdit skills (like myself) you can
easily perform the necessary modifications yourself with no fear of
destruction of data. However, ResEdit is a powerful tool and could be
dangerous in the hands of the ignorant, so the usual warnings apply.
DLOG 514
Script definition. Larger list of the script names shown when you select
"ScriptMaker" from the "Script" menu
DLOG 511
Define scripts. Shows the individual steps assigned to a script. Now
almost half of the possible script steps will be shown--two clicks of
the mouse and you can
see ALL of the script step choices. Nice.
DLOG 5000
Define fields. Create a larger list of the fields and reduce the amount
of scrolling. Also allows you to see the full length of the name in the
list of the fields instead of only in the "name" box portion of the
dialog box.
DLOG 5020
Reorder layouts. View more of the layout names in the dialog box and
minimize scrolling. Does NOT prevent trunctuating of the layout name in
the layout name pop up menu portion of the toolbox area. Two mouse
clicks to see all 67 layouts in my pet project database.
DLOG 5022
Sort records. View more of the field names before scrolling, though it
is only a modest increase.
DLOG 5039
Specify field order for import. View more fields with less scrolling.
DLOG 5040
Specify field order for export. View more fields with less scrolling.
8.7 The special name "-" as list separator
Whenever you use the "-" as a name, it may get displayed as a dotted
line within popup list views. You may use it to group similar or related
items. Within FMP you may use it for
* Predefined value lists of fields
Example for music storage media:
(every value is within a unique line, separated by <Carriage Return>)
CC
CC 60
CC 90
CC 100
-
CD
CD single
CD maxi
CD Box
CD Set
etc.
* Field Names
You may use the "-" as name, but it's not useful any more after you
sorted by any type / name etc. I recommend to sort by name and group
field names by a leading character or type, e.g. all parts of a name by
Name, Name_First, Name_Title, Name_Sex, Name_SSN etc.
Personally, I name fields for internal usage, such as date of creation
or serial number, within rectangular brackets, e.g. [date_of_creation]
I name calculation fields by a leading underscore, e.g.
_previous_serial_number = [serial_number] - 1 (That's a useful construct
to lookup values from the previous record)
There is no need to compose names as a single word, but within
calculations it's more useful just to double-click a name in order to
highlight it. For that reason I use the underscore as delimiter.
Others use e.g. a leading number or a leading character for the type of
the field, e.g. C for calculations, S for summary.
* Script Names
The "-" is very useful to structure the display of lists. There may
still be a real script behind this name. It may get called by other
scripts as usual. Use an ordinary name first, then edit any other
"perform script [name]" by selecting this script. You may change the
name afterwards to "-" again, but the reference won't get lost (I assume
the internal reference is not by name). BTW this works as well for
layouts and fields.
Warning: AppleScript references scripts by name:
Do Script "-" -- will perform the first script that is named "-"
* Layouts
Here the name "-" is most useful to hide layouts that contain
statistical fields or dialogs. You don't have direct access to them in
browse, finde and preview mode. Thus a user won't get confused by that
type of layout.
You may grant access to those layouts when you create scripts and link
them to buttons. As mentionned before use "Goto Layout [name]" by using
an ordinary name first, then change the name to "-".
My private solution is to jump to a layout by its number. For that
purpose I use a field named [mark]:
copy [select all, [mark]] -- thus the field gets selected, its content
gets buffered
paste literal ["3"] -- this is the number of a hidden layout
go to layout [take number from field [mark] ]
paste[select all, [mark]] -- thus the original contents get pasted to
[mark] again.
Within layout mode you may select hidden layouts by entering the layout
number, stepping through layouts by cmd-tab or dragging the handle of
the book.
8.8 WWW, HTML and CGI
How to link FileMaker Pro to the World Wide Web:
First, you will need a Web Server, such as WebStar.
Then, you will have to link FMP to it.
One solution is direct exchange of data via AppleEvents, AppleScript or
Frontier.
The other solution is to create a html output from within FMP:
* You may add html tags to fields by using simple calculations
Example:
_header = "<H3>" & header & "</H3>"
_text = "<P>" & text
Then export the html-formatted calculation fields instead of the
original field
* You may add or modify html tags by other applications
Consider simple find/replace tools, mail merge export and formatting,
AppleScript etc.
* You may add all tags to a html layout and use this data
The simple solution is to place the tags around the fields and use a
layout dump, e.g. by preview/copy all (information as PICT), or by
printing to a text file with Print2Pict.
For further information check the sections on FMPRO-CGI and ROFM CGI.
See also
http://edb518ea.edb.utexas.edu/scripts/cgix/cgix.html
http://www.uwtc.washington.edu/Computing/WWW/Mac/CGI.html
http://www.biap.com/machttp/examples/fmpro.sit.hqx
http://www.claris.com/TS/TIJ/fmcgi/article.html "Publish your database
on the Web"
8.8.1 HTML text conversion
8.9 Comments within calculations
Filemaker has no facility for this, yet it may [be] useful to annotate,
in order to remember what the calc was supposed to do.
Begin the calc field with:
If(1=1 ,"","My solution") &
(from jjl@knoware.nl (J. J. Lodder))
If (1, "", "Your description") &
should do as well.
Another one is
Left ("Yet another textual description", 0)
8.10 Empty fields
Find empty fields:
Enter an equal symbol as search parameter. This will check on exact
match to virtually nothing.
Number fields:
Their value is zero when no numerical values are included. You may use
other characters, but they won't get indexed. Thus a find on characters
within a number field will fail.
Calculation fields:
A calculation does not take place when the fields it relies on are
empty.. If (Test, "Yes", "No") thus will not produce a "No" while the
Test is empty. Previous calculated values may get kept instead of
deleted.
"Important: If Test refers to a field that does not yet contain a value,
then the If function is skipped and returns an empty result."
(from the FMP User's Guide, A-25)
You may force the calculation by forcing a value:
- If (Qualifier & "x" = "x", "Yes", "No"). This will force the
recalculation on existing, but empty fields, but it will still fail on
new emtpty records.
- Autoenter a value, e.g. default " " to Qualifier:
If (Trim(Qualifier) = "" , "Yes", "No")
Surprisingly enough, even by only marking the default value checkbox
without data made it work
- Use a special field, e.g. autoenter " " to a field named BLANK
If (Qualifier & Blank = " ", "Yes", "No")
FMP 3.0 offers options whether or not to calculate on empty fields.
8.11 Sort Order
The general sort order of records is [0-9][A-Z].
Any symbol that resembles an alphanumeric string gets sorted as its
equivalent, e.g. both upper and lower characters A and a as A, (=AE)
as A, (= copyright) as C.
All other characters get ignored while sorting, such as leading strings.
One special exception it the non-breaking space " " (character ID 220,
option-space). It is sorted first. Next is the space itself, including
all other characters that get indexed as space (see section on
indexing). Last come empty fields.
As a side effect you may find all filled fields not only by a search on
omitting all empty records (find field with contents that match exactly
nothing '=' & omit), but much faster by a search on greater equal 0 (
0)
If you'd like to sort case-sensitive, you may create an additional sort
criterion, e.g.
sort (calculation, result number) =
If(Exact(Upper(Left(text,1), Left(text,1)), "1: upper", "0: lower")
Then sort on text and sort instead on text alone.
8.12 Switch printers
FMP does not offer a built-in option to switch printers.
The Chooser is not scriptable itself.
QuicKeys is capable of receiving AppleEvents out of FileMaker and has a
very fast Chooser Switching utility.
KeyQuencer can do that as well. However, KeyQuencer can't choose between
network printers, just select a printer driver.
Yet another tool: Tempo
There is also an AppleScript extension (OSAX) called XCMD OSAX that will
accept commands to change the printer driver. It's available at:
http://www.scriptweb.com/scriptweb/osaxen/default.html
"Basically, to choose a printer, just drop the osax into your Scripting
Additions Folder (inside your extensions folder--you need Applescript)
and then go to Filemaker 3.0 and create a script with an Applescript
step like this:"
Choose Printer DriverName "LaserWriter 8" PrinterName "My Printer"
Zone "EtherTalk"
(from Paul Fabris <pfabris@ASTRAL.MAGIC.CA>)
"There is a OSAX called "Set Printer to" as part of the GTQ Scripting
Library v1.2 that does this. You need AppleScript and the OSAX
installed. Here is the OSAX library:"
set printer to: chooses the specified device type
set printer to string -- device type name
[named string] -- name of device
[using printer port/modem port] -- which serial port
to use
(valid for serial devices only)
[zone string] -- zone where device is
(from ehsan saffari <aa074@sfn.saskatoon.sk.ca>)
Comment: the page setup is stored within the FMP ScriptMaker command
"Page Size".
8.13 Error Codes
An Error Code is available immediately after its occurence and may get
requested by Status(Error Code),
e.g. within scripts as
If ["Status (CurrentError) <> 0"]
The Error Code is lost after the next step, since the next step may
return e.g. "0" again. Thus one may buffer the status to a certain field
[Status Current Error] of type global/number:
Set Field[[Status Current Error], Status (CurrentError)]
Here is a list of error codes taken from the helputility of FMP3 (by
romero@atlas.odyssee.net (Frank Romero))
Errors:
-1 Unknown error
0 No error
1 User canceled action
2 Memory error
3 Command is unavailable (e.g., wrong operating system, wrong
mode, etc.)
4 Command is unknown
5 Command is invalid (e.g., a Set Field script step does not have
a calculation specified)
100 File is missing
101 Record is missing
102 Field is missing
103 Relation is missing
104 Script is missing
105 Layout is missing
200 Record access is denied
201 Field cannot be modified
202 Field access is denied
203 No records in file to print or password doesn't allow print
access
204 No access to field(s) in sort order
205 Cannot create new records; import will overwrite existing data
300 The file is locked or in use
301 Record is in use by another user
302 Script definitions are in use by another user
303 Paper size is in use by another user
304 Password definitions are in use by another user
305 Relationship or value list definitions are locked by another
user
400 Find criteria is empty
401 No records match the request
402 Not a match field for a lookup
403 Exceeding maximum record limit for demo
404 Sort order is invalid
405 Number of records specified exceeds number of records that can
be omitted
406 Replace/Reserialize criteria is invalid
407 One or both key fields are missing (invalid relation)
408 Specified field has inappropriate data type for this operation
409 Import order is invalid
410 Export order is invalid
411 Cannot perform delete because related records cannot be deleted
412 Wrong version of FileMaker used to recover file
500 Date value does not meet validation entry options
501 Time value does not meet validation entry options
502 Number value does not meet validation entry options
503 Value in field does not meet range validation entry options
504 Value in field does not meet unique value validation entry
options
505 Value in field failed existing value validation test
506 Value in field is not a member value of the validation entry
option
value list
507 Value in field failed calculation test of validation entry
option
508 Value in field failed query value test of validation entry
option
509 Field requires a valid value
510 Related value is empty or unavailable
600 Print error has occurred
601 Combined header and footer exceed one page
602 Body doesn't fit on a page for current column setup
603 Print connection lost
700 File is of the wrong file type for import
701 Data Access Manager can't find database extension file
702 The Data Access Manager was unable to open the session
703 The Data Access Manager was unable to open the session; try
later
704 Data Access Manager failed when sending a query
705 Data Access Manager failed when executing a query
706 EPSF file has no preview image
707 Graphic translator can not be found
708 Can't import the file or need color machine to import file
709 QuickTime movie import failed
710 Unable to update QuickTime file reference because the database
is read-only
711 Import translator can not be found
712 XTND version is incompatible
713 Couldn't initialize the XTND system
714 Insufficient password privileges do not allow the operation
800 Unable to create file on disk
801 Unable to create temporary file on System disk
802 Unable to open file
803 File is single user or host cannot be found
804 File cannot be opened as read-only in its current state
805 File is damaged; use Recover command
806 File cannot be opened with this version of FileMaker
807 File is not a FileMaker file or is severely damaged
808 Cannot open file because of damaged access privileges
809 Disk/volume is full
810 Disk/volume is locked
811 Temporary file cannot be opened as FileMaker file
812 Cannot open the file because it exceeds host capacity
813 Record Synchronization error on network
814 File(s) cannot be opened because maximum number is open
815 Couldn't open lookup file
816 Unable to convert file
900 General spelling engine error
901 Main spelling dictionary not installed
902 Could not launch the Help system
903 Command cannot be used in a shared file
8.14 Get User's Name / Limit Access to Certain Users
Date: Wed, 10 Jan 1996 11:37:50 -0500
From: Chris Moyer <fmpro@MINDSPRING.COM>
Subject: Re: Password Question
Q: [...] limit access to people using the app to just those employees
that work at that particular location. [...]
A: Unfortunately, FileMaker 3.0 doesn't have a status function for
current group or password, so that part is a little funky, but the rest
is pretty straight up.
To work around the lack of password status functions, you need to use an
extremely modified variation on Michael Singer's old password trick. In
your case, you would set up groups that denoted location, then assign
individual passwords to a specific group. You need to create a
calculation field for each group, with the calculation equalling 1, 2,
3, 4, 5, etc. It doesn't matter what the values are, but they need to
corresponds with whatever code you currently use to denote location in
your Regular Location field. You need to have one more field, a global
field (either text or number format should work okay) called Location.
In your Access Privileges, you need to set up groups for each location.
For the purposes of this example, I'll refer to them as Location 1, 2,
3, etc. In the Access Privileges Overview, select the Location 1 group
and shut off access to all the location calcs except its own. Do the
same for the rest of the location groups.
In the ScriptMaker, you need to create a twisted version of a looping
script called Startup (or Fred or Lulu - you can call it whatever the
heck you want...) that has these steps:
Allow User Abort [Off]
Loop
Set Field ["Location", "Location 1"]
Exit Loop If ["Location>0"]
Set Field ["Location", "Location 2"]
Exit Loop If ["Location>0"]
Set Field ["Location", "Location 3"]
Exit Loop If ["Location>0"]
etc....
Exit Loop If ["Location=0"]
End Loop
Since you have access turned off for all of the location calculations
except one, all of those Set Field commands except one will fail,
returning a 0. I stuck a final Exit Loop If global does equal 0 in
there just so you can get out of the script if something went wrong.
Hypothetically, that step should never get executed. Just in case,
though....
If you had a ton of locations, you could make this into a regular loop,
but you'd have to use a layout and the go to next field step, and
another global. This way's a little more bullet-proof.
So...now you know what location a user belongs to, and they have no idea
how you know, so they probably won't be able to figure out how to defeat
your script even if they wanted to. What good does the location do you?
I'll tell you. By using calculated validations, you can selectively
lock records in a database that otherwise can be edited. Suppose you
have a Name field. Go into Define Fields, click Options, switch to
Validation options, select a calculated validation, and use this
formula:
Location = Regular Location
That's it. Set Strict Validation and give users a message explaining
that they can only edit records for their location, and you're done.
Oh, you do need to make that startup script a startup script in
Preferences, but after that you're done. You'll need to set this
validation up for every field that you want to restrict entry on.
8.15 Use // as literal text
The literal "//" within a text object is a place holder for the actual
date. Other special characters are e.g. "::" for the time, "||" for the
user name, "##" for the page number, "@@" for the record number.
But you need "//" in URLs, such as
<ftp://ftp.th-darmstadt.de//pub/database/filemaker-pro/faq.fm.sit>.
FileMaker Pro does not support characters, such as the regular
expression "\//", to escape a special meaning.
But there are various workarounds:
* place a space between both slashes: "/ /". When you format the space as
font size 1 point it's almost invisible.
* another solution is to use two separate text objects. The first object
ends with a slash, the second objects starts with a slash: "text1/"
"/text2"
You may place them one aside the other so that no space between is
visible. Tip: Verify the position by the position/size info and group
both objects together.
* Get the full URL or the double slash from a field.
FMP3 permits the usage of a global field, e.g. Double Slash = "//" to
store this value.
FMP3 also permits the usage of merged text where you may place fields
within a text's object environment: "text1 <<Double Slash>> text2".
* headds@world.net (David Head) reported to select the text object, then
assign to it the DATE format "none/none/none". This will use the actual
date, but display it as //.
8.16 Faster Screen Redraw
Here are some suggestions that may speed up the redraw of layouts:
Fields:
- Do not use border lines
- Do not use background colors
Fonts:
- Use a minimum of different fonts and styles
Picture Objects:
- Use the fastest drawing type of object.
- Objects drawn by FMP layout tools itself usually are optimized
for that job
- Objects drawn within other applications should be made
as small as possible:
- Use the minimum required resolution.
Cuts from screenshots (cmd-shift-3, rectangles may be cut even
within SimpleText) with 72 dpi are well suited.
- Use the minimum required color map
Personally, I switch to 16 color mode before I take a screenshot.
This will reduce memory, increase speed and does not require any
color mapping or dithering.
- use the minimum of objects
Example: you don't have to build a button from multiple drawing objects,
or a group of buttons by single buttons, each defined as a graphical
object and button of its own, but you may create them as a single
picture object (with minimum requirements) and place transparent FMP
objects (e.g. rectangles or circles) as buttons above
Other:
- Don't use background part colors
- Keep your layouts simple
- Instead of layouts filled with all fields, use several special
layouts with the required fields only
- Use the minimum window size
- hide unused applications and windows
- Maybe using a compressed database copy or a resorted clone can
speed up portals and lists.
- Assign more memory to FMP3 (less useful for FMP2.1).
- Use scripts to freeze the window while you modify multiple fields.
Screen redraw of lists became slower for FMP3, compared to FMP2.1.
"Use bring to front and send to back to layer things -
- layer all your static graphics (and text) to the back
- then layer all the local fields next
- layer the related fields on top of everything else
It won't actually reduce the total redraw time, but it will subjectively
feel faster."
(From: Eric Scheid <ironclad@planet.net.au>)
8.17 Date calculation
As indicated before the internal representation of dates is a continuous
number of days from 1.1.0001 on. This makes calculations on dates very
easy, since you may use them just as numbers.
The number of days between two dates is e.g.
StartDate - StopDate
A person's age thus may get computed e.g. by
Status(CurrentDate) - Birthday / 365
This will ignore the effect of leap years. A better estimation is
Status(CurrentDate) - Birthday / 365.25
The best suggestion, reported by hp.robin@infoboard.be (Henri-Pierre
Robin), is:
Year(Status(CurrentDate)) - Year(BirthDate)
- (Status(CurrentDate) < DayofYear(BirthDate))
It takes the boolean result 0 or 1 if the actual date is before the
birthday date of the year, indicating that it's not a full year yet.
For FMP3 you may use both Status(CurrentDate), perferable for the
unstored calculation, and Today, that gets updated on opening a
database.
FMP2.1 only offers the time consuming Today function. This function will
update the Today value within every calculation field and record,
regardless whether you are interested in its value. As a workaround you
should avoid the Today function, but use a scripted solution to paste
the actual date whenever required to a suitable "ThisDay" field.
8.18 Displaying Data as a Table
FMP is not a spreadsheet tool, but a record based database while there
is little dependency between the lines. Thus building tables is not a
simple job.
You have various choices to display your data as a table. The normal
format is to use a header line within a header part and display in list
view the records below as the selected and sorted lines of a table.
You may unify matching headers by subsummary parts. They get only
displayed in a list when you print or preview a page that is sorted as
specified for the subsummary part.
When an object's topmost line touches a part, it belongs to this part,
regardless how many other parts below it overlaps. This may get used to
make an object, such as a field, drawing or label, as belonging to a
header part, while it is displayed within the part below.
For FMP2.1 you could group objects together and the topmost line of the
group determined the part. This is gone for FMP3, but with merge text
you may get a similar output.
You then may build tables by multiple columns, e.g. to display in order
[leading ---------
text 1 | 3
object] --- ---
2 | 4
--- ---
Example: HTML table
It may get used to produce the html source code for a WWW page.
This object is within the header or subsummary part (pate break after
every occurence):
<HTML><HEAD><TITLE>My html database</TITLE></HEAD><CENTER>
<table width=99% border=1 cellpadding=1 cellspacing=1>
This single text object touches the header:
</TR><TR><TD><B>Label1
</TR><TR><TD><B>Label2 </B>
</TR><TR><TD><B>Label3 </B>
Now place single objects within the body
<TD> [Field1]
<TD> [Field2]
<TD> [Field3]
Those objects are on the same height as the labels that you want to use.
As a multicolumn preview, printed e.g. to a file, you may obtain a html
table with its central part similar to
<TR><TD><B>Label1 <TD> 1.1 <TD> 2.1 <TD> 3.1 </TR>
<TR><TD><B>Label2 </B> <TD> 1.2 <TD> 2.2 <TD> 3.2 </TR>
<TR><TD><B>Label3 </B> <TD> 1.3 <TD> 2.3 <TD> 3.3 </TR>
where "1.3" is the content of the third field of the first record. The
text object </Table> should be somewhere in the footer.
But what to do if you want neither a display by lines, nor by columns:
FMP3 offers the mechanism of related records and portals. Thus a simple
solution is to build a relation for every cell of a table and display
the matching information within.
Example: TimeTable
Day: Monday
Time: 8-9 9-10 10-11 ...
room 1 user1 user2
room 2 user3
room 3
You got one database "Table" that holds the table, another "Records"
that holds the combinations:
Day, Period, Room, User:
Monday, 8-9, 1, user1
Monday, 9-10, 1, user2
A proper relation is e.g.
Table:"Mon-08-1" to Records:keyfield
keyfield = Day & Period & Room
(formatted with additional text operations)
The second relation is
Table:"Mon-08-2" to Records:keyfield
etc. up to
Table:"Mon-17-99" to Records:keyfield
You now place the related field user as
8-9 9-10
room1 [relation1::user]
room2 [relation2::user]
etc. Use the feature that you may convert a time period, such as "8-11",
to three one-hour periods, separated by a carriage return for a proper
many-to-many keyfield, resulting e.g. in
"Tue-8-1Tue-9-1Tue-10-1"
It's cumbersome to define all these single relations, but straight
forward. But instead of many relations there may be suitable solutions
that use portals and multiple rows as well.
8.19 Zoom window size
In case you want to adjust and resize the window for an optimum size,
maybe even while switching layouts, you may use the Toggle Window [zoom]
command.
Create an invisible "frame" the size that you want your windows to
display. Use e.g. a rectangular object of the desired outer size with
"line: none" and lock it.
FMP will zoom to the outer edge of the frame. Therefore, everytime the
user goes to a different screen, they get the screen size that you want
them to see.
Of course there may be no other objects outside the frame. If you have
to use some "hidden" fields, place them hidden or invisible e.g. at the
top left edge.
This auto-zoom solution counteracts the user who likes to manually
resize the window. Thus you may perform e.g. a special "Zoom" script
instead of the Toggle Window script command:
If[GlobalZoom]
Toggle Window[Zoom]
End If
Global Zoom here is a global number field where you may assign a boolean
value whether or not to zoom windows.
Subject: 9 Data Exchange
9.1 Copy a layout from one database to another
It is not possible to copy complete layouts between databases. However,
it is possible to copy any picture information, fields and buttons to
other databases.
Open Layout mode, select, copy, go to other database, paste.
Fields will be copied only if a field with identical names is already
defined in both databases.
The link of scripts to buttons will be copied only if there is a script
with identical names in both databases.
9.2 Modify layout objects
First a tip to select all objects of a certain type:
In layout mode you can select objects by type. First select one object
of the type you want, for example, click on a field. Then press
Command-Option-A to select ALL the fields on your layout.
(from FMUG at MIT)
Now some other tips:
(1) While in layout mode you can hit the ENTER key and the tool palette
will toggle between the arrow tool and the last chosen object tool.
(2) Using the DUPLICATE command will offset the duplicated object by 6
pixels to the right and 6 pixels down. Using the ARROW keys you can type
7 left and 7 up to create a highlight or offset of the original object.
(3) Using the OPTION key on Mac or the ALT key on the PC will allow you
to drag copy a selection. Hold the SHIFT and OPTION or ALT keys and it
will constrain the copy while you drag.
(4) Locking your background objects will allow you to drag select your
foreground objects.
(5) Once a button is created, simply duplicate it and double click to
assign it another function. You don't have to go to the Define Button...
menu item.
(from Matt Petrowsky and its FMPro online user magazine / ISO)
9.3 Copy a record or list of records to other applications
* If no field is selected, the copy command will copy the current record
to the clipboard. The clipboard includes all fields, in order of
creation, as tab-delimited text without styles.
If you press option while doing this, all selected records will be
copied.
* Open Preview and Copy.
The records are copied to a PICT as they are displayed on the screen.
They can be pasted to and modified by other drawing tools.
* To copy text info only, see Print2Pict.
9.4 Export / Import
FMP supports multiple export and import formats for databases as
described in section 8 of the User's Guide.
Some aspects should be pointed out:
* Most export and import formats use the <Carriage Return> (ASCII-13) as
a record separator.
For that reason the returns within text fields get translated to
"vertical tabs" (ASCII-11).
Macs use <Carriage Return> as EndOfLine delimiter, while Unix takes
<Line Feed> (ASCII-10) and DOS takes both. This might confuse imports
and should be fixed before.
* Repeating fields are separated by <ASCII-29>. The export formats
"Tab-separated Text" and "Mail Merge" support repeating fields
Check the section on repeating fields for details. There's an export and
import option to split repeating fields.
* Take care of various limitations:
FMP supports text fields up to 32K characters, while BASIC, SYLK and DBF
use 255 or less characters.
Some special characters have to be translated when they are used as
field delimiters, e.g. quotation marks to double quotation marks.
There is no export format for pictures and sounds. Only the import from
one FMP database to another FMP database is possible.
* Various applications use output formats that may be imported by FMP
easily, while others have to be converted.
Many common text editors may get used to replace one type of delimiter
by another one, e.g. <quotation mark><colon><quotation mark> by <tab>.
Alpha allows to highlight, copy and paste text by columns.
Add/Strip allows to convert fixed width field formats by inserting
delimiters and deleting noise characters.
* Finally, you may use import to reorder your records.
Create a clone of your database "old", select all (if you wanted all),
sort, open the "clone of old", import from "old". Backup (or delete)
"old" and rename the clone to "old".
9.5 Export formatted text / RTF
Formatted text may get exchanged between applications by the usual
copy/paste.
On export any text format information is lost - only plain text will be
available. A mail merge export may get used for mail merge capable
applications to re-assign formats on complete fields.
In order to export formatted text, some additional steps are required.
The idea is to add the special formatting options by calculations of a
suitable format, such as RTF or HTML, and to use an application that may
handle this coding.
RTF (Rich Text Format) is the internal representation used on Macintosh
to exchange formatted text between applications. You may see a sample of
RTF coding if you save a document in RTF format from a suitable
application. The RTF codes below were collected in this manner by
j.f.robertson@newcastle.ac.uk (Forbes Robertson).
A proper RTF document consists of a header and special codes around the
text to format.
RTF codes
=========
bold \b
italic \i
underline \ul
return \par
Curly brackets are the way of splitting RTF codes from the actual text
that is plain ASCII. The above can be combined e.g. {\b \i sample text}
to give bold italic "sample text".
Font changes are a little different, composed from corresponding font
numbers in the header.
Fonts \f<n> -- font number <n> of header
Example:
{\f3 text \par}
To set different font sizes the same layout as fonts is used. But
instead of \f<n> the code is \fs<n> were n is twice the size of the font
you wish to use.
Example for font size 10:
{\fs20 text \par}
If you need to use the characters { } or \ precede them with a \.
RTF header
==========
It seems that the minimum header is
{\rtf1\mac\
A matching final } closes the document as footer.
The following information is part of the header as well:
There is a list of fonts with numbers eg
{\fonttbl{\f0\fswiss Chicago;} etc }
Then a list of colours eg
{\colortbl\red0\green0\blue0; etc }
Then a stylesheet. This part has several variables some of which are
obvious, while others may be word processor dependant as well
{\stylesheet{\s242 \f16 \sbasedon0\snext0 page number;}
{\f16 \sbasedon222\snext0 Normal;}}
Then details on the paper size and margins, eg:
\paperw11880\paperh16800\margl2016\margr1440\widowctrl\
enddoc\pgnstart5\fracwidth\sectd\sbknone\pgnx720\pgny15030
\linemod0\linex0\cols1\endnhere
In conclusion you need a header consisting of a lot of font info. The
best way to get this is to save a file in your word processor as RTF
then view the file without conversion.
Export
======
You may include the RTF codes by ordinary calculations.
Example: RTF Text (text calculation) =
"{{\b " & [Name] & "}, " & [First Name] &
"\par} {email: ""{\f0 \fs20" & [email] & """\par}"
It's recommended to export this as tab delimited text, with only the
calculated RTF field per record.
You may put in the header and footer info while you use a separate
record for each and ensure that they sort first and last in the sort
order.
Also can you get FM to save the exported text file with the correct
creator codes so that double clicking it opens it in your word
processor. This is possible to do using programs like Filebuddy etc.
Print to file
=============
You may create a special layout where you place the proper RTF or HTML
code around the fields, create proper header and footer parts and print
this layout to a file.
Example:
[header part]
{\rtf1\mac\
[body/list]
{{\b[name]}, [first name]\par}
{email:" {\f0\fs20[email]"\par }
[footer part]
}
The [fields] and text objects may get defined as sliding to the left.
FMP3 permits improved text merge options for the same purpose.
9.6 Access Files Remotely
Date: Thu, 18 Apr 1996 20:56:06 +1000
From: Graham Nicholson <grahamn@VISION.NET.AU>
Subject: Re: Update from a remote source
Something about accessing files remotely. I am note sure if it was for
maintenance only or if you wanted to use concurrently both on site and
away. If for maintenance or on line access only then...
OK, I do this to remote access a site that is under my control.
I offer you three solutions
1) Carbon Copy
2) Timbuktu
3) Windows 95
4) FMPro3 WWW Access
1) Carbon Copy
I use carbon copy for windows and have essentialy control of the login
computer. The database is running on the dial in which is both good and
bad, good speed wise because only screen redraws are passed machine to
machine not actual data (which ARA does). The speed is not great but
they have PC 386/40 which is bottom end for FMPro anyway, using 14.4
modems. I think with a fast machine/modem it should be quite acceptable.
Setup was a breeze, just install the remote software on each machine and
choose the modem type.
Pros - Speed of link is good. If the machine you log onto is networked
then you also have access to the network.
Cons - No cross platform capability (Windoze only (3.1 or Win 95)
2) Timbuktu
This has always been a very *cool* piece of software that allowed
control over appletalk and also dial in. Recently Timbuktu has been
updated to include also a Windows equivalent. There is an immediate
*Huge* advantage, you can control a Mac from a PC and also a PC From a
Mac (also PC-PC and Mac-Mac of course) so now if you need to access a
clients site which is PC based you can log on with your Mac :-) As with
Carbon Copy only screen data is passed on so speed of the link is not a
vital ingredient. Fairly processor intensive on both sides though, same
as Carbon Copy
Pros - Speed of link is good. If the machine you log onto is networked
then you also have access to the network. Cross Platform capability, oh
securtiy measures are good also
Cons - None spring to mind but I am sure there are some
3) Win 95
I believe that Win 95 (or Microsoft Plus anyway) has a type of ARA built
in and have seen it in action. No idea about performance speeds,
remember that you are now accessing a network not just screen data so
having it open on a machine on the network and logging on as a guest
would give better speeds than accessing the file off a server as a
shared file (I think, not too sure how FMPro handles this)
Pros - Built into OS so should be widely supported (Win 95)
Cons - I have not used the system, not even sure if FMPro would work
using this should in theory
4) WWW access.
If both are using FMPro 3 then it is possible for the "host" to have a
permanent internet connection and the dialup to access it as if it were
local talk (nearly, not quite as fast)
------------------------------
Subject:10 Useful Tools
10.0 Useful tools
This section is still at work. Please join your experience.
Most of the recommended tools are available at the usual umich and
info-mac sites and its mirrors. If you want any special tool included in
this list, please provide the appropriate information. I reserve to
include only tools that I consider as useful.
10.1 Print2Pict
Print2Pict is a chooser extension. Instead of printing to a real device,
the output is directed to a file. Different file formats are available.
The most important are TEXT and PICT.
URL: <ftp:/umich-mac/system.extensions/chooser/print2pict3.6.cpt.hqx>
10.2 Acid Jazz etc.
tools for fax, phone calls, etc.
URL: under construction
10.3 BBEdit Lite
Freeware text editor; powerful find / replace, suitable for handling
repeating fields (ASCII-29 dilemma)
URL:
<ftp://ftp.barebones.com/pub/freeware/>
<http://www.barebones.com/freeware.html>
<ftp://info-mac/text/bbe/>
It's very handy to edit calculation fields within, and to move to FMP by
Copy/Paste:
"Starting with BBEdit 3.0, BBEdit Lite 3.5 there is soft wrap
capability. This makes convenient editing of FMPR calc fields possible.
Calc fields are TEXT, and can be copied and pasted into. Copying to a
tmp BBEdit file makes powerful editing of formulae possible, useful once
you have formulae longer than the minuscule scroll window of FMPR.
Application: US versions use ,'s as separators, eg If(,,), some
localized versions may use ;'s, eg If(;;), depending on the installed
system. This makes copying of formulae from templates tedious. Compiled
formulae will run between versions, any change in a formulae caused
recompilation and all ,'s must be changed to;'s. BBEdit does it for you,
without errors, but you have to rescue bona fide ,'s occurring between
"".
Note: FMPR calc fields use Apple's text engine, and are thereby limited
to 32K."
(from jjl@knoware.nl (J. J. Lodder))
10.4 Ram Disk
Use a Ram Disk for import / export - it might be much faster.
URL: under construction
10.5 Send Mail from within FMP
You may send EMail directly from within FMP via TCP/IP by AppleScript
commands.
You will need e.g. the "TCP/IP scripting additions" osax. Its script
"emailer" will show a possible example.
<ftp://nic.switch.ch//mirror/umich-mac/util/script/
.cpt.hqx>
As another approach you may create outgoing messages in other,
scriptable mail applications. The template release of these FAQs,
faq.fm, contains a send mail button to do this via the included script
Eudora2FMP
10.6 Dump Mail to FMP
* Direct approach
FMP may download mail directly via TCP-IP connections. Marionet is a
commercial TCP tool suited for that purpose.
The AppleScript "TCP/IP scripting additions" may be suited for that
purpose as well.
<ftp://nic.switch.ch//mirror/umich-mac/util/script/
.cpt.hqx>
* Special Applications:
MailProcessor converts mailbox files to tab-delimited text that may get
imported into FMP. IMO the version I tried was slow, noninuitive and of
poor performance.
MailConverter by R Shapiro <rshapiro@BBN.COM> is an excellent tool for
converting mailbox formats, but doesn't include a database format.
However, R Shapiro might develop yet another tool for that special task.
I suppose this would be the premium choice.
* MacPerl
MacPerl was recommended to write a scriptable conversion utility. It's
very powerful on text operations.
Bob Dalgleish developped a MacPerl script that does an excellent and
fast job. Check on <http://www.sasknet.com/~dalgl/> for 'mailbox to
tsv'. It's still under construction, but already to recommend. It
requires MacPerl 5 (<ftp://ftp.switch.ch//software/mac/perl/>).
* AppleScript
Stefan Schtt (stefan@mouseup.pp.fi) wrote the consequent AppleScript:
++++++++++++++++++++++++++++++++++++
tell application "Eudora 1.5.1"
set many to (get number of message of mailbox 1)
-- (comment from the Author: mailbox 1 is the In box)
end tell
repeat with i from 1 to many
tell application "Eudora 1.5.1"
set day to word 3 of (get date of message i of mailbox1)
--this will get only the short date of the message.
set send to (get sender of message i of mailbox 1)
set sub to (get subject of message i of mailbox 1)
set bod to (get body of message i of mailbox 1)
--delete message i of mailbox 1 -- This one is optional
end tell
set lista to {day} & {send} & {sub} & {bod}
tell application "FileMaker Pro SDK"
Create Record With Data lista
end tell
end repeat
In the FMPRO database you need the following fields: Date, Sender,
Subject and Body. To be sure it will succeed, I think you should
create
the fields in this order. If your database for Eudora mails is the
frontmost window in FMPRO when you launch the script, then you don't
have to specify the name of the database.
++++++++++++++++++++++++++++++++++++
An improved version is included as Eudora2FMP with the FileMaker Pro
FAQs. Its major drawback: AppleScript is slow. On an LC II import is
about 100 messages per hour, twice the time of the import itself. The
import to FMP 2.1 is so slow due to extensive indexing of lenghty
message bodies; FMP3 can do much faster without.
<ftp://ftp.th-darmstadt.de//pub/database/filemaker-pro/faq.fm.sit.hqx>
Bob Cusick <clickware@aol.com> composed another set of templates
<ftp://ftp.fogcity.com/pub/Emailer/Utilities/>. They
contain an AppleScript to import from EMailer ( by Claris). They are an
example for most polished FMP 2.1 layouts - give it a try. FM Mailer is
freeware and unlocked. Bob Cusick announced to work on a new FMP3.0
version with improved features and direct import capability.
* HyperCard
"ETD (Eudora To Database) is a great utility that allows you to read a
Eudora mailbox, and create a file that is suitable for importing into a
database or spreadsheet (tab-delimited field, return-delimited record
format). I have included a template for use with FileMaker Pro.
Registered users (Shareware - $5.00 [US Funds]) can get support at
jacobson@phoenix.net."
(from Doug Jacobson <jacobson@phoenix.net>)
<http://www.phoenix.net/~jacobson>
10.7 Fax from FMP
* see Acid Jazz
* FaxExpress (fax software) from Glenwarne Ltd.
* FileFax allows for a clean and easy connection between FileMaker and
FaxStf , they have a demo version, the full version costs 100US$
<http://www.Xon-Xoff.com/web/FileFax.html>
An automatic mail answer is also available, FileFax@Xon-Xoff.com, to
receive an email documentation. No specific syntax required.
* FaxStf
* use Quickeys to change to a fax driver, and do a print command
* 4-Sight, LC has a network Fax solution that is now AppleScriptable...
It also includes some sample scripts that work with FM Pro.
515-221-3012 - Kevin Clark is a very helpful sales rep.
* Prefab Player (scriptable)
http://www.tiac.net/prefab/ , Scott Lawton <ssl@prefab.com>
* MacComCenter from Smith Micro Software Inc., 31 Columbia, Aliso Viejo,
CA 92656 (maybe slow, but works with AT&T Paradyne DataPort 14.4 modem)
* Windows: FileMaker is not capable of selecting a printer.
You will probably need to use WinBatch to do that (& any usual Fax
software)
(from various replies on FMPRO-L)
10.8 Speed Doubler
FMP on Power Macs with Speed Doubler ran 225% or 230% as compared to
100% for FMP without Speed Doubler.
(September test MacUP)
Actual problems on networks
10.9 ROFM CGI
"ROFM CGI (Russell Owens FileMaker CGI) is an application that allows
you to serve FileMaker Pro databases to the World Wide Web (WWW). It is
easy to set up and use. It runs on Macintosh computers (only), and
requires WebSTAR or MacHTTP, Filemaker Pro 2.1v3 and AppleScript 1.1
(which comes with system 7.5). To email notification of new records, you
will also need Eudora 1.5 (free) or 2.1 (commercial)." (From Russell
Owen's FileMaker CGI introduction)
The home site of ROFM CGI is http://rowen.astro.washington.edu/.
General-purpose subroutines for adding records and searching databases
are available as a compiled script (with included source code) called
"ROFMUtil". This script is included in the ROFM CGI package, which is
available from <http://rowen.astro.washington.edu>. The subroutines are
fairly well optimized, and require Wayne Walrath's ACME Script Widgets.
10.10 GTQ library
>Does anyone know if FMP3 has the capability of scripting your printer
choice?
There is a OSAX called "Set Printer to" as part of the GTQ Scripting
Library v1.2 that does this. You need AppleScript and the OSAX
installed. Here is the OSAX library:
set printer to: chooses the specified device type
set printer to string -- device type name
[named string] -- name of device
[using printer port/modem port] -- which serial port
to use
(valid for serial devices only)
[zone string] -- zone where device is
it works fine with my serial StyleWriter,
set printer to "StyleWriter 1200" using modem port
find it at info-mac
(From: ehsan saffari <aa074@sfn.saskatoon.sk.ca>)
------------------------------
Subject:11 Special Solutions
11.1 Templates for collecting CDs
A Box of Rain Database
Allison's CD DB
Audiofile 1.01
CD Catalog (replaced by The CD Database)
CD Club
CD Directory
CD Manager
CD Tracker PRO (replaced by Audiofile 1.01)
CDelux
CD-Lib1.0
CDatabase
CDBankE
Music Store 1.2
The CD Database 1.3
Klassik1.0
CDLite
CDLite Classical
CD Coyote (helper appl.)
Index of Umich-Mac (and ftp.claris.com):
SIZE DATE
(KBYTES) ARCHIVED COMPRESSION FORMAT(S)
-----------------------------------------------------------------------
<ftp://ftp.claris.com//pub/USA-Macintosh/Templates/FileMakerPro/
AllisonsCD_DB.sea.hqx>
<ftp://ftp.claris.com//pub/USA-Macintosh/Templates/FileMakerPro/
BoxOfRain.sea.hqx>
/mac/util/filemaker/aboxofrain1.1.sit.hqx
463 12/21/94 BinHex4.0,StuffIt3.50
Filemaker Pro 2.x templates designed to catalogue albums, CDs,
DAT, cassettes and other music media as well as song lyrics;
print collection lists and custom tape cassette inserts; more
buttons, scripts, graphics, special extras and neat tricks.
/mac/util/filemaker/audiofile1.01n.sit.hqx
425 8/6/95 BinHex4.0,StuffIt3.50
Music collection tracker with lots of features and
a nifty interface; this is the next step up from
CD Tracker Pro (and by the same author, so you see:
it's an <*tah-dah*> 'Authorized' version! *heh*duck*).
<ftp://ftp.claris.com//pub/USA-Macintosh/Templates/FileMakerPro/
CDatabase.sea.hqx>
/mac/util/filemaker/cdcatalog1.3.sit.hqx
119 11/20/94 BinHex4.0,StuffIt3.50
FileMaker Pro document for catloging compact discs allows
search, print, and report generation; v1.2 includes a number
of field changes and some changes in user interface.
/mac/util/filemaker/cdclubs.sit.hqx
66 2/10/95 BinHex4.0,StuffIt3.50
Are you one of those people who likes to send in a penny
to a CD Club and get back 10 CDs? Here are three templates
for you... one for the BMG Music Club (BMG Wantlist),
another for Columbia House (ColumbiaHouseWantlist) and a
third for Disc Club Account Info (where you can track how
much you've saved, or spent, so far). Includes FAQ and WWW
page for the real junkies.
<ftp://ftp.claris.com//pub/USA-Macintosh/Templates/FileMakerPro/
CD_Database1.2.sea.hqx>
/mac/util/filemaker/cddatabase1.3.sit.hqx
134 7/18/95 BinHex4.0,StuffIt3.50
Catalogs CDs; requires FileMakerPro 2.1 or greater; search,
print, generate reports; v1.3 has track numbers for each song,
smart sorting (ie: ignores 'the'), and more.
/mac/util/filemaker/cddirectory1.0a1.sit.hqx
46 10/7/94 BinHex4.0,StuffIt3.50
Keep track of your valuable CD collection Requires FileMaker 2.0.
/mac/util/filemaker/cdlibrary1.0.cpt.hqx
42 5/30/94 BinHex4.0,Compact1.51
A library archive for musical compact discs created in
FileMaker Pro; printing is disabled in demo.
/mac/util/filemaker/cdlite1.0.sit.hqx
292 5/15/95 BinHex4.0,StuffIt3.50
Simple database catalogs CD's for those who don't want or need
a lot of superfluous info in their databases; requires FMPro2.1.
/mac/util/filemaker/cdliteclassical1.1.sit.hqx
330 8/27/95 BinHex4.0,StuffIt3.50
Variation of CDelux/Lite, but more in line with the needs
of a classical cd collector; requires FilemakerPro; v1.1
has many new features and a user manual; free.
/mac/util/filemaker/cdlux1.1.sit.hqx
502 6/23/95 BinHex4.0,StuffIt3.50
Contains CDLite and CDLite Pro, cd cataloging databases; require
FileMakerPro2.1 and 1.4MB to run; free.
<ftp://ftp.claris.com//pub/USA-Macintosh/Templates/FileMakerPro/
CDTrackerPro3.02.sea.hqx>
/mac/util/filemaker/cdtrackerpro3.02.sit.hqx
451 5/15/95 BinHex4.0,StuffIt3.50
Nifty CD organizer; cool graphic interface tracks by
artist/album, title/song/label, more; imports and exports;
requires a 13" color monitor and FileMakerPro2.1 or later;
v3.02 contains many new features and user requested changes.
/mac/util/filemaker/musicstore1.2.sit.hqx
344 9/14/94 BinHex4.0,StuffIt3.50
FileMakerPro database archives data about your CDs, LPs, DATs, CD
Video, etc.
11.2 Numbers to Text
These fields will convert to text any amount under one million dollars.
Field Name Field Type Formula / Entry Option
Amt Number
Tn Calc (Number) = Int (mod (Amt,100))
Hd Calc (Number) = Int (mod (Amt,1000) / 100)
TTh Calc (Number) = Int (mod (Amt,100000) / 1000)
HT Calc (Number) = Int (Amt / 100000)
TnTx Calc (Text) =
" One Two Three Four Five Six Seven
Eight Nine Ten Eleven Twelve Thirteen Fourteen Fifteen
Sixteen SeventeenEighteen Nineteen "
TenTx Calc (Text) =
" Twenty Thirty Forty Fifty Sixty SeventyEighty Ninety "
AllNumberText Calc (Text) =
if (HT, trim (middle (TnTx, HT * 9 + 1, 9)) &
if (TTh, " Hundred ", " Hundred Thousand "), "") &
if (TTh, if (TTh > 19,trim (middle (TenTx, Int (TTh / 10) * 7 + 1, 7)) &
" " &
trim (middle (TnTx,mod (TTh, 10) * 9 + 1, 9)),
trim (middle (TnTx, TTh * 9 + 1, 9))) &
" Thousand", "") &
" " &
if (Hd, trim (middle (TnTx, Hd * 9 + 1, 9)) & " Hundred ", "") &
if (Tn > 19, trim (middle (TenTx, Int (Tn / 10) * 7 + 1,7)) &
" " &
trim (middle (TnTx, mod (Tn, 10) * 9 + 1, 9)),
trim (middle(TnTx, Tn * 9 + 1, 9))) &
" and " &
round ((Amt - Int (Amt)) * 100, 0) &
"/100"
(by Jim_Spelman@iguanabbs.com)
------------------------------
Subject:12 About
12.0 About
Composed by Martin Trautmann, <traut@th-darmstadt.de>, Germany
Please give any corrections, feedback, comments, improvements, requests
for additions or deletions or whatever you think might help!
Although sometimes still under the construction, the always latest
release is on:
ftp://ftp.th-darmstadt.de//pub/database/filemaker-pro/
compressed database: faq.fp3.sit (almost recent, includes Eudora2FMP)
or plain text: [FAQ] FileMaker Pro (less recent)
The database release is made available on info-mac and umich. Previous
releases are:
<ftp:/info-mac/info/sft/filemaker-pro-faq-95-12.hqx> "Info-mac mirrors"
<ftp://mac/util/filemaker/filemakerprofaq2.6.sit.hqx> "Umich mirrors"
The FAQ as text release might be found at:
<ftp://rtfm.mit.edu//pub/usenet/comp.sys.mac.databases/[FAQ]_FileMaker_P
ro_-_database_for_Macintosh_and_Windows> "Plain text"
<ftp://ftp.uni-paderborn.de//pub/FAQ/comp.sys.mac.databases/[FAQ]_FileMa
ker_Pro_-_database_for_Macintosh_and_Windows> "Plain Text, German
mirror"
<http://www.cis.ohio-state.edu/hypertext/faq/usenet/databases/filemaker-
pro/faq/faq.html> "HTML format"
<http://www.cis.ohio-state.edu/hypertext/faq/usenet-faqs/bygroup/comp/sy
s/mac/databases/top.html> "HTML format"
and the other FAQ sites that hold news.answers and comp.answers.
Try also the November release at:
<ftp://www.claris.com//pub/USA-Macintosh/Templates/FileMakerPro/
ro.FAQ>
Version history:
3.1 Maintenance release
3.0 FMP3 sorting portals, other FMP3 stuff
All comments without any warranty - please tell me if I missed any legal
aspects. Std. disclaimer, std. trade marks, std. copyright, no
commercial distribution without my permission, ...
-- you may test my new mail address at traut@th-darmstadt.de --
Martin Trautmann . Solid State Electronics Laboratory
Tel: ++49-6151-16-5141 . Technische Hochschule Darmstadt
Fax: -5233 . Schlossgartenstrasse 8
EMail: traut@iht.e-technik.th-darmstadt.de . D-64289 Darmstadt
Author of the Frequently Asked Questions on FileMaker Pro:
<ftp://ftp.th-darmstadt.de//pub/database/filemaker-pro/faq.fm.sit>
MultiPage
[ Usenet FAQs | Web FAQs | Documents | RFC Index ]
Send corrections/additions to the FAQ Maintainer:
traut@th-darmstadt.de (Martin Trautmann)
Last Update March 27 2014 @ 02:11 PM
|
Comment about this article, ask questions, or add new information about this topic: