Containers, references and automation

[reddust]

This post is about using container fields to store a reference to a file rather than storing the whole file in Filemaker and how to automate the storage process on a Mac.

I may be exceptionally dense but it has taken me a long time to come to grips with this issue and other novice programmers may be in the same situation as I was. I searched posts on this (and ancestor) sites unsuccessfully and found Filemaker literature unhelpful.

In fact the process is very simple.

I wanted to create a document storage system where a clients word documents could be accessed using Filemaker. In my case the files were created using MS Word but it might equally have been PDF files or JPEG files or whatever. I am using a Macintosh - both Tiger and Panther OS but the same principals apply in Windows.

I used a portal from within the “client” file to access letters belonging to clients. Each line in the portal represents another document concerning the client.

My problem was that whilst it was easy to create a new container reference manually

[right or control click on a container field; select the “insert file” option; check the “store only a reference to a file” box; select the file]

it was not clear how to do this automatically. Furthermore that process would only allow access from the computer where the storage was undertaken. Other network computers could not access the file.

To understand this process it is helpful to go to the “Defining File Reference” protocols. A useful section in the Help file covers this.

To insert a file automatically it is necessary to inset

1. The file name

2. The full path on the local computer

3. The network path

For example on a Macintosh, to insert a reference to a word file relating to John Smith where the file is titled “John Smith.doc“ stored in a folder “Current”

on the desktop of a user “Fred Dag” who's hard drive is entitled “Macintosh HD”

You can use a script to calculate the container field to equal:

file: John Smith.doc

filemac:/Macintosh HD/Users/Fred_Dag/Desktop/Current/ John Smith.doc

filemac:/Fred_Dag/Desktop/Current/John Smith.doc

with a carriage return inserted at the end of each line.

The first line defines the file.

The second line defines the absolute filepath using the computer on which the reference was created.

The third line will give access to any computer that can access the volume “Fred_dag” which is on the computer storing the client file.

It is not necessary for the actual Filemaker database to be stored on that computer.

I found it very helpful to create a text field, which was calculated on creation to equal the container field. This allows you to see (and understand) the format of stored references. Furthermore if you have already created references to a file (eg by the manual method above) you can add to the text reference of a related text field to add a networked machine and then use a script to recalculate the container field equal to the new text field to update the reference.

My final comment on this process is to note that by using Applescript on Macintosh computers it is possible to write a file to parse the name of a word.doc file; look up that file's client in Filemaker (check it exists; exclude duplicates etc); then create a new “child” letter record in which all the details are automatically inserted into Filemaker.

My word files are in the format lastname, firstname, year_of_birth, ddmmyy of creation eg “Doe, John64 101005.doc” referring to a John Doe born in 1964 and a letter written on October 10th.

I use applescript to parse the file name then search the Filemaker database and insert the reference to the letter. This way I can have a whole folder of recent client letters which I can automatically insert into filemaker with the click of a mouse.

I gather systems similar to Applescript are available for Windows but I have no experience in this.

If anybody is interested I can upload the applescript I use for this process.

I could have saved months of head-scratching (I am an occasional Filemaker programmer) if had understood the above. Hope it helps someone

[casiquiare]

Hi,

i' am working on this problem for 2 years, also found the applescript solution recently. My script is not finished, so i would be very interested to see your solution.

Actually, to attach files to one record, i create a folder, named like the folder ID, open it via applescript and put my files in it. But the folder get's crowded.

The ideal solution on a Mac would be to get the filepath from the Finder via ODBC. But i could not find any hint, if the Database of the Finder (probably SQLite) is accessible via ODBC.

Hope this gives some stimulating input.