Resolving A Remote Relative Path.

[jdu98a]

I have a very basic FM deployment with a database hosted by a PC and 1 or 2 other PCs accessing that database via FM sharing (open remote). Within the folder containing the database on the host computer is an excel file that frequently gets updated and needs to be imported into the database. I need this to be accomplished from any of the PCs accessing the database (whether as the host or remote).

When on the host PC a simple relative path can be used, File:ImportData.xlsx. However, when tried from one of the remote PCs it says the Excel file cannot be found. I found this on Filemaker.com concerning creating relative filepaths - "Relative Filepath - The path to a target file specified from the current database’s location. If the current database is opened remotely, the path starts from the local FileMaker Pro directory" This suggests to me that what I'm attempting can be done, but it is not clear exactly what that 'remote relative path' would look like. Can someone clarify this for me? Many thanks.

[IdealData]

In order for the remote PCs to access the Excel file they must first be able to "see" it on your network - therefore you must share the folder containing the file. But before you do...

Don't share the hosting FileMaker folder as this would give direct access to the FM file and possibly lead to corruption of the database if they opened from the network share. You would be better moving the Excel file to another folder and share that folder to the network - now the remote users must continue to use "host" access to get to the FM file.

The importing of the data from the Excel file will then need to be controlled by 2 different file paths, but they do not need to be relative, and it might be easier to setup using absolute paths.

Assuming you have a folder in C:\SharedFolder and this would contain your Excel file the the host setup would be

"filewin:/C:/SharedFolder/Excel.xlsx"

The remote users should setup a MAPPED NETWORK DRIVE, say F: which maps to the "SharedFolder" and the path would then be "filewin:/F:/Excel.xlsx"

In this way all you then need to do in your script is detect whether you are a host or a client (see Get(MultiUserState) function) and build you file path accordingly, either

"filewin:/C:/SharedFolder/Excel.xlsx" (as host)

"filewin:/F:/Excel.xlsx" (as client)

BEWARE: the remote users must have the mapped drive already connected before running the import script and all remote users must use the same drive mapping letter.

[jdu98a]

Thanks for the help. I got everything working. However, I made some modifications to the technique you suggested to make things a little more flexible and dynamic. First of all, for the host filepath I kept a relative path - file:Shared Folder/Excel.xlsx. This way, as long as that folder is kept with the database file I can be free to move the host file around and nothing will break.

In order to keep similar flexibility with the clients I used this type of absolute path for them - "filewin://" & Get (HostIpAddress) & "/Shared Folder/Excel.xlsx". By doing it this way, while I will still have to remap network drives if I ever move the database file, I will not have to change the coding within the database itself. This calculation will create the correct filepath regardless of what computer the file is on.

Thanks again for steering me int he right direction.

[IdealData]

Glad you got it working, but you missed my first caveat - DO NOT SHARE the folder containing the FM files, it is all too easy for a remote user to open them and then your file will break.

You can still achieve a relative addressing method by holding the file paths in a simple table. At start up you can populate a $$variable for the host or client. Now if you move any of the structure you can simple re-edit the table holding the file paths.

If you use the shared folder technique I outlined then you can extend this to a simple form of networking for other applications.

[jdu98a]

DO NOT SHARE the folder containing the FM files

No, no, I got that part. The folder that is shared does no contain the FM files themselves. The file system looks like this:

C:/Database Folder/Database.fp7

C:/Database Folder/Shared Folder/Excel.xlsx

It is not possible to open the database file as a shared file. So all is well.