Thought I'd seen it all

[RodSierra]

Well I thought I'd seen about everyway to incorrectly use a database program,but this latest one really threw me.

Customers been using FM5 for years, says he's doing all his invoicing on it, so should not be a problem for us to get him started and maintain his costing history. I was looking forward to a conversion without having to piece together info from 75 different Excel sheets. But then I get the data. I noticed it was one fp5 file in the folder, and an accounts folder, but during the initial copy it stated 17,000 files being copied. Started getting nervous. Seems that their Invoice data base had only two records in it, no line items, just repeating fields instead for the standard line item fields. Seems they would hand enter the info into this so called invoice data base, then save the file with one record, naming it the invoice number. The accounts folder contains 17,000 one record database files neatly sorted in folders by customer name, with the file name being the invoice number only. So here's my strategy, will put all these numbered files in one folder, do a mass conversion, not sure if FM11 will choke on a 17k file conversion, but we will see, then setup an import loop script using the numbers as the file references, trapping for errors on ones that are not there, then sort this whole mess out once that is imported. So what's your take on an import strategy, any other ideas?

[Vaughan]

Have you considered doing the data wrangling in FMP 6, so you end up with less files to convert to the newer version.

[RodSierra]

That's a plan if I run into trouble getting a large quantity of files to convert easily. It's been so long since I messed with FM6 or earlier, not sure how slow things would be, or what features I'm used to would not be available. Once this mess is in one table, it won't take too long to sort out, but getting it there the quickest way has me second guessing my import approach.

[IdealData]

You might think that's bad - but consider this one...

I actually got into FM programming 15 years ago because the guy who had the job I was applying for was deleting each work record after printing it. It only got discovered when the work shop wanted a repeat copy as they'd lost the first issue. Of course no repeat could be issued as the record had been deleted - his excuse was that they couldn't possibly keep every record as they didn't have enough disk space, so his resolution was to have an empty DB!!!

[comment]

Applescript?

[Fenton]

This AppleScript will get you the full Mac file paths of the files in a chosen folder, in a return-separated list. It puts them on the clipboard, so you could paste them into a FileMaker global field.

set theFolder to choose folder

tell application "Finder"

set theFiles to (files of theFolder whose file type is "FMP7") as alias list

set old_delims to AppleScript's text item delimiters

set AppleScript's text item delimiters to ASCII character 13

set theFiles to theFiles as Unicode text

set AppleScript's text item delimiters to old_delims

set the clipboard to theFiles

end tell

Then, in FileMaker, you could use a Loop, an incrementing counter and GetValue (global field; $counter) to pull them out one at a time. Using Substitute ($mac_path; ":"; "/"); then adding a prefix ("filemac:/") would make each an absolute FileMaker syntax path, for Import. You can break the repeating fields into records during Import (as you likely know).

[P.S. If FileMaker has trouble converting the many files, it would be possible to use the above AppleScript to get the "FMP5" files list. Then, after coercing the path to a FileMaker syntax path (as above), running a Loop script to Convert each file, one at a time. "Convert" is a script step (in the Files section); never used it, but it's there.]