14 Replies Latest reply on Jan 10, 2017 12:02 PM by electon

    Occurrence not refreshed

    sfpx

      Suppose this database

      occ.png

      where

      "C" and "C 2" are occurrences of table C

      the field "test" is an unstored calculated field  where its calculation is "Last(B::CID)"

       

      Data

      Table A

      AID=1

       

      Table B

      BID=1, AID=1,CID=1

      BID=2, AID=1;CID=2

       

      TABLE C

      CID=1

      CID=2

       

       

      On layout A you would normally get

      test =2

      C 2::CID=2

       

      Now let's delete the last record in table B and go back to layout A

       

      We get

      test=1

      C 2::CID =2

       

      If I refresh the layout I get the correct result of C 2::CID=1

       

      Now....why do I have to do a refresh ?

      I don't want to.

      I should not have to.

       

      Any way to always get the correct value of C 2 without using a refresh ?

       

      Note #1

      I can get it to work without a refresh with a calculated field based on other occurrences of A and B but I don't like adding occurrences just for simple field.

       

      Note #2

      The example above does not make much sense but it's just to illustrate the problem I have

        • 1. Re: Occurrence not refreshed
          philmodjunk

          That's not the results that I get. If I delete a record from B, the change in layouts commits the change and when I get back to A I find that Test has updated to show a different value for CID.

           

          By any chance are you using two different windows to do this?

          • 2. Re: Occurrence not refreshed
            sfpx

            Same window...navigating from layout B to A

            The 'test' field is updated correctly but not the occurrence of C 2

            • 3. Re: Occurrence not refreshed
              sfpx

              I finally decided to do a refresh (flush cache) in the deletion script.

              • 4. Re: Occurrence not refreshed
                philmodjunk

                I had a field from C 2 on my layout based on A. It also updated to show the correct value with no extra action on my part other than going to layout based on B, deleting the last record and then returning to the Layout based on A.

                • 5. Re: Occurrence not refreshed
                  electon

                  You could use a  join to a copy of B and sort the relationship in descending order by whatever field there is that indicates creation order, or unstored calc Get ( RecordID).

                   

                  The relationship will evaluate because it's not dependent on a field calculation in table A.

                  Even if you have two layouts open of A and B side by side.

                   

                  Thomas.

                   

                  Screen Shot 2017-01-10 at 19.21.59.png

                  • 6. Re: Occurrence not refreshed
                    sfpx

                    Download my example

                    Dropbox - occref.fmp12

                     

                    Go to B and delete the last record.

                    Return to A

                    You will have

                    Test= 1

                    Cn=Banana (which should be Apple)

                    • 7. Re: Occurrence not refreshed
                      philmodjunk

                      Chrome identifies the file as "malicious"--an ongoing problem with such file share sites. I am not about to take such a risk and discarded your download.

                       

                      Please use the advanced editor and upload your file or a more reputable site such as Drop Box if you want to share a file.

                      • 8. Re: Occurrence not refreshed
                        sfpx

                        Thanks for the reply Thomas.

                        There are many ways to get what I want but I wanted to avoid creating new occurrences of any table or doing a refresh.

                         

                        I guess that's impossible since it seems that a relationship based on a calculated field will not update even if the the calculated field is correctly updated. You have to refresh and flush the join cache.

                        • 9. Re: Occurrence not refreshed
                          sfpx

                          Dropbox - occref.fmp12

                           

                          I think that is the way Filemaker works.

                          A relationship based on a calculated field won't update unless you do a refresh (flush join cache) (which makes sense) .

                          • 10. Re: Occurrence not refreshed
                            electon

                            sfpx wrote:

                             

                            There are many ways to get what I want but I wanted to avoid creating new occurrences of any table or doing a refresh.

                            You already created a copy of C. I only substituted it with B 2.

                             

                            I guess that's impossible since it seems that a relationship based on a calculated field will not update even if the the calculated field is correctly updated. You have to refresh and flush the join cache.

                            I think it has to do with the extra hop from A via B to C.

                            • 11. Re: Occurrence not refreshed
                              sfpx

                              electon wrote:

                               

                              sfpx wrote:

                               

                              There are many ways to get what I want but I wanted to avoid creating new occurrences of any table or doing a refresh.

                              You already created a copy of C. I only substituted it with B 2.

                               

                               

                              I was not explicite enough, I need another occurrence of C accessible from A based on the field test (which can be many different things other than just the last record) .

                               

                               

                              I guess that's impossible since it seems that a relationship based on a calculated field will not update even if the the calculated field is correctly updated. You have to refresh and flush the join cache.

                              I think it has to do with the extra hop from A via B to C.

                              You are probably right.

                              • 12. Re: Occurrence not refreshed
                                philmodjunk
                                I think it has to do with the extra hop from A via B to C.

                                That can be a factor, but when I replicated the stated relationships and tested, I could not reproduce this particular case.

                                 

                                Factors that could be involved:

                                I made all my data changes manually. Perhaps sfpx used a script.

                                The original example does not specify field types and does not specify auto-enter options. While I don't think these would be a factor, I used number fields with auto-entered serial numbers and you never know...

                                I also used FileMaker 15.22 on a windows 7 platform.

                                 

                                I just set up the file for upload so that I can share it here. (It was part of a fiile that I'd used for several small tasks--including documenting a bug with repeating container fields so I "stripped down" a copy to remove stuff not germane to this issue.

                                 

                                Interestingly enough, if I added new records, I could replicate the "fail to update" issue, but not if I removed a record from B.

                                 

                                A less drastic way to force an update can be to set the  A::AID field to itself, but be careful as this can trigger undesirable relookups and other auto-enter options to take place so you have to consider if this will be an issue in your actual solution.

                                • 13. Re: Occurrence not refreshed
                                  sfpx

                                  philmodjunk wrote:

                                   

                                  I think it has to do with the extra hop from A via B to C.

                                  That can be a factor, but when I replicated the stated relationships and tested, I could not reproduce this particular case.

                                   

                                  Factors that could be involved:

                                  I made all my data changes manually. Perhaps sfpx used a script.

                                  The original example does not specify field types and does not specify auto-enter options. While I don't think these would be a factor, I used number fields with auto-entered serial numbers and you never know...

                                  I also used FileMaker 15.22 on a windows 7 platform.

                                   

                                  I just set up the file for upload so that I can share it here. (It was part of a fiile that I'd used for several small tasks--including documenting a bug with repeating container fields so I "stripped down" a copy to remove stuff not germane to this issue.

                                   

                                  Interestingly enough, if I added new records, I could replicate the "fail to update" issue, but not if I removed a record from B.

                                   

                                  A less drastic way to force an update can be to set the A::AID field to itself, but be careful as this can trigger undesirable relookups and other auto-enter options to take place so you have to consider if this will be an issue in your actual solution.

                                  Big thanks for taking the time to test this.

                                   

                                  I did not use any script in my test.

                                  Adding a record also does not refresh the occurrence.

                                   

                                  Basically, using a refresh (flush) is still the easiest solution even if I'm not a fan of it.

                                   

                                  So basically each time I modify the CID field of a any record of B, delete a record of B or add a record of B I have to do a refresh (flush) to make sure that the relationship A--C2 is refreshed.

                                  • 14. Re: Occurrence not refreshed
                                    electon

                                    All kind of weirdness going on. I can replicate the issue in OP's original file.

                                     

                                    The method I came up with gets the id correctly but not the related data.

                                    The data needs to sit in a portal on layout A ( even if B 2 is sorted ), then everything is ok.

                                     

                                    If a refresh [ flush cached join results is needed ], so be it. Can be costly depending on use.

                                    That's why FileMaker came up with Refresh Portal.

                                     

                                    Here's a file with some tests. Test the second A layout with a filtered portal too.