Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Problem updating Related fields


Recommended Posts

Hello there one and all,


I found this great forum as a result of having an intractable problem with updating related fields. I use Pro 8 Advanced and I am surprised that this problem is occuring at all and I am now worried that this issue is a basic flaw in Filemaker.


Here goes


I have 4 tables:

1) A Risk Table

2) A Consequence Table

3) A many to many Analysis Table

4) A constants Table that takes a value from the Analysis Table (3) and provides a corresponding text value which is displayed in the Evaluation Table (3) as a related text field




A Risk assessment of 1 and a Consequence assessment of 1 are put together as a calculated value in the many to many Analysis Table as 1.1. This calculated value is related to a 1.1 field in the Constants Table (4). 1.1 in Table 4 has a text value of "Low Risk"


"Low Risk" is then displayed relationally in Table (3)


It seems straightforward but -


The relationship does not update immediately when I create or change either a Risk or Consequence assessment from say 1 to 2. The calculated field recalculates immediately but the relationship simply does not update immediately as it should. It only updates after I go elsewhere in the database and do something unrelated and then come back to the layout later on. I badly need this relationship to update immediately. If it does not, my database is severely compromised in the real world.


I have tried the following none of which works:


1) Going into Preview mode and back to Browse mode. Ditto Layout mode.

2) Using text felds for the relationship

3) Using number fields for the relationship

4) Using the Refresh Window script step

5) Making calculated fields in Table (3) that are equal to the assessment fields in tables (1) and (2) and using those to calculate the final value

6) Removing the period or decimal on both sides eg "11" instead of "1.1"


7) I have triple checked all my field contexts and table occurences - they are all correct

8) I have triple checked my fields for any erroneous settings including stored and unstored calculation results, there are no problems at all.



The wierdest thing of all is that if instead of using Tables (1) and (2) to get related assessments, I simply create assessments directly in Table (3), the whole thing works perfectly and instantly. I find that very wierd. Is that a clue?


Unfortunately that is not a practical way of doing the database. It has to be based on Assessments from the related tables.


One final point is that this is a small part of a very large relational and calculation intensive tool but is is unfortunately at the heart of the tool.


Can anyone shed some light on this as I have hit the buffers with this problem


Thanking you in advance.

Link to comment
Share on other sites

I can't say I fully understood your structure ( a demo file would be useful), but it sounds like you have a relationship based on a field that depends on related value/s from another relationship.


Since Filemaker does not monitor changes in related tables, you need to force a screen refresh when changing a related value. You can do this by calling the Refresh Window step, with the 'Flush cached join results' option checked.


Note that this is purely a screen refresh issue. The actual data is always correct - just not yet displayed.

Link to comment
Share on other sites

Dear comment,


That was short and very very sweet - thank you.


I did as you suggested and checked the "Flush cache joined results" option and lo and behold the updates took place.


I am very pleased thank you.


I did not previously appreciate the point you made that Filemaker does not monitor related tables. It is good to know that the calculations are correct regardless of the screen update taking place.


Without wishing to take advantage of your interest, your reply leaves me with one secondary question. Is there a way that the database can be made to trigger a Script containing the "Refresh Window" step, automatically, each time I change an individual assessment field (ie without a button having to be manually pressed by the user?)

Link to comment
Share on other sites

Hey Boston!


Check out this plugin: Zippscript


Ray Cologon has a sample file using it posted on his website.




Look at his #8 sample file "Portal Side Bar Navigation"


I believe this will address what you are looking to do. It does require a plugin but that is not the end of the world. However, if you are planning to distribute your solution you will need to ensure that the people buying it either have the plug in or that you supply it or... Well it gets complicated and I;m not an expert on the subject. I just know that FileMaker Pro does not possess the native capabillity of what you are asking for and this plugin will give it to you.


If I am wrong please someone correct me. Also, if anyone has more advice on the plugin issue for Boston please jump in here and advise him.



Link to comment
Share on other sites

Triggering a script by changing a field value does require a plugin. There are several free plugins that do this, such as EventScript, or the aforementioned zippScript.


However, this is a bit drastic solution, and it may not be required. Usually, it is possible to "hide" the script in the user interface, so that the user is not aware of running it. In some cases, it is possible to cause a screen refresh by incorporating a trigger field in the calculation itself - but I would need better understanding of how your file works, in order to see if it can be done here (as I said earlier, I did not fully get your description).

Link to comment
Share on other sites

Belated thank you to you and Randy for your replies.


I wish I could post the relevent bit of my database to you, unfortunately, my employers are a little obsessive about their security and I am not permitted to do anything other then obey instructions, on pain of death.


I am going to take a look at Eventscript to see if that will do the trick for me.


I was told that one can make a number or text field a button as well and each time the field is accessed, it also triggers the script attached to the button. Unfortunately, it does not appear to work.



Link to comment
Share on other sites

If a field is also a button, it cannot be accessed by clicking on it - only the script is triggered. But you can include a Go to Field[] step in the script itself.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

Terms of Use