Use a field defined in the Invoice table that calculates the amount payable without using a script.
Thanks. But this very example is used by Filemaker themselves to explain the use of merge variables (http://www.filemaker.com/11help/html/create_layout.9.30.html#1064499). Two other examples they mention is calculating a purchase order number from a record number, and to display the number of pages of a report. It seems these examples don't take into account that the scope of a global variable is not limited to one window, and will normally produce wrong results when a second window is opened for the same layout. It only works correctly when the data is the same for each window, like the account name. This severely limits the use of merge variables.
I found a nice script that let's a user click on a header to sort the records by that column. If the column is clicked again, the sort order is reversed. It makes use of two global variable: to remember the column and to remember the sort order. For the headers it also uses lots of global variables to show an up or down arrow in the correct column. Imagine what happens when a second window is opened for that layout. All windows share the same variables for this up or down arrow.
And what about a layout that creates a report for a certain date the user specifies, say I have a table of contracts with a start and an end date. The user enters a report date, and a script counts the number of active contracts for that date (contracts with a start date on or before and an end date on or after the report date). Of course I want the report date in the report. Now the user opens a second window and enters another report date, to compare the number of active contracts for two different dates. If the two windows share their report date in the same global variable or global field, the report date in the first window will be wrong. Or at best the first window will change and display the same report as the second window.
I have found tons of examples where global variables were used that will give unexpected results when opening a second window for the same layout. Not all of those examples can reasonably be fixed by using fields in tables. I suspect that we need variables with a scope that is limited to a certain window. This might be circumvented by using a special table and enter records for each window, just to act as as a placeholder for data that is local for each window. But this seems an unnecessarily complicated hack. What do I use as a key to find the correct records for my window? It would probably mean I have to ensure each window name is unique.
For now it seems the easiest and safest solution is to prohibit opening a second window for the same layout, whenever merge variables are used and we expect the data to be different for the windows.
Please, tell me I'm wrong.
None of this changes the fact that you can use a calculation field to show this info. With a calculation field, you will be able to open as many windows as you want and each will display the correct information.
The fact that you can use a variable does not mean that you should use one and this is a case where a calculation field makes more sense.
Are you sure a calculation field can solve all of the examples I mentioned?
Let's look at the example where I have a bunch of contracts with a start and end date, like
January 1 - January 31
January 1 - February 28
January 15 - February 15
The user should be able to open a layout, enter a report date and get the number of active contracts. So the first window looks like:
Report on January 10 - active contracts 2
In a second window the user enters another date and gets:
Report on January 20 - active contracts 3
How can I get the reporting date into the report, so that each window keeps its own reporting date?
That doesn't match your original post:
a script is triggered that calculates the amount payable. The result is put in a global variable
The amount payable will be an amount computed from one or more fields in your database from a group of one or more records. Between found sets and relationships, a calculation or possibly a summary field should have no trouble showing that value.
"How do I get the reporting date into a report" is a different question and depends on how your database tables are designed as to whether or not that will be possible.
Sorry, I should have been clearer. My original example was inspired by the FileMaker manual. My real problem is more general as the use of multiple windows is biting me everywhere, because of the use of global variables (or global fields), hence the topic name. Using calculation fields does solve the simpler cases. Thanks. Now I am looking for solutions for the less simple cases.
"How do I get the reporting date into a report?" The table Contracts has the following fields:
Start Date Required Value
End Date Required Value
Active Calculation = ( Start <= $$ReportDate ) and ( End >= $$ReportDate )
Count Summary = Count of Start
When entering the layout $$ReportDate is set to Get(CurrentDate) and a find is performed for Active = 1. On the layout $$ReportDate and Count are displayed. There is a button that triggers a script that lets the user change $$ReportDate and does a new find on Active = 1. After changing the report date the user opens a second window for the same layout. That is when the trouble starts.
What is the proper way to do this in FileMaker? How should the table have been designed? (If it helps, I can upload a demo file.)
The key difference between your first example and the second is that the first can be derived from data stored in the database and the second is data entered/selected by the user for this specific report.
Since the user has to specify the start and end dates for your date range, I'd use fields in the database to collect the user input instead of using global variables. This is slightly simpler to set up if you have the users using drop down calendar equipped fields for the data entry. If you limited reports to a single window, these two date fields could be global fields, but with multiple windows, you have the same issue as global variables--the same value appears in all windows.
But you could set up a table of records for users to use to enter the start and end dates for each report and a relationship can control which record shows those dates in your windows.
And if you don't need the precise dates entered by the user or if these searches of the database, you can show the minimum (earliest) and maximum (latest) dates from your found set of records. These dates will be close, but not always exactly the same as the user specified date range. (But for large data sets, might be exactly the same 99% of the time.)
PS some additional notes:
Due to complications with "window clutter" and window management issues in windows systems, I usually limit my systems to no more than two open windows--a "main window" and a modal dialog window that is open just long enough for users to enter data.
There is no need for your "active" calculation if the only reason is to pull up a set of records for a report and it will be slow if you have a fair number of "active" records to find. Use a scripted find that uses a date range expression with the start and end dates to pull up a found set of records. You can find an example of that kind of script here:
Thanks for your answer. I really need multiple windows. They got us 27" displays for this. Entering the report date for each window into a table WindowVariables makes sense. What can I best use as a key to retrieve the correct reporting date? Window names aren't unique, and a user might be logged in on more than one device. I thought about generating a random number to uniquely identify the window, but I don't know how to link the random number to a specific window. Or could I trigger a scripts that keeps running as long as the layout is open? Is there a way to do communicate with such a script to ask for the values of variables? This variables would be local for this window instance of the layout.
And because layouts and tables are so closely related, how do I retrieve the correct reporting date from table WindowVariables on a layout for table Contracts? I am inclined to use ExecuteSQL() but perhaps I am overlooking some obvious solution.
BTW The user just enters one reporting date in a window. Each record has a date range (start and end).
If you restrict the creation of new windows to a script, the script can a) create a new record in a "window management" table and then b) create the new window and an auto-entered serial number or timestamp from this new record can be then used as part of the calculated name of the window. Thus, you can give windows unique window names.
And because layouts and tables are so closely related, how do I retrieve the correct reporting date from table WindowVariables on a layout for table Contracts?
That's a very good question and goes to the heart of this matter. The trick would be to set up an unstored calculation field that uses Get ( WindowName ) to access the name of the current window. That calculation field can then serve as a match field to the correct record in the Window Management table in order to display data unique to the report for that window.
This is a great answer. Thanks! Just what I was looking for, short of Filemaker Inc introducing variables that are local to windows. (IMO There really is a good case for this, as very many scripts I saw that use global variables will not work correctly for multiple windows. And if only one window is used, there is no reason not to use variables local to that window in stead of global variables.)
If I understand your solution correctly, there will be one record for each window. A ldisadvantage about this approach would be that I need to define fields for each variable I intend to use, regardless of the window. I was thinking about a table with fields for WindowID, VariableName and VariableContent. Then I have one record for each variable, and I can create new variables on the fly. But that will make it impossible to access the variables with the calculation field as you described. Now, custom functions to set and get the value of these variables would probably be quite handy. Unfortunately, I can't find a way to enter data into fields using custom functions. Am I overlooking something obvious?
short of Filemaker Inc introducing variables that are local to windows.
That would be a good idea. When you use Visual Basic to set up a form, you can set up "edit boxes" right on the form that can hold data specific to that form in a given window.
I recommend making a feature request where FileMaker Inc. Personnel can see it: http://www.filemaker.com/company/contact/feature_request.html
Your solution to set up a separate table with 'variables' and use an auto-entered serial number for the relation, works like a charm. Thanks again! Fortunately there is no need to create windows by script only. I use a script that is triggered when entering the layout.
However, if a find doesn't return any records for a certain reporting date, it is quite obvious for the user that the empty list means that there where no matching records. So the natural thing to do for the user is to enter another reporting date. But because there are no records in the found set, the relation with the record in the 'variables' table is lost. When the user tries to enter a new reporting date, Filemaker complains: "No records are present. To create a new record choose the New Record menu command. Any suggestions how to solve this?
Besides variables with a scope limited to a window, it would be nice to have entry fields in a layout for variables. I guess that's another feature request.
I'd add a button for the user to click when they want to try again. The "Hide When" setting can be set up to hide the button when there is a found set greater than zero so this button can appear only if the report criteria returns no records.
At first glance, the solution seemed to work. But using an unstored, calculated field based on Get(WindowName) as a match field, gives inconsistent results. I made a layout that displayed the match field in table 1 and the primary key field of table 2. As the relation between the two tables is "match field of table 1 = primary key field of table 2" they should always have the same value on this layout. But they don't.
So it seems I am back to square one. I just can't believe Filemaker overlooked such a basic notion as the proper scope of variables. And while I understand that using a changing calculated field as a match field, is a bit far fetched, Filemaker does allow it and shouldn't give incorrect results.
I guess as a last resort, I could try writing a plugin. That could give me storage with a scope local to the window. But there would be no easy way to get the data to and from a layout. Please tell me I'm wrong. I'm starting to consider ditching Filemaker. :-(