Unrelated table needed

[ThePopp]

I have created a database that manages trips for a small jump airline. They charge a mileage rate, airport fee, wait time, etc. I would like to put these into a customer editable area so they can change these as needed. I have tried to use globals, but the values appear to change for some unknown reason. I then tried to set up an unrelated table, but I get in the fields. I also tried to create a separate database with 1 table and 1 record (containing all the fields/values), set up the file reference, but I can't select the table/fields in the script editor.

I would be happy to use any of the above methods if they worked consistently. I've tried to run script debugger to troubleshoot why the globals change, but I haven't had any luck with that yet.

Any suggestions would be appreciated.

[Fenton]

First. Please change your "profile" to reflect what version of FileMaker you're using. I'm going to assume 8.5.

I'm also going to assume that we're talking about fields which have only one value, not lookup tables of multiple rates.

The best method, in my opinion, is a single-record table, using regular fields for data entry. Then a 2nd field for each field, a calculation, = 1st field, with Storage (•) Global. The "global" calculation field is then usable from any other table, without a relationship.

It is also multi-user friendly, in that you can modify the "globals" while the file is hosted, and the changes will stick; which they won't with global fields, unless you unhost the file before editing.

For safety it's best to use "record-level" access restrictions, in Accounts & Privileges, for the Globals table. So users cannot accidentally delete its 1 record, or create another record. Either of those actions would break its functionality.

Because it is a regular data table, though only 1 record, you must take it into consideration if you Save as Clone; which would delete its record and values.

Globals_TO.zip

[K1200]

Calculated globals sound very useful, but I'd like to add these questions to clarify things:

Where are the modified contents being stored by FileMaker when the database is closed and reopened?

Can a calculated global be defined in any table (i.e., a related one) and be used without regard to any such relationships?

And specifically for a single-user environment, how is this different from setting up a table using the "x" relation operator? I was under the impression that all fields in an "x-related" table could also be accessed from any layout.

Thanks for any help.

[comment]

Can a calculated global be defined in any table (i.e., a related one) and be used without regard to any such relationships?

Yes and no. That is, it can be defined in a related table, but it will not be very useful there. A global calculation is stored, and it will not update when the referenced field is modified in another table.

When the global is in the same table as the referenced non-global, it acts as a "publisher" - on one hand, it recalculates when the non-global is modified, and on the other hand, it IS global, so it is accessible from anywhere without requiring a relationship. So you don't need to complicate your graph by connecting every table to the "preferences" table - and that should answer your second question.

[K1200]

comment, thanks for the clarification.

While you were responding, I added the "where is it stored" question. Would you mind filling in that bit of information, as well?

[comment]

I don't know - I suppose where all stored calculations are stored, somewhere in the file?

[K1200]

I guess I didn't ask that question quite right. How about this:

I'm looking at the example file that has a single Globals table with a single Data field, yet there are four occurrences of the global result, one for each of the four records in the Data table. Also, the global field contents are preserved upon a shutdown/restart cycle in an apparent exception to the general rule "global fields will not retain their values after shutdown" (from White Paper for FMP Novices). By what mechanism are the four contents being retained?

Obviously there's something I'm misunderstanding here, but I don't know what.

[comment]

"global fields will not retain their values after shutdown"

That is not strictly correct. Global fields do retain their values. In fact, they retain their values only too well. They do not retain MODIFICATIONS to their values, made by individual users - when the file is served. Every time a served file is re-opened, the globals are reset to the values that were set the last time the file was opened in single-user mode.

The technique described by Fenton is designed to enable (some) users of served files to set "global" values and make them stick. The other side of this is that - unlike regular global fields - these settings affect ALL users.

there are four occurrences of the global result

There is only one global result. What you see is regular text fields set to auto-enter the global result at record creation - without a relationship.

[K1200]

Very enlightning. I've always operated in the single-user environment, so the "served" aspects never comes into play. Now I understand -- except for this one thing:

What you see is regular text fields set to auto-enter the global result at record creation - without a relationship

I still can't see the mechanism by which this occurs. Where are these "regular text fields"? In which table? If the answer is "neither", please elaborate.

[comment]

They are in the Data table. The global calc is in the Globals table. The Globals table has only one record.

[K1200]

O.K., I (finally) get it:

The calculation in the Data table "captures" whatever global result is "published" by the Global table at the point that each Data record is created.

Now everything in Fenton's explanation makes sense. Thanks for helping me with this.

[djlane]

Hi Comment,

I have a global field which references an unstored calculation in the same table but it does not recalculate when the calculated value changes. The calculation is get(current date). When I change the windows system date and refresh the screen, the calculation changes but the global does not. See attached. Is there something that I have misunderstood?

I was planning to use the global to calculate whether or not a demo system was expired (expiry date > current date.

global_test.zip

[comment]

An unstored calculation field does not hold a value - only a formula. So it's value cannot be modified, and the global that references it will not recalculate as a result of the date changing, or screen refreshing etc. - these are all non-events.

Why wouldn't your field be an unstored calculation? And why do you even need this in a field, when you can check anytime and anywhere if ExpiryDate (presumably a global) is greater than Get(CurrentDate)?