Capture File Name during Import

[LaRetta]

I need to stop a User from importing the same file name twice. This file name is defined by the transaction company and is unique. Is there a way to 1) Capture the file name in a table (as a record) during an import and 2) check against this table and refuse to allow an import of that file name (into another table) if it already exists?

[Fenton]

I know a couple (free) ways to do this on Mac. But I don't know how to bring up a "choose file" dialog on Windows, without using a plug-in (Troi File is good; there are others cheaper). It is likely possible, using VB, but I don't know it.

So, here's a (clunky) work-around. Create a container field. Use the Insert File, with [x] As reference only. That'll let 'em pick the file. The filepath is then available as the last line of GetAsText (container). You could then parse out the name, and compare.

If everything's cool, you set the file path into a script Variable, then continue with the real Import.

The main drawbacks that I can see are: 1. It says Insert File at the top of the dialog, and 2. The [x] As reference only is in the dialog; they could change it! I imagine you could start with a dialog saying "Don't touch the checkbox!" :-|

It would be nice if you could "lock" that choice with the script step.

It would be even nicer if FileMaker would give us a plain "choose file" dialog, that just returned the filepath (any file), but did nothing else.

Of course, there's also the work-around of importing into a dedicated table, checking the data afterwards, with relational key(s), then moving the data over if it's new.

Or using Import with Matching records on a unique key, after showing all records. But that's both slow, and it could overwrite existing changes; since there's no option to "Import matching, but don't change matching records."

Confusing.

[Mikhail Edoshin]

The Insert File workaround is quite good and it works under Windows as well. Also, you can check whether the user cleared the "Reference Only" checkbox. If he did, then the result of GetAsText() will be not a file reference, but a file name. A file reference starts with "file:" and typically has two lines: one with "file:" and another with platform-specific "filemac:" or "filewin:"; a file name is simply a file name.

[LaRetta]

Hi Fenton,

Or using Import with Matching records on a unique key, after showing all records. But that's both slow, and it could overwrite existing changes; since there's no option to "Import matching, but don't change matching records."

Yep, neither import option (Add New Records or Update/Add New) would fit the bill in this case. It's a prior post from here and Users found a way to break it (import used Update/Add to protect from double-pulls but then the ONE wonky 'header' record wasn't always the first record any more because older records appeared in the import set) and I had no field I could sort on (after import) to guarantee the wonky record was always the first (which needed to set some fields and then be deleted). They also asked for a way to know if a file was already pulled - they were afraid they'd miss one.

Your container method worked wonderfully! I've created file names using variables but never a file reference before. Very cool! I can now also test files being imported and stop an import if an invalid file is selected. I was so excited about this that I ended up adding this functionality in five more places in my solution!

Hi Mikhail,

That checkbox concerned me. I test for file name (only) vs. full path with ValueCount = 2 (if true, only reference stored). Does this sound safe or will there be times that a file reference will have more (or less) values? I dislike surprises. I suppose I could look for ":" or even "/", assuming they are illegal in a file name but I dislike assumptions as well; looking for LeftWords ("file"; 1) alone would scare me. I'm from old DOS school; it still strikes me as strange to see front-slash in paths.

Thank you both; you've gotten me through another head-scratcher!

LaRetta

[Fenton]

I believe there's always 2 lines from Insert File, As reference only. If the file's on a server the 1st line is only file:file name; but it's still 2 lines. But you could use ValueCount ( GetAsText (container) ) <2 to test.

Another point; we're really only using the container to capture the file path; it should be a global field, for no other reason that there's only 1 and it's easier to clear; which it probably should be cleared as soon as you get the file path into a Variable.

So if some doyo unchecks the box and embeds a 500 MB file, you can clear it immediately.

[LaRetta]

Ah, I was using a standard container, Fenton. I liked the idea so much that I wanted to store the reference in case someone wanted to see the original file (I created table to store one record per file name). However, it could be downloaded from web from a few different systems and if they move the file, it'll break anyway. I think it's time that we determine set locations for some of these files. Could we set up one default location (possibly on our network server) where Users can't rename the directories?

Currently, if script recognizes a file only (in that container), it deletes the record but I see your point about a 500MB file. I'll be working with our Product pictures soon as well and I need to standardize where these files are stored. Thanks for your help; I'll get to work studying graphics storage issues as well.

L

[Fenton]

If you standardize the locations of the Product images, so that the database can calculate where they are, you won't have to Insert them. You can just calculate the path to them, with a container result, and they will show.

If you change the server, or other minor change to the file path later, you can redo the calculation and the pictures will still show; which they won't if they're inserted or imported as reference.

You might still want to Insert as embedded some thumbnails. They will of course remain the same if you change servers (they'll also remain the same if you change the original however).

Most decent image editor apps will let you do "batch" thumbnail production. Heck, even FileMaker can do it with Import Folder (and you don't have to import the full-size Image). You do of course have to know which record they go with; this may be possible using the file name and Import wMatching.

[LaRetta]

I'm on a roll and, now that I have ThumbFakeImages, I'm having a blast as well! I LIKE working with files and now I want to standardize every file and graphic in our business.

UPDATE: I already have a text field (in our Preferences table) in which the Owner can specify where shipment/posting pdf's are stored. It should be easy then for him to modify this text field for storing everything we need.

Edited December 17, 2006 by Guest
Added Update

[Brudderman]

So, here's a (clunky) work-around. Create a container field. Use the Insert File, with [x] As reference only. That'll let 'em pick the file.

This is what I have been doing, but I do have a question. I don't see a way to specify a file filter (e.g., "*.qif" if I want to see only files with QIF extensions in the Insert File dialog.

Is there a way to do this? No big deal, but just a bit cleaner.

James

[Fenton]

Well, ThumbFakeImages was an example of the "easiest" way to do something; as the 1st poster was a beginner. It is not necessarily the way you'd want to enter an entire product catalog. There was 1 and only 1 image, no proper thumbnail.

The disadvantage is the every image in the portal is full size; it just looks smaller. FileMaker has to render the full-size image then shrink it in order to show that. Not fast; especially if you had large images, extra-especially if you used them in a List view. Also, the full-size images are embedded, which increases the file size dramatically. But it's easy to build, and the images will not disappear if you move the file or change the network.

In other words, each solution's use of graphics with FileMaker has its own environment and requirements, and the best overall method has to take those into consideration.

Standarizing the image type, quality and location of the image files is always good. If you need to create a big folder structure for them, you should look into one of the file plug-ins, Troi File, or Productive Computing has a new (cheaper) one. Create folders from FileMaker :-! Or use Windows command line; but you may need Wim or Genx for that :-]

[Fenton]

James, the only place I've seen this kind of "filter" ability for a file dialog (for Windows) is in the Troi File plug-in. Possibly it's also possible with command line. It's available in AppleScript, though it's tricky, because files on Macs may or may not have extensions, may or may not have a File Type; so you have to check both.

Edited December 18, 2006 by Guest

[Brudderman]

Thanks, Fenton!