From the second version of my first database (back in 2002) I have used my FileMaker databases to control the creation of a structured folder system to enable the efficient and uniform storing of structured data for each of the businesses I have worked with.
Early on in my development of FileMaker systems I stumbled across the fantastic plugins offered by Troi Automatisering, in particular their File Plugin, now at version 5, which I will be creating this custom function to use.
This plugin enables me to create, with relative ease, a series of fields and scripts that allowed the automatic creation and opening of folders and files straight from the database. Relatively simple if you’re running FileMaker in a closed network, but it starts to get more complicated very quickly.
I’ve had the good fortune to design almost exclusively for Mac only networks (phew) although I have had to deal with mixed environments where my customers are still in transition from PCs to Macs so I’ve had to ensure that any system is compatible with both.
The Preparation
Determining the Windows System Version:
Since the route to the Documents folder hasn’t ever changed in Mac OS X I don’t need to worry about the version used, however, this is not the case in Windows where there is a different file path to the “My Documents” folder on Windows XP and the new versions Vista and Windows 7.
So I need to use the Get ( SystemVersion ) Function to determine whether or not the user is using Windows XP or a newer version. According to the FileMaker help the function returns the following information for Windows:
5.1 for Windows XP SP2
6.0 for Windows Vista
So I needed a test for which version of Windows is running. I did attempt to design my own custom function to determine this, but perhaps because I was unwell I wasted a good deal of time messing around with this before I came to my senses and looked at Matt’s github page.
His os.versionName custom function (which I’ve renamed as os.versions) is a quick and easy solution to this problem, which can go well beyond my needs, but will achieve what I want by returning the name of the Mac or Windows OS running on the users system.
It returns a simple easy to interpret name from the Get ( SystemVersion ) so the 5.1 response from the function is returned as “Windows XP” or 6.0 is returned as “Windows Vista”.
Dealing with Windows Folder Path Separators:
Windows file paths use the “\” separator. For some reason you can’t easily use these in FileMaker calculations (if someone knows why please let me know in the comments). So I’ve taken the easy step of creation of new global variable $$windowsnetworksymbol to contain the “\” separator.
n.b. To ensure this variable is set each time the FileMaker file is launched I’ve added it to a script called “Set Global Variables”, which I run as part of my start-up script.
Dealing with Mac Folder Path Separators
Mac file paths used by the plugin are formatted with the “:” separator. The only other thing you need to worry about is that a file path starts without any separator.
It is the product of this function that we have been working towards editing. The basic idea is to be able to call the Get ( DocumentsPath ) Function and then edit it’s product to point instead to the location of the Dropbox folder (assuming it is installed in the default location).
The Get ( DocumentsPath ) Function will return the following:
\C:\Documents and Settings\YourUserName\My Documents\ in Windows XP
\C:\Users\Your User Name\Documents\ in Vista or Windows 7
/MacintoshHD/Users/Your User Name/Documents/ in Mac OS X
And we know from my earlier blog post: Default @Dropbox File Paths that the default file paths for Dropbox are:
\C:\Documents and Settings\YourUserName\My Documents\My Dropbox\ in Windows XP
\C:\Users\YourUserName\Documents\Dropbox\ in Vista or Windows 7
/Macintosh HD/Users/YourUserName/Dropbox/ in Mac OS X
So now we have all the building blocks in place it’s time to construct our custom function:
The path.dropbox Function
The function itself is relatively simple once we have done the above preparation. It has no parameters and is made up of a simple Case Statement which asks two questions:
1. Is the user running a version of Windows XP?
2. Is the user running either Windows Vista or Windows 7?
If both these questions are negative we will assume that the use is running Mac OS X.
Once we have determined which OS the user is using we will then grab the documents file path using the Get ( DocumentsPath ) Function and then reformat it to be compatible with the Troi File Plugin.
Formatting for Windows
We use the Replace Function to make this assessment:
Replace ( Get ( DocumentsPath ); 1; 3; “” )
This will replace the “\C:” part of the resulting Get ( DocumentsPath ) with simply “”.
We encase this Replace Function within a Substitute Function so that we can substitute the “/” separator for the “\” separator contained within our $$windowsnetworksymbol and get a properly formatted Windows file path for the Troi Plugin. So we get:
Substitute ((Replace (Get(DocumentsPath); 1; 3; “”)); [“/”; $$windowsnetworksymbol])
With both Windows File Paths to the Dropbox we then simply need to append the correct location which we can easily do by adding:
& “My Dropbox” & $$windowsnetworksymbol in Windows XP
& “Dropbox” & $$windowsnetworksymbol in Vista or Windows 7
Formatting for Mac
We again use Replace Function to remove the leading “/” and as before encase this within a Substitute Function so that we can substitute the “/” separator for the “:” separator. So we get:
Substitute ((Replace (Get(DocumentsPath); 1; 1; “”)); [“/”; “:”]; [“Documents:”; “”])
As you’ll notice we have also added to the Substitute Function a statement to replace the “Documents:”, which will strip back the Mac File Path to the User so we can then append:
& “Dropbox” & “:”
which will get us to the default location for the Dropbox on the Mac.
The Final Function:
Case (
os.version = “Windows XP 64-Bit” or “Windows XP”; Substitute ((Replace (Get(DocumentsPath); 1; 3; “”)); [“/”; $$windowsnetworksymbol]) & “My Dropbox” & $$windowsnetworksymbol;
os.version = “Windows 7” or “Windows Vista”; Substitute ((Replace (Get(DocumentsPath); 1; 3; “”)); [“/”; $$windowsnetworksymbol]) & “Dropbox” & $$windowsnetworksymbol;
Substitute ((Replace (Get(DocumentsPath); 1; 1; “”)); [“/”; “:”]; [“Documents:”; “”]) & “Dropbox” & “:”)
Please note that I have not yet had the opportunity to test this on a Windows system, but I’ve followed paths that worked in the past. As soon as I have had the opportunity to test it I will post any corrections here.
I’m a small business consultant enabling small business owners to achieve sustainable growth, whilst working part-time at Tees Valley Arts. For more about me personally see peterneal.co.uk