Export records, save one at a time?

[CamMac2]

I need to find a way to export records, one at a time, saving each record with a different filename, automating the process with a script.

What I have written so far stops with the dialog box waiting for the filename. I am generating filenames within a special field - I want to copy the contents of this field and paste into the dialog box (automatically) and (automatically) hit return, and continue on with the script.

(The actual problem: I need to export certain records once a month to an accounting database(proprietary). The database programmers have written an "application program interface" (API) that will always scan a certain directory. When the API sees a file named "filename.in", it runs a validation to check the file for errors, then adds the contents to its database. It then sends back the file as "filename.out" that will contain additional info that I will need to import back into Filemaker as "update matching records..." Have not even started that end of my scripting)

Client won't go with ODBC, because he says he will lose the verification/error checking capabilities of his API.

I have seen posts that hint at what I need to do, using Troi or macros, but haven't figured it out yet.

I have looked at Troi - but don't see a function that will name a file with my individual names (I see "createfile" - creates blank file AND I see "setcontents" to change the contents of an existing file, but I don't see how to apply this to my need)

Thanks greatly in advance for any thought or ideas!

[CobaltSky]

Exporting records one at a time will not be too difficult, since FileMaker exports only the found set. So if you set up a script to find individual records (whether via a scripted find or by an Omit/Show Omitted sequence within a loop) you will be able to isolate each record and export it separately.

However dynamically allocating a different file name to each exported record is a challenge that is going to require a third-party plug-in, as it's not a feature that is supported by any of the current versions of FMP.

There are a number of plug-ins available that will allow you to rename files. How you would go about creating export files with names taken from a field in the database is to export the files to a default filename/path, then have the script change the name to the serired name beore the next export occurs.

Of the various plug-ins that can be configured to do this, two of the better known are DialogMagic from New Millennium Communications, and Troi File from Troi Automatisering:

DialogMagic - New Millennium Communications:

http://www.nmci.com

Troi File - Troi Automatisering:

http://www.troi.com/software/fileplugin.html

Both provide free downloadable demos, but it sounds like you are already evaluating the Troi offering. In the example files provided with both the above plug-ins, you will find demos of the proces of renaming a file, as ooutlined above.

[Fenton]

I just did something like this. It works quite well. You'll need to set up some calculations for the folders and file paths. You can do this from the current location of the database file, using Status(CurrentFilePath). It needs to be modified somewhat however, to match the operating systems syntax.

Compare the Status(CurrentFilePath) to what you get when choosing a file with Troi and you'll see what has to be changed.

The reverse of what Ray said is also doable. You can import many files, by temporarily change each file's name to a "fixed" name; then change it back, or better yet, move the file to a Done folder. Troi does all this. It's quite fast, 'cause moving a file is basically just changing its name.

You can get a list of files in a folder, and do them 1 at a time. Here's an example of a filepath calculation, good for both platforms (not as thoroughly tested on Windows, but seems to work):

Case( Abs(Status(CurrentPlatform)) = 1, <-- for Mac

Substitute(Substitute(Left(Status(CurrentFilePath), Position(Status(CurrentFilePath), "/", 1, PatternCount(Status(CurrentFilePath), "/"))), "/", ":"),

"file::", "") & "Imports:FPS_Attachments:Tech_Import.fp5", <-- for Mac

Substitute(Substitute(Left(Status(CurrentFilePath), Position(Status(CurrentFilePath), "/", 1, PatternCount(Status(CurrentFilePath), "/"))), "/", ""),

"file:", "C:") & "ImportsFPS_AttachmentsTech_Import.fp5"

)

[CamMac2]

Thank you - that puts me on a better track than where I was starting...

I also have to export null values - the API is expecting 40 fields, but only 19 are in my database. The programmer says "send a null value". Any way to do this upon export other than by creating 21 fields named null_01 through null_21?

Thanks!

[CobaltSky]

The answer depends on the format of the exported files you will be creating (eg tab delimited, csv, etc). However assuming it is a text file, one option would be to have Troi File append the 21 null field values to each record after it is exported.

This could be done with the:

External("TrFile-AppendContents", "...text-to-append-here..." )

function.

If the file structure is such that the null values all come at the end of the record, essentially you would need to append 21 additional separators to the end of the single record export file.

If the null values are required to be distributed throughout/amongst the other 19 values then you will require a slightly more complex procedure. One possibility would be to have a script concatenate the fields, inserting a place-holder character wherever a null is to appear, and place the concatenated string into a global field. Then export the global on its own. Finally, run the Troi File function:

External("TrFile-Substitute", "-IgnoreCase |PathToFile||PlaceHolder|DelimiterDelimiter" )

to replace all occurrences of the place-holder character with a pair of delimiters. The resulting file will then be 'seen' as containing the requisite null values by the API on upload.

[CamMac2]

Thanks Ray and Fenton - The nulls are scattered, so I'll try the second solution. I've gotten a lot of this working, using plugins - but I came across a "what should have been obvious, but wasn't" problem. Ray mentioned it before:

>Exporting records one at a time will not be too difficult, since FileMaker exports only the found set. So if you set up a script to find individual records (whether via a scripted find or by an Omit/Show Omitted sequence within a loop) you will be able to isolate each record and export it separately. <

I made a script to loop through the found set, exporting at each record with a unique filename, but what I missed was Each exported file has the complete found set as its contents! duh.

I tried adding an omit show omitted, but I end up losing my original found set, and show omitted shows entire database. What else am I missing?

Thanks!

[CobaltSky]

Hi,

I'd say that what else you're missing is a sequence of steps to save the found set prior to the loop sequence in your script, and a counterpart procedure to reinstate the found set after the loop has run its course.

Also, assuming that you only want to export the records that are in the found set to begin with, you'll need an additional procedure to ensure that you target the appropriate group of records (since with the first 'sho all records' step, the found set will be lost).

There are a number of ways to go about it, but the most common technique is to create a list layout which has only a recordID field on it, then have your script switch momentarily to that layout, perform a 'Copy All Records' step, then return and paste into a global field. You will this have captured a list of all the recordIDs of the records in the found set.

With the above in place, if you create a relationship from the global to the recordID field, a Go To Related Record [show] (GTRR) step will reinstate the found set whenever you wish.

I'd suggest that you consider a similar technique to locate each of the records int the found set in turn. Ie create a separate global text field and a counter (global number field). Set up your script to parse out the Nth recordID (based on the number in the counter field) from the captured found set list and place it in the global text field. Then use a second relationship and a GTRR to isolate the found records one at a time, incrementing the counter field each time you pass through the loop.

With this technique, the values in the saved set field will remain unchanged, so when the loop has worked its way through all of them, reinstating the found set will be a readily available option.

That's it in a nutshell (and it's already getting to be a sizeable post!). Give a shout if you get stuck.

[CamMac2]

I found this tip on the Troi site -

Copying Record data: You can copy and paste the contents of an entire record by choosing Copy (Mac: command-C or PC: ctrl-C) with the record selected. When you paste what you've copied into another program, you get a tab-delimited text containing all of the data displayed in the current layout for that particular record.

You can also copy data from more records, not just the one you're viewing. Hold down the option key (Mac) or Shift key (PC) while choosing Copy (or press command-option-C (Mac) or ctrl-shift-C (PC)). This copies the data from all the currently found records. If you paste what you've copied into a word processor you get tab-delimited text. And if you paste the copied data into Excel, the data instantly transforms into a spreadsheet.

Hmmm. I'm actually trying to get the contents of one record at a time into a tab-delimited file. Maybe I'm not looking to export at all, but maybe just create a layout that has all my fields in order, and use a plugin to put that info into the file. Hmmm...

[Fitch]

If you're going to use Troi File, the answer is: don't export! Instead, use TrFile-CreateFile and TrFile-AppendContents. That way you'll have total control and can easily loop through each record and create a file for that one record.