Dynamic file import

[Dagel]

I have two database files: EmailOrders.fp5 and Assembly.fp5. I'm using the plug-in SMTPit to download an order (e-mail attachment) to my hard drive. Each new attachment is named sequentially upon arrival (Order1.txt, Order2.txt, etc). That name is captured in a field within EmailOrders.

I'd like to automate the process of importing these tab-delimited attachments into the Assembly.fp5 database.

I thought I could calculate the full path name in EmailOrders, copy it to the clipboard, open the Assembly file, create a new record, paste into an ImportPath field and run an import script. No such luck!

I just looked at the Troi File plug-in but from what I can tell, it still requires the manual selection (through a dialog) of the file to import.

Thanks for any ideas!

[Fenton]

I assume that you're on Windows, since you don't mention AppleScript. Troi File plug-in doesn't require a dialog. You can specify the filepath in its External function.

The basic problem with Import into FileMaker is that once you set up the import, the file name and location is fixed; it cannot be changed with any kind of script (that I know of). The solution is to use a tool like Troi File or AppleScript to rename the file you want TO the fixed name, import it, then rename/move it. Then continue with the next file. Either tool can list the name of all files in a folder, to give you the list to loop through. Maybe you know all this.

There is an example in the Troi File FileManipulation file, the Move files tab, Move a File and Rename; because moving or renaming a file are practically the same thing.

External("TrFile-MoveFile", gTheFileSpec & "|" & gDestFileSpec)

(This is from an older version of the plug-in, the newest version may be slightly different.)

[Dagel]

Fenton-

I am actually doing this on a Mac and the Applescript idea sounds like the way to go. I'll just also need to copy the file into an archive folder before renaming in case something goes awry.

Thanks so much for the great suggestion!

[Fenton]

You could duplicate each file and move & rename it in one step with the duplicate command, using the full path of the "fixed name file" as the "to" location. If you use "with replacing" the operation will simply replace the fixed import file each time.

But I thought of another way which may be faster. AppleScript can read text files directly. It can also set or create a FileMaker record to tab-delimited text, using the prefix "data". You just need a layout with the fields, and only the fields, in the correct order on the layout (top-left to bottom right).

Replace the choose folder line with your folder.

set theFolder to (choose folder with prompt "Select the folder")

set fileList to list files theFolder

tell application "Finder"

	repeat with theFile in fileList

		if file type of theFile is "TEXT" then

			try

				set mytext to read theFile

			on error

				beep

			end try

			

			tell application "FileMaker Pro"

				tell layout "Text" of database 1

					create new record with data mytext

				end tell

			end tell

		end if

	end repeat

end tell

[Dagel]

You've truly gone above and beyond the call of duty! Since I already had my import database pre-defined, I decided to loop an AppleScript which recognizes any new attachments, copies the text attachment, renames to the default import file name and moves the file into an import folder. Your suggestion really helped.

Thanks much!