Import Excel file to multiple tables

[lenserman]

I have an Excel file used to communicate production information with a factory.  It contains information from 5 different tables in my FM solution and is updated weekly with an UPDATE FILE(xlsx).  I can export easy enough, but I am uncertain how to best import the updated data.  

 

I have a script that will import/update via multiple import scriptsteps into the appropriate tables in a single ImportScript.  Problem is FM asks to relocate the file each time a new import scriptstep calls for the Excel file.  

 

The name or location of the UPDATE FILE may change each week and I'm concerned users will get confused and make mistakes.

 

Is there a way to get Filemaker(12) to remember the Excel filename and/or filepath it is importing in the first Import scriptstep and then apply that name or path as a variable to subsequent scriptsteps in the same Single ImportScript?

 

Hope that makes sense, Any help is greatly appreciated.

[Wim Decorte]

FM won't remember from the first important but each import can work with the same file path.  Instead of asking the user for the file at the time of the first import, use the Insert File script step to ask the user to locate the file, insert it into a container field and script an "export field contents" of that container field to a location and file name that know (in a variable).  Then for each import point the import script step to that variable.

[lenserman]

Wow.  Sounds like a cool solution.  I'll give it a shot and report back win or lose.  Thanks for the suggestion.

[lenserman]

Thanks for the help, Wim.  I got a glitch though.  It took a while to write the script and it works on my desktop, but I have discovered it will not work in a Server setup.  

 

The Compatibility for both Insert File scriptstep and Export Field Contents scriptstep are not Server compatible.  Sorry I should have mentioned this at the beginning.

 

Anyone have another suggestion how to accomplish the task of importing an Excel file into multiple tables as described at the top that would also work in a Server setup? 

[Wim Decorte]

If it is server-side then you know the location and the file name of the source, right?  Then you shouldn't have this problem... so go back to the beginning: is the file always named the same and always in the same location?

[lenserman]

I must apologize that this is the first Server DB I've ever created, so please forgive my lack of knowledge.  I don't have certainty which of the users will be importing a file and each week the file name would most likely be different or different names from different factories.  Of course the format of the Excel file will be consistent for field mapping.  But, I won't have control who imports the file or the name unless I make a user requirement.

 

I've been thinking that I will make a user rule and tell the users that whomever imports a file, must put it on their desktop and give it the same name for each time they perform an import (example; ImportFileName.xslx).  Then I would point the DB to their desktop via "file:" & Get (DesktopPath) & "ImportFileName.xlsx" for each import scriptstep.  In a server setup, I don't think I have to do more than that to access whichever user's desktop is accessing the file, Or Do I?

 

Alternatively, I guess I could create a Field for users to paste the name of their file into which could be captured in a $$ImportFileName Variable and then passed to the scriptsteps with  "file:" & Get (DesktopPath) & $$ImportFileName.  

 

If you have any comments on these approaches or other suggestions they will be greatly appreciated.  Thanks for your help. I'm a bit over my head on this project, particularly in regards to making certain things will function via a server setup.

[Wim Decorte]

I think you are confused

 

From what you describe it is the users that will do the import from something on their machine.   Not the server in a server-side script.  The fact that the file is hosted on a server does not make the script a server-side script so the server compatibility does not come into play.  Server-side scripts are called by special Server Schedules that you set up and those have no user interaction whatsoever.

 

So the fact that it worked when you had the FM file on your machine and not when the file is on the server has nothing to do with the "insert file" not being server compatible.  The whole routine should just work just like it did for you.

It is not the server that will access the user's desktop, it is the user's FileMaker Pro that will interact with the user's machine.

 

With the file hosted, step through your original script with the script debugger on and see where it fails and what error it returns.

[lenserman]

Wim,  This is a very big lesson you have given me here.  Thank you.  I am just about to put this up on a Server to begin testing and to date haven't done so.  Your clarification comes like a bolt of lightning realization.  I did not really understand the Server/Client relationship fully and may not until I get to see how things function and see if I have any issues. 

 

Again, Thank you so much for your generous participation in this forum.  You're a champion!