Top Document: [FAQ] FileMaker Pro - database for Macintosh and Windows Previous Document: 7 Scripting Next Document: 9 Data Exchange See reader questions & answers on this topic! - Help others by sharing your knowledge 8.1 Removing password Contact Claris. They can do it for you, but it may take some time (> 2 weeks). They have a form for you to fill out. The actual release is rather safe against hackers, but successful cracks were reported. 8.2 Undocumented features Shortcuts: Control-C exits a field (like clicking in the record area) Control-[ deletes the current field (like Select All & Delete) Control-_ selects within the field to the end of the field text (From Sam Malenfant <Sam_Malenfant@CARLETONBE.OTTAWA.ON.CA>) In the Define fields dialog, the default button changes to Done (instead of Create or Change) if you hit the delete key or erase the name of the selected field. (reported by JoelBowers@eWorld) Shortcuts that work both in ScriptMaker and Sort, Export/Import: Tab toggles control between the steps and the script In the steps, type the first letter to move to a step In the steps, use up and down arrow keys to move to next step When the step is highlighted, use the spacebar to trigger move step to script In the script, spacebar triggers the "specify" dialog for the highlighted script step To move a highlighted step up or down in the script, use command key + up/down arrows (From Sheila Kliewer) Script steps: A Copy[] line in a Script without a specific field when not in any field produces a tab and repeating-char delimited string of the whole record, which can in turn be pasted into a specific field (where for instance it might be parsed to get at Field'n' or Field'n'Repetition'm'). (From Sam Malenfant <Sam_Malenfant@CARLETONBE.OTTAWA.ON.CA>) Invisible characters, such as tab or repeating-separator-29 can be pasted into a text field and used in calculations with Position() for instance to find and replace. With scripting or "default data", a field call TAB for instrance, might be useful for parses. (From Sam Malenfant <Sam_Malenfant@CARLETONBE.OTTAWA.ON.CA>) 8.3 Shortcuts A new tip for FMP3 for Macintosh: When in layout mode you can hold down the control key when selecting a layout object. Depending on the object, you will be presented with a popup menu of actions that you can take on that layout object. These include the text attributes, colors and formatting, setting of a portal's relations etc.. Undocumented Shortcuts, most of them collected from FMP2.1 for Macintosh release: Control-C exits a field (like clicking in the record area) Control-[ deletes the current field (like Select All & Delete) Control-_ selects within the field to the end of the field text (From Sam Malenfant <Sam_Malenfant@CARLETONBE.OTTAWA.ON.CA>) In the Define fields dialog, the default button changes to Done (instead of Create or Change) if you hit the delete key or erase the name of the selected field. (reported by JoelBowers@eWorld) Escape is even faster... Shortcuts that work both in ScriptMaker and Sort, Export/Import: Tab toggles control between the steps and the script In the steps, type the first letter to move to a step In the steps, use up and down arrow keys to move to next step When the step is highlighted, use the spacebar to trigger move step to script In the script, spacebar triggers the "specify" dialog for the highlighted script step To move a highlighted step up or down in the script, use command key + up/down arrows (From Sheila Kliewer) Documented Shortcuts from the Quick Reference: c = /command/apple, o = option, s = shift * Layout Mode: Fields: Redefine a field on a layout o-double click Objects: Constrain resizing to horizontal or vertical s-drag a handle Duplicate by dragging o-drag an object Move selected objects by one pixel at a time Arrow keys Release grid while resizing c-resize Reset default format of an object c-click Squares object being resized o-resize Select all objects of same type c-o-A Parts: Drag selected layout part across objects o-drag Reorder selected part s-drag Reorient part labels c-click T-square on/off c-T Tools: Constrain line tool to horizontal or vertical o-drag Constrain oval tool to a circle o-drag Constrain rectangle tool to a square o-drag * Browse Mode Edit: Copy c-C Copy found set c-o-C Delete without confirmation c-E Delete without confirmation c-o-E Paste c-V Paste without style c-o-V Paste from last record c-' Paste from last record and move to next field c-s-' (German release: use instead of ') Move: Next field Tab Previous field s-Tab Next Record / Request / Layout c-Tab or o-Tab Previous Record / Request / Layout c-s-Tab or o-s-Tab Text: multiple for Align left/right/center/full, Bold, Italics, Underline etc. Next point size up c-s-> Next point size down c-s-< One point larger c-o-s-> One point smaller c-o-s-< (how about the German keyboard where > is s-<?) Delete one character left of cursor Delete Delete one word left of cursor o-Delete Move cursor by one word o-Left/Right Move cursor to beginning/end of line c-Left/Right Misc: Help c-? Network dialog box c-o-O Show/Hide status area c-o-S Zoom Window Full/Previous Size c-s-Z 8.4 Compatibility between DOS and Mac release There's not much problem about. Check the manuals, appendix E, "Cross-Platform Considerations". Have another look at <http://www.claris.com/TS/Featured/CrossPlat/Article.html> - take care to use 8.3 file names, - use the same fonts: Different fonts and small differences within the same font family may produce different display. Take care of proper alignment and use a little wider fields The standard setup of Mac contains Helvetica, while on Windows a similar font is named Arial (same for Times and Courier). There's a translation table to use the equivalent font. See [FontSubstitutes] section of the WIN.INI file, e.g.: [FontSubstitutes] Helv=MS Sans Serif Tms Rmn=MS Serif Times=Times New Roman Helvetica=Arial Monaco=Courier New Otherwise, the standard translation is PC Mac: MS Serif Times Times New Roman Times Times New York Tms Rmn Times Courier New Courier Courier Monaco Commercial equivalent font packages are available. I recommend TTConverter to translate TrueType fonts between Mac and PC <ftp://ftp.switch.ch//mirror/umich-mac/util/font/> - Printing: expect slightly different results - Pictures: on PCs use the setting "Store Macintosh picture formats" to display as PICT on Macs; - other problems due to specific platforms: Differences in character sets (e.g. not equal, greater than or equal) and exact color mapping. Certain ScriptMaker commands work different (e.g. send mail, page setup) AppleScript is not supported on PCs, PC batch commands don't work for Mac. JPEG and QuickTime are special. 8.5 Buttons - You may use any text and picture within a layout as button. Select an object and use the menu Scripts / Define Button (for FMP2.1) to assign a script to the button. - Fields may not be used as buttons for FMP2.1. You may create a proper background for a field that looks like a button and place an invisible button above the field. For FMP3 you may group a field with a button function. This will not permit to select this field by mouse click, but by script or tab order only. There is no status function to detect what field you clicked on. If you need this information, you'll have to create one script per button & field. - An invisible button is an object that doesn't contain any visible information, e.g. a rectangular box without filling and outline. - Objects created by the layout tools of FMP get displayed very fast while using little memory. - Since the drawing capabilities of FMP are limited, you may take virtually any other application to create objects and to paste them on FMP layouts. - Objects created by other tools may use very complex picture formats and increase processing time and memory requirements. Take care to use the minimum resolution (e.g. 72 dpi for screen buttons), the minimum of components and the minimum color depth. - As an option you may design complete button areas and backgrounds by separate tools, paste them on a layout and place invisible buttons above the according area. * Example: The buttons of the [FAQ]FMP database (faq.fm) were created e.g. by drawings within FMP itself, designed at 400 %, displayed at 100 %. The color depth was set to 16 colors by the control panel "monitors". Then a screenshot was made by cmd-shift-3. The screenshot picture was opened by SimpleText and a rectangular button selected and copied directly from that display, pasted to an FMP layout and defined as a button. The buttons were marked as non-printing objects. - Try to assign a different line color to a button. Thus you may get variations of a button easily. The greyed buttons within faq.fm were produced by assigning a dark grey to them. This function is gone for FMP3 8.6 Resources for dialog boxes (from Dale Saukerson <sauke001@GOLD.TC.UMN.EDU>) FileMaker is great, but I find myself doing excessive amounts of scrolling thru the various dialog boxes when revising a database' structure and layout. Many of the dialog boxes are minimal size, even for a small 14" monitor. To correct this, I used ResEdit to increase the size of the offending dialog boxes. Listed below are the numbered DLOG resources for common dialog boxes, for version 2.1v1 of FileMaker Pro for the Macintosh. By opening these, you can drag the elements around and increase their relative length, much like you would move objects around in a FileMaker layout. If you have even minimal ResEdit skills (like myself) you can easily perform the necessary modifications yourself with no fear of destruction of data. However, ResEdit is a powerful tool and could be dangerous in the hands of the ignorant, so the usual warnings apply. DLOG 514 Script definition. Larger list of the script names shown when you select "ScriptMaker" from the "Script" menu DLOG 511 Define scripts. Shows the individual steps assigned to a script. Now almost half of the possible script steps will be shown--two clicks of the mouse and you can see ALL of the script step choices. Nice. DLOG 5000 Define fields. Create a larger list of the fields and reduce the amount of scrolling. Also allows you to see the full length of the name in the list of the fields instead of only in the "name" box portion of the dialog box. DLOG 5020 Reorder layouts. View more of the layout names in the dialog box and minimize scrolling. Does NOT prevent trunctuating of the layout name in the layout name pop up menu portion of the toolbox area. Two mouse clicks to see all 67 layouts in my pet project database. DLOG 5022 Sort records. View more of the field names before scrolling, though it is only a modest increase. DLOG 5039 Specify field order for import. View more fields with less scrolling. DLOG 5040 Specify field order for export. View more fields with less scrolling. 8.7 The special name "-" as list separator Whenever you use the "-" as a name, it may get displayed as a dotted line within popup list views. You may use it to group similar or related items. Within FMP you may use it for * Predefined value lists of fields Example for music storage media: (every value is within a unique line, separated by <Carriage Return>) CC CC 60 CC 90 CC 100 - CD CD single CD maxi CD Box CD Set etc. * Field Names You may use the "-" as name, but it's not useful any more after you sorted by any type / name etc. I recommend to sort by name and group field names by a leading character or type, e.g. all parts of a name by Name, Name_First, Name_Title, Name_Sex, Name_SSN etc. Personally, I name fields for internal usage, such as date of creation or serial number, within rectangular brackets, e.g. [date_of_creation] I name calculation fields by a leading underscore, e.g. _previous_serial_number = [serial_number] - 1 (That's a useful construct to lookup values from the previous record) There is no need to compose names as a single word, but within calculations it's more useful just to double-click a name in order to highlight it. For that reason I use the underscore as delimiter. Others use e.g. a leading number or a leading character for the type of the field, e.g. C for calculations, S for summary. * Script Names The "-" is very useful to structure the display of lists. There may still be a real script behind this name. It may get called by other scripts as usual. Use an ordinary name first, then edit any other "perform script [name]" by selecting this script. You may change the name afterwards to "-" again, but the reference won't get lost (I assume the internal reference is not by name). BTW this works as well for layouts and fields. Warning: AppleScript references scripts by name: Do Script "-" -- will perform the first script that is named "-" * Layouts Here the name "-" is most useful to hide layouts that contain statistical fields or dialogs. You don't have direct access to them in browse, finde and preview mode. Thus a user won't get confused by that type of layout. You may grant access to those layouts when you create scripts and link them to buttons. As mentionned before use "Goto Layout [name]" by using an ordinary name first, then change the name to "-". My private solution is to jump to a layout by its number. For that purpose I use a field named [mark]: copy [select all, [mark]] -- thus the field gets selected, its content gets buffered paste literal ["3"] -- this is the number of a hidden layout go to layout [take number from field [mark] ] paste[select all, [mark]] -- thus the original contents get pasted to [mark] again. Within layout mode you may select hidden layouts by entering the layout number, stepping through layouts by cmd-tab or dragging the handle of the book. 8.8 WWW, HTML and CGI How to link FileMaker Pro to the World Wide Web: First, you will need a Web Server, such as WebStar. Then, you will have to link FMP to it. One solution is direct exchange of data via AppleEvents, AppleScript or Frontier. The other solution is to create a html output from within FMP: * You may add html tags to fields by using simple calculations Example: _header = "<H3>" & header & "</H3>" _text = "<P>" & text Then export the html-formatted calculation fields instead of the original field * You may add or modify html tags by other applications Consider simple find/replace tools, mail merge export and formatting, AppleScript etc. * You may add all tags to a html layout and use this data The simple solution is to place the tags around the fields and use a layout dump, e.g. by preview/copy all (information as PICT), or by printing to a text file with Print2Pict. For further information check the sections on FMPRO-CGI and ROFM CGI. See also http://edb518ea.edb.utexas.edu/scripts/cgix/cgix.html http://www.uwtc.washington.edu/Computing/WWW/Mac/CGI.html http://www.biap.com/machttp/examples/fmpro.sit.hqx http://www.claris.com/TS/TIJ/fmcgi/article.html "Publish your database on the Web" 8.8.1 HTML text conversion 8.9 Comments within calculations Filemaker has no facility for this, yet it may [be] useful to annotate, in order to remember what the calc was supposed to do. Begin the calc field with: If(1=1 ,"","My solution") & (from jjl@knoware.nl (J. J. Lodder)) If (1, "", "Your description") & should do as well. Another one is Left ("Yet another textual description", 0) 8.10 Empty fields Find empty fields: Enter an equal symbol as search parameter. This will check on exact match to virtually nothing. Number fields: Their value is zero when no numerical values are included. You may use other characters, but they won't get indexed. Thus a find on characters within a number field will fail. Calculation fields: A calculation does not take place when the fields it relies on are empty.. If (Test, "Yes", "No") thus will not produce a "No" while the Test is empty. Previous calculated values may get kept instead of deleted. "Important: If Test refers to a field that does not yet contain a value, then the If function is skipped and returns an empty result." (from the FMP User's Guide, A-25) You may force the calculation by forcing a value: - If (Qualifier & "x" = "x", "Yes", "No"). This will force the recalculation on existing, but empty fields, but it will still fail on new emtpty records. - Autoenter a value, e.g. default " " to Qualifier: If (Trim(Qualifier) = "" , "Yes", "No") Surprisingly enough, even by only marking the default value checkbox without data made it work - Use a special field, e.g. autoenter " " to a field named BLANK If (Qualifier & Blank = " ", "Yes", "No") FMP 3.0 offers options whether or not to calculate on empty fields. 8.11 Sort Order The general sort order of records is [0-9][A-Z]. Any symbol that resembles an alphanumeric string gets sorted as its equivalent, e.g. both upper and lower characters A and a as A, (=AE) as A, (= copyright) as C. All other characters get ignored while sorting, such as leading strings. One special exception it the non-breaking space " " (character ID 220, option-space). It is sorted first. Next is the space itself, including all other characters that get indexed as space (see section on indexing). Last come empty fields. As a side effect you may find all filled fields not only by a search on omitting all empty records (find field with contents that match exactly nothing '=' & omit), but much faster by a search on greater equal 0 ( 0) If you'd like to sort case-sensitive, you may create an additional sort criterion, e.g. sort (calculation, result number) = If(Exact(Upper(Left(text,1), Left(text,1)), "1: upper", "0: lower") Then sort on text and sort instead on text alone. 8.12 Switch printers FMP does not offer a built-in option to switch printers. The Chooser is not scriptable itself. QuicKeys is capable of receiving AppleEvents out of FileMaker and has a very fast Chooser Switching utility. KeyQuencer can do that as well. However, KeyQuencer can't choose between network printers, just select a printer driver. Yet another tool: Tempo There is also an AppleScript extension (OSAX) called XCMD OSAX that will accept commands to change the printer driver. It's available at: http://www.scriptweb.com/scriptweb/osaxen/default.html "Basically, to choose a printer, just drop the osax into your Scripting Additions Folder (inside your extensions folder--you need Applescript) and then go to Filemaker 3.0 and create a script with an Applescript step like this:" Choose Printer DriverName "LaserWriter 8" PrinterName "My Printer" Zone "EtherTalk" (from Paul Fabris <pfabris@ASTRAL.MAGIC.CA>) "There is a OSAX called "Set Printer to" as part of the GTQ Scripting Library v1.2 that does this. You need AppleScript and the OSAX installed. Here is the OSAX library:" set printer to: chooses the specified device type set printer to string -- device type name [named string] -- name of device [using printer port/modem port] -- which serial port to use (valid for serial devices only) [zone string] -- zone where device is (from ehsan saffari <aa074@sfn.saskatoon.sk.ca>) Comment: the page setup is stored within the FMP ScriptMaker command "Page Size". 8.13 Error Codes An Error Code is available immediately after its occurence and may get requested by Status(Error Code), e.g. within scripts as If ["Status (CurrentError) <> 0"] The Error Code is lost after the next step, since the next step may return e.g. "0" again. Thus one may buffer the status to a certain field [Status Current Error] of type global/number: Set Field[[Status Current Error], Status (CurrentError)] Here is a list of error codes taken from the helputility of FMP3 (by romero@atlas.odyssee.net (Frank Romero)) Errors: -1 Unknown error 0 No error 1 User canceled action 2 Memory error 3 Command is unavailable (e.g., wrong operating system, wrong mode, etc.) 4 Command is unknown 5 Command is invalid (e.g., a Set Field script step does not have a calculation specified) 100 File is missing 101 Record is missing 102 Field is missing 103 Relation is missing 104 Script is missing 105 Layout is missing 200 Record access is denied 201 Field cannot be modified 202 Field access is denied 203 No records in file to print or password doesn't allow print access 204 No access to field(s) in sort order 205 Cannot create new records; import will overwrite existing data 300 The file is locked or in use 301 Record is in use by another user 302 Script definitions are in use by another user 303 Paper size is in use by another user 304 Password definitions are in use by another user 305 Relationship or value list definitions are locked by another user 400 Find criteria is empty 401 No records match the request 402 Not a match field for a lookup 403 Exceeding maximum record limit for demo 404 Sort order is invalid 405 Number of records specified exceeds number of records that can be omitted 406 Replace/Reserialize criteria is invalid 407 One or both key fields are missing (invalid relation) 408 Specified field has inappropriate data type for this operation 409 Import order is invalid 410 Export order is invalid 411 Cannot perform delete because related records cannot be deleted 412 Wrong version of FileMaker used to recover file 500 Date value does not meet validation entry options 501 Time value does not meet validation entry options 502 Number value does not meet validation entry options 503 Value in field does not meet range validation entry options 504 Value in field does not meet unique value validation entry options 505 Value in field failed existing value validation test 506 Value in field is not a member value of the validation entry option value list 507 Value in field failed calculation test of validation entry option 508 Value in field failed query value test of validation entry option 509 Field requires a valid value 510 Related value is empty or unavailable 600 Print error has occurred 601 Combined header and footer exceed one page 602 Body doesn't fit on a page for current column setup 603 Print connection lost 700 File is of the wrong file type for import 701 Data Access Manager can't find database extension file 702 The Data Access Manager was unable to open the session 703 The Data Access Manager was unable to open the session; try later 704 Data Access Manager failed when sending a query 705 Data Access Manager failed when executing a query 706 EPSF file has no preview image 707 Graphic translator can not be found 708 Can't import the file or need color machine to import file 709 QuickTime movie import failed 710 Unable to update QuickTime file reference because the database is read-only 711 Import translator can not be found 712 XTND version is incompatible 713 Couldn't initialize the XTND system 714 Insufficient password privileges do not allow the operation 800 Unable to create file on disk 801 Unable to create temporary file on System disk 802 Unable to open file 803 File is single user or host cannot be found 804 File cannot be opened as read-only in its current state 805 File is damaged; use Recover command 806 File cannot be opened with this version of FileMaker 807 File is not a FileMaker file or is severely damaged 808 Cannot open file because of damaged access privileges 809 Disk/volume is full 810 Disk/volume is locked 811 Temporary file cannot be opened as FileMaker file 812 Cannot open the file because it exceeds host capacity 813 Record Synchronization error on network 814 File(s) cannot be opened because maximum number is open 815 Couldn't open lookup file 816 Unable to convert file 900 General spelling engine error 901 Main spelling dictionary not installed 902 Could not launch the Help system 903 Command cannot be used in a shared file 8.14 Get User's Name / Limit Access to Certain Users Date: Wed, 10 Jan 1996 11:37:50 -0500 From: Chris Moyer <fmpro@MINDSPRING.COM> Subject: Re: Password Question Q: [...] limit access to people using the app to just those employees that work at that particular location. [...] A: Unfortunately, FileMaker 3.0 doesn't have a status function for current group or password, so that part is a little funky, but the rest is pretty straight up. To work around the lack of password status functions, you need to use an extremely modified variation on Michael Singer's old password trick. In your case, you would set up groups that denoted location, then assign individual passwords to a specific group. You need to create a calculation field for each group, with the calculation equalling 1, 2, 3, 4, 5, etc. It doesn't matter what the values are, but they need to corresponds with whatever code you currently use to denote location in your Regular Location field. You need to have one more field, a global field (either text or number format should work okay) called Location. In your Access Privileges, you need to set up groups for each location. For the purposes of this example, I'll refer to them as Location 1, 2, 3, etc. In the Access Privileges Overview, select the Location 1 group and shut off access to all the location calcs except its own. Do the same for the rest of the location groups. In the ScriptMaker, you need to create a twisted version of a looping script called Startup (or Fred or Lulu - you can call it whatever the heck you want...) that has these steps: Allow User Abort [Off] Loop Set Field ["Location", "Location 1"] Exit Loop If ["Location>0"] Set Field ["Location", "Location 2"] Exit Loop If ["Location>0"] Set Field ["Location", "Location 3"] Exit Loop If ["Location>0"] etc.... Exit Loop If ["Location=0"] End Loop Since you have access turned off for all of the location calculations except one, all of those Set Field commands except one will fail, returning a 0. I stuck a final Exit Loop If global does equal 0 in there just so you can get out of the script if something went wrong. Hypothetically, that step should never get executed. Just in case, though.... If you had a ton of locations, you could make this into a regular loop, but you'd have to use a layout and the go to next field step, and another global. This way's a little more bullet-proof. So...now you know what location a user belongs to, and they have no idea how you know, so they probably won't be able to figure out how to defeat your script even if they wanted to. What good does the location do you? I'll tell you. By using calculated validations, you can selectively lock records in a database that otherwise can be edited. Suppose you have a Name field. Go into Define Fields, click Options, switch to Validation options, select a calculated validation, and use this formula: Location = Regular Location That's it. Set Strict Validation and give users a message explaining that they can only edit records for their location, and you're done. Oh, you do need to make that startup script a startup script in Preferences, but after that you're done. You'll need to set this validation up for every field that you want to restrict entry on. 8.15 Use // as literal text The literal "//" within a text object is a place holder for the actual date. Other special characters are e.g. "::" for the time, "||" for the user name, "##" for the page number, "@@" for the record number. But you need "//" in URLs, such as <ftp://ftp.th-darmstadt.de//pub/database/filemaker-pro/faq.fm.sit>. FileMaker Pro does not support characters, such as the regular expression "\//", to escape a special meaning. But there are various workarounds: * place a space between both slashes: "/ /". When you format the space as font size 1 point it's almost invisible. * another solution is to use two separate text objects. The first object ends with a slash, the second objects starts with a slash: "text1/" "/text2" You may place them one aside the other so that no space between is visible. Tip: Verify the position by the position/size info and group both objects together. * Get the full URL or the double slash from a field. FMP3 permits the usage of a global field, e.g. Double Slash = "//" to store this value. FMP3 also permits the usage of merged text where you may place fields within a text's object environment: "text1 <<Double Slash>> text2". * headds@world.net (David Head) reported to select the text object, then assign to it the DATE format "none/none/none". This will use the actual date, but display it as //. 8.16 Faster Screen Redraw Here are some suggestions that may speed up the redraw of layouts: Fields: - Do not use border lines - Do not use background colors Fonts: - Use a minimum of different fonts and styles Picture Objects: - Use the fastest drawing type of object. - Objects drawn by FMP layout tools itself usually are optimized for that job - Objects drawn within other applications should be made as small as possible: - Use the minimum required resolution. Cuts from screenshots (cmd-shift-3, rectangles may be cut even within SimpleText) with 72 dpi are well suited. - Use the minimum required color map Personally, I switch to 16 color mode before I take a screenshot. This will reduce memory, increase speed and does not require any color mapping or dithering. - use the minimum of objects Example: you don't have to build a button from multiple drawing objects, or a group of buttons by single buttons, each defined as a graphical object and button of its own, but you may create them as a single picture object (with minimum requirements) and place transparent FMP objects (e.g. rectangles or circles) as buttons above Other: - Don't use background part colors - Keep your layouts simple - Instead of layouts filled with all fields, use several special layouts with the required fields only - Use the minimum window size - hide unused applications and windows - Maybe using a compressed database copy or a resorted clone can speed up portals and lists. - Assign more memory to FMP3 (less useful for FMP2.1). - Use scripts to freeze the window while you modify multiple fields. Screen redraw of lists became slower for FMP3, compared to FMP2.1. "Use bring to front and send to back to layer things - - layer all your static graphics (and text) to the back - then layer all the local fields next - layer the related fields on top of everything else It won't actually reduce the total redraw time, but it will subjectively feel faster." (From: Eric Scheid <ironclad@planet.net.au>) 8.17 Date calculation As indicated before the internal representation of dates is a continuous number of days from 1.1.0001 on. This makes calculations on dates very easy, since you may use them just as numbers. The number of days between two dates is e.g. StartDate - StopDate A person's age thus may get computed e.g. by Status(CurrentDate) - Birthday / 365 This will ignore the effect of leap years. A better estimation is Status(CurrentDate) - Birthday / 365.25 The best suggestion, reported by hp.robin@infoboard.be (Henri-Pierre Robin), is: Year(Status(CurrentDate)) - Year(BirthDate) - (Status(CurrentDate) < DayofYear(BirthDate)) It takes the boolean result 0 or 1 if the actual date is before the birthday date of the year, indicating that it's not a full year yet. For FMP3 you may use both Status(CurrentDate), perferable for the unstored calculation, and Today, that gets updated on opening a database. FMP2.1 only offers the time consuming Today function. This function will update the Today value within every calculation field and record, regardless whether you are interested in its value. As a workaround you should avoid the Today function, but use a scripted solution to paste the actual date whenever required to a suitable "ThisDay" field. 8.18 Displaying Data as a Table FMP is not a spreadsheet tool, but a record based database while there is little dependency between the lines. Thus building tables is not a simple job. You have various choices to display your data as a table. The normal format is to use a header line within a header part and display in list view the records below as the selected and sorted lines of a table. You may unify matching headers by subsummary parts. They get only displayed in a list when you print or preview a page that is sorted as specified for the subsummary part. When an object's topmost line touches a part, it belongs to this part, regardless how many other parts below it overlaps. This may get used to make an object, such as a field, drawing or label, as belonging to a header part, while it is displayed within the part below. For FMP2.1 you could group objects together and the topmost line of the group determined the part. This is gone for FMP3, but with merge text you may get a similar output. You then may build tables by multiple columns, e.g. to display in order [leading --------- text 1 | 3 object] --- --- 2 | 4 --- --- Example: HTML table It may get used to produce the html source code for a WWW page. This object is within the header or subsummary part (pate break after every occurence): <HTML><HEAD><TITLE>My html database</TITLE></HEAD><CENTER> <table width=99% border=1 cellpadding=1 cellspacing=1> This single text object touches the header: </TR><TR><TD><B>Label1 </TR><TR><TD><B>Label2 </B> </TR><TR><TD><B>Label3 </B> Now place single objects within the body <TD> [Field1] <TD> [Field2] <TD> [Field3] Those objects are on the same height as the labels that you want to use. As a multicolumn preview, printed e.g. to a file, you may obtain a html table with its central part similar to <TR><TD><B>Label1 <TD> 1.1 <TD> 2.1 <TD> 3.1 </TR> <TR><TD><B>Label2 </B> <TD> 1.2 <TD> 2.2 <TD> 3.2 </TR> <TR><TD><B>Label3 </B> <TD> 1.3 <TD> 2.3 <TD> 3.3 </TR> where "1.3" is the content of the third field of the first record. The text object </Table> should be somewhere in the footer. But what to do if you want neither a display by lines, nor by columns: FMP3 offers the mechanism of related records and portals. Thus a simple solution is to build a relation for every cell of a table and display the matching information within. Example: TimeTable Day: Monday Time: 8-9 9-10 10-11 ... room 1 user1 user2 room 2 user3 room 3 You got one database "Table" that holds the table, another "Records" that holds the combinations: Day, Period, Room, User: Monday, 8-9, 1, user1 Monday, 9-10, 1, user2 A proper relation is e.g. Table:"Mon-08-1" to Records:keyfield keyfield = Day & Period & Room (formatted with additional text operations) The second relation is Table:"Mon-08-2" to Records:keyfield etc. up to Table:"Mon-17-99" to Records:keyfield You now place the related field user as 8-9 9-10 room1 [relation1::user] room2 [relation2::user] etc. Use the feature that you may convert a time period, such as "8-11", to three one-hour periods, separated by a carriage return for a proper many-to-many keyfield, resulting e.g. in "Tue-8-1Tue-9-1Tue-10-1" It's cumbersome to define all these single relations, but straight forward. But instead of many relations there may be suitable solutions that use portals and multiple rows as well. 8.19 Zoom window size In case you want to adjust and resize the window for an optimum size, maybe even while switching layouts, you may use the Toggle Window [zoom] command. Create an invisible "frame" the size that you want your windows to display. Use e.g. a rectangular object of the desired outer size with "line: none" and lock it. FMP will zoom to the outer edge of the frame. Therefore, everytime the user goes to a different screen, they get the screen size that you want them to see. Of course there may be no other objects outside the frame. If you have to use some "hidden" fields, place them hidden or invisible e.g. at the top left edge. This auto-zoom solution counteracts the user who likes to manually resize the window. Thus you may perform e.g. a special "Zoom" script instead of the Toggle Window script command: If[GlobalZoom] Toggle Window[Zoom] End If Global Zoom here is a global number field where you may assign a boolean value whether or not to zoom windows. User Contributions:Top Document: [FAQ] FileMaker Pro - database for Macintosh and Windows Previous Document: 7 Scripting Next Document: 9 Data Exchange Single Page [ Usenet FAQs | Web FAQs | Documents | RFC Index ] Send corrections/additions to the FAQ Maintainer: traut@th-darmstadt.de (Martin Trautmann)
Last Update March 27 2014 @ 02:11 PM
|
Comment about this article, ask questions, or add new information about this topic: