 |  |
FileMaker Tutorial
- SETTING A FIELD REPETITION
By Shawn D. Hogan - Question:
I would like to know if there is any way to set a repetition. For example, say I want the third repetition in a repeating field to be "March". Can I set it to this value?Answer:
The set field function has the ability to specify the repetiion when selecting which field.
- GETTING LIST OF LAYOUTS
By Shawn D. Hogan - Question:
In FileMaker Pro 4.0, I would like to print out the names of all my layouts. I use many different ones and would like to print them out so I can notate when I use them in case someone else has to do my job. I cannot find any way to do this on my own.Answer:
You can use the LayoutNames design function in a calculation in order to get it...
LayoutNamesÊ("database name")
- TITLE FOOTER
By Shawn D. Hogan - Question:
How can i put a field only on the footer of the last page (and not on
the footer of the rest of pages)?Answer:
Put it in a Title Footer section.
- SPLITTING REPEATING FIELDS
By Shawn D. Hogan - Question:
I was given a database with several repeating fields next to each other
that look similar to this in the layout:
bill 37 12 b 180
bill 37 12 b 180
bill 37 12 b 180
bill 37 12 b 180
When I export these fields as tab seperated text I get this as a text file
the only way it comes out is:
bill bill bill bill bill bill 37 37 37 37 37 37 12 12 12 12 12 etc...
Is there a way to make FMP think that each row is a seperate record and/or
somehow get a text file that would look like the first example?Answer:
1. Save a clone of the database
2. Open up the clone database and import the original database into the clone
3. When importing, the second dialog will allow you to "Import Values In Repeating Fields By: Splitting Them Into Separate Records"
You now just eliminated the need for repeating fields, and you should be able to export them just fine...
That should work... {shrug}
- IMPORTING VALUE LISTS
By Shawn D. Hogan - Question:
The product FMP 3.xx, there is no way available during export to copy out one or more value list, or to import these value list into an duplicate template.
This feature is require if the value list was edited in the original template so they will appear in the drop down box when import to a dup template. I have over seven hundred users that each have a FMP Rolodex (runtime), that have made value list additions above what the master template contained. And now a new and improvement template needs to be rolled. Any suggestions?Answer:
Unfortunately there is no way to move value lists between files. What I usually do is define a dynamic value list that is based on a repeating field. Then if they want to edit the value list, they can edit the repeating field. And since it's a repeating field, it CAN be moved between data files.
- CDML TEXT ENCODING
By Shawn D. Hogan - Question:
I am using an AppleScript to automatically import mail messages from QuickMail Pro to a mail archive. In turn, I am making this mail archive available for searching via CDML. The problem I'm having is that the formatting of the text messages does not hold. That is, line breaks/paragraphs in the imported mail messages are not acknowledged when viewed via a web browser. Is there something in CDML that will allow me to keep my text formatting?Answer:
Use the Encoding parameter of the field tag. If you encode it with BREAK encoding, it should work for ya...
Here is a copy/paste from the CDML Reference:
Syntax
[FMP-Field: Field name, Encoding]
Parameter(s)
First parameter: The name of the field
Second parameter (optional): Encoding. Use one of the following reserved words:
Raw - Don't perform any encoding
URL - Perform URL encoding
HTML - (default) Perform HTML encoding
Break - Perform HTML encoding and replace soft returns with
- IMAGE MAP SEARCHES
By Shawn D. Hogan - Question:
Is there any way to have different areas of an image map trigger different FMPro searches?
Example: Clicking once on an area of an image map that corresponds with a room location triggers a FMPro search that returns the inventory of computers in that room (search by location field).Answer:
Of course... but it's more of an HTML thing than a FileMaker Pro thing...
Since the search is really just a fancy URL that hits a FileMaker Pro database, you would just use those URLs for your image map.
- CALCULATING OFF SUMMARIES
By Shawn D. Hogan - Question:
I'm trying to figure out how to use division on summary fields in a subsummary part. Specifically:
The subsummary is sorted on the hour of the day. For each of the 24 hours, a total number of traffic accidents is displayed (summary field) and a total number of fatal accidents is displayed (also summary field). I need the report to display the percentage of fatal accidents each hour. Simply dividing the one summary field by the other results in a "?".
I assume there is a way to do this, but can't find it. Any help you could provide would hopefully bring this project to a merciful end.Answer:
You cannot calculate based on Summary fields (as you have probably noticed).
What you can do is this... instead of summary field(s), use calculated fields that use the GetSummary function. It will work exactly the same as the Summary fields, but will give you the ability to calculate off them.
- PORTAL REFRESH PROBLEM
By Shawn D. Hogan - Question:
I'm having a problem with a portal in a relational DB. Very simple DB with a master file and 3 related files. All files are related via the Student ID #. All relationships appear to be working just fine. THE PROBLEM IS: that the portal info doesn't automatically update itself (show up) in the master file after a new related record has been created via the related file (I'm not creating related records via the portals in the master file, just via the related files). the only way the portal will update is to close the master file and then re-open the master file. once I do this, the portal info now shows up and is correct on the master file. I can't figure out why this is happening since the portal is working, just not immediately showing the info in the portal in the master file. all other aspects of DB works fine.Answer:
It's a screen refresh problem... the easiest way to work around it is after you create the new related record, and you want it to display properly in the master file, flip to a different layout and then right back to the original layout. (Just add it to the end of your script that creates the data in the related files).
- SERIALIZATION WITH SCRIPT
By Shawn D. Hogan - Question:
I want a serialization system that maintains number sequential integrity and does not require reset of auto enter counter fields in x number of files. The Max function is too slow once file size gets above 1000. I would hope to accommodate most user delete scenarios. Any ideas?Answer:
This one gets a bit tricky, but I've done it many times... so I know it can be done using something similar to the following script:
--------
Enter Browse Mode []
Find All
Unsort
Go to Record/Request/Page [Last]
Set Field ["gNext Record ID", "Customer ID + 1"]
If ["Next Record ID Validation::Identification = 1"]
Set Field ["gNext Record ID", "Max(Global_MainMenu::Customer ID) + 1"]
End If
Next Record/Request
--------
Other things you need to define:
Identification = Calculated Field that results a number (=1)
gNext Record ID = Global Number Field
Customer ID = Autoentered Calculation (=gNext Record ID)
Next Record ID Validation = Self-joined Relationship (=gNext Record ID::Customer ID)
Global_MainMenu = Self-joined Relationship (=Identification::Identification)
- SETTING ZOOM LEVEL ON GRAPHICS
By Shawn D. Hogan - Question:
I have a calculation field which will return a percentage value. I want to be able to use that percentage value to modify the contents of a picture container. That is, if I get a value of "60%" in my calculation field, I want the corresponding graphic to be displayed in the picture container.
It would be really cool if I could also have a series of radio buttons that could allow the user to 'modify' the style of the graphic (e.g. '3D', 'flat', 'perspective') in conjunction with the calculation field. Is this where a portal could be used? Any help would be appreciated. Thanks in advance.Answer:
Mmmmm... well there is no easy way to do it... but what you could do is increment the graphic zoom level by 10s and then create 10 calculated fields... each of which return a certain zoom level of the picture.
Then overlay them on top of each other, and make the calculations only return the picture if the zoom level is set it its particular zoom level.
- ADDING 1 TO AREA CODES
By Shawn D. Hogan - Question:
I need help on figuring out a script for FMP4.0. I wrote a very basic script which switches from my phone book layout to my client detail layout, and then dials the phone number (via modem) in a specified field. It works just fine-- when the field does not contain any area code information.
The problem is that probably 90% of this field does have an area code in it (which is input as "(313) 123-4567"). When I try to dial these, I get the usual "you must dial a "1" when dialing this number" message.
How can I put together a script that will perform a calculation to solve the missing "1" problem?Answer:
The easiest way to do it would be to go to the Dialing Preferences (in the Application Preferences) of FileMaker Pro, and set one of the settings to:
If text begins with (313), then replace with 1(313).
- CDML IF STATEMENT
August 17, 1998 - By Shawn D. Hogan - Question:
I want to have CDML/HTML display not display a set of fields depending upon whether one of the fields is equal to "yes." How can I do this?Answer:
Try this:
[FMP-If: Field .eq. yes]
Display this data if it's true.
[/FMP-If]
- SUMMARIZING ZIP CODES
August 16, 1998 - By Shawn D. Hogan - Question:
I am in a quandry. I work with Filemaker 3.0 on a PC. I have been using same data and I need to create a report that will sort and count in sequence by zip code only. So basically, I need to know what formula to use to set up a report that will count the number of entries per zip code for my data.Answer:
Sort the report by Zip Code. Then make a layout that has a sub-summary section on it that is summarized by Zip Code. And finally, make a summary field that is the Count of Zip Code, and put that field in the sub-summary section.
- CDML FORMAT FILES
August 9, 1998 - By Shawn D. Hogan - Question:
I am designing a medium sized site for auto classifieds and am planning on using a Filemaker Pro Database. The ISP I am using has Filemaker Pro 4.0 as a stand alone server but we are having problems with the format files used with custom web publishing.
The server gives an error "Format File Not Found" when trying to access the database using the correct CDML tags in the URL. The files are located in the same folder as the database itself. (The only folder I can access)
If you have a minute could you give some advise?Answer:
The CDML format files MUST be in the Web folder within the FileMaker Pro directory...
- GETTING RID OF TODAY RECALC
July 21, 1998 - By Shawn D. Hogan - Question:
Is there a way to get rid of the today recalculation? Every time I open a FileMaker Pro database, it does a painfully slow today recalculation.Answer:
Yep... replace all your Today functions with Status (CurrentDate).
And remeber to make any calculation that contains the Status (CurrentDate) function to be unstored.
- CALCULATING YEARS MONTHS DAYS
June 19, 1998 - By Shawn D. Hogan - Question:
I know this question is not difficult for you, but it's driving me crazy. How do I write a calculation that will let me know the years, months, and days of service an employee has accumulated. Your help is GREATLY appreciated.Answer:
Well, all you need is two dates to work with. A start date and an end date. Let's say you want to know how many days an employee has been employed as of today...
It's easy... Make a couple calculations that result as numbers as follows:
Years = Int ((Status (CurrentDate) - Hire Date) / 365.25)
Months = Int (((Status (CurrentDate) - Hire Date) - (Years * 365.25)) / 30.44)
Days = (Status (CurrentDate) - Hire Date) - (Years * 365.25) - (Months * 30.44)
You can of course replace 'Status (Current Date)' with any date field.
- RADIO BUTTON FINDS
June 19, 1998 - By Shawn D. Hogan - Question:
I have a database that I would like to perform finds on using a selected radio button as the Find criteria. Nowhere can I find in the script steps a command to "select" a radio button in Find Mode. I could do it manually, but I'm trying to make the interface as easy as possible for rookie-FM users, so they don't need to perform an extra step.
My usual way to accomplish a similar task is (in the script) to go to Find Mode, select or paste-into the field, perform the find, then sort the records for display. I just can't seem to get a radio button to "select". The script will go to the button, but not "click" on it.Answer:
Instead of using copy/paste to set fields in find mode, use the Set Field function.
So for example you have a radio button with two possible values (Yes or No), it uses the field 'RadioButton'.
So, if you wanted to set it to 'Yes', simply use this script step while in find mode:
Set Field [RadioButton, "Yes"]
Basically just forget the fact that itÕs a radio button and you will be fine.
- GETTING AWAY FROM COPY PASTE
June 18, 1998 - By Shawn D. Hogan - Question:
In the layout mode, you have the ability to make a list of entries that automatically pop up when you click or tab into a field (value-lists). This entry can either be written directly into the variable or can be drawn from another file. I work with a Database that controls all the support questions that come in to our company. We have a database listing all our resellers (Reseller.fp3) and one listing the support files (SIF.fp3). Between these two a relation ship have been defined based on the reseller name, and the reseller name appear as a value list in SIF.fp3, drawn from the Reseller.fp3.
In Reseller.fp3, there is a list of the technicians that call in the support calls, as many of our customers are in the Far East or eastern Europe, some names are spelled in a way that is not natural for us, thus the need to draw on this list of names in order to get the name right, both because of statistics, but also as a courtesy to the reseller, that we are able to spell their names right.
Now If I make a value list based on the field technician in Reseller.fp3. It lists all the technicians listed in the entire reseller.fp3, not just the ones associated to the specific reseller. Is there some way that this value list can be linked not only to the database, but to a specific record within the database?
The workaround that I have created is to make a portal, drawing the technician field into the SIF database, and making a button executing a script that copies the selected name. This works fine, and even have the advantage that I am able to edit the data on the technicians locally, instead of having to go to the other database. There is however an other problem with this solution.
The script look like this:
Copy [Select]
Paste [Select, "Contact person"]
Very simple and straightforward, however if no field is selected, and this button is activated, FileMaker crashes!!!! This obviously is not a viable solution, although knowing this happens helps avoiding it.
If you have a solution to above I would be very grateful, the solution is needed not only for this but also for a database keeping track of specific parts mounted in some of our products.Answer:
Well... you are on the right track with the portals... But instead of copying/pasting a selection, try this:
Go to field format for the name field(s) in the portal. Take away the ability to enter the field. Then you can use a transparent rectangle and overlay it to cover the entire portal line. Make that rectangle a button that triggers a script. You can make a one-line script that does what you were trying to do without copying or pasting... Try this:
Set Field [Contact Person, Resellers::Reseller Name]
That's assuming that Resellers is the name of the relationship used in the portal, Contact Name is the target field and Reseller Name is the field in the reseller database you are trying to get the data from.
- LOCKING LAYOUTS
June 18, 1998 - By Shawn D. Hogan - Question:
Can I lock a layout so the mode can't change?
I want a layout to only ever be in find mode. So a user can not be in browse mode. If they are and they add data in a field to search on, because of a relationship, it changes the data in the same field in the related database.Answer:
Yeah, you can... but it would mean that you have to control everything through scripts. If you don't give the user access to the menus, they can't do it.
You can set that in the Passwords section, just set the available menu commands to "Editing Only".
- USING ENTER TO SWITCH FIELDS
June 18, 1998 - By Shawn D. Hogan - Question:
Is there a option to allow me to switch from tab mode when entering data to "enter/return" key?
This would be nice when you only have one line fields to fill.Answer:
No... not directly.
You could use script to do it though. You would use a bunch of the Pause/Resume script steps that use the Go To Field script steps after each one. Then when you hit the enter key (on the keypad, not on the keyboard) it will resume the script, go to the next field, etc. You could then insert another pause for however many fields you want to go through.
- CHANGING FIND CRITERIA
June 17, 1998 - By Shawn D. Hogan - Question:
I have a script that finds records by the contents of the field selected (selecting "pink" in the description field finds all records containing "pink"; selecting 6-12-98 under date finds all records dated 6-12-98, etc...).
The script copies the selection and pastes it into the corresponding field in find mode, executes the find and pauses until the user selects continue; pretty straight forward. However, to prevent the find from returning similar records ("pink elephants" and "pink Cadillacs"), I've included a Paste Literal step that pastes "==" into the find field first and then pastes the users selection. Works great unless the user selected a "blank" field. Because of the paste literal step, the find will always contain "== " and that seems to prevent standard "no records found" errors (which I could easily capture).
I need the script to be able to determine if the selected field is empty, BEFORE it executes the find step.
I could write an individual "if" statement to handle potential "blanks" for every field by name, but I know there has to be a smarter way (maybe using a global and "set field" step?).
I know I could do it if there was only a "Status (CurrentField*Value*) function.
I'll bet I'm missing something obvious-- what is it???Answer:
You are on the right track... since you are only taking parts of a single field, you can't use the Set Field function directly. But you can paste it (with the standard paste) to the field while in find mode. Then add a script step that uses Set Field to manipulate the field data you just pasted. Something like this:
So set the field you just pasted to, to be the following:
If (IsEmpty (FieldName), "", "==" & Fieldname)
And remember you will need to be in find mode for the paste AND set script step.
- MERGE FIELDS WITHIN A FIELD
June 17, 1998 - By Shawn D. Hogan - Question:
1. Is it possible to call a field within a field, similar to <<filename:fieldname>>?
2. I like my contacts solution to warn me of birthdays, etc. Currently the only way I've done it is to have it run a script every time it starts up, doing a "find" for birthdays that take place within 0..10 days, then displaying a message if that occurs asking me if I'd like to view them. Is it possible to just have a calculation of some sort display these records with birthdays within the next 10 days somewhere on the form without having a dialog pop up if they occur?Answer:
1. To some extent... you can define your own variables that can be passed to fields, and then use a calculated field for the output... for example, this is a calculation I use for one of our systems that allows variables (<FName>, <LName>, etc.) to be inserted automatically...
Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Letters::Letter Body, "<Login>", Login), "<Pass>", Password), "<FName>", First Name), "<LName>", Last Name), "<Company>", Company Name), "<Status>", Status), "<Type>", Account Type), "<Class>", Account Class), "<AreaCode>", Phone Area Code), "<Phone>", Phone), "<Street>", Address), "<City>", City), "<State>", State), "<Zip>", Zip Code), "<Date>", DateToText(Status(CurrentDate))), "<Balance>", If(Balance Due < 0, "-", "") & cBalance_Credit Text Output), "<Region>", Region)
2. The only way to do any "automatic" notification without a script being triggered would be if it was possible to index a field that was based on the Status (CurrentDate) or Today function. But since that can't happen, that pretty much rules that one out unfortunately.
- COUNTING RECORDS FOR A REPORT
June 17, 1998 - By Shawn D. Hogan - Question:
I am writing a database in which the user can enter an unlimited number of files. However, for one of the reports, the user must select a maximum of 40 files to list on the report.
I have tried to do this by creating a 'check box' on each record in the 'list' view, so the user can go down the list and select (or deselect) the records they want in the report, and I put a field at the top which counts the number of 'check boxes' selected. I want a script that limits the selection of records to 40, and puts up a message when the user exceeds that number of selections. I haven't been able to come up with a solution that works.......Answer:
Make the selection checkbox field validated by a calculation. Make the validation calculation check to see if the number of check boxes are less than 41. Then you can specify a dialog box to show if it doesn't pass the validation.
- FINDING WITHIN A RANGE
June 17, 1998 - By Shawn D. Hogan - Question:
A real estate database. One field: Minimum SF (Square Foot) Available,
One field:
Maximum SF Available.
During a Find:
I want a single field, in which a user can put in a single SF figure
(i.e. 2000 SF), and have FMP Find every record in which this single
figure falls between the Minimum and Maximum SF Available.
How do I relate this single Find field to the two database fields? Is
there a scripting solution?
or
I want a single field, in which a user can put in a range SF figure
(i.e. 2000...4000 SF), and have FMP Find every record in which this
range figure overlaps between the Minimum and Maximum SF Available (i.e.
find records with 1000 SF min and 3000 SF max; 3000 SF min and 5000 SF
max; and 2500 SF min and 3500 SF max).
How do I relate this single range Find field to the two database fields?
Is there a scripting solution?
Note: the database's uses extend far beyond this FileMaker solution, so
I cannot change the two database fields to a single field holding a
single number. If it is possible to combine the min and max figures into
a single "range" field and work with it that way, that may be a
possibility.
Thanks in advance for any (and all) insights into this problem.Answer:
The easiest thing to do it build the find request using a script. When a user is entering the find criteria, make them enter the number(s) into global fields in browse mode.
Once the numbers are collected, enter find mode and build the request with a script. Let's say your min and max fields were calls Minimum and Maximum, and your global capture field is Global. And you wanting to find everything that fell between that number
A basic script to build the request would be:
Enter Find Mode []
Set Field [Minimum, ">" & Global]
Set Field [Maximum, "<" & Global]
Perform Find []
I know that is a real basic example, but it should work...
- TRIGGERING SCRIPTS WITH CDML
April 10, 1998 - By Shawn D. Hogan - Question:
I have completed my databases they work wonderfully, I am about to finish the HTML and CDML and so far all is great. However I cannot find How I get the HTML or CDML tags to start a script written in the DB? Any ideas where to look?Answer:
This is just a direct copy/paste from the CDML reference...
-Script tag
-----------
What it does
Specifies the FileMaker Pro script that should be performed after the finding and sorting of records (if any) during processing of the action.
Value is
Name of the script to perform.
Syntax example(s)
Perform a script using a link
<a href="FMPro?-DB=db.fp3&-Format=rslt.htm&-Script=Omit+Script&-FindAll">"Run script"</a>
Perform a script using a form action
<form action="FMPro" method="post">
<input type="hidden" name="-DB" value="names.fp3">
<input type="hidden" name="-Format" value="results.htm">
<input type="hidden" name="-Script" value="Omit Script">
<input type="submit" name="-FindAll" value="Run Script">
</form>
Other tags that are required
-DB, any action tag
See also
-Script.PreFind, -Script.PreSort
That should probably help ya a bit... :-)
- REINDEXING FILEMAKER PRO
April 10, 1998 - By Shawn D. Hogan - Question:
I currently use a software program written in DB/2 that automatically reindexes all of the database before opening it. This has proved extremely useful in that I've never had any problems with the database, which contains a considerable amount of data.
We are currently transforming the software to FileMaker Pro. Is there a function in FM Pro that basically does the same thing - reindex the database to prevent corruption?Answer:
Not automatically... then again, I've never experienced any index problems with them... If you WERE though, you could always perform a recover, which reindexes the database among other things.
- RECORD LEVEL ACCESS
April 10, 1998 - By Shawn D. Hogan - Question:
I am attempting to set up a database containing records with (at minimum) two fields: User and URL. I need the individual user to only be able to edit records containing their user name. (In other words, record level access control) The gestalt of it is to allow users to publish a list of URLs to the web. Thanks for your help!Answer:
First of all, you are going to need to lock down the menus and control it all on your end. So in the Password Privileges, you need to give Editing Only access.
On the most basic level, the easiest way to do it would be to make a calculation that was Status (CurrentUserName), define a self-joining relationship that related from your new calculated field to your Created By field.
Basically, now you can use the script step, Go To Related Record (with the show only related records option on). It will show only the records created by them. You could get "fancy" and show the related records via a portal or something.
That's the basic concept behind it... hope that helps...
- COUNTING PAGE NUMBERS
April 10, 1998 - By Shawn D. Hogan - Question:
I would like to be able to print on the bottom of a printout something to the effect of, "page 1 out of 4 pages"
I need to do a find, the result is variable, sometimes 1 record sometimes hundreds of records. Then, I need to send a printout of the find and on the bottom of the page I need to print that the number of pages is: , e.g "page 1 of 6", or " page 2 of 4", or " 3 of 9" , etc.Answer:
You could use a script that basically does this:
Freeze Window
Enter Preview Mode
Go To Record/Request/Page [last]
Setto Status (CurrentPageNumber)
- MATH ERROR IN FILEMAKER PRO FOR WINDOWS
April 10, 1998 - By Shawn D. Hogan - Question:
There is a math error with the Int (integer) function of FileMaker Pro for Windows. It's been bugging me for a month now, and I finally tracked it down.Answer:
Here's an example... A calculation that is Int (9.95 * 100) should obviously return 995... right?
Wrong.
On the Macintosh it returns 995, on the Windows version of FileMaker Pro, it returns 994. Don't ask me why, because I have no earthly idea... Just thought people might want to know.
Verified to occur in FileMaker Pro 3.0 and 4.0 for Windows, and NOT to occur in FileMaker Pro for Macintosh.
- OBJECTS ON A PER RECORD BASIS
March 10, 1998 - By Shawn D. Hogan - Question:
I have a layout that contains around 1200 records. Each record represents a single part. A record contains the part number, description, vendor, etc. I want to link the corresponding document to each single part or record.
What I have been doing is (in layout mode) going to EDIT> OBJECTS>INSERT OBJECTS, click on "create from file", choosing Microsoft Word Document, choose the corresponding document, and display as an icon. When the icon is inserted into the layout, it is displayed on each of the 1200 records. I do not want this because it only pertains to one record. Do you know what I mean?Answer:
You can insert opjects on a per record basis if you create a Container field. When you are in browse mode, you can click in the container and then go to the Insert Object command (just like if you were in layout mode).
For everyone who doesnÕt know, objects are only available on the Windows version of FileMaker Pro
- FINDING UNIQUE RECORDS
March 6, 1998 - By Shawn D. Hogan - Question:
I have what could be a dumb FileMaker question? I am currently creating a database for use as part of our companies intranet. One script I need to create I canÕt seem to get , it could very well be very simple. What I need the script to do is:
Got to: [Field A]
if: [field A {is a unique number}]
then, Go to [field B,{reset to 0,restart increment by 1}]
else:[go to (field B, increment by +1]
End if:
What is in { } is the part I can not figure out how to script can anyone help... itÕs probably is obvious.Answer:
Make a self-joining relationships that relates Field A to Field A. Then make the If comparison the following:
Count (New Relationship::Field A) > 1
- SOUND CONTAINER PROBLEMS
March 6, 1998 - By Shawn D. Hogan - Question:
We have a solution that we are just about to market, expect for one troublesome sound container. It is located on our startup screen, and is meant to be a startup sound associated with the focus of this solution.
Of course it is not necessary to the functionality of the database, but we've gotten a definate "must-have" from those who have seen (heard) it.
My problem is that the sound works fine for a few launchings, but after that it seems to lose the sound and the poor user is confronted with a sound recording prompt upon startup. Needless to say, this has intimidated most of my test users.
Because the container can accept cut & pasted data, I thought that we might have somehow hit the Command-V shortcut and replaced the sound, but after much testing, that's not the case. I have set the size of the container to 1x1 pixel, it has transparent borders and I tucked it away in the corner of the layout so as not to be disturbed. Also, I've tried disallowing entry into the sound field, but then the script cannot perform it's "Select/Perform" function in the sound container.
Help!!! I am sure that this is not a novel idea, so is there a standard procedure for ensuring that this sound is stored "permanently"?Answer:
To make sure no one can make a change to it, make the container validated by a calculation that is "1=2". Which obviously, will never be true. Also, don't allow a user to override the validation. Then if someone tries to change it, it will tell them they have to revert the field (since 1 doesn't equal 2). You can also give them a custom dialog box if you want.
- SHOWING CUSTOMER AGING ON STATEMENTS
March 6, 1998 - By Shawn D. Hogan - Question:
How would you create a mailable statement that would show compounded interest? I have a client who wants to place at the bottom of his monthly statement a table with 30-60-90 days and include the balace owed.Answer:
Well... basically make 3 calculated fields in the invoice file as follows:
c30 Days = If (Invoice Closed <> "Yes" and Status (CurrentDate) - Invoice Date <= 30, Customer ID, 0)
c60 Days = If (Invoice Closed <> "Yes" and Status (CurrentDate) - Invoice Date <= 60 and c30 Days = 0, Customer ID, 0)
c90 Days = If (Invoice Closed <> "Yes" and c30 Days = 0 and c60 Days = 0, Customer ID, 0)
Then in the customer file, you can make 3 relationships that relate the Customer ID to your new fields. Then you can Sum the invoice balance based on the relationships, and you will "magically" have a 30-60-90 balance... :-)
- VARIABLE BODY LENGTH
March 6, 1998 - By Shawn D. Hogan - Question:
I am trying to write a Data Base with the following fields.
(Time) (Info)
I want to be able to put in a Time (formatted as time) and put in Info (formatted as Text)
I want it to display all the record typed in in this format.(Time) (Info) 0800 I type info in the Info field 0900 Then I add another record to the file and I want it to have no blank spaces in it. 1000 Whenever I display it, it does not look like it should. It looks like below.
This it what it looks like (the wrong way)0800 I type info in the Info field 0900 Then I add another record to the file and I want it to have no blank spaces in it. 1000 Whenever I display it, it does not look like it should.
How do I get rid of the blank space between 0800 and 0900.
If I make use the columnar report I can use slide/printing to move contents of the field up but the "body" will not move up.Answer:
If you use the 'Also reduce the size of the enclosing part' checkbox in the Sliding Objects definition, it should work for you. That will kill any blank space (and slide the body up).
- SYNCHRONIZING FILES
March 6, 1998 - By Shawn D. Hogan - Question:
I have been wondering for some time what procedure to use to synchronize two FileMaker files. We have a system in our office of multiple people using FileMaker for quotes to customers, and it's necessary periodically to synchronize the various files so everyone has the same history data. How do you do this? I know of no feature that FileMaker has that allows directly synching different FM files.Answer:
Well, the easiest thing to do is allow everyone to log into the same database via multi-user. But if you don't want to do that, you CAN synch files based on their modification date and unique identifiers, but it is a MAJOR hassle. It basically consists of importing all the databases around into one master database, and then writing a script that sorts it based on the recordÕs unique identifier, and then deleting all the records with the same identifier with the lowest dates/times. So you are only keeping the record with the highest modification date/time.
- MULTIPLE PORTALS PER LAYOUT
March 6, 1998 - By Shawn D. Hogan - Question:
I am trying to help a user "fix" some databases he created. He wants to see all pertinent information from 4 databases on one screen. Databases look like this: Database A tracks people who own cars. Database B tracks cars. Database C tracks car parts. Database D tracks part suppliers. Relationships look like this: 1 person can own many cars; 1 car can have many parts; 1 part can have many suppliers. How do I see the supplier information (Database D) in the people who own cars database (Database A) using FileMaker Pro v.3?Answer:
First, make 4 portals on 1 layout.
Make the first portal show all the people, using a key field (like a calculation that results in '1' or something like that.) Then make it so that when you click on a name, it sets a global field to the person's unique identifier (for relationships).
Make a relationship that relates that global field to the cars file. And then make the second portal based on that relationship. When you click on a car, set another global field to it's unique identifier and then make a relationship to the parts file that is based on the global field.
You can continue this process for as many levels as you want. One thing you may want to think about, is in the script that sets the global fields, also make it clear the global fields that are at a lower level (so if you click on anything, the portals at the lower level won't show info that goes with another record.)
- FORCE A CALCULATION TO UPDATE
March 6, 1998 - By Shawn D. Hogan - Question:
I have a FileMaker 3.0 call tracking database that does ticket aging based on a contact by date & time and an appointment date & time. I've finally figured out ways to determine when the aging should be displayed (no reason to show contact by age if the ticket is closed, etc.), but I have yet to find a way to have the aging calculations continually update as time moves on through the day. I've found that if you copy and paste the contents of the time & date fields that the aging fields are updated, but I don't want to kludge something together that does that. There has to be some way to auto-calculate these fields. This is a cross-platform database, hosted on an NT server so I cannot use anything other than ScriptMaker, or calculation fields. Also, this needs to be automatic and transparent to the user, meaning that adding a button attached to a script will not work, or a script alone is not an option.
I would eventually like to go to FileMaker 4, but my initial tries at hosting an existing database through the web were less than satisfactory, so I'd like to complete this in 3.0)
Fields used/defined: * = Auto update needed
CONTACT BY:
CB Day Display- Calculation (The display rules)
CB Time Display- Calculation (The display rules)
*CB Day Age- Calculation (The aging)
*CB Time Age- Calculation (The aging)
CB Day- Date (Auto-entered Status(CurrentDate) + Day(1))
CB Time- Time (Auto Entered )
APPOINTMENT:
App Day Display- Calculation (The display rules)
App Time Display- Calculation (The display rules)
*App Day Age- Calculation (The aging)
*App Time Age- Calculation (The aging)
App Day- Date (Entered by user when appointment is made)
App Time- Time (Entered by user when appointment is made)
I believe, but don't quote me, that since the Display field's calculations are based on the Age field's return, that if the Age field changes, that the Display field will show the update automatically.Answer:
Two things you can do here... first, use the Status (CurrentTime) function to get the current time. You may or may not be doing that already...
Secondly, for the calculations that need to be updated on the fly, click on the Storage Options button, then select the 'Do not store calculation results -- calculate only when needed' check box.
This will force a recalculation every time the field is used or displayed.
Let me know if that helps at all...
- LARGE FIELD EXPORTS
March 6, 1998 - By Shawn D. Hogan - Question:
How can I get a field with more than 255 characters into s dBASE memo field?
I'll accept any workable solution such as taking lots of 255 characters and putting them in a new field before exporting, or making an ascii file with a tab delimiter for every soft return in a large FMP field.Answer:
Well, the 255 character restriction is imposed by dBASE... not FileMaker Pro. You can have a 65,000 character field in FileMaker. So, when FileMaker exports that format, it won't allow longer than 255 characters, otherwise it would be an invalid dBASE file.
That's about all you can do, is split it into multiple fields... For example, if your field name was 'Memo' and let's say the longest one was under 1,000, you could split it into 4 fields (Memo Output 1-4)...
Memo Output 1 = Left (Memo, 255)
Memo Output 2 = Middle (Memo, 256, 255)
Memo Output 3 = Middle (Memo, 512, 255)
Memo Output 4 = Middle (Memo, 768, 255)
You could of course, define more if it's longer...
Does that help at all? {shrug}
- EXPORTING DATA WITH CALCULATED FILENAME PATH
February 18, 1998 - By Shawn D. Hogan - Question:
Is there any way to export files with a calculated filename and/or path?Answer:
Yep... It requires a little workaround though. On the Macintosh, you can do it with a calculated AppleScript. Then you can execute the AppleScript after you export the file. On the PC, you can do something similar, but with the Send Message script step, and use a calculated send message. Just pretend you are sending DOS commands with it.
We use a database to generate our website (http://www.data-point.com). In that database, we use the following calculated AppleScript to move and rename the exported file to the proper location...
"tell application ""Finder""¦
move file """ & Website Path & ":" & Subdirectories &If(IsEmpty(Subdirectories), "", ":") & Filename & """ to trash¦
move file ""Eden:temp.html"" to folder """ & Website Path & If(IsEmpty(Subdirectories), "", ":") & Subdirectories & """¦
set name of item """ & Website Path & ":" & Subdirectories &If(IsEmpty(Subdirectories), "", ":") & "temp.html"" to """ & Filename & """¦
end tell"
- AUTO ENTRY NOT WORKING
February 18, 1998 - By Shawn D. Hogan - Question:
I would like to auto-enter a default date (that is contained in another field in the same database) in a date field that is also used in a relationship. When I try to do it by defining a calculation in the auto-entries options, I loose the relationship because apparently a date field with a calculated automatic entry cannot be indexed, even when the program is specifically instructed to do it. Is there a solution ?Answer:
Well, you can do it...
If you are trying to calculate a related record based on a relationship, you must make sure the relationship is valid before it tries to pull it in. The only way to make a relationship work BEFORE any data is in the record is to have the relationship key field a global field.
If you do that, it should work fine...
- GO TO RELATED FILE
February 18, 1998 - By Shawn D. Hogan - Question:
I have not been able to master how to use the subject scripting command. I am always stumped when I have subscripts about WHICH subscript (First? Middle? Last?) the command should go in! Is there some general rule I am missing here?
In a chain of scripts that work in a related file, WHERE do I put the "GoToRelatedRecord" command?Answer:
Well, you can put subscripts where ever you want. They just execute in sequential order. Like the main script will pause until the subscript is done. Then it picks up where it left off.
You put it in the main file. (All it does is set the found set in the subfile to the related records using that relationship).
- FILEMAKER PRO TO PAGEMAKER QUARK
February 18, 1998 - By Shawn D. Hogan - Question:
Can you tell me what the best resources are to send text formatted with tags into PageMaker or Quark? For one report I'm doing (this is for a client who produces this report once a year), I have to create a layout with summary parts on it, print to text using Print2Pict, use Add/Strip to replace all the space runs with tabs, etc. Then the user can place the text into their PM/Q document.
There's got to be an easier, more cross-platform way of getting this info from FM into PM/Q.Answer:
Well... you could always build text based reports with calculations. (Similar to how we build our website). We have calculations that generate the HTML and spit it out for us... (http://www.data-point.com)
But that can get tedious when building it. But works awesome once it's done. Just need to use calculated fields with the GetSummary function instead of summary fields...
- EMPLOYEE WAGE REVIEW
February 18, 1998 - By Shawn D. Hogan - Question:
I am developing a FileMaker 3.0 application for wage/salary review. Each employee's hourly rate is displayed and it is desired that the reviewing manager enter a percent increase or a dollar increase, which will result in the calculation of a new hourly rate (display only). If the manager enters a percent increase, the dollar increase should calculate automatically (without having to click a button); if a dollar increase is entered, the percent increase should be calculated. How do I make the percent increase and dollar increase fields both an input field and a calculation field? I have tried several approaches, but have not had any luck. Any ideas?Answer:
Well, first of all, you can't make an input field a calculated field. You can take two different paths to get what you want...
You could have a button that was labeled 'recalc' or something that triggers a script that calculates the new wage for you. That would be the easiest way.
Another easy way would be to create a some extra fields, a number field to input their hourly rate, then another number field to input their percentage, and then a calculated field that calculates the new one for you.
The ideal way to do it would be to define the input hourly rate to perform a lookup every time the percentage changed, but unfortunately that would create a circular reference and FileMaker won't allow it.
- TEAM TOTALS
February 18, 1998 - By Shawn D. Hogan - Question:
My problem deals with a file in which placegetters in a race are entered for each event. The finishers are entered by a number which is related to another file. This works fine.
Each finisher scores a certain number of points depending on the place. The points scored appear in a related file and the aspect that I cannot work out is how to total up what each team scores for each event contested (given that a team may have more than one representative in the event) so that a grand aggregate for all of the events can be calculated.Answer:
Okay... Are the teams the placegetters? Basically, you do a calculation that is something like:
cTotal = Sum (Relationship Name::Points)
That will give you the points for the individual. Now to get a total of the points for a team, you of course will need some sort of identifier that is unique to each team. Then do a self-join relationship that is a relationship that the file is related to itself. And for the two related field, use the unique identifier (let's call that relationship 'Team'). Then you can do a calculation that is the following:
Sum (Team:cTotal)
So basically you are doing a sort of 2-level deep relationship since cTotal is a sum of a relationship as well. And that should do it for ya...
- CLEARING AN ENTIRE REPEATING FIELD
February 18, 1998 - By Shawn D. Hogan - Question:
Is there a way to "clear" all of the fields in a repeating field. When I use the "clear" command it only clears the first field of the repeating field.Answer:
You can use the Set Field command and set it to "". Unfortunately, you have to repeat this for each field repetition. (You can set which repetition you are doing)...
- DIFFERENT LAYOUTS FOR DIFFERENT USERS
February 18, 1998 - By Shawn D. Hogan - Question:
My boss has decided he wants one universal database that will accommodate all the paperwork at our three locations. And he wants it all turnkey: we do all the designing here and they simply push buttons. The problem is - he doesn't want modifications for each location. He wants the whole system to be identical at each location.
What complicates this is that every office has different forms based on their sales. We tried overlapping fields on some layouts, but it got too messy - especially with sliding, etc. So I need to be able to click on one button and depending on which location the person is at, it goes to the layout they need.
I thought of tying it to user name, but then that means every time a new person is hired/fired, I have to go in and reconfigure the scripts and then "upgrade" every database at all three offices. Is there an easier way? [He'd rather not have multiple buttons that say "office 1 layout", "office 2 layout", etc.]Answer:
Well, instead of hard-coding the user name to the scripts, you could have a separate table (file) that basically is a list of user names and what location they belong to. Then you can base it on the user name and if someone is hired or fired, it would be a matter of editing that file (one record per user would work fine).
You could get a little more complicated with a table-level access control scheme that allows you to define what each user has access to. And it can be done without getting into the password setup. But that might be more than you need.
- DEFINING RELATIONSHIPS WITH A RUNTIME
February 18, 1998 - By Shawn D. Hogan - Question:
Is it possible for a user of my FM runtime user to easily change a relationship. I have file called Senators. I want it to be able to relate to files that contain vote information, Module1 and Module2.
Right now, I have the user manually download files and rename them manually Module1 or Module2. This works okay. But it confuses some of my less computer literate users. I would rather have the user be able to enter the name of the related file in a field and have the relationship change with a script. This way the user could type FebuaryVotes and have the relationship change.
Possible?Answer:
Nope... defining (or changing) relationships is not possible in the Runtime version of FileMaker Pro.
Instead of different files, why not have all the information in 1 file, then they could see only what they wanted by changing the relationship key. Might work better, and be easier. {shrug}
- NETWORK SLOWNESS
February 18, 1998 - By Shawn D. Hogan - Question:
Have a fairly large solution containing five related files residing on a power mac which is Ethernet connected to two other Mac's at this time. It is Slow!!!! When FileMaker on the resident machine is not active (in the foreground) it is more than slow. The FileMaker doc's are pretty vanilla on this subject. I have Chris Moyer's book and it is helpful, but I still keep hoping that maybe there is something I can do to speed this process up a little.
Would appreciate any words of wisdom concerning this subject. I'm not exactly network savvy, and could use any help I can get.Answer:
First of all, if you are not using the TCP/IP protocol as your FileMaker Pro transport, use it if at all possible. It's vastly faster than AppleTalk or IPX/SPX.
The next thing you may want to do is invest in FileMaker Pro Server, which is made for serving large FileMaker Pro databases.
And finally, the last thing you may want to do is get FileMaker Pro Server for NT instead of Macintosh. I'm a total Macintosh guy, but the fact remains that FileMaker Pro Server for NT will kick the Mac version's butt up and down the street. For our clients, they usually have the FileMaker Pro Server as an NT box with Macintosh clients.
I guess in order to go into any more detail, I would need to know what specific tasks are slow... :-)
- ECHOING BACK NULL CHARACTERS
February 3, 1998 - By Shawn D. Hogan - Question:
I need a field in my database just like any ordinary password field, with the bullets substituting the real password, locked for copy. How to do it?Answer:
Well, there is no realistic way to do it... You could always just set the text color to be the same as the background color. Not ideal, but it will at least work. {shrug}
- A WAY OUT OF LAYOUT HELL
February 3, 1998 - By Shawn D. Hogan - Question:
I am setting up a test which gets the questions/answers/review info (for wrong answers)/congratulation comments (for right answers)/ and sundry comments from another file in which all the relevant info exists on 1 (of 99) records for each of 99 questions. The length of the questions and multiple choices answers for each varies. I can set it up with each element a separate field (with a match field to the data file), but then I have to make the fields large enough to accommodate the longest of the possible entries and it looks like... GARBAGE. Not to mention that the radio boxes and submit button at the bottom (in the footer, in fact) may be a mile away from the shorter of the questions. This way looks great in Preview, but it won't run in preview, so Who cares?!
OR... I can set it up with most of the stuff in a single text field, everything merged. But then I lose the ability to use varied text attributes and I'm still confronted with some of the same length problems. I could, I guess, live with this solution if I could figure out how to make the Footer occur after the last of the actual text has, instead of at the bottom of the field (which may vary from a few lines to hundreds). And If I can do this... is there any way to integrate graphics and/or scripted buttons within the merge field so that they appear adjacent to the same thing (Choice A, B, etc.) regardless of the length?
I'm stymied and frustrated by being so close and yet so far (heck, I'm so impressed with myself for making it all work just the way I want but, it would seem, incapable of making it look the way I need it to). I would appreciate any and all hints on ways I might get, at least, closer to a usable layout. Thanks for your time and help!!!Answer:
The whole design may work better if it was a little different... Instead of a big layout with all the questions, why not just have a single layout with 1 question? Then when they are done with the questions, then can click a next button. You could then make the next button trigger a script that brought up the next question (without changing the layout). So you could have one small layout with a single question on it.
If you decided to do it like this, you would probably want to make the questions a related file and then the answers another related file.
- FILEMAKER PRO 4.0 PLUG-INS
February 3, 1998 - By Shawn D. Hogan - Question:
I've seen in FileMaker 4.0 there is this concept of externals or extras, a plug-in type architecture. Is this method as flexible as the externals used in something like 4D, or are they limited to actions that happen outside the database, i.e. glorified export filters.
For example could one be written to add different calculation functions that could be accessed in the normal way through FileMaker, or perhaps to make complex substitution easier through the use of an array? Here I'm thinking about converting ascii to web text, but being able to customize that conversion so that some characters are not converted.Answer:
Yep... it's possible... I dunno if you noticed, but if you disable the Web Companion plug-in, you don't have as many functions in calculations (they are in the External functions).
I haven't seen the plug-in SDK kit yet, but I already have a few plug-ins I want to create.
Like SMTP support to send and receive email directly, and a credit card authorization program without using an external program.
So yeah, the plug-in architecture looks pretty cool from what I HAVE seen. You can add calculation functions, pass information to the plug-in to perform tasks if needed, etc.
- SLIDING OBJECTS AND PAGE BREAKS
February 3, 1998 - By Shawn D. Hogan - Question:
I have a quote generation database. The user can select from any combination of about 50 products and services related to scanners. The problem: I generate the quote using sliding fields. If the product is not selected the field is blank and the one below it slides up. This creates a nice quote without spaces regardless of which option is selected. However, the quote is three pages worth of sliding fields. If the quote runs over a page, FileMaker breaks the page right through a field. This doesn't happen all the time. I can't measure out the right distance between fields and page break because there are spaces to separate certain products and the fields vary in size. It seems that this would be a simple thing for Claris to fix, but according to people in my users group Claris knows about it, but are unable (unwilling?) to fix it. A solution would be greatly appreciated. Thanks.Answer:
While in layout mode, hold down the Option key (Shift on the PC) while selecting Layout Setup. Undocumented hidden feature will appear (New Break checkbox). That should take care of it for ya... :-)
- ROUNDING NUMBERS
January 21, 1998 - By Shawn D. Hogan - Question:
I have created an invoicing database for my boss. The total on each invoice includes Tax etc., and are rounded off to 2 places. The problem is that, when I run off a statement it calculates the totals of say 100+ invoices on the original un-rounded invoice totals, the statement total can then be off by a small amount.
Is there anyway to work around this. Is there a way of totaling rounded sums accurately?
Can the Invoice total be saved as a rounded total?Answer:
Yep, use the Round function. LetÕs say that your current invoice total calculation looks like this:
Sum (Line Items::Line Item Subtotal)
If you make it read the following, you will be all good:
Round (Sum (Line Items::Line Item Subtotal), 2)
The 2 is the precision you want to round (in this case, 2 decimals).
- USING SUMMARY FIELDS IN CALCULATIONS
January 21, 1998 - By Shawn D. Hogan - Question:
I recently read the charting article in FMPro Advisor magazine. I have set up all of the fields necessary for the charts, but want to know how to get the data from the sub summary into the global fields that hold the charted values.
For example: I run a report to see how many service calls we did in a month. The field is "sTotal Number Records" (a summary field of the count of Record Number) in a sub summary sorted by "cMonth" (a calculated field based on the record's creation date). My chart data fields are "gJanCalls", "gFebCalls", etc.
I was hoping to do this in FileMaker because when I try to export "cMonth" and "sTotal Number Records", summarized by "cMonth" to tab-delimitated text I don't get the sub summary totals, I get the total number of records in the "sTotal Number Records" field.Answer:
Turn your summary fields into calculations that use the GetSummary function. That will allow you to do the same thing as the Summary fields, but will let you play with the values (for example, pass them to a global field).
The format for GetSummary is: GetSummary (field to summarize, sort field)
One other thing you may want to consider. If you are just counting records in your found set, you could use Status (CurrentFoundCount)...
Much faster than summarizing the records (check out my article on it).
- VALUE LISTS IN THE HEADER
January 21, 1998 - By Shawn D. Hogan - Question:
I am developing a database for student grading. I use value lists as a method to select various weighting formulas to apply to grades. I have placed these pop-up value lists in the Header of the page. For practical purposes for teacher's using this database, all students should be shown at all times which necessitates the use of "View as List". Is there a 'work around' that will allow the {pop up value lists} to work correctly when in "List View" instead of having to revert to "Form View"? Also - is there a way to allow these value lists to be 'global' but to be indexed?Answer:
Unfortunately, no... it's not so much an issue with the value lists though. It just comes back to the fact that in List View, FileMaker Pro does not allow entry into any fields except the fields that are in the Body portion of the payout.
You can't index a global field. What you could do though is have a separate file (a preferences file or something) that has only one record that the value list(s) are generated from. Of course, the field couldn't be a global field in that file either, but it might as well be since there is only one record.
- GIMME THE LAST REPEATING FIELD
January 16, 1998 - By Shawn D. Hogan - Question:
I created a database to track documents and their copy number. I have a repeating field that shows a history of who has/had a specific copy number.
One layout shows all of the repeating fields.
"John..Had
Bob...Had
Ken...Has"
A second layout includes only one of the repeating fields. I want the second layout to show the latest inputted name. (Ken)
However it shows only the first name. (John)
Is their a way to format the layout to show the last field? (Possibly a script or calculation to provide input into another field) I do not want to have to reorder the data.Answer:
Yep... try the following calculation:
Last (Repeating Field)
- GET SUMMARY FUNCTION
January 15, 1998 - By Shawn D. Hogan - Question:
I am trying to summarize data in a transaction file by fiscal year. I have been trying to figure out how to use Get Summary feature, but have not been able to master it. Any help would be appreciated.Answer:
Well, the GetSummary feature works similar to a SubSummary part of a layout, in that it needs to be sorted by the field you are trying to summarize in order for it to work properly. Other than that, it's pretty straight forward.
- FISCAL YEAR TOTALS
January 15, 1998 - By Shawn D. Hogan - Question:
Again, for my example, I've got Joe Smith, who made three payments of $500 in the 1996 fiscal year and made two payments of $50,000 in the 1997 fiscal year.
What I want is for each payment record to also display the totals for 1996 and 1997.
In the subsummary report I created I could total up the payments for each year it was sorted by, which gives me the total for the year the payment was made, in the subsummary part.
But how do I get this information to show in the record, not the subsummary, and how do I get the totals for all the years into the record?
Again, many thanks for your help with this. It will be a great day when I learn the solution.Answer:
Check out Fiscal.fp3 to download an example of how to go about doing this.
It would take me longer to explain how to do it than it would to just DO it.
- SEND CONTACTS EMAIL WITHOUT KNOWING ADDRESS
January 15, 1998 - By Shawn D. Hogan - Question:
IÕm using FileMaker Pro to maintain a client database. I need users that are logged into the database to be able to send email to any of the clients without the user being able to know the clients email address. Is there a way to have a single computer act as a mail server that sends messages for the users?Answer:
The Send Mail script step in FileMaker Pro is setup so that it sends email through the userÕs email client. Unfortunately, this makes it so the user could open the email and see who it went to.
The way I usually get around this is to create something like an email queue. Where users can create emails (with a FileMaker Pro front-end), and then a dedicated Òemail serverÓ processes the emails in the queue at set intervals.
You can create a infinitely looping script that checks to see if any emails need to be sent out every so often. Just insert a Pause Script step within the loop and specify how long you want to pause the script. So if you pause the script for 10 minutes, it will check the email queue every 10 minutes to see if anything needs to be sent.
- WINDOW SCROLL BAR DEAD
January 15, 1998 - By Shawn D. Hogan - Question:
I have a script that generates a report, but only the first page worth of data shows on the screen in browse mode. I have the layout set to View As A List and it still does it.Answer:
This is another one of those great things that I wish someone at Claris would explain to me. If you happen to perform a script in the right sequence, the window scroll bars will not be displayed even though it should be. I have found FileMaker Pro 3.0 and 4.0 for the Macintosh to do this, and it seems to be a refresh problem.
If you perform a subscript that performs a find, then switches to a certain layout, and then sorts the data, you will occasionally get this problem. If you reverse the Go To Layout script step and the Sort script step, it will not happen.
- OMIT GLITCH
January 15, 1998 - By Shawn D. Hogan - Question:
I have a script in one of my databases that I build a find with. What seems to happen is the find request(s) that Omit records, actually Finds the records instead. What the heck is going on here?Answer:
I ran into this problem when developing Optigold ISP. When I was developing a script that built a find request that included omissions, it would work fine until a user triggered it. Then it would do what you described.
Since it worked for me but not them, I started to look at the differences between my setup and their setup. The only difference was that the status area was locked and hidden for them, and it was hidden but not locked for me. It turns out that If you use the Omit script step for building a find, it will totally ignore it if the status area is locked and hidden. It still builds the request, but instead of omitting it, it finds it.
I thought that this was pretty lame, and figured it was a bug in 3.0 and even went so far as to let Claris know about it, but low-and-behold, it still does it in 4.0. So... um... whatever... {shrug} Maybe someone at Claris could let me know the reasoning behind this?
What I ended up doing as a work around is before the building the find request, I unlocked (but continued to hide) the status area, and then when it was done, I locked it again. And as long as you donÕt let the user abort the script, then itÕs all good.
- CONCATENATED RELATIONSHIPS
January 15, 1998 - By Shawn D. Hogan - Question:
I had two unrelated FileMaker Pro databases that I needed to relate to pull in some information that one was missing. The only identifiers I had to match duplicate records was name, city and state. I created a concatenated field in both files that was the name, city and state for relationship purposes. Sometimes the relationship failed and would relate to a record that was very close, but not the same. I checked the two concatenated fields for both records and they are different. Why is FileMaker Pro relating these two records?Answer:
The answer is simple... FileMaker Pro only indexes the first 20 characters of a word (or what it perceives as a word). LetÕs say your concatenated field was the following calculation:
First Name & Middle Name & Last Name & State & City
And you had two people with the same name but different cities and states...
Billie Joel Underwood
Coronado, CA
Billie Joel Underwood
Boulder, CO
So even though the two concatenated fields are different...
BillieJoelUnderwoodCACoronado
BillieJoelUnderwoodCABoulder
The internal index that FileMaker Pro uses to relate the two records would be identical because it ignores anything after the 20th character.
billiejoelunderwoodc
Thus, FileMaker Pro will relate the two records.
Of course... all of this is totally ignoring the fact that a relationship like that is very bad database design. :-)
- CONSOLIDATING RELATED FILES
January 13, 1998 - By Shawn D. Hogan - Question:
I have a database that has 3 call activity files. One for customers, contacts and tech support. Is there any way to consolidate the 3 call activity files into a single call activity file?Answer:
By defining the following fields: Customer ID, Contact ID, Tech ID and Call Activity Text, you can relate the contact, customer or tech support file to itÕs ID field in the call activity file. Then use the Activity Text field to store the call activity info.
- MANY TO MANY RELATIONSHIPS
January 13, 1998 - By Shawn D. Hogan - Question:
I have a FileMaker database that is a listing of employees that shows all the projects they may be working on. There are a few problems I have run into...
1. IÕm using a repeating field to list the projects an employee is working on and it just doesnÕt cut it, as an employee can be working on any number of projects.
2. I want to be able to see what employees are assigned to a specific project.Answer:
The best way to solve your dilemma would be to design a many-to-many relationship scheme for your files.
In your case, a many-to-many relationship is when you have the ability to assign any employee to multiple projects AND the ability to assign any project to multiple employees. The basic many-to-many relationship scheme requires 3 files... An employee file (Employee.fp3), a projects file (Project.fp3) and then a related file (Related.fp3) that you use to tie the two together.
The employee file should contain a unique identifier field (Employee ID) for the employee records, the projects should also have a unique identifier field (Project ID) for each record. Your related file should have 2 fields (Employee ID and Project ID) for the link between the employee and project file.
Your 3 files could be defined as follows:
Employee.fp3Field Definitions Identification Indexed,Calculation,=1 Employee ID Indexed,Number,Auto-Entered Serial Employee Name Text gProject To Add Global,Text cProject ID Add Calculation,=Project Add::Project ID Relationships Assigned Projects Employee ID =::Employee ID Related.fp3 Project Add gProject To Add =::Project Name Project.fp3
Project.fp3Field Definitions Identification Indexed,Calculation,=1 Project ID Indexed,Number,Auto-Entered Serial Project Name Text gEmployee To Add Global,Text cEmployee ID Add Calculation,=Project Add::Project ID Relationships Assigned Employees Project ID =::Project ID Related.fp3 Employee Add gEmployee To Add =::Employee Name Employee.fp3
Related.fp3Field Definitions Identification Indexed,Calculation,=1 Employee ID Indexed,Number Project ID Indexed,Number cEmployee Name Calculation,=Employee::Employee Name cProject Name Calculation,=Project::Project Name cEmployee To Add Calculation,=Global Project::cEmployee ID Add cProject To Add Calculation,=Global Employee::cProject ID Add Relationships Employee Employee ID =::Employee ID Employee.fp3 Project Project ID =::Project ID Projects.fp3 Global Employee Identification =::Identification Employee.fp3 Global Project Identification =::Identification Project.fp3
When you create a record in the related file, the Employee ID is the unique identifier of the employee you are assigning to the group. The Project ID is the unique identifier of the project you are assigning to the employee.
If you view the Assigned Projects::cProject Name through the portal, you will be able to view all the projects an employee is assigned to.
If you view the Assigned Employees::cEmployee Name through the portal, you will be able to view all the employees assigned to the specified project.
When assigning a project to an employee, set the gProject To Add field to the project you want to assign the employee to. Then, you could use button that executes a script to create the related record in the related file, and then sets the Project ID field to whatever the cProject To Add field is.
When assigning an employee to a project, set the gEmployee To Add field to the employee you want to assign the project to. Then, you could use button that executes a script to create the related record in the related file, and then sets the Employee ID field to whatever the cEmployee To Add field is.
Uh... {scratching head} does that make sense? :-)
- SPEEDING UP SUMMARY COUNTS
January 4, 1998 - By Shawn D. Hogan - Question:
I have a contact database with 50,000+ records. When I run a report, it takes a few minutes just to calculate the total number of records shown in the report. Is there anything to speed this process up?Answer:
This is something I have noticed as well, if you use a summary field that gives you a count of a field, it takes an incredible amount of time to return a result. ItÕs fine to do it this way until you get into the tens of thousands of records, then it becomes a noticeable hassle.
What you can do is use a calculated field that returns the number of records in your found set by using one of the Status commands as follows:
Status (CurrentFoundCount)
Keep in mind that this cannot be used if you are trying to count the number of records in a Sub-Summary, as it will return all records in your found set. But if you are just trying to get a total number of records shown on a report, it works very well (and fast).
- CROSS-PLATFORM ISSUES
October 22, 1996 - By Shawn D. Hogan - One little note if you are developing cross-platform solutions on a Windows computer. Make sure you have the store Macintosh Graphics turned on in the preferences! I learned this on the hard way one time.
- STORING IMAGES
September 11, 1996 - By Dickon R. Thompson - You can use a global field set as a repeating container which stores graphics or, better still, images drawn in FileMaker Pro 3.0. Use either icons or colored patches and then access these by a calculation field, DisplayColours:
Case(FieldA = "Urgent", GetRepetition(gColours, 1), FieldA = "Tepid"; (GetRepetition(gColours, 2))
FieldA is you criteria field (could be a calc itself) and gColours is your global repeating container field.
Then place copies of the ColourDisplay field behind all data fields which you would like to appear coloured. Great to indicate outstanding performance, overdue fines and the like.
If you have the time and foresee doing lots of these eye-catching calcs, set up a separate file called ColourSource (you choose!) which has one global repeating container field of 88 reps, one for each colour in the FP3 spectrum. You can then relate this file to any/all future files and always be able to use the same GetRepetition numbers.
- COLORING LAYOUTS
September 11, 1996 - By Dickon R. Thompson - Color a layout by clicking on the body part label rather than creating a rectangle and coloring the shape. This allows you to have seamless color for all parts (Header, Body, Footer) and stays in the background - if you send an object to Back it will still be in front of the colored part.
One small glitch is that objects which are given no color may sometimes not show the background color (this is a screen refresh problem) until the enter key is pushed or record altered - I have no explanation yet.
- INCORPORATING APPLESCRIPT
July 15, 1996 - By Shawn D. Hogan - If you do your development on a Macintosh, you have the ability to incorporate AppleScripts into your solution. One of the nicer features of this is the ability to calculate the source code of an AppleScript.
As an example, I have a database system that I use to develop web sites. Once they are developed, I can use an AppleScript to FTP the new files into the proper locations of my web site automatically. A calculated AppleScript for a portion of this task would look like this:
"tell application ""Fetch 3.0.1""¦
activate¦
put into transfer window ""www.digitalpoint.com"" item alias ""Eden:Web Site:members:" & Site URL &":"" text format text binary format Raw Data¦
end tell¦"This is the part that FTPs the new directory to the web server.
- CHECKING FOR A NULL FIELD
June 13, 1996 - By Shawn D. Hogan - In FileMaker Pro 2.1, it seemed to be a common practice of many developers to check if a field was empty or zero by leaving the operator off. For example, if someone was to check if a field called TOTAL was empty, they might do the following:
If (TOTAL, "Not Empty", "Empty")
It works in most cases, but when you convert to 3.0, it usually did not work. The way I would personally do it would be the following:
FileMaker Pro 2.1: If (Length (TOTAL) = 0, "Empty", "Not Empty")
FileMaker Pro 3.0: If (IsEmpty (TOTAL), "Empty", "Not Empty")
- INDEX LIMITATION
May 31, 1996 - By Shawn D. Hogan - While on a consulting job, I stumbled across a limitation of FileMaker Pro 3.0 (no other versions were tested). The internal index that is created for sorting, searching and relationships is cut off after 20 characters. Two fields with the following data in them would register as identical fields:
robinquasebarthcasandiego
robinquasebarthcasantafe
FileMaker's index on those two fields would be:
robinquasebarthcasan
Thus, making it impossible to do a proper relationship without reformatting the fields.
Note: This example was only for the purpose of showing the problem. It was not intended to show quality coding examples.
 |  |  |  |
 |  | ||
| Add your FileMaker site to the premier web ring! | |||
Return to Digital Point Solutions' Home Page