Updating Calculated Fields Dynamically

[Tamarin2087]

Being new to the forums, I hope that this question is placed correctly.

I am developing a database using FM Pro Unlimited to display help desk phone analysts' statistics. The supervisor accesses a web page using Web Companion, enters a date and is given individual and team stats for that day, week, and month. I have 4 tables that house raw data that have a series of key fields defined based on the ID of the individual and the date. The data is used by series of tables to do the periodic calculations as follows:

Table Names

Supervisor Daily

Supervisor Weekly

Supervisor Monthly

Agent Daily

etc, etc

These tables have a key field that is an unstored calculation returning a text result. So, for instance, if Supervisor #1 wants to see data for 2/1/2003 the key would look like "1- 2/1/2003".

My problem is that when the date is changed either from the Web Companion or directly in FM the calculated statistics are not updating. The key field is updating but the values remain unchanged until I click in the calculated fields in the application.

Any thoughts that you can offer are greatly appreciated.

[Vaughan]

"These tables have a key field that is an unstored calculation returning a text result."

Hmmm. Key fields -- as in relationship keys -- *must* be indexed in FMP. Unless you refer to another meaning of the word "key."

Check that the field is unstored. Unstored forces it to be calculated each time it's required. Note that if the field is used as a relationship key it'll no longer work because it's unstored.

[Tamarin2087]

I should probably be more specific about the fields being used for the relationships. Using the term "key" is a bit misleading. The data tables are the many side of a one-to-many relationship. The fields used to make this relationship are stored and indexed. The "one" side of the relationship in the scoring table is unstored. I have used this technique before in smaller databases in FM with success. Because it is on the "one" side, FM did not require that it be stored or indexed. As the date changes it changes the unstored realtional field and selects a new set of rows from the data table.

The calculations are being made correctly, they are just not being propigated in the calculated fields until I place the cursor in that field. Once I've done that, the new value is displayed.

I think I am going to have to redesign the way the calculation tables generate their relatioanal fields. But I am not sure yet how I will go about it.

[Vaughan]

In general, unstored calculated fields and portal displays do not "refresh" or re-calculate until the record is exited or the user changes fields.

Common "refresh" techniques include a quick switch between browse and preview modes, exiting the record, and changing portal rows, but all of these require tight scripting and imply a tight control over the interface.

Consider posting your files so we can take a look.

[Tamarin2087]

Thank you Vaughn! The toggle between Preview and Browse did the trick. I hadn't thought of that approach before. The only worry left is how I am accomplishing that task. Because I am using IWP with the interface suppressed in favor of my own buttons I have a very limited array of tools to trigger events. So the result is a script that cylcles through the toggle script for each file I need to refresh. It is doing this on a continuous loop.

Is there a way to start a script from a logical function occuring in a field? I haven't found one yet, but if there were it would be the cherry on top of the cake.

I would like to upload the files and I may do so tomorrow. I will have to remove all the proprietary data and put in some dummy numbers first.

Thanks again for the help!

[Tamarin2087]

I solved the last problem, finally. I found another post addressing the problem of activating scripts from field calculations. Once I downloaded and installed ScriptIt and combined that with the toggle method you mentioned it solved the problem completely.