Is this a field of type number with an auto-entered calculation or a field of type calculation?
What relationship links table 1 to table 2?
Calculation fields that refer to data from a related table, such as the example you seem to have here, cannot be stored calculations. They "update when needed"--which is when the field appears on a layout or is referenced in some other fashion. But the update does happen automatically when that reference occurs.
So what you describe seems just a little different from what I would expect for either an auto-entered calculation or an unstored calculation field. This leaves me unsure as to what you actually have set up here.
thanks for the quick response. Here is the easy design for better explanation of what I want to do:
Table 1 administers storage-shelves, each with a unique ID and an occupation flag. If the shelf is occupied I want to set it to "1".
Table 2 administers items that I can put in the shelves, each shelf can hold precisely one (1) item to make it easy. So the table has a field named "shelf_uid" where I can select where I want to store my item.
So my tables would look like:
Table: Shelves | Fields: SHELF_UID, Occupation (type calculation)
Table: Items | Fields: ITEM_UID, shelf_uid (text)
Now in the calculation of Shelves::Occupation I use an SQL query:
ExecuteSQL("SELECT COUNT (shelf_uid) FROM Items WHERE shelf_uid = ?"; "", "", Fields::SHELF_UID)
This query should search in my Items table for any record that uses my shelf (via the shelf_uid) and return the number of records found. As soon as there is at least one record my number will change to greater 0.
Now what I want it to do is, as soon as I select a shelf in the fields table - so the shelf is occupied - I want filemaker to automatically execute the SQL query in the Shelves table and update my records. I want my occupied shelf to be flagged instantly - and not to change to the Shelves-Layout just to have the fields updated.
Instead of ExecuteSQL--which explains the update issue that you are having here, just use:
Count ( Items::Shelf_uid )
Provided that you link these tables in Manage | Database in a relationship, this much simpler calculation will automatically update as more items are logged as stored on specific shelves.
well I tried that but I still have to change to another record and go back to my initial one to get the calculations updated. :)
What is happening is that the data is not yet committed to the database. You shouldn't need to change records, just clicking the background of the layout should produce the same result. Commit Records is also a script step and you can set up a script trigger to commit the new record automatically.
Thanks for the help! :)