9 Replies Latest reply on Feb 24, 2014 8:09 AM by philmodjunk

    Self Join on External TO

    jayg

      Summary

      Self Join on External TO

      Product

      FileMaker Pro

      Version

      13

      Operating system version

      OSX 10.8.4

      Description of the issue

      When self joining an external TO in the relationship graph the values in the second occurrence are not available unless the second file is open.

      Steps to reproduce the problem

      Create 2 files and reference Table A from File 1 in File 2.  In file 2, create a self join of TO A.  Relate Table B in File 2 to Table A.

      File 1:
      Table A

      File 2:
      Table B -> Table A -> Table A2

      If File 1 is closed, data from the Table A2 is not accessible.  When opening File 1, the data is now accessible from Table B.  When closing File 1, the data is still accessible until closing and reopening File 2 without File 1 open.

      Expected result

      Data from Table A2 should be accessible by File 2 regardless of whether File 1 is open or not.

      Actual result

      Data from Table A2 is only accessible if File 1 is opened prior to its access by File 2.

        • 1. Re: Self Join on External TO
          philmodjunk

               If File 1 is closed, the data in Table A should also not be accessible, let alone A2. But references to Table A should automatically open the file in the background.

          • 2. Re: Self Join on External TO
            jayg

                 When I open File 2, I am able to make calculations based on references to Table A.  But the calculations on Table A2 don't work unless File 1 is explicitly open.  If I then close File 1, I am still able to make calculations based on Table A2 but those calculations fail again if I close and reopen File 2 without opening File 1.

                 I reference Table A from File 2 using an IP in the file path.  Maybe that's the issue...

            • 3. Re: Self Join on External TO
              philmodjunk

                   You cannot access data from another table unless it is open. But as I stated before, FileMaker will quitely open the referenced file for you as a hidden file. You won't see it listed in the Windows menu unless you open the Show sub menu.

                   But I cannot reproduce this in Windows 7--perhaps this is a Mac only issue.

                   What I did.

                   I defined the same two files, two tables and three table occurrences that you described in your original post. I then defined two calculations that used the sum function to compute a total from the related records. One field computes the total of the fields in Table Occurrence A and the other from the same field in Table Occurrence A 2.

                   I put these fields on a layout that was blank of all other layouts.

                   While still in layout mode, I selected the file for these tables and closed it.

                   I then saved changes and entered Browse mode.

                   Both calculation fields displayed the same correct total and I found that the second file had been automatically re-opened as a hidden file.

                   Could it be that your two table occurrences use different data source references to the same file? If your file is on a different location on the network or hosted, it's possible to lose that data source connection in a number of different ways--such as clicking cancel when the dialog to enter a password opens when FIleMaker first attempts to open that file. If you have two data source references to the file, one might be so "interrupted" and the other functional. Then you'll get the file open as a hidden file, but references via the "interrupted" data source reference will not return any data.

              • 4. Re: Self Join on External TO
                jayg

                     I just created the same  simplified scenario.  Oddly, it works correctly when the files are local but when uploaded to Filemaker Server, I am only able to calculate data from the first TO.  In this simple scenario, I'm trying to auto enter data calculated from A2 into B.  Locally, this is fine.  When served, I can only calculate from Table A.

                • 5. Re: Self Join on External TO
                  philmodjunk

                       To repeat a question: are you using separate data source references for each table occurrence?

                  • 6. Re: Self Join on External TO
                    jayg

                         Tables A and A2 are from the same externally referenced data source.

                    • 7. Re: Self Join on External TO
                      philmodjunk

                           _g is a typical naming convention for fields with global storage and they behave by different rules when the file is hosted over a network. Is it a global field? If so, have you checked to be sure that _g_Active has the value you expect it to have? If it's empty or has the wrong value, that would explain the behavior you are reporting.

                      • 8. Re: Self Join on External TO
                        jayg

                             Ahh, Yes!  Changing the global to an unstored calculation cleared it all up.  

                             Thank You!

                        • 9. Re: Self Join on External TO
                          philmodjunk

                               For more on how global fields function in hosted database systems: http://help.filemaker.com/app/answers/detail/a_id/3604/kw/global