dynamic file references to FM files

[michael_w]

Hi - I'm trying to setup a scripty that allows me to import data from another FM DB, but I want to be able to specifcy the file name/reference dynamically, i.e. I'd like to store the file name/ref in a field.

I have searched various forums and found many suggestions for importing images, text files, etc, but none relating to importing another FM DB. I have played around with my FM7 but so far no luck. For example, I tried to use XML import by specifiying the file name in the calculation field but it keeps saying it couldn't open the file "file:foo.xml".

Cheers, Michael

[Fenton]

I have not seen anyone trying to do this particular thing. It is not common. I can see some special circumstances where it would happen, importing various files sent in by remote FileMaker runtime applications.

Assuming this is necessary and not caused by inadequate design, I see 2 alternatives. The first would be to treat the FileMaker files like any other files in a folder. You would use AppleScript (or a plug-in, or command line) to rename each file in succession, to the fixed name specified in the Import step of the master file. Then Import. Then rename each to its original name, and optionally move it to a "Done" folder. There are examples of this here in fmforums (for other kinds of files, it's the same routine), or I can attach one.

[upon further reading I see that you just want to import 1 file, but specify the name. In that case, use the method above, but just do 1 file. If the file is not going to even be in a fixed folder location, then AppleScript (etc.) can move/duplicate it to the fixed location as it renames it. If it duplicates it you'd want to delete it. Or duplicate it to the "temporary items" folder, where it will eventually be removed automatically (not just put in the trash). Which is easier/faster depends partly on the size of the file. But unless it's huge, duplicating to the temp items is easiest.]

This would assume (as would most methods) that the Import order is exactly the same for each file (preferably that the files were all clones from an original file), so that the fields would automatically line up for each import. The process would be run from the Master file.

[Actually AppleScript could run the external scripts in each file, from the Master file. It can specify a file by name, open it, run its script, and close it. All it needs is a list of the files. But this seems trickier than just renaming the files.]

The 2nd method would be export from each file, to a fixed tabbed text or xml file. Then call a script in the Master file to import that. This would not require AppleScript or a plug-in; but it would have to be triggered from each of the files.

You cannot just "open" an xml file. You need an xsl file written to translate the xml into a form FileMaker can match to its fields. It is safer than importing tabbed text, as it can match fields even if the data is not in the same order (I think, never mixed one up to test). But I would think that tabbed text would work fine in your situation.

[michael_w]

Thanks Fenton - I guess the AppleScript/renaming files is the easiest option, though not very elegant.

Maybe you're right and my design could be better. Here's why I think I need the the dyanmic file ref:

I have a created a DB accounting solution. All tables are stored in one single FM file. I have multiple users, each maintaining their own individual data, BUT each user also has an FM DB from an old accounting solution and I need to import the balances and last recorded conversion rates etc. (because the data contains accounts in multiple currencies and also non-cash accounts like shares and funds).

When I create an update to my solution the user can 'upgrade' by exporting all data using provided FM scripts and then opening the new version (blank) and importing the data using provided FM scripts again. However, each user needs to adapt the file ref that points to the last balances from the previous solution. This step needs to done only once and therefore I did not want to create a relationship for this (for which I would still need a dynamic file ref for the various users). If I could store the file ref in a field, I could design a layout with a file ref field and an update button and the user would no loner have to change the file ref directly, which could be dangerous...

Any idea how I could do this in a better way?

Cheers, Michael

[Fenton]

That's kind of a sticky situation. If it is only a one-shot deal it seems the easiest would be to set up the fixed-file FileMaker Import and have AppleScript rename the old file to the fixed name. For AppleScript to know which file to rename you'd want them to put both files in the same folder (easiest) or something; with no other FileMaker files in the folder (it could look at the extension and ignore other types of files).

Are the earlier files are pre-7? If so then you'd be exporting a text file from the old file then importing. But that implies that the export is already in place in the old file; in which case you should know what the exported file is named? Also, is it only 1 old file, or is several related files?

It's also suspicious when you say "adapt the file ref that points to the last balances from the previous solution". Does this mean that they are creating multiple files on their own, only the last of which is the "latest"?

[michael_w]

Sticky indeed

Anyway, the old balance files have been exported by FMP7, i.e. I can import real FM files. It's a single file and not related to any table. I do know what the exported file is called, but the caller will have them in different locations. As it is an accounting solution, all files for one accounting each year are kept in its own folder. The typical name for a folder is '<user> <year>'. This was done to allow multiple users sharing the same computer to maintain their own DB - under OS9 we had no real separation of user areas, which is why the user name ended up in the folder name. I said 'latest' as a user has potentially other exported files from earlier years, but I don't need to worry about them as I only need to import the latest one.

Cheers, Michael.

[Fenton]

One of the drawbacks of using FileMaker like another application; ie., separate files for such reasons, rather than as a real relational database is that you lose control over exactly where everything is. It requires user intervention for updates, because you have to know exactly where/what the old file is.

I would include an empty "Import" folder in the folder with the new version of the FileMaker database, and tell them they must go find their previous file and put it in that folder. Then it doesn't matter what the exact name of the file is. AppleScript can just rename whatever FileMaker file they put into the folder, then FileMaker can Import it. You could test that it was a FileMaker file, and that there was only 1.

The field order would have to be the same. Hopefully neither of you has deleted any fields in that order. Otherwise I don't know if it'll match. Matching names is pretty smart, but I don't know if it can compensate for deleted fields in a saved Import order. Never tried that.

[michael_w]

Thanks very much for help Fenton

I have decided to use an AppleScript - but I have a few problems getting this to work in FM...

How can I use a working AppleScript work in FM?

Cheers,

Michael