I want to schedule a weekly email on FMS to send out an Excel listing with all records from a table.
I've looked at the "save records", but this seems to be for the user's computer, not the server, but I could be mistaken.
This task is probably very easy to do in FileMaker.
Thanks,
Make a script that exports your records to an Excel file and emails it. Use the script in a weekly server schedule. Note that server imposes limitations on both script steps. Look up FileMaker's help documentation for more information.
I scheduled this script in FMS:
Go to Layout [ “TEST_LAYOUT” (TEST_LAYOUT) ; Animation: None ]]
Show All Records
Export Records [ With dialog: Off ; Create folders: Off ; “TEST.xlsx” ; Unicode (UTF-16) ]
The FMS scheduler after running the scheduled script says "Succeeded", but there is no file on the server created anywhere.
Running the same script from a local FMP client creates the file in the user directory.
Any ideas why FMS isn't creating the file on the server?
Thanks,
The help documentation mentions there are limitations when exporting via server. Location where the file is saved is one of these limitations. Where are you attempting to save the file?
I assumed, just using the filename would make the file be stored in an appropriate place (like the FMS "Documents" folder). Not sure how I could get "Successful", but no file anywhere by that name created on the AWS server.
This finally worked:
filewin:/C:\Program Files\FileMaker\FileMaker Server\Data\Documents\Test.xlsx
Thanks for your help and patience. 
I suggest you use Get (TemporaryPath), Get (DocumentsPath) and other similar functions to build your file path. This will make your code more robust.
That sounds like a great idea, but I thought the documentation said that those GET functions didn't work on FMS in this context.
From the FMP Help for Server paths: Get functions that return information about other folders are not supported.
Am I reading that correctly? If I go to the link, Get(DocumentsPath) is one of those GET functions that supposedly are not supported in this context.
Some Get (xxxPath) work on server. Each function documentation mentions compatibility and differences between clients.
From the Get (DocumentsPath) documentation:
When running on server-side scripts, Get(DocumentsPath) returns the location of the Documents folder. The Documents folder is used as a shared location that scripts from different sessions or other processes on the machine can use to import or export files.
From the Get (TemporaryPath) documentation:
The temporary folder name begins with S, followed by a number representing the session of the database engine during which the operation took place. Because your operating system controls the location of temporary files, the exact path returned may be different from the examples shown. The actual path returned also depends on which FileMaker client is executing the function.
In FileMaker Pro, the temporary folder and any files placed in it are deleted when FileMaker Pro is exited or quit. In server-side scripts, each script runs in its own session; once the script is completed, the session terminates and the temporary folder is deleted.
Just as clarification on this part:
If we consider two things that we are likely to care about:
FMS was able to successfully authenticate into the file, find the specified script, and invoke it.
When the script executes, does every step execute without an error, do one or more steps encounter an error condition?
Then:
When the FMS Admin Console tells you that the schedule was run with "Success", it is only addressing the first point, above, i.e., it is indicating that FMS was able to find the target file, authenticate into it, find the named script, and then invoke the script. An example of a "not successful" outcome when running the schedule would happen if the script could not be found (perhaps it was deleted), or perhaps the credentials that the schedule had been supplied did not work to authenticate into the file.
The "Success" outcome that shows in the Admin Console when running the schedule does not reflect anything about whether the individual steps in the script were executed with or without encountering an error condition.
To find out whether the individual steps in a script encountered error condition(s), two common methodologies are:
Get( LastError ) function after script steps that are deemed worthy of error trapping.It's not at all unreasonable to start with a belief that the "Successful" outcome registered in the Admin Console might imply zero errors encountered by the script steps in the scheduled script, but, as mentioned above, it actually has a different meaning.
Hopefully the above breakdown helps a bit.
You're amazing, Steve! Great info as always. Thanks so much!!!
Trying to get this syntax right.
This does not work in the output of the Export Records (no file created anywhere on server):
file:Get(DocumentsPath) & "scriptOutputs/Test.xlsx"
What is the correct syntax?
Thanks,
This is another good example of something that seems like it should work, when in fact, it's done slightly differently.
The key to know is that the dialog where one specifies a path for export is not a calculation dialog, meaning it does not support the use of calculations, functions, etc..
The three things that I know of that are supported by this type of "file path" dialog are:
Note: Multiple entries comprised of the above can be included, via separating the entries with a newline char.
A common pattern, therefore, is to have a Set Variable script step just prior to the Export Records script step. Use the Set Variable script step to set the desired path into a variable, and then reference that variable in the path dialog of the Export Records script step.
So something like:
Followed by:
One thing that I have really grown to like about this extra step is that it makes for easier debugging when stepping through the script, because I can watch the variable, e.g., $_export_file_path, in the data viewer, and make sure that the value looks reasonable.
EDIT: Fixed calculation above. Originally it included "file:" & as a prefix before Get( DocumentsPath ), which was incorrect.
Great input above, and I'll just add this comment: For this kind of task, I typically use Get (TemporaryPath) instead of Get (DocumentsPath) unless one wishes to retain a copy every week in the server's documents directory. If, in the event I do wish to retain a copy of the exported Excel file, my preference is to store it in a container field in the database so it can be easily retrieved for review or whatever. It just depends on one's preferences and use case.
Awesome Steve, thank you!
This still doesn't work. No idea. That paths are correct, but I get a 100 "scripting error" when I do this, meaning it can't create the file (again) on the server. So far, only the hardcoded path works every time.
Hmmm....
If you are agreeable to posting a screenshot of the script, in particular, the steps most in question, we could take a look to see if we spot anything.
Other than that, it sounds like maybe you are already monitoring the value set into $_export_file_path to see how it compares to the hardcoded path which does work, but if not, I would absolutely recommend trying to do that, as well. In particular, I mean checking the value that the variable has when running on the server.
Another thought is to question the specifics of the calc that I offered above. I am wondering if I might have introduced a problem there.
In MacOS, prepending the "file:" prefix has never been necessary; all that I've ever needed was to append the relative file path after either Get( DocumentsPath ) or Get( TemporaryPath ). I don't have the familiarity with Windows deployments to speak to what works there, but part of the purpose of having the Get functions in the first place is to help smooth out differences across platforms. As such, I think it might be worth trying:
One other thing is that, with a subdirectory (scriptOutputs) in the path, either that directory needs to already exist, or the option that allows the Export Records command to create folders/sub-directories as needed must be enabled. I suspect that this is already as it should be in your case, @OliverBarrett
I would like to test this, but for some reason, I can't deactivate a script step any more.
I installed FMP20 yesterday and then uninstalled it. Not sure that was what happened, but now no matter what I try, every script step stays enabled.
Once I get this issue resolved, I'll try your suggestion and report back.
Thanks,
...just activate "use advanced tools" in preferences - that is what happened when I installed FM20 - than deactivating script steps should work again
Danke!
I just realized that and, yes, deactivating scripts again works!
What's odd is that installing FMP20 disables this setting for FMP19 also which I have running on the same machine.
Thanks Harvest!