Trying to use a global variable for the file path in an external data source?
Getting the error “The file $$filePath could not be opened. (Not Found)“?
Then this article is for you!
In FileMaker 16 and above, we are able to use a variable for the path to an External Data Source (“EDS”), such as another FileMaker file. You might be using multiple data files in a situation where you’re using the separation model, for example. It can be difficult, however, to implement a variable path EDS without errors. For this article, we’ll assume a situation where you have one UI.fmp12 file and one Data.fmp12 file, where the latter contains all your solution’s primary data. We will be opening UI file manually and in turn want it to automatically open the Data file based on a variable EDS path.
The big hurdles here are:
- Opening the external Data file based on a global variable.
>> Problem: FileMaker will sometimes validate the script reference to the EDS before it actually runs the Startup script, so FileMaker does not yet know the value of your file path variable, which causes a very inconvenient error.
- Your Startup script (triggered from OnFirstWindowOpen in the File Options) needs to access data located in the external data file.
>> Problem: If the EDS is defined using a variable, even with the solution below, the startup script is going to complete before the EDS is opened and its data made available.
Okay, so how do we make sure the files are open before we reference the external data source or any fields in the Data file in our startup script?
Here are the steps:
- Define an external data source (EDS) using a global variable for the file path. We’ll call the EDS “data file” and the global variable name for the path will be “$$filepath”.
- Create a script called “open data” in our UI file with following lines:
Install OnTimer Script Note: it’s important to have the empty Install OnTimer Script here to stop it from repeating after it runs this script the first time.
Open File [Open hidden:Off; "data file"]
- The “startup” script in our UI file will have the following lines:
Set Variable [$$filepath; Value:"file:data"]
Install OnTimer Script ["open data"; Interval: 0.1]
- Remember to set the Startup script to run in: File >> File Options… >> Script Triggers >> OnFirstWindowOpen
- Make sure the startup layout does not contain references to the external data source! If the initial layout (on file startup) contains any fields or references to the external data source, you’ll get an error since FileMaker will attempt to automatically load the EDS data, but we have not yet set the $$filepath variable. I prefer to use a relatively simple, empty “Loading” layout for startup to avoid this issue: File >> File Options… >> Open [tab] >> Switch to layout…
That’s basically it. Those two scripts will open our Data file without error. Now, if we want to have additional Startup script steps that access any fields in the Data file, they’ll need to be in the “open data” script, or in a separate script called from that one. The reason is the Install OnTimer Script step in Startup will queue up the Open data script to run after the Startup script has completed, so FileMaker will not attempt to validate its references until after the Startup script has completed. That last part is the key. Because of that, the global variable containing the file path is set into memory prior to FileMaker attempting to verify the EDS path. If the Startup script were to contain any references to the EDS, or table occurrences in the EDS, it would give us that “file could not be opened” error.
Please comment below with any questions, comments, or improvements!