Path: senator-bedfellow.mit.edu!dreaderd!not-for-mail Message-ID: Supersedes: Expires: 31 May 2004 11:22:46 GMT X-Last-Updated: 1996/10/09 From: Martin Trautmann Newsgroups: comp.sys.mac.databases,de.comp.databases,comp.answers,news.answers Subject: [FAQ] FileMaker Pro - database for Macintosh and Windows Organization: iht/THD Reply-To: traut@th-darmstadt.de (Martin Trautmann) Followup-To: poster Approved: news-answers-request@MIT.EDU Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Keywords: Frequently Asked Questions, FAQ, FileMaker Pro 2.0 / 2.1 / 3.0 , FMPro, FMP, FMP3, Claris, relational database, application, Macintosh, Windows, Windows95, comp.sys.mac.databases Summary: This posting contains a list of Frequently Asked Questions (and their answers) on the application FileMaker Pro. The current release of FMP is a simple to use, yet powerful flat database that offers some relational capabilities and multiple views. FMP is a product of Claris Corp and is available both for Macintosh and Windows Operating Systems. This FAQ gets posted on comp.sys.mac.databases regularly. Originator: faqserv@penguin-lust.MIT.EDU Date: 17 Apr 2004 11:26:54 GMT Lines: 3900 NNTP-Posting-Host: penguin-lust.mit.edu X-Trace: 1082201214 senator-bedfellow.mit.edu 576 18.181.0.29 Xref: senator-bedfellow.mit.edu comp.sys.mac.databases:42240 de.comp.databases:3555 comp.answers:56854 news.answers:269788 Archive-name: databases/filemaker-pro/faq Posting-Frequency: monthly Last-modified: 1996/10/09 Version: 3.1 URL: "Text release" URL: "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 : 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 , 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 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 FMPro online user magazine / ISO 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 ) trial software: updaters from 3.0v1 FMPro 2.1: US: 2.1v3 trial software: updater from 2.0: 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: 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 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: "P1P2" This will relate to both pupils ZZZ (P1) and ZZX (P2) Within the Pupil database you may use Pupil: P1, Teachers: "T1T2" 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 ) 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" 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 ) 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 ) ------------------------------ 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. "BA" 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 ) * export all text, open a text editor with spelling capabilities and create a dictionary with all words, * export all text, convert all blanks to Carriage Returns, thus creating a list of all words, import, use strategy to remove all duplicates, * to print the index of a particular field, create a new layout, selecting the columnar report option. Select the field that you wish to print the index of. Double click on the body part and make it a sub summary when sorted on the field in question. Now sort the database on that field and print from this layout. (from tscott@metz.une.edu.au (Timothy Scott)) * If you want to export just the index of the field, create a new field of type summary. Sort the database on the field you want to export. Select the field you want to export and deselect the others. Now select your summary field just by clicking on the field name. You now get an extra option "Summarise By..." - click on this button, tick to the left hand side of the field name, click on OK and then untick the new field (if you want to) that FMPro wishes to export. (from tscott@metz.une.edu.au (Timothy Scott)) * Consider Print2Pict to get the contents of a print as readable text file. * A final solution to extract a table of all field contents by Excel Did you ever try to create an index for all fields of a FMP3 database? Here's a quick and dirty method that I just developped by using excel macros (German release 4.0) steps required: export all from fmp, e.g. tab delimited open for Excel, option e.g. text tab delimited create a macro table make the proper macros, then let it go. The two macros required are, as pasted from Excel, Extract (a) =AUSWÄHLEN("S1") =ZELLEN.EINFÜGEN(1) =AUSWÄHLEN("Z1S1") =FORMEL("=Z1S2") =AUSWÄHLEN("Z2S1") =FORMEL("=WENN(GLÄTTEN(ZS(1)) = GLÄTTEN(Z(-1)S2);"""";GLÄTTEN(ZS(1)))") =KOPIEREN() =AUSWÄHLEN("Z1S2") =ENDE.AUSWÄHLEN(4) =AUSWÄHLEN("ZS(-1)") =AUSWÄHLEN(POSTEXT(AKTIVE.ZELLE())&":Z3S1") =EINFÜGEN() Dateiname=DATEI.ZUORDNEN(1) =KOPIEREN("s2") =NEU(1) =EINFÜGEN() Dateineu=DATEI.ZUORDNEN(1) =GEHEZU(Z1S2) =RÜCKSPRUNG() (hm, pasting from Excel as international macro doesn't work. Maybe someone does request the excel macro file itself and translate it properly. Here's a dictionary: select = auswählen, insert = zellen.einfügen, formula = formel, copy = kopieren, reftext = postext, cell = zelle, active = aktive, paste = einfügen, new = neu, get.document = datei.zuordnen. goto = gehezu, return = rücksprung, trim = glätten, row = Zeile, r = z, column = Spalte, c = S, activate = aktivieren, sort = ordnen, edit = bearbeiten, delete = löschen, if = wenn, datei.berechnen = calculate.document, paste.special = inhalte.einfügen) This macro creates a first column that computes its value as "" if it is the same as the previous one, otherwise the value itself, both related to the next, second column. IT REQUIRES THAT THE FIRST COLUMN OF THE TABLE HAS NO EMPTY CELLS AND AN ENTRY FOR EVERY RECORD. The second macro takes an unprocessed column, moves it to the second column, sorts, computes the values and appends them to a new table. You must place it as the second column of your macro table, otherwise the goto commands will fail. SortAndFilter (b) =AKTIVIEREN(Dateiname) =AUSWÄHLEN("S3") =KOPIEREN() =AUSWÄHLEN("S2") =EINFÜGEN() =ORDNEN(1;"Z1S2";1) =AUSWÄHLEN("S3") =BEARBEITEN.LÖSCHEN(1) =WENN(Z1S2="";RÜCKSPRUNG();) =DATEI.BERECHNEN() =AUSWÄHLEN("S1") =KOPIEREN() =AKTIVIEREN(Dateineu) =AUSWÄHLEN("Z1S(1)") =INHALTE.EINFÜGEN(3;1;WAHR;FALSCH) =ORDNEN(1;AKTIVE.ZELLE();2) =GEHEZU(Z2S2) =RÜCKSPRUNG() As you might see, this macro shall stop on the first, blank column. You may force a new execution of the second script. If you assigned a proper name and opt-cmd abbreviation (in my case it is Name "SortAndFilter" for Z1S2, cmd-opt-b) the simple cmd-opt-b keystroke will let it run up to the next blank column. Now, maybe someone else can help me on the "" problem - unfortunately this is not empty, but sorts in front of any other value. How may I force it to be empty? Assigning NV() is no good, skip blank lines on paste.special with results only does not work, and the German docs are incredibly poor. 4.5 Number format Entries may be up to 255 characters in only one line. Most characters are accepted, but only numbers will be recognized. Thus you can enter currency symbols, decimal points, percent symbols etc. The valid numbers are 0..9 Leading "-" is recognized as minus , (comma) and . (dot) may be used as delimiters. The symbols are treated differently in international versions! In Germany "," is a decimal delimiter, "." marks thousands, in US it's just vice versa. FMPro seems to recognize the system settings. Yes and True are translated to 1 No and False are translated to 0 All other characters are just ignored, except blank and Š (="...") that work as separators. No additional blank is appended to the index value. Only 122 characters will get indexed. Examples: "1.2 DM" is "1.2" (US) or "12" (German) "-3" is "-3" "4-5" is "45" "6Š7" is "6" and "7" "8 Pounds 9 Pence" is "8" and "9" Some less known fact is, that the internal representation will use 15 digits only! Thus the TextToNum(of a 16 digit credit card number) will get rounded to 15 digits. The random function only produces 15 digit values. Counting serial numbers from 999 999 999 999 999 will continue with 999 999 999 999 1000 thus taking the leading part as text, increasing only the last part before the delimiter. This is the same usage as serial numbers with a leading string. The maximum possible serial number is "2147483647". Counting from there on FMP may produce "-2147483648", "--2147483647", "---2147483648". 2147483648 equals 2^31. The possible integer range of 32 bit is -2147483647 ... 2147483647. As indicated elsewhere, 2147483647 ist the last possible number for a goto last record script. One may assume that the internal representation of numbers is either as 32 bit integers (within the range -2^31 ... +2^31) or as 64 bit floating number with 54 bits mantissa (2^54 = 1.8 * 10^16, artificial limit at 10^16 - 1), one bit sign and maximum 9 bits exponent for a 64 bits representation. A typical size of 32 bit CPUs for the data type floating double (64 bits) is 2.2 * 10^-308 to 1.7 * 10^308 (plus and minus, 15 digits precission) 4.6 Date format Entries may be only one line long. The date format depends on the system settings. Possibilities are day/month/year, month/day/year and year/month/date. For further explanations the German format DD.MM.YYYY is used. Input may be any valid one to four digit number, from x.x.x to xx.xx.xxxx. The valid range is from 01.01.0001 to 31.12.3000. Any single non-numerical character may be used as delimiter. If the year is omitted, the actual year will be used. Taking only two digit years should take the actual century. Examples: "1.1.1" is "01.01.0001" "29.2.94" is "29.02.94" "1-4+04" is "01.04.04" "1-4" is "01.04.95" Some more words on the actual date format: The date is stored similar to a numerical value. Calculations on dates will work like those on numerical values. Conversions are done automatically but may work different than you might expect! Take care and it will be very powerful. The date with the number 1 is "01.01.0001" although our calendar didn't exist at that time, but only since the Gregorian calendar was introduced today 728 460 is "16.06.1995" maximum 1 095 727 is "31.12.3000" next higher values might be displayed as "?" and won't get indexed, but e.g. 1 000 000 000 is "04.01.*0,1" as date 4.7 Time format Although various output formats are possible, the input is limited to numerical values and the colon as delimiter. AM/PM is available for 12-hour formats. Additional time information is allowed, depending on your system settings. Example: while typing, it was "15:48:23 Uhr", indexed as "15:48:23" Input format is hours/minutes/seconds Input may be from "x:xx" to "xx:xx:xx" in the valid range The valid range is between -32767..32767 hours. Similar to the dates, the internal representation can be handled as number in seconds from time "00:00:00" (see above). The time with the number 0 is "00:00:00" -60 is "-00:01:00" Numbers outside the valid range may be converted properly. Tip: If you needed the input format minutes/seconds, just use "HH:MM" instead! Most of the time this is fine, e.g. for recording the length of CD tracks. If you need the real time for another calculation, convert it by a division /60. 4.8 Hidden fields In order to paste values to a certain field, e.g. while using scripts, find dialogs etc., the field must be available on the choosen layout. If the field shall be available but not be displayed you may - assign a field size of 1 by 1 point to the field: select layout mode, display and edit size (recommended) - assign an invisible font color to the text: choose background color as text font - hide the field behind another field: just move another field on top of it. Fields are staggered by order of creation but may be moved by Arrange forward/backward - exclude the field from the tab order: edit the tab order, exlude it or move it to a late tab order (recommended as additional step) FMP3 offers a new command "Set Field" that works both on visible and excluded fields. Set Field also works on fields in the header or footer of a list layout, while those fields are not editable otherwise. Warning: Set Field may only enter data of the proper type, such as a real date to a date field. If you want to find e.g. the records within the last thirty days, by a find string including a ">" symbol, you have to use a paste command instead, such as Paste Result["> "&Status(CurrentDate)-30] 4.9 Sorted lists FMP3 offers pop up lists based on two fields. In general they get alphabetized by their second field. If you don't want this behavior, turn off indexing for the second field or, if you have to have it indexed, make an unindexed calculation field to show the value. (from Stefan Schutt ) ------------------------------ 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 may look like separate repetitions, but are in fact within the same field. (by Steve Rothman ) * 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. 5.2 Convert nonrepeating fields to repeating field Create 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 23 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 - 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 , exported as comma delimited, with fields: Name, Text "A","1" "B","1" "A","2" It should be merged to a database "A","1""2" "B","1" This can be done by sorting the records and replacing the with . 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" "A","2",3,1, "B","1",2,2,"B" Export the fields MR and Number, replace ," with " 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 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 {, } exists record 10 test: satisfying a test repeat with every record whose cell "searchfield" = FindCriterion "show every record whose cell is " 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 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 ) 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 ) 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: 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 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 does NOT work. A workaround is: set recID to create new record at database set record ID recID of database to (see ROFMUtil for a subroutine to create ). (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" ) 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 then error "Database " & & " does not exist" else if (not exists cell then error "Cell " & & " 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 ) 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. 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/MenuEvents1.1.2.sit along with a companion program ftp://gaea.scriptweb.com/applescript/addons/MenuGrabber1.1.2.sit 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/PlayerInfo.sit) 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 ) 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 ) ------------------------------ 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 ) 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 ) 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 ) 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 ) 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- - 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 - 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 ) 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 ) 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 = "

" & header & "

" _text = "

" & 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 ) "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 ) 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 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 . 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 <> 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 ) 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): My html database

This single text object touches the header: where "1.3" is the content of the third field of the first record. The text object
Label1
Label2
Label3 Now place single objects within the body [Field1] [Field2] [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
Label1 1.1 2.1 3.1
Label2 1.2 2.2 3.2
Label3 1.3 2.3 3.3
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-1¶Tue-9-1¶Tue-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 (ASCII-13) as a record separator. For that reason the returns within text fields get translated to "vertical tabs" (ASCII-11). Macs use as EndOfLine delimiter, while Unix takes (ASCII-10) and DOS takes both. This might confuse imports and should be fixed before. * Repeating fields are separated by . 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. by . 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 -- font number of header Example: {\f3 text \par} To set different font sizes the same layout as fonts is used. But instead of \f the code is \fs 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 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: 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: 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. 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. * 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 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 for 'mailbox to tsv'. It's still under construction, but already to recommend. It requires MacPerl 5 (). * AppleScript Stefan Schütt (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. Bob Cusick composed another set of templates . 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 ) 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$ 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 * 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 . 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 ) ------------------------------ 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) ----------------------------------------------------------------------- /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*). /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. /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. /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, , 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: "Info-mac mirrors" "Umich mirrors" The FAQ as text release might be found at: "Plain text" "Plain Text, German mirror" "HTML format" "HTML format" and the other FAQ sites that hold news.answers and comp.answers. Try also the November release at: 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: