Exporting from Runtime to FM server

[Eastern Snake-Neck]

I am currently developing a database for a reasonably large organisation. It will be deployed to users as a Runtime.

From time to time, there will be a need to export a limited amount of data from the individual Runtime solutions back to a central database run by FileMaker Server.

Is it possible to do this via the internet or a secure intranet?

Regards,

Michael.

[IdealData]

Runtimes DO NOT NETWORK - so direct transfer of data to another system cannot be done this way.

[comment]

Well, it's true that the runtime APPLICATION cannot be shared - but that's no reason why the requested scheme couldn't be implemented as described. A runtime can certainly export data to a local folder accessible from the network, or directly to a remote location on the network. And even a runtime could import these files, so this could actually work without Filemaker Server or Pro.

[Steven H. Blackwell]

there will be a need to export a limited amount of data from the individual Runtime solutions back to a central database run by FileMaker Server.

This will require some sort of client application capable of accessing the database hosted by FileMaker Server. This client application will open the file and then trigger the import of the data exported from the runtime's data file.

The runtime engine itself is not such a client. You will need FileMaker Pro or another application that can make an ODBC or JDBC connection to the hosted file. If all the runtimes can have their data exported onto a single machine, you will need only a single copy of that application to run a batch process on the various data files.

In the long run, the best vehicle for doing the import to the hosted file is a copy of FileMaker Pro. And you must presumably have at least one on hand, since you're creating runtimes.

HTH

Steven

[bcooney]

Naive question, but why not just build a web front-end. I've been involved with runtimes to a "master" and it can be a logistical nightmare.

[Eastern Snake-Neck]

Hello All,

Thank you for your replies.

I have a limited amount of experience with FM Server, and it has all been with my colleagues using FileMaker to gain access.

However, with the current project, the users will only have a Runtime solution. The 'main database' will be powered by FileMaker Server.

It seems from the replies that it may indeed be possible for me to achieve what I need to do via an ODBC or JDBC connection. And I like the idea of a web-front end.

But (and here's the bit I'm not too sure about) how do I set this up?

Ideally, I would have a button in the Runtime solutions, say 'Upload data'. What I would want to happen when a user clicked that button, is that it triggered a script that connected to the external FM Server and the FM Server then imported the specified data from the Runtime solution. In a perfect world, this would all happen in the background, so the Runtime user did not actually see the FM Server database.

I have never done anything like this before. Could someone give me a vague idea of what such a script may look like?

Regards,

Michael.

[Steven H. Blackwell]

deally, I would have a button in the Runtime solutions, say 'Upload data'. What I would want to happen when a user clicked that button, is that it triggered a script that connected to the external FM Server and the FM Server then imported the specified data from the Runtime solution. In a perfect world, this would all happen in the background, so the Runtime user did not actually see the FM Server database.

Please go back an re-read what I said in my prior post. You cannot use a runtime engine in this fashion. The engine cannot make a connection to FileMaker Server. For that you need FileMaker Pro, an ODBC or JDBC application, or possibly a web based connection. However you cannot import data to a hosted file via a web connection in any way I am aware of at least.

In terms of cost, time, complexity, and reliability, you would really be better off using FileMaker Pro client here.

Steven

[comment]

What I would want to happen when a user clicked that button, is that it triggered a script that connected to the external FM Server and the FM Server then imported the specified data from the Runtime solution.

That is the weak point in your plan:

First, there's no way a runtime can communicate directly with a hosted solution (perhaps, if you are on an Mac-only network it could be done by Applescript - I don't know for sure).

But even if you could - you cannot import from a runtime when the runtime file is open. Although you COULD import from a closed file, that would defeat your purpose of importing only SOME records (if a file is closed, ALL records will be imported), and the entire scheme would be flimsy.

Note also that the server application cannot import at all - you need to have a client station with Filemaker Pro installed for this.

You can still EXPORT the data from the runtimes and have the "main" file import it, either periodically, or by some other form of alert (e.g. an Applescript attached to a folder action, assuming they are all on the same network).

you cannot import data to a hosted file via a web connection in any way I am aware of at least.

You can import XML data from anywhere on the web.

[Fenton]

bcooney's idea of just building a web interface, via IWP or custom web publishing, solves the synchronization problem. But it depends on the complexity of your application whether that would be be the best solution. As she says, synchronization of master and slaves is kind of a pain.

Here's some other ideas, based on something I'm working on, which is quite different, but the methods may be adaptable.

First, in your case I would want a single dedicated Mac to run FileMaker Pro client. It could possibly be Windows, but then you do not have AppleScript Folder Actions, which would be useful in this case. But basically you just need some method to watch for new files in the folder; so it could be any capable scheduling tool, possibly even a FileMaker script running a continuous Loop (be sure you can safely stop it however).

When the FileMaker client sees a new file in the folder, either being told by the Folder Action, or by looking at the folder via one of the above loops, it would import it, then move it (via AppleScript or other tool). Remember, this is a full FileMaker Pro client, running the hosted file, so it would be importing into the hosted file.

The method used to transfer files to this machine from the others would be FTP. This allows other machines to transfer files safely, without requiring OS Sharing on that machine. You just need 1 folder to allow FTP access (I didn't do that part of it, but any Mac IT guy would know how).

Macs can FTP directly, using AppleScript and command line tools. On Windows there is the free MooPlug, which can FTP. In our case we are using the 360Works FTPeek plug-in, which is cross-platform, full-featured and has a very reasonable site license price.

Frankly, I don't know if this is a good idea. But I don't see why it wouldn't work.

In our case the clients had FileMaker Pro, and we are importing into FileMaker via FTP, just to get the file list (PDF files mostly).

[bcooney]

Another idea, especially if "off-line" data entry is required, would be SyncDek. WorldSync

[Eastern Snake-Neck]

Thank you again for your replies.

Steven, my apologies for misreading your original post - it was dawn here on Sunday morning when I read it, so I wasn't at my cognitive best!

comment, I like your idea of using Applescript, that is worth exploring further.

Fenton, your ideas have got me thinking as well.

And finally, thank you bcooney. I'll check out SyncDek.

Regards,

Michael.

[comment]

You never said this data needed to be synchronized. If it does, then going about it in this way would be an exceptionally bad idea, IMHO. This could work, in a limited fashion, if each user is exclusively in charge of maintaining their own set of data, with the central database serving as a browsing repository.

[Eastern Snake-Neck]

Hi comment,

No, the data does not need to be syncronised.

Each user is in charge of their own data. From time to time, some of their data needs to be transferred to a central database.

Regards,

Michael.

[bcooney]

No, the data does not need to be syncronised.

Oh, forget about SyncDek. I jumped to conclusions.

[Fenton]

I guess it's my fault, for using the "sync" word. It's only a "1-way sync", or update.

The AppleScript method you'd want to look at is Folder Actions. Here's a link to some Apple documentation on it. You can download the entire AppleScript language guide as a PDF (upper left corner of page).

http://tinyurl.com/5s2yrs

It's a little difficult to upload an example of a folder action, because it is in 2 pieces. One of which is the AppleScript itself, which begins with a specific folder action handler (see examples). This AppleScript can be in either the OS Folder Actions folder, or your User Folder Actions folder; probably use the former for what you're doing (maybe they can be anywhere, but these are the 2 default locations).

The other part of Folder Actions is to attach one of the above AppleScripts to a folder. If you right-click (control-click) on a folder, at the bottom of the drop-down menu you'll see such things as: Attach Folder Action..., Configure Folder Actions... (which shows you your FA scripts, and can open its folder in the Finder).

Once you attach an FA to a folder, it will run whenever an item is added to the folder (or whatever hander trigger action you specify), then run the rest of whatever else you write after that. This is what the handler trigger and basic "repeat with item" looks like for handling items added to a folder:

on adding folder items to this_folder after receiving added_items

	try

		repeat with i in added_items

bunch of AppleScript here to do something with each item

		end repeat

	end try

end adding folder items to

It is a good idea to move the items somewhere else, outside this folder (which is usually what you want to do anyway). Otherwise it is possible to get yourself into a recursive loop, which you don't want. There are (complex) ways to avoid that; but better to avoid entirely.



Here is a more complex script, to show what can be done. I used this to parse the file name of the dropped item(s), into sections, using "_" as the delimiter (client ID, case ID, doc type). It then moves the items, creating subfolders as needed for a hierarchy. This is fairly easy to do with the "mkdir -p" Unix command; so the final file path has been converted/built as a Unix path.

on adding folder items to this_folder after receiving added_items

	set lib_folder to path to library folder from local domain as text

	set clients_folder to lib_folder & "WebServer:Documents:scan:sorted:"

	set unix_clients to POSIX path of clients_folder

	try

		repeat with i in added_items

			set unix_file to quoted form of POSIX path of i

			tell application "Finder" to set file_name to name of i

			set sections to my parse_name(file_name)

			set section_count to count sections

			set client_folder to item 1 of sections

			if section_count > 2 then

				set case_folder to item 2 of sections

				if section_count > 3 then

					set doc_folder to item 3 of sections

				end if

			end if

			if section_count > 3 then

				set unix_path to quoted form of (unix_clients & client_folder & "/" & case_folder & "/" & doc_folder & "/")

			else

				if section_count > 2 then

					set unix_path to quoted form of (unix_clients & client_folder & "/" & case_folder & "/")

				else

					set unix_path to quoted form of (unix_clients & client_folder & "/")

				end if

			end if

			do shell script "mkdir -p " & unix_path & ";mv " & unix_file & " " & unix_path

		end repeat

	end try

end adding folder items to



on parse_name(file_name)

	set d to AppleScript's text item delimiters

	set AppleScript's text item delimiters to "_"

	set pieces to text items of file_name

	set AppleScript's text item delimiters to d

	return pieces

end parse_name

[comment]

Another option is to mail the exported files and set up Mail rules to handle them (some Applescripting is still necessary).

[Eastern Snake-Neck]

Thank you all for your comments, thoughts and replies. Your help is very much appreciated.

Regards,

Michael.

[Fenton]

Comment's email method is probably the easiest, as it solves the file transfer method on its own. If I was doing this, my biggest concern would be how to find out when (if) the method did not follow thru completely, if the Mail rule or Folder Action did not run.

In the case of a Mail rule, I think you'd want to move the email into a mailbox (out of the Inbox) at the end, and move the file attachment out of whatever folder you "saved" it into before importing; in the case of a Folder Action, move the file. So there would be some way to check (likely with another AppleScript, run as a diagnostic) to see if some files did not get imported into FileMaker. They would be the emails still in the Inbox, the files still sitting in the 1st location. The user is going to expect that they were imported, and would have no feedback as to whether it worked.

Yes, it's all kind of a PITA, and not 100% reliable. But that's the price paid for being too cheap to buy real copies of FileMaker. I can understand going thru all this for other file manipulation reasons. But you would not need ANY of this if they had FileMaker clients, or a IWP interface to the hosted file.