6 Replies Latest reply on Jul 1, 2014 8:02 AM by BenOtto

    Update calculation fields in non-selected layout

    BenOtto

      Title

      Update calculation fields in non-selected layout

      Post

           hi,

           Suppose I have to tables, Table 1 and Table 2. Table 1 includes some information that I can enter manually, lets say field 1 and field 2. Table 2 has a field with automatic calculation, lets say with value "Table1::field1 + Table1::field2".

           When I am in the Layout of Table 1 and enter the values of field 1 and field 2 I would like filemaker to automatically calculate the new result in table 2 in the same instance.

           However what happens is, that the value isn't calculated until I switch to the layout of table 2, alias "touch" the table. It doesn't make a difference whether I set the field in table 2 to unstored or stored.

           How can I get filemaker to always update the field without leaving the layout of Table 1?

            

           Cheers

            

           benjamin

            

        • 1. Re: Update calculation fields in non-selected layout
          philmodjunk

               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.

          • 2. Re: Update calculation fields in non-selected layout
            BenOtto

                 Hi Phil,

                  

                 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.

            • 3. Re: Update calculation fields in non-selected layout
              philmodjunk

                   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.

              • 4. Re: Update calculation fields in non-selected layout
                BenOtto

                     Hi Phil

                     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. :)

                     cheers

                      

                     Benjamin

                • 5. Re: Update calculation fields in non-selected layout
                  philmodjunk

                       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.

                  • 6. Re: Update calculation fields in non-selected layout
                    BenOtto

                         Thanks for the help! :)