May 9, 2017 Blog FileMaker FileMaker Techniques Claris 16Claris FileMaker 16filemaker 16 FileMaker 16: External Data Sources One of the new features in FileMaker 16 is the ability to set an external data source dynamically by using a variable. So, what does that do? How does it work? What does that mean for FileMaker development? What is an external data source? It provides the ability to have multiple customers share a user interface file but each have their own data files. In previous versions of FileMaker, external data sources were fixed. Each user had to have their own set of files, or record level security had to be used to block access to other user’s records in one data file. Record level security can really bog down a system, so separate data files have serious performance benefits. However, if each customer has their own file and there are thirty customers, a small UI change meant that you would have to replace thirty different files. In FileMaker 16, we can have as single UI file that all users login into. The file looks at who is logging in, dynamically sets the data file name in a variable, and makes sure they only connect to their data. A small UI update can be achieved by replacing a single file not the server. How it Works The UI file will not attempt to open the data file until it “needs” it, whether it is because there is a reference to it in a script that is called, or a field from the data source on a layout. Once that happens, the UI file will “resolve” the relationship and connect. If this is defined as a variable, the variable needs to be defined before the external data source can be accessed. This means the variable will most likely be defined in a startup script which the purposely accesses the data file to make sure that relationship is resolved correctly. That would be scripted something like this: If [ Get ( AccountPrivilegeSetName ) = “Customer1” ] Set Variable [ $DataFile ; Value: “file:Customer1_Data.fmp12” ] Else If [ Get ( Account PrivilegeSetName ) = “Customer2” ] Set Variable [ $DataFile ; Value: “file:Customer1_Data.fmp12” ] End if Go to Layout [ “Data” (Data) ; Animation: None ] The last line of of the script above goes to a layout based on the data source, forcing the relationship to be resolved while the script is still running and the variable still exists. Alternatively, a global variable could be used. The scope would extend outside of the script, and then the open script wouldn’t need to force the relationship to be resolved right away. Issues to Consider While this new feature has remarkable potential to simplify UI development on vertical market solutions, there are some issues that will have to be addressed. All of the ones that come to mind revolve around security. A way will have to be developed to handle the accounts between the UI and Data file. Do you have all of the accounts in the UI file and then only the applicable accounts in each data file? Or do you have an auto-login in the UI file and some other process for logging into the right data file with the right account? Another issue is the “Re-Login” script step. Once the connection is made to the data file, it will be fixed until both the UI and Data file are closed and reopened. This means if a user uses re-login to log in as a user who does not have access to the file they are going to remain connected to a file they no longer have access to. Also, what if a user needs access to all of the data files? Letting them pick on login might be an option, but having to close the whole system and start it back up to switch data could become cumbersome for someone managing several customers on a vertical market solution.. Join the Conversation What potential do you see for this feature? Might it have other benefits? How about other drawbacks? What solutions do you see for dealing with these drawbacks? By Thomas Meglasson
11 Comments Andy Posted on 2:28 AM - October 11, 2017 I am attempting to use a variable (global) for the file path of an external data source for the first time. I have tried everything I can think of to get the file to change data file but my UI file constantly links to the original file. The global variable changes as expected but the original file opens and I can’t understand why. Any ideas why this is happening? Thomas Meglasson Posted on 8:27 AM - October 11, 2017 In the File Options, do you have the “Switch to layout” box checked? And do you have a layout specified that does not use a table occurrence group with any references to the external data file? The variable must be set before the relationship between the UI and data files is resolved. If the system opens to a layout that is in a table occurrence group that references the external data source the relationship will be resolved at that point, even before any script set to the on first window open trigger. When I use this feature I create a globals table in my UI file, a table occurrence of that globals table with nothing attached to it, and a “Loading…” screen based on that table occurrence. I set “Switch to layout” to on and specify that layout. quarku Posted on 3:31 PM - October 12, 2017 Guys is there any chance to change the file without re-login or restart the file? I get the idea to use initial layout without any table occurance connection to external data but still it is require a re-login 🙂 Thomas Meglasson Posted on 3:00 PM - October 16, 2017 There doesn’t appear to be any way to change which file an external data source is pointing at without closing both the UI and data files. Furthermore, once the path to an external data file is resolved and the data file is open, there doesn’t appear to be any way to close the data file and open another one as long as the UI file has any table occurrences from the data file. The “close file” command only seems to hide other open files. The only work around we have found is to make a third file with no table occurrences at all from the UI or data files. The third file can then close the UI and data files, and reopen them. At that point the external data source path between the UI and data files will be reset, which the documentation calls “resolved.” Jason Tallman Posted on 9:24 AM - March 17, 2018 I love the use of this for vertical solutions. But doesn’t this go against Filemakers new guidelines regarding having multiple companies data files on the same instance of Filemaker Server? David Weiner Posted on 3:41 PM - March 20, 2018 Actually, this use case is for a vertical solution, which is specifically permitted by FileMaker’s SBA (Solution Bundle Agreement) licensing. In other words, you are allowed to have multiple customer data files if you have an SBA licensed server. Another use case that wouldn’t require any special licensing is if you have one interface file connecting dynamically to data files for the same customer. AppWorks has a customer using just this model, where they have two offices that share the interface, but not data. Sam Posted on 12:52 AM - May 15, 2019 Thank you for your article. I have 1 question. Is that possible to create relationship between UI file and the data source file. That means i create 1 relationship for all external data sources by changing the value of global variable? David Weiner Posted on 11:54 AM - May 16, 2019 Yes, it’s possible to do that. As mentioned though, you cannot just change the global variable and have the data source change immediately. You need to close both the data file and UI file completely in order to change the data source. The solution seems to be having a third file that has no relationship to the data file at all, where you run the script to set the global variable. Sam Posted on 1:30 AM - May 17, 2019 Thank you. If i am going to use webdirect(control file), webdirect(UI file), A,B,C….datasource file. Is that possible? David Weiner Posted on 12:57 PM - May 17, 2019 Actually, I’m not sure if it would be possible with Webdirect, since you need to be able to close files via script step, and the ‘Close File’ script step is not supported on Webdirect. You can certainly connect to a variable data source on Webdirect, though, so there may be a way to get it to work. Maybe some clever use of the ‘Select Window’ step? Phil Posted on 4:52 PM - July 13, 2020 @Sam – yes it works on WebDirect for me – finally! Note that you can’t just use ?$var= on it’s own in your url, you must also call a script that it is in like this ?script=&$var (can also use ?script=¶m). That also means you can’t run a triggered ‘on open’ script in file options because that somehow kills your $var too. @Thomas thank you for this helpful article. After hitting my head against a brick wall for what seemed an eternity, this line made all the difference.. “The last line of of the script above goes to a layout based on the data source, forcing the relationship to be resolved while the script is still running and the variable still exists.” I had thought that just setting the variable $DataSource was enough, but I then called another script to go to a layout that would use the data source, so my $var lost it’s scope when I called the new script, so no data source. This is a great improvement for FMP16 but can be quite tricky as the documentation is limited.