Filemaker containers - AS puts data in but cant get it out

Scripting Forums AppleScript | Classic Mac OS
,
#1

Hi AppleScripters,

Thanks for all the useful info peppered throughout these list archives.

I’m stumped though on one aspect of handling Filemaker container fields in Applescript and haven’t been able to find any answers searching here or any other Filemaker info spots on the net.

I have a large serially-connected, collection of scripts interacting with several programs to manipulate the contents of a constantly growing, extremely large respository of image files. At the end of this chain, I store the images in Filemaker container fields - in addition to their being out on disk in the file system visible from the Finder, of course. It’s quite a complex workflow chain - one I’m not anxious to meddle with.

Now, I have no trouble getting my images into the container field in a FMP record. All you need is a VALID alias and a piece of AS such as:

tell application "Filmaker Pro"
	set cell 'image' of current record of document theDtbTableName to (alias theValidFilePath)
end tell

I specify above the or spec must be VALID, because Filemaker won’t allow you to add file path to a container field, if there isn’t already there on the disk. There are many reasons why - especially within the context of creating an automated workflow pipeline such as this - that you need to do just this. This validation “feature” of container fields is not documented at all. In fact, container fields are very poorly documented in general.

Back to the problem at hand. You can also get the contents of this container field out - the actual image file content out again with the following line of AS:

tell application "Filmaker Pro"
	copy cellValue of cell of current record of document theDtbTableName
end tell

Here’s where I’m stuck.

I didn’t think ahead of time to have a separate field for storing the filename and/or path of the image file I’m storing in Filemaker, but I now need to get that information out of Filemaker in order to pass the images on to other image processing programs for further action. I’d simply add such a field to the database, but the Applescript code handling files on there way through this workflow pipeline is quite complex, and I’d rather not have to go in a modify it. There’s also the issue of the 100,000s of records already in the database. I can’t easily run them back through a modified pipeline JUST to get a file path which, as I explain below, is already stored in the Filemaker database file.

The reason I didn’t orignally store this info was because I thought it was already being stored by Filemaker. This is, in fact, true. First of all, as I mentioned above, before you store a file in a container field, Filemaker first verifies the or object you give it, making certain the file actually exists before it lets you store it in the database. I think this is a major failing of the container mechanism in Filemaker. Most major Relational Database Management Systems (RDBMS) have more flexible mechanisms for storing such binary objects enabling you to store anticipatory information about a binary file you intend to create later. It doesn’t have to already exist out in the file system.

So - anyway - clearly, Filemaker was processing the or specification, and I thought this might mean it was actually storing this information, too. There was an addition reason I had for thinking this. After storing an image file in a container field, if you place that field in a Filemaker visual layout, then Filemaker will render the image content in the layout as you browse through the records in the database. If you then go out and delete the images files from disk and go back and look at the database layout, the container field now is blanked out and shows some indication the original file content no longer exists.

This clearly indicates Filemaker must store the file specification, in order for it to go out to the file system and grab the file content to render within the database layout. It also needs the spec, if it is to determine when the file has been deleted from disk.

Finally, if you use the brute force method of opening such a Filemeker database in a text editor such as BBEdit, you’ll see the actually file paths tucked in with a lot of gobblediegook, since these database files are really binary files that happen to store some of their information as ASCII text.

SO -

Does anyone know how I might access a container value’s file spec info from Applescript? As I say, the info is there, but I can’t find any verbs or objects in Applescript that can get me to it. As I mention above, Filemaker’s Applescript documentation is pretty slim, as is it’s documentation on container fields. The documentation on the combined topic of Applescripting Filemaker container fields is virtually non-existent. It took me a while just to get me where I am now - able to add data to a container field. There is ABSOLUTELY NO DOCUMENTATION ANYWHERE saying you can’t add a well-formed file alias to a container field, if the file doesn’t actually exist. That one alone took me nearly entire day to figure out, some time back.

Many thanks ahead of time for any helpful info you can offer.

Cheers,
Bill Bug

Bill Bug
Senior Analyst
Laboratory for Biological and Anatomical Informatics
Department of Neurobiology & Anatomy
Drexel U. College of Medicine
Philadelphia, PA
email: William.Bug@drexel.edu

#2

Your right, the path is in there; but I think it may be for internal use only. Because images are so large, FileMaker can just store a reference to a file and retrieve it when needed. I believe in FM6 this is the default, not sure about FM5 or earlier. Somewhat unrelated to this but interesting none the less is that if the actual image is stored, when a new image is put into the database, FM looks in its library for another image with the same dimensions. If there is one, it will compare the images bit by bit to see if they are the identical and only store the reference if that is true.
I have sent an email to someone who knows more about FM then I do. If he responds, I’ll let you know. I also wonder if another database like Cumulus or Portfolio could access the information? I really don’t see anything in AS that will work but maybe I need to put that on the long list of things I have been wrong about.

#3

Many thanks for taking the time to digest my post and offer helpful feedback, Don.

A few quick comments:

Are you sure this is true? If they were gonna do this, I would hope they’d make it a storage option in the ‘Define Fields’ tabbed dialog. For container fields - at least in FMP5 - all storage options (repeating fields, indexing, etc.) are disabled. It sounds reasonably practical if your main concern is keeping a database as small as possible, you don’t expect to be storing more than a few hundred images and don’t expect each image to get much bigger than about 100,000 pixels (approx 300 x 300 pixels - pretty teeny in these days of 4 MPixel consumer cameras). Beyond that, there are certain conditions where the time it takes to perform a pixel by pixel comparison task against all the images in your database would quickly explode to the point where the process of adding a new image would likely take many minutes even on a multi-processor G5. There are certainly computational tricks they could use to decrease the performance time by a few orders of magnitude, but if you’re trying to build a multi-million record database of totally unique images that all have the same 2D dimensions you’d definitely not want this behaviour. Still, it does sound like something that might have been implemented in the first pass at adding container fields to FMP back 5 years ago or so, back when people were very unlikely to be storing massive image repositories in FMP and storage & CPU performance issues would make it a sensible thing to be doing. If you were storing millions 2000 x 2000 images where large groups of the images - as many as 1000 - are nearly identical - as I am doing, this procedure would bring your FMP app to a screeching halt.

On a possibly related note, I have found the AS interface to FMP to be alarmingly slow. Selecting larges sets of records, even from tables having nothing but small sized text & numeric fields can take seconds to minutes! The same query run as a SELECT ODBC statement from a Windows client I’ve written runs - over the network mind you - in a second or so.

The ODBC driver & SQL engine seem very significantly more efficient than whatever FMP has put in place for interpreting AS-based queries. Is this just me, or is it a general observation?

Thank you very, very much. This is a very bothersome problem for me right now, and it would be great if there were a way to do this either in AS or some combination of AS and Filemaker Scripting.

Excellent point. You are absolutely right. Both of these products would do what I’m trying to do. Had they been a little more programmable 3 years ago when I started this project, I’d definitely had prefered them over FMP. In actual fact, gradually, over the last year, I’ve been porting to Mac OS X what was formerly a Mac OS 9-based workflow system with all transactions managed out of FMP. I much prefer using Java within Unix for this kind of job pipeline control, building on the J2EE framework running within a J2EE server such as JBoss. The database is PostgreSQL, most of our image processing apps have been Unix apps all along and the various clients (human & code alike) we need to give access to this intelligent workflow system are much more easily integrated within this framework.

That’s the happily-ever-after ending of the story, and I don’t see it coming for another 9 months or so. In the meantime, we are processing as many as 3000 images/day, so I need to figure out how to get this working in AS & FMP. If need be, I’ll go back and code in a file path field, but that would be a moderate amount of work on a code base I’m trying to retire. :frowning:

Sadly, I think you are probably correct.

Thanks again for taking the time to help me out.

Cheers,
Bill

#4

With regard to the image comparison, I checked “Using FileMaker 5.0” by Columbre and Peterson(I think that’s the right name) and that is what they say (page 74).

The speed of searches you refer to is comparing apples to oranges but you phrase it perfectly. The SQL query is using the database engine while Applescript is something put in place to interpret the query and then use the database engine. Apple events are very slow. I can’t stress this enough. It is much better to do as much within a FileMaker script and then grab the result with Applescript. I am not a great mind when it comes to this stuff but I enjoy doing it and I’m stubborn. I describe my style as throwing something against the wall until it sticks. In the process, I tend to learn more than I set out to do. Anyway, I have looked at searches from every side and this is what I have learned. Always create an unstored calculation that is Status(CurrentFoundCount) and never use an Applescript “whose” clause if you are concerned with speed.

When I wrote about Cumulus or Portfolio, I meant as ODBC compliant databases can they see and access those file paths in FileMaker and then make them available to your script? I didn’t have time to find and install Cumulus on my system today at work; so I couldn’t test that theory. It may be klunky, but it wouldn’t be the first time I temporarily used a third program as a temporary fix. Sometimes it quicker to hammer in a nail with the handle of a wrench because that is all you have.

Using HexEdit, I noticed that every path is preceded by “FPTHA>” in the file. So it is easy enough to get the paths but I wasn’t able to determine what record a path belongs to. The information has to be there. Unfortunately, the programmer at work that I always go to is off until Wednesday of next week. This is the kind of stuff that he seems to excel at. I’ll see what he says next week.

#5

Absolutely - I couldn’t agree more. It’s really good to hear another person who’s using AppleScript with FMP say it this way. Given the complexity of the workflow we are driving from AS, the code we’ve written leans heavily on the pseudo-object-based character of AS and breaks the reusuable pieces into AS library objects. Basically, each application has an AS automation wrapper library to go with it. We’ve gone to great pains to cut the AE calls within these individual libraries down to an absolute minimum. Even then, I’ve made certain to add a comment to each method citing the number of AE calls it makes. This helps you to pick the routines that best fit the task & also have the least number of AE calls. For FMP its particularly pernicious, given how one typically is passing highly “tupled” data objects in or out of the database tables. SQL and it’s cousin’s ODBC & JDBC are highly optimized with this need in mind, whereas AppleScript is supremely bad at it. Just like you, I’ve always found it’s best to do all my data manipulation either within FMP with ScriptMaker scripts or in the body of my AS code (without any nasty AE calls) - then make as few AE calls as possible passing the data between AS & FMP in the largest chunks you can afford.

I think you’ve learned a lot more about this topic than nearly anyone else I’ve talked to! That may just speak of my own lack of exposure on the topic, but I rather think you’ve pretty much plumbed the depths of what’s possible with AS & FMP, where programatic data passing is concerned.

Sorry, I misunderstood what you were saying. You make a very good point. Filemaker container fields are the only ones I’ve never tried to access or manipulate via the FMP ODBC driver. I’m not certain what kind of info I’d be able to get to. I already have custom .NET client for entering image meta data into these FMP tables. A few lines of code would tell me what I’m able to get from the Container fields. My guess is the Filemaker ODBC driver - at least the one for FMP5 - will have only minimal support for Container fields. Still, it’s definitely worth checking out.

Thanks for the great suggestion.

You got it. You can see it there, but, as you said previously, there doesn’t seem to be any documented way to get to it in AS. As you say, there’s really no obvious way to know how to associate the paths with a given record, which makes the info tantalizing, but useless. Before I’d go to the point of writing a binary import filter to the FMP database file format in order to parse out the entire record contents, I think I’d be better off simply writing some simple lines of Applescript to create ASCII descriptions of the image file directory tree contents that I could then suck into a new “image file path” field. Come to think of it, I could probably write a few lines of csh Unix shell script that would pipe the directory tree contents to an ASCII file. The “do shell script” verb added to AS in OS X has been a real goldmine! That’s probably the way I’ll go, if there’s no way to get to this file path info via AS, ODBC or Filemaker ScriptMaker scripts.

Thanks very much, again, Don for all your helpful thoughts and suggestions. You’ve really moved me ahead here significantly. I’d been throwing and throwing things at the wall, but nothing had been sticking. :wink:

Have a great weekend in the snow!

Cheers,
Bill

#6

This should work for extracting both the file name and path to file from a cell that contains a picture in FileMaker.

Hope that helps.

Scott

[This script was automatically tagged for color coded syntax by Convert Script to Markup Code]