10 Replies Latest reply on Sep 30, 2010 4:48 PM by jonasg

    Relationship update: odd behavior

    jonasg

      Title

      Relationship update: odd behavior

      Post

      Hi everyone,

      working with FM11, I've run into the following problem: Related records don't update correctly. In fact, in order to reflect changes in record relation, the only way to update the related records is to open the Manage Database dialog, perform a small change (like moving a table occurence on the graph), and accepting the "change".

      Let me elaborate. The relationship setup is fairly complex, and I will try to keep it "simple":

      Projects have items and offers. Most items usually appear once in every offer. Eventually, one of the offers will be accepted by the client. That offer then becomes the one and only accepted_offer for that project. There is a m:n joining table connecting items and offers. Those joining table entries also contain more information about that particular offer/item combination, as well as providing an ID field for attaching notes (another table) and tasks (the sum of which makes up the item's price for that offer).

      What I would like to do is display the info from the accepted_offer for every item. On a layout based on items, I have

      1. a portal that lists all of the project's offers, each line having a button that will set that offer as the accepted_offer for the project.
      2. another portal based on joining table entries, showing things like description and price, which can vary between offers.

      NOW: When changing the accepted_offer, one would expect the content of the joining table portal to be replaced with the item's description and price from the new accepted_offer, right?

      As I said, this works only when I make "changes" to the DB structure, which makes me suspect a possible FM caching bug. ALSO weird: when trying to perform a find in the second portal, I get this error:

      "This operation cannot be performed because one or more of the relationships between these tables are invalid." There isn't much to find on the net about this, but most (if not all) of it seems to be related to erraneous use of the "on delete cascade" feature in the relationship properties dialog. But like I said, this happens in FIND mode.

      I have tried different combinations of removing relationship attributes like the one described above, with no apparent results.

      If this wasn't too much too read already, I would really love some help on this truly annoying problem. Thanks a bunch!

        • 1. Re: Relationship update: odd behavior
          philmodjunk

          The devil is definitely in the details here and we'd need to know exactly how you set up both portals and their underlying relationships.

          Here's a bit of a wild guess:

          Is there a filter set on the portal that isn't updating? That's the first possible issue that comes to mind. With filtered portals, getting the portal to update after a value referenced in the filter expression is changed often requires this script step in order to get it to update:

          Refresh Window [Flush Cached Join Results]

          This script can usually be perfromed by a scripttrigger set on each field referenced in the filter expression or included in any scripts where the values might be modified.

          • 2. Re: Relationship update: odd behavior
            jonasg

            Thanks, Phil! Using that script step solved the update issue. Of course, the error in find mode still persists. I will further examine this before getting into more detail.

            • 3. Re: Relationship update: odd behavior
              jonasg

              I have built a test database from scratch to reproduce the issue. Could this have to do with the multi-predicate join from items to items_in_offers2?

              screenshot

              (sorry for the edit:) There are no filtered portals. Only conditional formatting (green fill) for the project's accepted bid. The yellow TOs are based on what I called joining table earlier.

              • 4. Re: Relationship update: odd behavior
                philmodjunk

                Is Main Layout based on Projects or Items?

                • 5. Re: Relationship update: odd behavior
                  jonasg

                  Sorry, I meant to mention that. It's based on Items.

                  • 7. Re: Relationship update: odd behavior
                    philmodjunk

                    I assumed so and also note that Project_accepted_offer_ID is apparently a global field judging by the relationship graph.

                    Ive created three tables in a test file to replicate Items---<Items_in_Offers 2------<item_offer_tasks 2

                    When I create related records in both the 2nd and 3rd tables, then perform a find on the layout entering criteria in the portal to the third table (item_offer_tasks 2 in your file), I don't get an error message.

                    One key detail to keep in mind that may give you a clue:

                    When you enter find criteria in a portal, you are asking file maker to find all records in the layout's table occurrence that have a related record in the portal's table that matches the specified criteria. Also, the in between table may be a factor here. In order to see a record in this portal a record related to the current record in items that is linked to the record in the portal must exist. If there is no record in this intermediate table to "bridge the gap" the records in item_offer_tasks won't appear in the portal.

                    Given that I can't recreate the error message, it's possible your file or an index is damaged. You might try recovering the file and checking to see if the recovered copy works. If recovery fixes the problems and there are no problems reported during the recover process, you might try importing all your data into a clone (empty copy) of the file or run the recover again on the original file but specify advanced options: Copy File Blocks As Is and Rebuild Field Indexes only. Either option rebuilds your field indexes from scratch and this can solve some file issues.

                    • 8. Re: Relationship update: odd behavior
                      jonasg

                      Thanks for all your input so far! Guess I should've uploaded my test file more quickly...

                      Project_accepted_offer_ID is in fact a calculation field that passes the current project's accepted offer on to the join table. I didn't make it global because there could be items from multiple projects, and I couldn't use the field as a join criterion.

                      Maybe the whole issue is due to the use of a calculation field within join criteria? I wouldn't know why, but is it considered bad practice to use one in such way?

                      You are right about the in between tables needing to "bridge the gap". What makes me ponder, however, is that the related records show up correctly in browse mode. If the bridge was missing, the portal shouldn't display any records whatsoever, right?

                      • 9. Re: Relationship update: odd behavior
                        philmodjunk

                        That's it! Changing the field in my test file to an unstored calculation field replicated the error. In Find Mode your unstored calculation field does not have a value and this triggers the error message as FileMaker then sees this field as empty when performing the find.

                        A global data field (not a calculation field) does not have this limitation.

                        You'll need to find a way to change this field to one that is stored or which is a global data field. One option may be to set up a data field that is updated with the value of the unstored calculation field's value via script trigger every time a field referenced in the calculation is updated. That may be simple or complicated to do.

                        • 10. Re: Relationship update: odd behavior
                          jonasg

                          Thanks! That was very helpful. You sure devote a lot of time to this forum, I really appreciate it!

                          Let's see where my approach will end up on the simple<-->complicated spectrum...