Export records in an Excel file on the FMP server HD

[totoleheros]

Hi,

I have a FMP server 15 running on a iMac which has very restricted access to the web. But it can have access to a Google Drive. Now I have a database where I store information about the staff of my company sorted by department. And finally, I have a server running the website of the company where I'd like to have the most updated staff listed on each department webpages.

I'd like to have a script that would output the staff members on files stored on my Google Drive where I can ask the website to parse the listing. 

My issue: how can I export the staff members on the server hard drive? Ideally, can I ask a script to export directly on the Google Drive folder of my FMPS iMac? Alternatively, the script could export on the 'FileMaker Server/Data/Documents' folder, and I'll find a way to automatically transfer the exported file in the Google Drive Folder. So, how can I ask a script to export my records in this 'FileMaker Server/Data/Documents' folder?

You may have a more straightforward solution: I'll take it!

Many thanks in advance!!

AN

[Ocean West]

You may have more power with using a plugin (local and on server ) to target other folder paths - take a look at BaseElements Plugin.

[totoleheros]

@Ocean West: Thank you for your suggestion. I have installed the BaseElements Plugin but I must admit that I have hard time to understand how to use it within my script. Which function would you suggest to use to output my export file in the Google Drive folder. I have given the write/read permission to fmserver to the Google Drive and the target folder.

Here is the script that I have tested but I have a dialog box appearing on the client side asking to select the target folder (on the client hard drive of course) (FMP function names freely translated from French, sorry if these are not the correct one):

Define variable [$path; Value: BE_SelectFolder ("filemac:/SSD/Users/XXX/Google Drive/GoogleDriveFMP/Team1.xlsx")]

Export records [Dialog box: No; "$path" ; Unicode (UTF-16)]

The BE_SelectFolder might not be the right function to use in my case...

Thank you for your help!

[Ocean West]

Select folder just prompts you to choose a folder useful to return the path needed but would not work on server since it spawns a dialog.

try making $path the hard coded string.

 

[totoleheros]

What do you mean by "making $path the hard coded string"? In my case, $path="filemac:/SSD/Users/Username/Google Drive/GoogleDriveFMP/Team1.xlsx". But the export function doesn't work in FMP server. And then what's the use of BaseElements Plugin ? Sorry if these are silly questions.

[Ocean West]

No questions are silly

you are correct exporting excel is not a server supported function.

You could generate the excel file (client side) into a container field and a using BE you can export the container field out to a path. (been a while since i tried)

BE_ExportFieldContents ( field ; outputPath )

there are other options perhaps building a CSV into a text field - by looping thru found records or using SQL statement to build the data needed. Then exporting the content with filename hard setting the .xlsx file extension or .csv

Other options would be writing to a text file with ScriptMaster plugin

here is a caveat about permissions and FMS directories.

https://baseelementsplugin.zendesk.com/hc/en-us/articles/203885818-File-Permissions-when-running-under-FMS

[totoleheros]

I had the same idea as yours to use a container field but fall short there because I do not know how to generate an Excel file that would be inserted in the container field. Any tip to share here?

Again Thank you very much!

[totoleheros]

OK, I have been able to create a script to store the export xlsx file into a global container field. Now, can someone tell me how to use the Base Elements plugin to export the content of this container field into a folder located on the hard drive of the FMP Server (in my Google Drive folder). This is the last step that I am struggling to pass. Thank you if you have a clue (I find difficult to understand how to use the function of this plugin...).

[IdealData]

As Ocean West said

BE_ExportFieldContents ( field ; outputPath )

The "outputPath" is actually a local operating system file path. i.e.. NOT "filemac:/SSD/Users/Username/Google Drive/GoogleDriveFMP/Team1.xlsx" but instead

"/SSD/Users/Username/Google Drive/GoogleDriveFMP/Team1.xlsx"

If you try to use this as a SERVER plugin then the above path won't work due to FMServer path restrictions and you must confine yourself to the available Documents and Temporary folders. You can get the absolute path to those folders by:

1. Navigating in the MacOS Finder and then dragging the folder to the Terminal application.
2. Use the "pwd" command to display the path.

Apologies if I have mixed up your path information but at least this is in the same perspective as your machine.

[totoleheros]

@IdealData: Thank you. Then, if I get it correctly, I cannot have a script running on the server, that would export my file in a folder outside the Document or Temporary folders. This means that the Base Elements Plugin cannot help and I have to find a way to automatically transfer my files from the Documents or Temporary FMS folders to my Google Drive folder. I got it right?

[IdealData]

That's right.

Have you tried installing Google Drive inside the server's Documents folder?

Alternatively, consider the following Base Elements help:

Quote

Writing outside the FMS folder

You will need to add the fmserver user to any folder that you want to write to.  But also you will need access to READ from any of the parent folders.  For example if you want to create a file inside this path :

/Base/Docs/March/report.xls

Then the fmserver user needs read access to /Base and /Base/Docs and write access to /Base/Docs/March

This presumes that the "fmserver" user is the user account that FMServer is running under.

For all Base Elements help please refer to https://baseelementsplugin.zendesk.com/hc/en-us

The help is not aimed at beginners, and every word counts.

[totoleheros]

Well I give up with this solution, there is no simple way to do what I want. I’ll stick to a manual solution ... thank you everybody for your kind help.

[Agnes Riley]

Silly question, have you converted the path to BE path? There's a function for that if I remember correctly. @IdealData recommended the path change, but you haven't confirmed you did it.

Edited February 21, 2018 by Agnes Riley

[totoleheros]

@Agnes Riley I did install my Google Drive inside the servers’ Document folder. This allowed to have my export file stored there. Then, I faced a new issue: each new export was named with a new number and the previous was deleted: Team_1.xlsx, Team_2.xlsx, etc. This means that that I should get a different name each time for parsing for my website. Far too complicated for me and too much developing time as compared to do it manually. That’s the reason why I gave up. Sorry that I did not explain this in my previous post.

Again, thank you for your help!

[Agnes Riley]

With BE and MBS you can rename files and do all kinds of manipulation. It might take awhile to perfect things but they will work. Last I built a process when the Excel file was basically reconstructed from FileMaker and then FTPd to a server. It's crazy cool. So I don't recommend you give up. Get some help, automate what you need. :-)

[totoleheros]

@Agnes RileyThank you for your kind encouragements but I am running out of time and competence for this. I thought that it would take me one hour or two to have it done. I have spent almost 2 days to finish with a solution that requires my manual intervention. Plus, the updates won’t be so frequent in the staff listing and it is therefore not worth the effort. Best regards.