I have a student records solution into which I regularly want to import attendance data that's coming from Excel files.  Each day's attendance is stored in 7 separate Excel files.  The user populates the files, then runs a script to import and process them.   When I was developing the solution on a stand alone machine I set up a table that contains the path and filenames of the 7 files that the script loops through.  If I ever had to move to different paths or decided to change file names, I could just change the paths and names in the table.

Fast forward to trying to implement this solution via FMServer (...FMServer newbie alert!).  I figure I can just put those files....hmmm...where?  On the server in the same directory as the solution?  Problem is I don't want the users who populate the Excel files having access to that directory.  So I decided to put them on a DropBox folder that's shared between the server and the users who will be populating the Excel files.  But now I don't know what path to use to reference the files from the solution.

Specifically, the .fmp12 file is in the root directory of the server as in <servername>/Databases/mysolution.fmp12.  The files I need to access are physically located on a different machine than the server under it's DropBox folder as in <othermachine>/DropBox/DataSupport to which I have created a symlink on the server that makes that folder look like <servername>/DataSupport.  How do I reference that location from the solution?  Is it even possible to do so?  If so, that would seem like a security risk.

So then it occurred to me that these files shouldn't reside on the server, but rather on the clients. But then how do I reference the locations on the clients?  The only client paths that I know how to identify reliably are from Get(DocumentsPath), Get(DesktopPath), Get(DesktopPath) and Get(PreferencesPath). Should I set up a preferences file of some sort that documents the necessary paths on the client machine(s)?

I suspect someone is going to say that I should put the files in the container directory on the server and access them through containers in the solution.  Can I set up the container directory to be a symlink to a Dropbox share?  Is that the preferred solution for this kind of situation?

Thanks for your thoughts and advice,

How about letting the users put the files wherever they want? Give them the option to pick the file in the import step.

Thanks David.  This is a process that gets repeated a lot - every day, 7 files per day.  Prompting for all of those files gets really tedious and the process is very repetitive so I want to avoid it.  But your suggestion reminded me that I also have a user preferences table.  I could store user specific paths in that.  User can set that path...I think I'll try that.

I don't understand the situation you describe:

Each day's attendance is stored in 7 separate Excel files.  The user populates the files, then runs a script to import and process them.

Why is this roundabout trip necessary? Why not enter the data directly into the Filemaker solution?

And where do these 7 Excel files come from to begin with? You ask where to put them - but in order for the user to populate them, they must already exist somewhere. This is quite confusing.

 

Sorry for the confusion - I was trying to be concise.  The Excel files are exported from the web site that hosts the system through which our teachers take attendance.  I have a script that automates the export of these files into their default file names.  I set up my browser to always save the files in a particular directory - that's the directory that, nominally, I am trying to access from the solution.

Still not clear, I am afraid:

The Excel files are exported from the web site that hosts the system through which our teachers take attendance.

If they take attendance through the web site, what is there for them to populate in these files? IOW, what is the purpose of this complicated maneuver?

 

I have a script that automates the export of these files into their default file names.

A Filemaker script? How exactly does that work?

 

I set up my browser to always save the files in a particular directory - that's the directory that, nominally, I am trying to access from the solution.

I don't think you will have the same control over your users' browsers. My impression is that you're attacking this from the wrong angle. Doesn't your site offer other options to get the data?

Edited October 6, 201510 yr by comment

I've seen this sort of convoluted setup in educational environments before...

I would surmise the educational institution has been provided with a web-based solution that they have little control over - the teachers enter data via the website, and the website generates preset export files for further use / analysis.

If so, my general solution has been to have a Scheduled Task (Windows) or cron job (Mac) that grabs the files (via ftp or DropBox) and copies them to the FileMaker Server documents directory, for a Server Schedule to then import and process at a later time.

 

There must be a database driving the website, no chance to hook into into directly (through ESS) or indirectly (through 'import from ODBC source')?  That's where I would focus on.