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.
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...
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.
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.
To repeat a question: are you using separate data source references for each table occurrence?
Tables A and A2 are from the same externally referenced data source.
_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.
Ahh, Yes! Changing the global to an unstored calculation cleared it all up.