fmp17 FMServer: How to keep an external file name for repeated imports

[BAleiHi]

Hi,

My solution opens an Excel file and loops through 3 tabs in it, importing and processing the contents of each.  What I really need to do is get the user to specify an Excel file (preferably by opening a script generated default directory and filtering to only Excel files...something I can't seem to get to happen) then, in a loop, import data from 3 separate tabs in the file (tab names are fixed but import mapping is different for the different tabs) and process that data.

When I'm running locally I can get the input file name by using Insert File into a container to hold on to the file name and can set up the imports specifying the filename, tab, and import order for each import step.  This seems like a really clumsy way to get a filename that can be accessed multiple times without repeated user input, but it seemed to be the only approach back when I wrote .V1 of this script (a couple years back).  But it worked for a local database.  Now I'm trying to run from FM Server where Insert File is not supported.  So how do I do this?  I'm running on Mac and Windows so any solution that involves plugins needs to work for either (seamlessly).

Conceptually, this seems to be very simple ... get a filename and access it multiple times.  But I'm tearing my hair out trying to make this work.  Thoughts or guidance?  Thanks

[mr_vodka]

18 hours ago, BAleiHi said:

Hi,

What I really need to do is get the user to specify an Excel file (preferably by opening a script generated default directory and filtering to only Excel files...something I can't seem to get to happen

You can use a plugin such as Troi to do this or wait for the next version of FMP, 18 as they've added file based script steps.

 

18 hours ago, BAleiHi said:

Now I'm trying to run from FM Server where Insert File is not supported.  So how do I do this?  I'm running on Mac and Windows so any solution that involves plugins needs to work for either (seamlessly).

Conceptually, this seems to be very simple ... get a filename and access it multiple times.  But I'm tearing my hair out trying to make this work.  Thoughts or guidance?  Thanks

 

Earlier you stated that you want the user to specify an Excel file.  Then later you said you want to run it on FM Server.  Can you clarify what you mean?  If you are running a script server side, you can use import but it has to be from only specific directories such as the FMS documents folder or the temp folder. 

 

 

 

[BAleiHi]

Thank you, Mr. V, for the Troi tip - I'll look into that.  Re: your second point, I apologize for inexact language - as a newcomer to server based solutions, I'm sure I'm confused in ways I don't even know about.   Please allow me to try again.  My solution is hosted on FMServer, but is opened by one of several machines running their own copy of FM17 Advanced.  When I say I want to "run it on the server" I mean only that I'm running the server hosted version of the file from one of those machines.  All those machines are networked and have access to a common directory that contains the Excel file that I want to import.

I *also* have a copy of the solution on my local machine where I do most of the development, only uploading it to the server when the development version is stable.  When I run the solution locally, the "Insert File" thing works fine...it's when I'm trying to run the hosted file that causes the problem since "Insert File" does not work.  If I don't specify the filename in the import script step, the user can do it at run time, but has to do it three times.  The only workaround I can think of is to hardcode the filename and pull the full path from a user profile table that is unique to each machine which feels very clumsy.

[mr_vodka]

11 hours ago, BAleiHi said:

When I run the solution locally, the "Insert File" thing works fine...it's when I'm trying to run the hosted file that causes the problem since "Insert File" does not work.

I'm not sure what you mean here. You should be able to to insert the file into your containers field in a hosted solution.

 

11 hours ago, BAleiHi said:

All those machines are networked and have access to a common directory that contains the Excel file that I want to import.

If you have a common network shared location, then you should be able to capture the file name and path since you can use the UNC file path.

 

One more thing you can look at is using a global container field which you can then store the path in a text field.

[BAleiHi]

Thank you again Mr. V.  Turns out my analysis of the situation was flawed in several respects.  Your comments helped me try some other things and I realized that when I use InsertFile to get a filename and store it into a variable instead of into a container field, everything seems to work just fine - whether I'm in the hosted file or in a local copy of it.  So I think I'm off the hook.  I appreciate your help!